Mysql – How to set slow query log in MariaDB the.cnf

mariadbmy.cnfMySQLmysql-5.6performanceslow-log

It should be easy, but when I put these lines:

log_slow_queries = 1
long_query_time = 1
log-slow-queries = /var/log/mysql/slow_query.log
log_queries_not_using_indexes

in [mysqld], MariaDB fails to restart.

I looked at the docs, but could not figure out what is wrong in my configs.

Also , I've set the file permission to /var/log/mysql/slow_query.log

# ls -al /var/log/mysql/slow_query.log 
-rw-rw-r-- 1 mysql mysql 744682 Dec  4 23:46 /var/log/mysql/slow_query.log

However, I can set the parameters in command line:

SET GLOBAL slow_query_log_file = '/var/log/mysql/slow_query.log';
SET GLOBAL LONG_QUERY_TIME = 1;
SET GLOBAL slow_query_log = 'ON';

and see that they are set:

+---------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name       | Value                                                                                                        |
+---------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_filter     | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_rate_limit | 1                                                                                                            |
| log_slow_verbosity  |                                                                                                              |
| slow_launch_time    | 2                                                                                                            |
| slow_query_log      | ON                                                                                                           |
| slow_query_log_file | /var/log/mysql/slow_query.log                                                                                |
+---------------------+--------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

and even the slow queries are being written to the log. But I want to make the config persistent by writing them to my.cnf. So appreciate your help.

Best Answer

You said you added this

log_slow_queries = 1
long_query_time = 1
log-slow-queries = /var/log/mysql/slow_query.log
log_queries_not_using_indexes

That's the old way MySQL 5.0 sets the slow query log.

To activate the slow query log for MySQL 5.6 and MariaDB, you must use the following

Therefore, you need this in my.cnf

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow_query.log
log_queries_not_using_indexes