If you are a web developer, if you want to debug your app or improve its performance, then you need to refer to various log files. Logging is the best choice to start troubleshooting. As far as the famous MYSQL database server is concerned, you need to refer to the following log files:
- error Log : It contains error messages that occur when the server is running (including, of course, when the service starts and stops)
- General Query Log : This is a general log of what MYSQLD is doing (connection, disconnect, query)
- Slow query log : As its name, it records a "slow" query SQL statement
Binary logs are not involved in this article. Binary logs require very high server hardware configuration and are only useful in certain scenarios (for example, master-slave replication, master-slave installation, some data recovery operations). Otherwise, it is a real "performance killer".
The official documentation for MYSQL logs reference http://dev.mysql.com/doc/refman/5.7/en/server-logs.html.
Enable log log related parameters via MYSQL configuration in
[Mysqld]Part.
To edit a MySql configuration file:
nano/etc/mysql/my.cnf
The above is the default installation directory under Debian, other Linux distributions may be different, the parameters of MYSQL server in this file are as follows:
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware the 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's 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 used as easy-to-replay backup logs or for replication.
# note:if You is setting up a replication slave, see README. Debian about
# Other settings-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
MYSQL installation By default does not enable all log files (except for the error log under the Windows platform). Debian install MYSQL By default, the error log is sent to the syslog.
The error log is based on the/ETC/MYSQL/CONF.D/MYSQLD_SAFE_SYSLOG.CNF configuration and the error log is pushed to the syslog:
[Mysqld_safe]
Syslog
This is the recommended practice。 If for some reason you do not want to tell the error log to the syslog,/etc/mysql/conf.d/mysqld_safe_syslog.cnf the above line in the file, or delete the file directly, and then/etc/mysql/ Add the following line to the MY.CNF:
[Mysqld_safe]
Log_error=/var/log/mysql/mysql_error.log
[Mysqld]
Log_error=/var/log/mysql/mysql_error.log
General query log to enable the General query log, uncomment (or add) the related rows:
General_log_file =/var/log/mysql/mysql.log
General_log = 1
Slow query log to enable the slow query log, uncomment the related rows (or add them):
log_slow_queries =/var/log/mysql/mysql-slow.log
Long_query_time = 2
Log-queries-not-using-indexes
Restart MYSQL server After configuration modification the above method requires a service restart to take effect:
Service MySQL Restart
or use SYSTEMD:
Systemctl Restart Mysql.service
Runtime enable logging after MySQL 5.1 We can enable or disable logging at run time.
The runtime enables logging and logs on to the MYSQL client (mysql-u root-p) and then execute:
SET GLOBAL general_log = ' on ';
SET GLOBAL slow_query_log = ' on ';
Disable logging at run time and log in to the Mysql client (mysql-u root-p) After execution:
SET GLOBAL general_log = ' OFF ';
SET GLOBAL slow_query_log = ' OFF ';
This approach applies to all platforms and does not require a restart of the service.
Show log results error log as set above, you can display the error log with the following command:
tail-f/var/log/syslog
Note: If you do not configure the error log file, MYSQL will save the error log in a file named {Host_name}.err, under the data directory (usually/var/lib/mysql).
Normal query log as set out above, you can display the normal log by using the following command:
tail-f/var/log/mysql/mysql.log
Note: If you do not configure the normal log file, MYSQL will save the normal log in the data directory (usually/var/lib/mysql) under a file named {Host_name}.log.
Slow query log after you set the above method, you can display the slow query log by using the following command:
tail-f/var/log/mysql/mysql-slow.log
Note: If you do not configure the slow query log file, MYSQL will save the normal log in the data directory (usually/var/lib/mysql) under a file named {Host_name}-slow.log.
Circular logs do not forget to scroll the log, otherwise the log file may become very large.
In Debian (and Debian-derived series such as Ubuntu), after the initial MYSQL installation, the circular log has been used Logrotate:
Nano/etc/logrotate.d/mysql-server
For other Linux distributions, you may need to make some changes:
#-I put everything in one block and added sharedscripts, so this MySQL gets# flush-logs ' d only once.# Else the Binar Y logs would automatically increase by N times every day.#-the error log was 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 C reate 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 isn't 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}
Verify that the server configuration usesShow variables like '%log% ';To check the server and log file related variables:
[Email protected] ~ #
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) and/or, Oracle, its affiliates. All rights reserved.
Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names 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 |
+-----------------------------------------+--------------------------------+
$ rows in Set (0.00 sec)
Server variable related official document reference http://dev.mysql.com/doc/refman/5.7/en/server-options.html.
When the log mysql default installation is enabled, all log files are not enabled (except for the error log on the Windows platform). The error log is sent to Syslog by default on Debian.
In fact, in many cases, log files can provide a solution to a critical problem:
- Always enable the error log
- In these cases, open the normal query log (preferably at run time): Check that your app handles MySQL database connections correctly (a common mistake is to connect to MySQL multiple times from a single script), monitor the execution of queries from your app, test memcached ( or similar software), check whether a query is executed by DB or memcached
- When your app is causing performance degradation for some reason and you want to find these slow queries, enable slow query logging (MYSQL is best configured in a short time, such as 2-3 days)
Example below is an example of a MySql plain log:
131021 17:43:50 Connect [email protected] as anonymous on Pnet_blog
Init DB Pnet_blog
Query SELECT count (id) as total_posts from posts WHERE date_published are not null and date_published <= ' 20131021144 350 '
Query SELECT * From posts WHERE date_published are not null and date_published <= ' 20131021144350 ' ORDER by Date_publ ished DESC LIMIT 0,10
+ Connect [email protected] as anonymous on Pnet_blog
Query SELECT ID, title, impressions from tips WHERE date_published are not NULL and date_published <= ' 20131021144350 ' ORDER by date_published DESC LIMIT 0, 10
+ Quit
Quit
131021 17:44:28 Connect [email protected] as anonymous on Pnet_blog
Init DB Pnet_blog
Query SELECT * from posts WHERE url= ' how-and-when-to-enable-mysql-logs '
Query UPDATE posts SET impressions=impressions+1 WHERE id= ' 41 '
Query SELECT URL, post_title from posts WHERE date_published are not NULL and date_published < ' 20131020150000 ' ORDER by date_published DESC LIMIT 0,1
Query SELECT URL, post_title from posts WHERE date_published are not NULL and date_published > ' 20131020150000 ' ORDER by date_published ASC LIMIT 0,1
Query SELECT * From posts WHERE date_published are not null and date_published <= ' 20131021144428 ' and date_published >= ' 20130421144428 ' ORDER by Impressions DESC LIMIT 0,10
protected Connect [email] as anonymous on Pnet_blog
* Query SELECT ID, title, impressions from tips WHERE date_published are not NULL and date_published <= ' 20131021144428 ' ORDER by date_published DESC LIMIT 0, 10
$ Quit
$ Quit
Original link: http://www.pontikis.net/blog/how-and-when-to-enable-mysql-logs.
When and how do I turn on MYSQL logs?
