Forceing MySQL Logs to File

By default, MySQL can write its slow query log and general log to either a file or a table in the mysql schema. If you want predictable file-based logging, you need to set the log_output variable explicitly.

Setting log_output to FILE

Add the following line to your MySQL configuration file (typically /etc/mysql/my.cnf or /etc/my.cnf):

[mysqld]
log_output = FILE

The valid values for log_output are:

• FILE - writes logs to the files specified by slow_query_log_file and general_log_file
• TABLE - writes logs to mysql.slow_log and mysql.general_log tables
• FILE,TABLE - writes to both

Why prefer FILE?

Writing logs to tables means every log entry is an INSERT into InnoDB, which adds I/O overhead and can bloat the mysql schema. File-based logging is simpler to rotate with logrotate, easier to tail in real time, and keeps the mysql database lean.

Verifying the setting

After restarting MySQL, confirm the active value:

SHOW VARIABLES LIKE 'log_output';

You should see FILE (or FILE,TABLE if you chose both).

Rotating log files

Once logs are file-based, pair them with a logrotate rule so they do not grow unbounded:

/var/log/mysql/*.log {
    weekly
    rotate 4
    compress
    missingok
    notifempty
    postrotate
        /usr/bin/mysqladmin -u root -p'password' flush-logs
    endscript
}

The flush-logs command tells MySQL to close and reopen its log files, which is what triggers the rotation cleanly.

• Technology
• SysAdmin

• Sql
• SysAdmin