How and When To Enable MySQL Logs

If you are a web developer, you need to refer to various log files, in order to debug your application or improve its performance. Logs is the best place to start troubleshooting. Concerning the famous MySQL database server (or MariaDB server), you need to refer to the following log files:

  • The Error Log. It contains information about errors that occur while the server is running (also server start and stop)
  • The General Query Log. This is a general record of what mysqld is doing (connect, disconnect, queries)
  • The Slow Query Log. Ιt consists of “slow” SQL statements (as indicated by its name).

This article does not refer to The Binary Log. This requires very high standards server hardware and is useful only in special cases (e.g. replication, master – slaves setup, certain data recovery operations). Οtherwise, it is just a “performance killer”.

The official documentation about MySQL logs is available here.

Enable logs from MySQL configuration

Logging parameters are located under [mysqld] section.

Edit MySQL configuration file:

nano /etc/mysql/my.cnf

This is the default setup for Logging and Replication (in Debian server). In other distributions the structure may be different, but you can always use MySQL server parameters:

# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
#log_slow_queries       = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name

All log files are NOT enabled by default MySQL setup (except the error log on Windows). Default Debian setup sends Error log to syslog. The other log files are not enabled.

Error Log

Error Log goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf, which contains the following:

[mysqld_safe]
syslog

This is the recommended method. If, for some reason, you do not want Error log to go to syslog, comment the above lines in /etc/mysql/conf.d/mysqld_safe_syslog.cnf or completely remove this file. Then, add in /etc/mysql/my.cnf the following lines:

[mysqld_safe]
log_error=/var/log/mysql/mysql_error.log

[mysqld]
log_error=/var/log/mysql/mysql_error.log

General Query Log

To enable General Query Log, uncomment (or add) the relevant lines

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

Slow Query Log

To enable Slow Query Log, uncomment (or add) the relevant lines

log_slow_queries       = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

Restart MySQL server after changes

This method requires a server restart.

service mysql restart

or, using systemd

systemctl restart mysql.service

Enable logs at runtime

Since MySQL 5.1 you can enable and disable logs at runtime.

To enable logs at runtime, login to mysql client (mysql -u root -p ) and give:

SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';

To disable logs at runtime, login to mysql client (mysql -u root -p ) and give:

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

This method works on any platform and does not require a server restart.

Display log results

Error log

With the above settings, you can display Error log using

tail -f /var/log/syslog

REMARK: If you do not specify Error log file, MySQL keeps Error log at data dir (usually /var/lib/mysql in a file named {host_name}.err).

General Query log

With the above settings, you can display General log using

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

REMARK: If you do not define General log file, MySQL keeps General log at data dir (usually /var/lib/mysql in a file named {host_name}.log).

Slow Query log

With the above settings, you can display Slow Query log using

tail -f /var/log/mysql/mysql-slow.log

REMARK: If you do not specify Slow Query log file, MySQL keeps Slow Query log at data dir (usually /var/lib/mysql in a file named {host_name}-slow.log).

Log rotation

Do NOT ever forget to rotate logs. Otherwise, log files may become huge.

In Debian (and Debian derivatives as Ubuntu etc) log rotation using logrotate is already present after initial server setup (“Debian packages pre-configuration”).

nano /etc/logrotate.d/mysql-server

in other distributions, some changes may be needed

# - 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 {
        daily
        rotate 7
        missingok
        create 640 mysql adm
        compress
        sharedscripts
        postrotate
                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 a error please report a bug.
                  #if ps cax | grep -q mysqld; then
                  if killall -q -s0 -umysql mysqld; then
                    exit 1
                  fi
                else
                  $MYADMIN flush-logs
                fi
        endscript
}

Check out your server configuration

TIP: Use show variables like '%log%'; to examine your server variables related to log files

