Using MySQL query logs to debug authentication issues

I recently installed LogAnalyzer, and after the install completed I noticed that nothing was being displayed in the web interface. I figured I fat fingered something, but needed a way to verify this. Luckily for me I was using MySQL, so I enabled MySQL query logging and low and behold I proved my hypothesis:

120212 12:09:33     6 Connect   rsyslog@localhost on
                    6 Init DB   Access denied for user 'rsyslog'@'%' to database 'Syslog'

To fix this I logged into the database and changed the password for the rsyslog user:

$ mysql -u root -h localhost –password
mysql> use Syslog

mysql> SET PASSWORD FOR rsyslog = PASSWORD (“XXXXXXXXX”);

Everything began working once the application could authenticate, and I was able to start playing around with LogAnalyzer. Noting this here in case I fat finger another password in the future. :)

Enabling MySQL query logging

I recently installed the LogAnalyzer graphical syslog analysis tool. After the install completed I went to the “Show Events” page and noticed that no data was being displayed. I wanted to see which queries were being sent by LogAnalyzer to my MySQL database instance, so I enabled query logging by adding the following two statements to the [mysqld] block in the /etc/my.cnf configuration file:

general_log=1
general_log_file=/var/log/query.log

The first line enables logging, and the second line tells MySQL were to write the logs. Once enabled you can see the queries executed against your server by paging out the contents of /var/log/query.log. This will have one or more entries similar to the following:

                  233 Query     Select FOUND_ROWS()
120212 13:15:07   233 Quit      
120212 13:15:12   234 Connect   rsyslog@localhost on 
                  234 Init DB   syslog
                  234 Query     SHOW TABLES LIKE '%SystemEvents%'
                  234 Query     SELECT SQL_CALC_FOUND_ROWS id, devicereportedtime, facility, priority, fromhost, syslogtag, processid, infounitid, message FROM SystemEvents ORDER BY id DESC LIMIT 100

Pretty cool, and definitely super useful for debugging problems and figuring out how restrictive you can be with your GRANT statements. Viva la MySQL!

Using the rsyslog MySQL plug-in to send syslog data to a SQL database

I have been experimenting with ways to better manage the logs my servers generate. Depending on who you ask, folks will recommend sending your logs to a remote syslog server that writes the logs to disk, some may recommend sending it to a log analysis tool similar to splunk, and others would recommend feeding it to a SQL database. I’ve talked before about setting up syslog-ng for remote logging, and in this case I wanted to experiment with something new. I also didn’t have money to buy a tool like splunk, so I decided to start experimenting with funneling syslog data into a MyQSL database.

Setting up syslog to write messages to a MySQL database is crazy easy to do on CentOS 6. The built-in syslog daemon (rsyslog) has database plug-ins for several opensource databases, which can be installed with the yum package manager:

$ yum install rsyslog-mysql

Once the plug-in is installed you can run the provided createDB.sql script (this is part of the rsyslog-mysql package) to create a database (the default database will be named Syslog, though you can edit the createDBL.sql file if you want to call it something else) as well as the tables the log entries will be stored in:

$ rpm -q -l rsyslog-mysql-4.6.2-12.el6.x86_64 | grep createDB.sql
/usr/share/doc/rsyslog-mysql-4.6.2/createDB.sql

$ mysql -u root -h localhost –password

mysql> source /usr/share/doc/rsyslog-mysql-4.6.2/createDB.sql

If this completes successfully you should have two brand spanking new tables:

mysql> use Syslog;

mysql> show tables;

+------------------------+
| Tables_in_Syslog       |
+------------------------+
| SystemEvents           |
| SystemEventsProperties |
+------------------------+

The SystemEvents table is where log data is stored and has the following structure:

mysql> desc SystemEvents;

