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.
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 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;
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:
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
}