Logging in MySQL

Logs are the first and most simple tool to determine the status of the system and identify errors. There are four core logs in MySQL:

  • Error Log — the standard log that collects errors during the server work (including start and stop);
  • Binary Log — a log of all database change commands, is needed for replication and backups;
  • General Query Log — main log for requests;
  • Slow Query Log — logs slow queries.

Error Log

This log contains all the errors that occurred during the operation of the server, including critical errors, stops, reboots and warnings. It’s the start point in case of system failure. By default, all errors are displayed in the console (stderr), and you can record an error in the syslog (by default in Debian) or a separate log file:


# Errors will be written in mysql_error.log

We recommend to keep this log enabled for quick error detection. And to understand what certain error means MySQL has perror utility:

shell> perror 13 64
OS error code  13:  Permission denied
OS error code  64:  Machine is not on the network

# Explains the meaning of the error codes

Binary Log

The binary log records all database change commands,it is useful for replication and recovery. To enable:

log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days    	= 5
max_binlog_size     	= 500M

# Specifies the location, lifetime, and the maximum file size

Note that if you do not want to scale the system and implement fault tolerance, the binary log is better off. It is resource demanding and reduces system performance.

General Query Log

This log contains all SQL-queries received, information about the connected clients. It can be useful for the analysis of indexes and optimization, as well as the identification of erroneous requests:

general_log_file    	= /var/log/mysql/mysql.log
general_log         	= 1

# Enables log and indicates the location of the file

It can also be enabled / disabled during the operation of the MySQL server:

SET GLOBAL general_log = 'ON';

SET GLOBAL general_log = 'OFF';

# You do not need to restart the server for the application

Slow Query Log

The log is useful for determining the slow and inefficient queries. Read more in this article.

View logs

To view the logs on Debian (Ubuntu) execute:

 # Error log
tail -f /var/log/syslog

 # Query log
tail -f /var/log/mysql/mysql.log

 # Slow query log
tail -f /var/log/mysql/mysql-slow.log

# If the logs are not specified separately, then located in /var/lib/mysql

Log rotation

Do not forget to compress (archive, rotate) log files so they take up less space on the server. To do this, use the utility logrotate, by editing the configuration file /etc/logrotate.d/mysql-server:

# - I put everything in one block and added sharedscripts, so that mysql gets

#   flush-logs'd only once.
#   Else the binary logs would automatically increase by n times every day.

# - The error log is obsolete, messages go to syslog now.
/var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log {
        rotate 7
        create 640 mysql adm
                test -x /usr/bin/mysqladmin || exit 0
                # If this fails, check debian.conf!
                MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
                if [ -z "`$MYADMIN ping 2>/dev/null`" ]; then
                  # Really no mysqld or rather a missing debian-sys-maint user?
                  # If this occurs and is not an error please report a bug.
                  #if ps cax | grep -q mysqld; then
                  if killall -q -s0 -umysql mysqld; then
                    exit 1
                  $MYADMIN flush-logs

# Compresses and archives the necessary logs, cleans files


MySQL also keeps a log of data description language. It collects DROP_TABLE and ALTER_TABLE data type operations. The log is used to recover from failures that occurred during the execution of such operations. DDL Log is a binary file, not intended for the user read, so do not modify or delete it.

The most important

Always enable error log, use the queries log to verify the application’s connections to the database, check requests and use memcached. Slow queries log is useful for MySQL optimization.

Подпишитесь на Хайлоад с помощью Google аккаунта
или закройте эту хрень