Differences

This shows you the differences between two versions of the page.

Link to this comparison view

mysql_logs [2013/10/03 12:37]
mysql_logs [2013/10/03 12:37] (current)
Line 1: Line 1:
 +====== 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>​