This morning I was doing some troubleshooting on a PHP program that couldn’t seem to access the locally running MySQL database server. Upon searching for some logging to figure out what was going on, I came across the fact the MySQL as installed from the Arch repositories, doesn’t enable much along theses lines.

MySQL supports error logging to /var/lib/mysql directory by default. The file is usually in the form .err. This file shows mysqld startup errors and other basic troubleshooting information. I was looking for something a little more in depth, such as client connections, and SQL statement logging. Apparently this type of logging is referred to as general logging. After some experimentation, it appears that enabling this log requires a few simple system changes.

Enabling general logging for MySQL

The first step is to figure out where you want MySQL to store its general log. In our example, we will put it in the /var/log/mysql directory, in file called general.log. This directory and file must be created, and permissions set so that mysql can write to it. MySQL will not create this file on its own. So lets create the directory and file with the following commands:

sudo mkdir /var/log/mysql
sudo touch /var/log/mysql/general.log
sudo chown mysql:mysql -R /var/log/mysql


Then, in order tell MySQL to enable general logging, a couple of lines so be added to the /etc/mysql/my.cnf file in the [mysqld] section – see last two lines in the example my.cnf file below:

# The MySQL server
[mysqld]
port = 3306
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
general_log = 1
general_log_file = /var/log/mysql/general.log

That’s all it takes! Save the my.cnf file, and restart mysqld with:

/etc/rc.d/mysqld restart

You should now the general.log file in /var/log/mysql. An example might look like this (showing some client connections and select statements):

/usr/bin/mysqld, Version: 5.5.21-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
120318 10:10:26 1 Connect UNKNOWN_USER@localhost as on
1 Quit
120318 10:10:39 2 Connect root@localhost on
2 Init DB webcalendar
2 Init DB webcalendar
2 Query SELECT COUNT( cal_value ) FROM webcal_config
2 Query SELECT cal_permissions FROM webcal_access_function
WHERE cal_login = 'admin'
2 Query SELECT cal_permissions FROM webcal_access_function
WHERE cal_login = '__default__'
2 Init DB webcalendar
2 Query SELECT cal_login, cal_lastname, cal_firstname, cal_is_admin, cal_email, cal_passwd FROM webcal_user ORDER BY cal_lastname, cal_firstname, cal_login


The above snip shows MySQL starting and an application hitting a database.

Configuring MySQL to log errors to the system log

That last thing I want to cover is getting MySQL to log to the system log, instead of its own log in /var/lib/mysql. I mentioned above that MySQL logs errors to /var/lib/mysql/.err. We will make a simple change to log those same messages to the system log, named to the daemon.log.

This one is pretty simple – add the following section to the /etc/mysql/my.cnf file:


[mysqld_safe]
syslog

And then restart MySQL with:

/etc/rc.d/mysqld restart

You should see messages in the daemon.log file that look something like this:

Mar 18 10:14:54 localhost mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
Mar 18 10:14:54 localhost mysqld: 120318 10:14:54 InnoDB: The InnoDB memory heap is disabled
Mar 18 10:14:54 localhost mysqld: 120318 10:14:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins
Mar 18 10:14:54 localhost mysqld: 120318 10:14:54 InnoDB: Compressed tables use zlib 1.2.6
Mar 18 10:14:54 localhost mysqld: 120318 10:14:54 InnoDB: Initializing buffer pool, size = 128.0M
Mar 18 10:14:54 localhost mysqld: 120318 10:14:54 InnoDB: Completed initialization of buffer pool
Mar 18 10:14:54 localhost mysqld: 120318 10:14:54 InnoDB: highest supported file format is Barracuda.
Mar 18 10:14:54 localhost mysqld: 120318 10:14:54 InnoDB: Waiting for the background threads to start
Mar 18 10:14:55 localhost mysqld: 120318 10:14:55 InnoDB: 1.1.8 started; log sequence number 2218816
Mar 18 10:14:55 localhost mysqld: 120318 10:14:55 [ERROR] /usr/bin/mysqld: Can't find file: './performance_schema/events_waits_current.frm' (errno: 13)


In summary, the above changes will allow you to “see” what MySQL is doing both in terms of the daemon itself, and the actual activity being processed by the database server. These changes could also work on other distros, but in particular ArchLinux by default enables only error logging to the MySQL directory only.

Up next – configuring syslog-ng as syslog server for network devices.