root@cosmos ~ # mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 144332
Server version: 5.5.31-0+wheezy1 (Debian)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show variables like '%log%';
+-----------------------------------------+--------------------------------+
| Variable_name                           | Value                          |
+-----------------------------------------+--------------------------------+
| back_log                                | 50                             |
| binlog_cache_size                       | 32768                          |
| binlog_direct_non_transactional_updates | OFF                            |
| binlog_format                           | STATEMENT                      |
| binlog_stmt_cache_size                  | 32768                          |
| expire_logs_days                        | 10                             |
| general_log                             | OFF                            |
| general_log_file                        | /var/lib/mysql/cosmos.log      |
| innodb_flush_log_at_trx_commit          | 1                              |
| innodb_locks_unsafe_for_binlog          | OFF                            |
| innodb_log_buffer_size                  | 8388608                        |
| innodb_log_file_size                    | 5242880                        |
| innodb_log_files_in_group               | 2                              |
| innodb_log_group_home_dir               | ./                             |
| innodb_mirrored_log_groups              | 1                              |
| log                                     | OFF                            |
| log_bin                                 | OFF                            |
| log_bin_trust_function_creators         | OFF                            |
| log_error                               |                                |
| log_output                              | FILE                           |
| log_queries_not_using_indexes           | OFF                            |
| log_slave_updates                       | OFF                            |
| log_slow_queries                        | OFF                            |
| log_warnings                            | 1                              |
| max_binlog_cache_size                   | 18446744073709547520           |
| max_binlog_size                         | 104857600                      |
| max_binlog_stmt_cache_size              | 18446744073709547520           |
| max_relay_log_size                      | 0                              |
| relay_log                               |                                |
| relay_log_index                         |                                |
| relay_log_info_file                     | relay-log.info                 |
| relay_log_purge                         | ON                             |
| relay_log_recovery                      | OFF                            |
| relay_log_space_limit                   | 0                              |
| slow_query_log                          | OFF                            |
| slow_query_log_file                     | /var/lib/mysql/cosmos-slow.log |
| sql_log_bin                             | ON                             |
| sql_log_off                             | OFF                            |
| sync_binlog                             | 0                              |
| sync_relay_log                          | 0                              |
| sync_relay_log_info                     | 0                              |
+-----------------------------------------+--------------------------------+
41 rows in set (0.00 sec)

Server variables official documentation is available here.

When to enable logs

Using default MySQL setup, all log files are NOT enabled (except the error log on Windows). Default Debian setup sends Error log to syslog.

Actually, there many situations where log files can provide solutions to critical problems:

  • Always enable Error log.
  • Enable General query log (preferably at runtime) when you want to
    • check that your application handles correctly MySQL database connection (a common mistake is to connect multiple times to MySQL from a single script)
    • monitor executed queries from your application
    • test memcached (or similar software), checking out if a query was executed or memcached has handled the request
  • Enable Slow Query log (preferably from MySQL configuration file for a short period of time, e.g. 2-3 days) when your application performance is reduced for some reason and you should detect the slow queries.

Example

Here is an example of MySQL General query log:

131021 17:43:50    43 Connect   root@localhost as anonymous on pnet_blog
                   43 Init DB   pnet_blog
                   43 Query     SELECT count(id) as total_posts FROM posts WHERE date_published is not null AND date_published <= '20131021144350'
                   43 Query     SELECT * FROM posts WHERE date_published is not null AND date_published <= '20131021144350' ORDER BY date_published DESC LIMIT 0,10
                   44 Connect   root@localhost as anonymous on pnet_blog
                   44 Query     SELECT id, title, impressions FROM tips WHERE date_published IS NOT NULL AND date_published <= '20131021144350' ORDER BY date_published DESC LIMIT 0, 10
                   44 Quit
                   43 Quit
131021 17:44:28    45 Connect   root@localhost as anonymous on pnet_blog
                   45 Init DB   pnet_blog
                   45 Query     SELECT * FROM posts WHERE url='how-and-when-to-enable-mysql-logs'
                   45 Query     UPDATE posts SET impressions=impressions+1 WHERE id='41'
                   45 Query     SELECT url, post_title FROM posts WHERE date_published IS NOT NULL AND date_published < '20131020150000' ORDER BY date_published DESC LIMIT 0,1
                   45 Query     SELECT url, post_title FROM posts WHERE date_published IS NOT NULL AND date_published > '20131020150000' ORDER BY date_published ASC LIMIT 0,1
                   45 Query     SELECT * FROM posts WHERE date_published is not null AND date_published <= '20131021144428' AND date_published >= '20130421144428' ORDER BY impressions DESC LIMIT 0,10
                   46 Connect   root@localhost as anonymous on pnet_blog
                   46 Query     SELECT id, title, impressions FROM tips WHERE date_published IS NOT NULL AND date_published <= '20131021144428' ORDER BY date_published DESC LIMIT 0, 10
                   46 Quit
                   45 Quit