MySQL Log Management

Error Log

Identify Error Log

Shrink Error Log

To shrink the error log...

Move/Rename or Delete the error log file using os commands then run the following from mysql...

flush logs;

Or from mysqladmin...

mysqladmin flush-logs

Configure logrotate

Paste following into /etc/logrotate.d/mysql...

/mysql/data/*log {

# create 600 mysql mysql

notifempty

weekly

rotate 5

missingok

compress

postrotate

# just if mysqld is really running

if test -x /mysql/current/bin/mysqladmin && \

env HOME=/root/ /mysql/current/bin/mysqladmin ping &>/dev/null

then

env HOME=/root/ /mysql/current/bin/mysqladmin flush-logs

fi

endscript

}

For all files ending with log in /mysql/data

Create new files with 600 permissions

Don't rotate if log file is empty

Rotate weekly

Maximum of 5 versions of a file

If the logfile is missing, ignore and continue

Compress old versions with gzip


After Rotate is complete...

... if the mysqladmin file exists...

... and mysqladmin ping says db is up...

... then ...

... flush-logs ...

...

End postrotate commands


Slow Query Log

TODO

Binary Logs

TODO:

PURGE BINARY LOGS

SHOW SLAVE STATUS

Amazon RDS

TODO:

  • CALL mysql.rds_rotate_slow_log;

  • CALL mysql.rds_rotate_general_log;

Table Based Logs

TODO

  • TRUNCATE mysql.general_log;

  • TRUNCATE mysql.slow_log;