MySQL Logs

• MySQL
• MySQL Configuration
• MySQL Optimization
• maatkit
• logrotate

• http://dev.mysql.com/doc/refman/5.5/en/log-file-maintenance.html

By default, MySQL doesn't keep any logs.

All logs need to be properly secured, since they can contain sensitive data within queries.

Logs can quickly grow in size, especially slow queries if watching for rows that don't use indexes on lookups. Be sure to setup proper log rotation.

• mk-index-usage
• mk-query-advisor
• mk-query-digest
• mk-table-usage

The default value for a query to take before being logged is 10 seconds. The minimum amount this can be set to is 1 second. The long_query_time value is measured in seconds.

Setup slow query logging in my.cnf:

log-slow-queries = /var/log/mysql/slow.log
long_query_time = 1

You can also log slow administrative statements:

log-slow-admin-statements

Include queries that do not use indexes for row lookups in the statements:

log-queries-not-using-indexes

• The Error Log

The error log contains information indicating when mysqld was started and stopped and also any critical errors that occur while the server is running. If mysqld notices a table that needs to be automatically checked or repaired, it writes a message to the error log.

You can specify where mysqld writes the error log with the –log-error[=file_name] option. If the option is given with no file_name value, mysqld uses the name host_name.err by default. The server creates the file in the data directory unless an absolute path name is given to specify a different directory.

Setup error logging in my.cnf:

log-error=/var/log/mysql/mysqld.log

Enable the general query log without restarting the database:

SHOW VARIABLES LIKE 'general_log%';
SET GLOBAL general_log_file = '/var/log/mysql/query.log';
SET GLOBAL general_log = 1;

• Percona: Binary log file size matters (sometimes)

Binary log is recommended for aiding to restore data.

You can remove all the binary logs if desired:

RESET MASTER;

Purging a range of binary logs is also possible. See the PURGE BINARY LOGS syntax.

Binary logs are flushed automatically, based on the max_binlog_size. Max out at 100 megs:

max_binlog_size = 100M

Use mysqladmin, either flush-logs or refresh:

mysqladmin flush-logs
mysqladmin refresh

Query:

FLUSH LOGS;

Percona ships with a logrotate configuration file located at /etc/logrotate.d/percona-server-server-5.5. By default it:

• Keeps 7 days worth of logs
• Rotates daily
• Compresses logs

Here's a sample file for /etc/logrotate.d/mysql:

/var/log/mysql/slow.log {
    daily
    rotate 30
    missingok
    ifempty
    sharedscripts
    create 660 mysql mysql
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}