no way to compare when less than two revisions

Differences

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

@@ 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>

Trace:

Differences

Navigation

Search

Toolbox

QR Code