CentOS Question posted in
The official documentation can be found here.

Still can't enable the slow query log – CentOS

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

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search