Still can’t enable the slow query log

Environment

This is my environment:

• System: CentOS 7
  • virtual machine, created by virtualbox, only mysql installed
• MySQL: 5.7.35
  • Setup MySQL by this chinese toturial: link
• my.cnf:

[mysqld]

# slow query log
slow_query_log=on
slow_query_log_file=/var/log/mysql/slow_query.log
long_query_time=1

# UTF-8
collation-server=utf8_general_ci
init-connect='SET NAMES utf8'
character-set-server=utf8

# disable case sensitive
lower_case_table_names=1

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

I have created the log file and executed these as root:

chown -R mysql:mysql /var/log/mysql
service mysqld restart

Problem

The slow query log is not enabled

When I try this in mysql as root:

show variables like '%slow%';

It shows these:

+---------------------------+-------------------------------+
| Variable_name             | Value                         |
+---------------------------+-------------------------------+
| log_slow_admin_statements | OFF                           |
| log_slow_slave_statements | OFF                           |
| slow_launch_time          | 2                             |
| slow_query_log            | OFF                           |
| slow_query_log_file       | /var/log/mysql/slow_query.log |
+---------------------------+-------------------------------+

Tried

When I try to enable the query log manually:

set global slow_query_log = ON;

Error

ERROR 29 (HY000): File '/var/log/mysql/slow_query.log' not found (Errcode: 13 - Permission denied)

others:

• add general_log=on under [mysqld] in my.cnf
• replace on or ON by number 1

Did I do anything wrong?
Maybe a simple stupid mistake but I can’t figure it out…

Thank you very much

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Given the directory ownership is correct, and that its Centos7, you’ll need to fix the selinux permission.

The manual entry is on this page using mysqld_log_t, the same as the error log, because that will allow logrotate rules to access it.

semanage fcontext -a -t mysqld_log_t /var/log/mysql/slow_query.log

Then you should be able to enable, even at runtime:

set global slow_query_log = ON;

Method 2

There are far too many settings to get it right:

log_output = FILE
slow_query_log = ON
slow_query_log_file = (fullpath to some file)
long_query_time = 1
log_slow_admin_statements = ON
log_queries_not_using_indexes = OFF

More discussion and more options: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0