MySQL Logging

MySQL Log Files

By default, logging files are located under

c:\mysql\data

or

/var/lib/mysql

 

Log mysqld option

(To enable)
Description Files Default On Production
Binary log --log-bin=log-bin Log SQL updates/changes for recovery & replication data: log-bin.*

index: log-bin.index (list of current log-bin.* files)
Off Recommended
Error log --log-error Log server error message host_name.err On Windows, "on" unless it starts with --console

On Unix, "on" when starts with mysqld_safe or mysql.server
Recommended
General Query log --general_log Log client connections and all queries host_name.log Off For troubleshooting
Slow query log --slow_query_log Log all slow query to the server host_name-slow.log Off Recommended
Relay log See section on replication Slave plays back the log to sync with master data: hostname-relay-bin.*

index: hostname-relay-bin.index

(list of current relay-bin.* files)

Events replay status: relay-log.info
Off For replication

Logging can be turn on/off dynamically

SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';

MySQL Log Operations

Create a user specify for flushing log

mysql> CREATE USER 'mysqllog'@'localhost' IDENTIFIED BY 'somepassword';
mysql> GRANT RELOAD ON *.* TO 'mysqllog'@'localhost';

MySQL Flush and Rotate Logs

Flush logs to files, and create & start logging in a new file

Login mysql as mysqllog

mysql> FLUSH LOGS

In mysqladmin

% mysqladmin -u mysqllog -p flush-logs

Convert binary & relay log data into text format

Both binary and relay log are in binary format. To convert the data into readable SQL text:

% mysqlbinlog log_file > out.sql

Configure & Read slow query log

By default, SQLs take longer than 10s will be logged when --slow_query_log is set. To change the time value to 30s:

--long_query_time=30

Slow query log is in text format. Use mysqldumpslow to summarize its content

MySQL Log Maintenance

Expire Old Binary Log Automatically

Use if

  • Full backup is done regularly and
  • Replication is not needed or no slaves will be out of sync longer than n days

To expire binary logs older than 21 days, add this in the option file

[mysqld]
expire_logs_days=21

Expire Binary Log if no Guarantee when Replication is Done

Use if

  • Some slave may pause replication with master for an un-known amount of time
  1. For all slave,
    mysql> SHOW SLAVE STATUS\G
    
    Master_Log_File: log-bin.000021
    
  2. Find the lowest numbered master log file among all slaves
  3. Purge up to one log before the lowest numbered log in the Master server
    mysql> PURGE MASTER LOGS TO 'log-bin.000020';
    

Expire Relay Log

It is done automatically when all events in the relay logs are replicated

Log Expiration & Rotation

  1. Add a custom script (log_rotate)
    1. Rename current slow query log & general log (if any). For example,
      timestamp=$( date +%d%m%y%H%M%S )
      mv hostname.log hostname.log."$timestamp"
      mv hostname-slow.log hostname-slow.log."$timestamp"
      
    2. Flush log
      % mysqladmin -u mysqllog -psomepassword flush-logs
      
    3. Rename error log

      timestamp=$( date +%d%m%y%H%M%S )

      mv hostname.err-old hostname.err."$timestamp"
    4. Archive any error/general/slow query logs older than 21 days to an external drive
    5. Remove any error/general/slow query logs older than 21 days
  2. Add a daily log_rotate cron job
    0 0 * * * /path/to/log_rotate
    

It is suggested to use this custom script to flush logs since it can retain some critical logs