+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| ID                 | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| CustomerID         | bigint(20)       | YES  |     | NULL    |                |
| ReceivedAt         | datetime         | YES  |     | NULL    |                |
| DeviceReportedTime | datetime         | YES  |     | NULL    |                |
| Facility           | smallint(6)      | YES  |     | NULL    |                |
| Priority           | smallint(6)      | YES  |     | NULL    |                |
| FromHost           | varchar(60)      | YES  |     | NULL    |                |
| Message            | text             | YES  |     | NULL    |                |
| NTSeverity         | int(11)          | YES  |     | NULL    |                |
| Importance         | int(11)          | YES  |     | NULL    |                |
| EventSource        | varchar(60)      | YES  |     | NULL    |                |
| EventUser          | varchar(60)      | YES  |     | NULL    |                |
| EventCategory      | int(11)          | YES  |     | NULL    |                |
| EventID            | int(11)          | YES  |     | NULL    |                |
| EventBinaryData    | text             | YES  |     | NULL    |                |
| MaxAvailable       | int(11)          | YES  |     | NULL    |                |
| CurrUsage          | int(11)          | YES  |     | NULL    |                |
| MinUsage           | int(11)          | YES  |     | NULL    |                |
| MaxUsage           | int(11)          | YES  |     | NULL    |                |
| InfoUnitID         | int(11)          | YES  |     | NULL    |                |
| SysLogTag          | varchar(60)      | YES  |     | NULL    |                |
| EventLogType       | varchar(60)      | YES  |     | NULL    |                |
| GenericFileName    | varchar(60)      | YES  |     | NULL    |                |
| SystemID           | int(11)          | YES  |     | NULL    |                |
+--------------------+------------------+------+-----+---------+----------------+

The column descriptions are pretty much self explanatory, and you can reference the rsyslog documentation to get more specifics on the purpose of each column. In order for rsyslog to be able to write to the database, you will need to create a user (using root is not recommended) and grant the privileges to allow the user to INSERT new data. For my purposes I created an rsyslog user and restricted them to INSERT’ing data into just the Syslog database:

mysql> grant INSERT on Syslog.* to rsyslog identified by “PASSWORD_HERE”;

This completes the MySQL configuration. To tell rsyslog to start sending messages to the Syslog database, you will need to add directives similar to the following to /etc/rsyslog.conf. You will also need to restart the rsyslog service:

$ModLoad ommysql
*.*       :ommysql:rsyslogdb.prefetch.net,Syslog,rsyslog,PASSWORD_HERE

The first line loads the MySQL plug-in, and the second line tells rsyslog to send all of the log entries (you can pair this down to specific facilities and priorities) to the Syslog database on rsyslogdb.prefetch.net. It will use the provided user (rsyslog in this example) and password (PASSWORD_HERE in this example) to login to the database. If everything worked as expected you should be able to view the log entries in the SystemEvents table:

$ mysql -u root -h localhost –password

mysql> use Syslog;

mysql> select * from SystemEvents limit 5;

