Saturday, December 31, 2011

General Logging in MySQL

I was looking to investigate some connection problems we were having to a MySQL database and wanted to be sure that the client was connecting to correct machine. MySQL 5.1 doesn't provide this information on it's own, but it is contained in the general log. However the log contains all statements executed against MySQL so will have a performance impact on a production machine. The general log can be enabled/disabled while MySQL is still running which means you can enable the logging, capture the events your interested in and then disable it again. Todo this connect to MySQL as a user who can set GLOBALS (typically root) and run:

mysql> SET GLOBAL general_log_file='/var/log/mysql.log';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL general_log = 1;
Query OK, 0 rows affected (0.00 sec)

Then the file /var/log/mysql.log should start filling up with statements. Once your done disable the general log:

mysql> SET GLOBAL general_log = 0;
Query OK, 0 rows affected (0.01 sec)

In my case I was looking for connection messages so a simple grep pulled out the lines I was interested in:

grep Connect /var/log/mysql.log | less