Mysql server running as replica doesn't record the slow queries in the slow query log, even though it's enabled

Question

I am running a MySql 8.0.29 server serving as a replica for another server on an ubuntu 20.04.4 LTS box.

'slow_query_log','ON'
'slow_query_log_file','/var/log/mysql/mysql-slow.log'
'long_query_time', '10.000000'

I ran a query that took approx. 20s, however, the query doesn't show up in mysql-slow.log. The file is empty (size 0).

Any ideas where I can look to find out why the query is not recorded in the slow log? I checked the error logs, and syslog and there is nothing related to this. File permissions are ok as well.

Thanks

Update: it worked for a while after flushing the slow log, then it lost it again. Not sure what it is...

Answer 1

I ran FLUSH SLOW LOGS and now it got its brains back. The slow queries are logged again. I still have to investigate what could have caused mysql not to update the file.

Today I checked the logs, and they were empty again. I think it's logrotate that interferes with the logging. I found this article and they suggest flushing the log:

https://oguya.ch/posts/2016-04-13-safely-rotating-mysql-slow-logs/

/var/lib/mysql/mysql-slow.log {
    size 1G
    dateext
    compress
    missingok
    rotate 20
    notifempty
    delaycompress
    sharedscripts
    nocopytruncate
    create 660 mysql mysql
    postrotate
        /usr/bin/mysql -e 'select @@global.slow_query_log into @sq_log_save; set global slow_query_log=off; select sleep(5); FLUSH SLOW LOGS; select sleep(10); set global [email protected]_log_save;'
    endscript
    rotate 150
}

My configuration is:

/var/log/mysql/mysql-error.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log {
    daily
    rotate 7
    missingok
    create 644 mysql adm
    Compress
}

Answer 2

What values do you have for these?

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
log_slow_slave_statements = ON    
log_slow_replica_statements = ON
log_slow_extra = ON

(Some may be missing from your version; that is OK.)