vk | jquery autocomplete ajax | jquery ui autocomplete ajax json | install harden debian
 


 

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

Related Posts

You may also be interested in

Εγγραφή!
Sign-up for our free email newsletter. Get updates when new tutorials and tips are published. You can unsubscribe anytime with a click.

Your comments are welcomed!

This site actively encourages commenting on any post. Comments are not pre-moderated, but this community does not tolerate direct or indirect attacks, name-calling or insults. Please, read terms of use and Comment Policy at privacy policy.

comments powered by Disqus
Dynamically Bind Params in Prepared Statements with MySQLi PHP and Javascript Internationalization using Gettext and Poedit