Enabling Slow Query Log for MySQL on Amazon AWS EC2 Ubuntu Based AMI

There are two methods using which slow query logs can be enabled for MySQL.

Changing Global Variables

This is runtime approach for logging slow query. MySQL server restart is not needed in this case. But, these changes go away with MySQL server restart or machine reboot.

These are the steps to log slow MySQL queries:

1. Login to MySQL shell

$ mysql -p -u root
Welcome to the MySQL monitor.  Commands end with ; or g.
mysql>

2. Enable slow query logging

mysql> set global slow_query_log = 1;

0=logging disabled, 1= logging enabled, default is 0.

3. Set log file location

mysql> set global slow_query_log_file='/location/of/log/file/mysql-slow.log';

Default location of file is “/var/log/mysql/mysql-slow.log”. Make sure the file has the appropriate permissions.

4. Set Long Query Time

mysql> set global long_query_time=integer_time_in_seconds;

SQL statements that took more than ‘long_query_time’  seconds to execute will be logged to the log file specified above.

Changing Config File (my.cnf)

This will require MySQL server to restart. These changes persist when the MySQL server restart or machine reboot.

We need to edit my.cnf file. It is usually located in /etc/mysql/ directory

$ sudo vi /etc/mysql/my.cnf

Go to “[mysqld]” section and uncomment following lines and make change

log_slow_queries = /location/of/log/file/mysql-slow.log
long_query_time = integer_time_in_seconds

ERROR 29 (HY000)

Make sure the log file (/location/of/log/file/ in above example) has proper permission. Else or following error may appear:

ERROR 29 (HY000): File '/location/of/log/file/mysql-slow.log' not found (Errcode: 13)

Ensuring right permissions is sufficient

$ sudo chmod -R o+w /location/of/log/file/

AppArmor Related Issues

Recent Ubuntu Server Editions comes with AppArmor and MySQL’s profile might be in enforcing mode by default. This is common in some of AWS EC2 AMIs.

1. Run following command to check

$ sudo aa-status
Apparmor module is loaded.
 5 profiles are loaded.
 5 profiles are in enforce mode.
 /sbin/dhclient
 /usr/lib/NetworkManager/nm-dhcp-client.action
 /usr/lib/connman/scripts/dhclient-script
 /usr/sbin/mysqld
 /usr/sbin/tcpdump
 0 profiles are in complain mode.
 2 processes have profiles defined.
 2 processes are in enforce mode.
 /sbin/dhclient (646)
 /usr/sbin/mysqld (2062)
 0 processes are in complain mode.
 0 processes are unconfined but have a profile defined.

2. If mysqld is listed as in above output, make following change: Edit /etc/apparmor.d/usr.sbin.mysqld

$ sudo vi  /etc/apparmor.d/usr.sbin.mysqld

3. Enter following line at the end of file:

/location/of/log/file/* rw,

4. Now reload apparmor

sudo /etc/init.d/apparmor reload