- 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.
- 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.
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