Enabling MySQL Logging on Archlinux

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
port = 3306
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
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:


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.

Ubuntu vs Gentoo vs Arch – A comparison

Trials with Ubuntu and Gentoo Linux

Arch Linux is now my default distribution – supplanting Ubuntu Linux. Why? Well, its a long story, but I will try to keep it to the point. I am a tinkerer at heart – I love Linux because it allows you to tinker – essentially its a wide open book. Ubuntu has taken Linux down a different path. Ubuntu’s specialty is ease of use, and it excels at that. Ubuntu’s ease of use comes at a price though – flexibility and customization is somewhat diminished due to Ubuntu’s focus on the user experience.

I first moved to Ubuntu in 2008, when Hardy Heron, 8.04LTS was released. I came over as a frustrated Gentoo User. After using Gentoo Linux since 2004 (I used Redhat exclusively before that) I became a bit disenfranchised with Linux as a whole. It seemed that in order to get complete customization and control, I was going to have to suffer through hours of patching and compiling. Ever compile OpenOffice.org on a Pentium 4? It takes hours. I loved Gentoo for its ultimate in customization, but loathed it for the update process.

Ubuntu was a breath of fresh air in many ways – Install it and it just worked. It renewed my faith in Linux. Over time though I discovered several shortcomings. Canonical’s use of Gnome themes that were difficult to change colors on, their move to the non-standard Unity desktop, the fact a total reinstall (or upgrade) was needed to get to a new version of the OS, sticking with specific kernel versions for 6 months at a time, and the fact that by default there isn’t a way on Debian based distributions to get a consistent and reliable dump of daemon status (ie rc-status on Gentoo or rc.d list on Arch) – this is ground level Unix functionality and Ubuntu just plain doesn’t provide a workable solution! These shortcomings led me to look elsewhere after a few years.

One major advantage in to Gentoo, for my needs, is a that it’s a rolling release distribution. This means that kernel, system libraries, the tool chain and applications are updated in place over time, without requiring a wholesale reinstall or upgrade process like typical binary release distros. When you have 10 machines, this becomes a major benefit in keeping them in sync in terms of OS and software versions.

I actually went back to Gentoo for a bit on one system, only to find the same old issues.

Enter Arch Linux

On a whim one night I was perusing Distrowatch, and stumbled upon Arch Linux. Arch is one of the few rolling release distributions that is binary based (minimal compiling for upgrades) and isn’t based on Gentoo Linux. It’s billed as a simple, lightweight Linux, and it definately lives up to its name. Arch has an active user community, has a large and regularly updated core and community maintained package base, and offers the ability, similar to Gentoo, to compile thousands of other packages from the Arch User repository. There is that word again – compiling. But honestly, I have had to do very little of it.

Don’t let the simple, lightweight, moniker steer you aware, Arch is capable of running the most advanced server functions and the latest desktop environments. The lightweight and simple is in maintaining the system. Most system configuration is handled in one file – /etc/rc.conf. The daemon startup order is specified by one line in rc.conf – the order of them named is the order they will start! Imagine that – no messing with symlinks in /etc/init.d anymore.

Package management is simple, kernel updates and initrd images are handled automatically, and support for diskless machines and thin client build processes is second to none.

What’s not to love?

I am sure that their are individuals who would find Arch too much work, and frankly Ubuntu is probably best for them. Linux wouldn’t be Linux with a choice of distros. Its one of its key selling points for me.

Coming up next – more system administration hints for Arch Linux!

Got another distro that you like – leave a comment and let me know!