Setting up MySQL

Whether you use Windows or Linux, there are many instruction guides available for installing MySQL. On almost every modern Linux distribution, you’ll find MySQL included by default or readily available for installation as a package. Some of the Linux OS installation routines even include the steps to initialize the database service and run it by default on start-up. There is an enormous amount of information that goes into working with relational databases like MySQL, SQL language syntax and commands, and other aspects of database operations well beyond the scope of these instructions or what is needed to work with Snort. The intent of this task is to get MySQL installed and minimally configured so thatyou can use it to store Snort to log output in the database. Note: As of Snort v2.9.3, direct output logging to a database such as MySQL was been deprecated from the tool, so Snort output is first directed to another location or tool (such as Barnyard2 or other tools that read Snort’s unified2 output format) and then the output handler uses the database to store the log and alert information.

Because the purpose of this activity is not to become expert with MySQL, and because you have plenty of opportunity to install Snort, BASE, or programs from source, we’ll assume for this task that you will be installing MySQL on Linux using either a default MySQL instance that came with your distribution or installing MySQL using the package manager. You will find the official installation guides for multiple operating systems in Chapter 2 of the online MySQL reference manual at http://dev.mysql.com/doc/refman/5.5/en/installing.html. The only choice that leaves you with is what version to download and install. The current stable release is MySQL Community Server v5.7.17, which can be downloaded from http://dev.mysql.com/downloads/mysql/. Most Linux distributions do not include this latest release in their packages, but the 5.5 version available in the package repositories of many Linux distributions (including Ubuntu 14.04) is perfectly suitable for logging Snort alerts.

Once you have MySQL installed and started (if you are not running it as a service, you will need to navigate to the /usr/bin subdirectory or wherever else you have MySQL installed and use the mysqld command from the command line), you need to log in to MySQL to make preparations to use it with Snort. The primary tasks are to create the Snort database (where the log entries will be written) and to create a MySQL database user account for Snort. Remember that MySQL commands need to have a semicolon at the end.

  1. Open a command shell by searching for and selecting Terminal from the Dash Home in the Ubuntu desktop.
  2. Navigate to the directory where MySQL is installed – the typical Linux default is /usr/bin/: $ cd /usr/bin
  3. Run the MySQL client, logging in as root: $ mysql -u root -p
  4. Enter the password at the prompt that follows. If you are successful, you will see the MySQL command line prompt: mysql>
  5. Create the database for Snort: mysql> CREATE DATABASE snort;
  6. Create a new user for Snort: mysql> CREATE USER snort@localhost;
  7. Create a password for the Snort user account (feel free to use something more secure when you do it yourself): mysql> SET PASSWORD for snort@localhost=PASSWORD(‘snortpass’);
  8. Assign access rights to the Snort user account: mysql> GRANT INSERT, SELECT on root.* to snort@localhost;
  9. Assign access rights to the Snort user account: mysql> GRANT CREATE, INSERT, SELECT, DELETE, UPDATE on snort.* to snort@localhost;
  10. Make sure you keep track of the Snort username, password, and database name, because you’ll need this information for the barnyard2.conf file.
  11. Log out of MySQL using the exit command.

The last step on the MySQL side is to create the database tables Snort will use for logging. Prior to Snort v2.9.3, Sourcefire included a script with the Snort source file package to create the tables, but the schema creation script is now distributed with Barnyard2. There is a subdirectory called “schemas” created as part of unpacking the Barnyard2 tarball, and the “create_mysql” file in the schemas directory is essentially a listing of all the SQL commands needed to create the tables in the Snort database. Using the “<” character, we can tell MySQL to load this text file and run the commands contained in it. So, to create the Snort tables:

  1. Locate the schema creation file. It should be in the temporary installation directory used in the previous step (Installing Tools from Source), such as /usr/src/snorttemp/barnyard2-2-1.13/schemas. Note the full path to the file.
  2. Switch to the directory where MySQL is installed: $ cd /usr/bin
  3. Run the command to create the tables: $ mysql -D snort -u root -p < /usr/src/snorttemp/barnyard2-2-1.13/schemas/create_mysql
  4. Enter the MySQL root password when prompted.
  5. Now if you log in to MySQL you can look at the tables to check that everything worked.
  6. $ mysql -u root -p (Enter the root password when prompted)
  7. mysql> use snort;
  8. mysql> show tables;
  9. exit

Now MySQL is installed and ready to use with Snort; we have basically set up MySQL to be ready for the kind of data Barnyard2 wants to write to the database. So far, we have focused separately on installing Snort and MySQL, although with the MySQL instructions we put the pieces in place so that MySQL could receive Snort logging information from Barnyard2.