+----+------------+---------------------+---------------------+----------+----------+-----------+--------------------------------------------------------------------------------------------------------+------------+------------+-------------+-----------+---------------+---------+-----------------+--------------+-----------+----------+----------+------------+-----------+--------------+-----------------+----------+
| ID | CustomerID | ReceivedAt          | DeviceReportedTime  | Facility | Priority | FromHost  | Message                                                                                                | NTSeverity | Importance | EventSource | EventUser | EventCategory | EventID | EventBinaryData | MaxAvailable | CurrUsage | MinUsage | MaxUsage | InfoUnitID | SysLogTag | EventLogType | GenericFileName | SystemID |
+----+------------+---------------------+---------------------+----------+----------+-----------+--------------------------------------------------------------------------------------------------------+------------+------------+-------------+-----------+---------------+---------+-----------------+--------------+-----------+----------+----------+------------+-----------+--------------+-----------------+----------+
|  1 |       NULL | 2012-02-11 15:42:01 | 2012-02-11 15:42:01 |        0 |        6 | centos6-1 | imklog 4.6.2, log source = /proc/kmsg started.                                                         |       NULL |       NULL | NULL        | NULL      |          NULL |    NULL | NULL            |         NULL |      NULL |     NULL |     NULL |          1 | kernel:   | NULL         | NULL            |     NULL |
|  2 |       NULL | 2012-02-11 15:42:01 | 2012-02-11 15:42:01 |        5 |        6 | centos6-1 |  [origin software="rsyslogd" swVersion="4.6.2" x-pid="3891" x-info="http://www.rsyslog.com"] (re)start |       NULL |       NULL | NULL        | NULL      |          NULL |    NULL | NULL            |         NULL |      NULL |     NULL |     NULL |          1 | rsyslogd: | NULL         | NULL            |     NULL |
|  3 |       NULL | 2012-02-11 15:42:25 | 2012-02-11 15:42:25 |        0 |        6 | centos6-2 | imklog 4.6.2, log source = /proc/kmsg started.                                                         |       NULL |       NULL | NULL        | NULL      |          NULL |    NULL | NULL            |         NULL |      NULL |     NULL |     NULL |          1 | kernel:   | NULL         | NULL            |     NULL |
|  4 |       NULL | 2012-02-11 15:42:25 | 2012-02-11 15:42:25 |        5 |        6 | centos6-2 |  [origin software="rsyslogd" swVersion="4.6.2" x-pid="5932" x-info="http://www.rsyslog.com"] (re)start |       NULL |       NULL | NULL        | NULL      |          NULL |    NULL | NULL            |         NULL |      NULL |     NULL |     NULL |          1 | rsyslogd: | NULL         | NULL            |     NULL |
|  5 |       NULL | 2012-02-11 15:42:22 | 2012-02-11 15:42:22 |        1 |        5 | centos6-1 |  test                                                                                                  |       NULL |       NULL | NULL        | NULL      |          NULL |    NULL | NULL            |         NULL |      NULL |     NULL |     NULL |          1 | matty:    | NULL         | NULL            |     NULL |
+----+------------+---------------------+---------------------+----------+----------+-----------+--------------------------------------------------------------------------------------------------------+------------+------------+-------------+-----------+---------------+---------+-----------------+--------------+-----------+----------+----------+------------+-----------+--------------+-----------------+----------+
5 rows in set (0.00 sec)

Awesome! Once you have all of your hosts pointing to your database you can use the power of SQL to sift through the data and correlate events (I wonder how useful this would be when applied to digital computer forensics). You can also use tools like LogAnalyzer to visualize your log data. I’ve thought of hundreds of things I can do with my log data, I just need to spend some time coding them up! :)

Display the default values of MySQL my.cnf settings

MySQL is configured through the my.cnf configuration file, which typically resides in /etc. There are dozens of configuration settings that can be added to this file, and you can view the full list by running mysqld with the “–help” and “–verbose” options:

$ /usr/libexec/mysqld –help –verbose | grep -i ^relay

relay-log                         slave-relay-bin.index
relay-log-index                   slave-relay-bin
relay-log-info-file               relay-log.info
relay_log_purge                   TRUE
relay_log_space_limit             0

The configuration directive will be printed on the left, and the current value of the directive will be displayed on the right. When I get a “how do I do X” thought I typically will cross reference directives with the official documentation to see how to configure the server to do what I need it to. It’s also just plain useful to know what you can do with the server.

Getting MySQL running on a CentOS Linux server

I started playing with MySQL back in the 4.X days, but never invested a lot of my time since my day job required me to support Oracle databases. I’m trying to branch out more now, and recently picked up a copy of MySQL, MySQL High Availability and PHP And MySQL. There are a slew of things I would like to web-enable, so I’m hoping to learn everything I can about PHP and MySQL in the next few months.

To allow me to start experimenting with PHP and MySQL, I needed to create a test environment. My MySQL environment consists of two CentOS 6 virtual machines running MySQL 5.1.X. Getting MySQL working on these two machines was amazingly easy. First, I installed the mysql packages with yum:

$ yum install mysql mysql-server

Next I started up the MySQL services and made sure they started at boot:

$ chkconfig mysqld on

$ service mysqld start

And finally I secured my MySQL installation by running the mysql_secure_installation script:

$ /usr/bin/mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

Once this completed I restarted MySQL and could login using the root user (additional accounts will be added in the near future):

$ service mysqld restart

Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

$ mysql -h localhost -u root –password=XXXXXXXX

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.52-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

It took me 5 minutes to get MySQL to a state were I could enable replication and create databases. Nice!