====== 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.

==== Slow Query Log ====

  * [[http://www.maatkit.org/doc/mk-index-usage.html|mk-index-usage]]
  * [[http://www.maatkit.org/doc/mk-query-advisor.html|mk-query-advisor]]
  * [[http://www.maatkit.org/doc/mk-query-digest.html|mk-query-digest]]
  * [[http://www.maatkit.org/doc/mk-table-usage.html|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'':

<code>
log-slow-queries = /var/log/mysql/slow.log
long_query_time = 1
</code>

You can also log slow administrative statements:

<code>
log-slow-admin-statements
</code>

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

<code>
log-queries-not-using-indexes
</code>

==== Error Log ====

  * [[http://dev.mysql.com/doc/refman/5.0/en/error-log.html|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'':

<code>
log-error=/var/log/mysql/mysqld.log
</code>

==== General Query Log ====

Enable the general query log without restarting the database:

<code>
SHOW VARIABLES LIKE 'general_log%';
SET GLOBAL general_log_file = '/var/log/mysql/query.log';
SET GLOBAL general_log = 1;
</code>
==== Binary Log ====

  * [[http://www.mysqlperformanceblog.com/2012/05/24/binary-log-file-size-matters/|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:

<code>
RESET MASTER;
</code>

Purging a range of binary logs is also possible.  See the [[http://dev.mysql.com/doc/refman/5.5/en/purge-binary-logs.html|PURGE BINARY LOGS syntax]].

==== Flushing Logs ====

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

<code>
max_binlog_size = 100M
</code>

Use mysqladmin, either ''flush-logs'' or ''refresh'':

<code>
mysqladmin flush-logs
mysqladmin refresh
</code>

Query:

<code>
FLUSH LOGS;
</code>
==== Log Rotation ====

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

== General Configuration ==

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

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