- Login to your server via SSH.
- To edit the MySQL settings with nano editor issue the following command. You can also use other editors like: Vi, Pico, Emacs etc.
nano /etc/my.cnf
- To enable mysql slow query log, add following lines under [mysqld] section.
long_query_time=3 | This statement tells MySQL server to log any query that takes longer that 3 seconds.
log_slow_queries=/var/log/mysql/log-slow-queries.log | This statement tells MySQL server where to log the slow queries.
log_queries_not_using_indexes=YES | This is an optional statement that logs queries that don’t use an index.
After adding above lines, enter Ctrl + O to save the changes and then Ctrl + X to exit. - Create MySQL log directory and slow log file using following commands.
mkdir /var/log/mysql
touch /var/log/mysql/log-slow-queries.log
chown mysql.mysql -R /var/log/mysql - When you’ve made the changes to my.cnf file and created the log file you need to restart mysql server with following command.
service mysqld restart