Differences
This shows you the differences between two versions of the page.
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> |