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:
4. Now reload apparmor
sudo /etc/init.d/apparmor reload