If you are a web developer. If you want to debug your app or improve its performance, you need to go to a variety of 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 includes error messages that occur when the server executes (including, of course, when the service starts and stops)
- Normal query Log : This is a record of what mysqld is doing (connection. Disconnect, query) of the general log
- Slow query log : As its name, it records a "slow" query SQL statement
Binary logs are not involved in this article. Binary logs require a very high server hardware configuration. And only in specific scenarios (for example, master-slave replication, master-slave installation.) Recovery operations for some data) are useful.
Otherwise, it is a real "performance killer".
The official documentation for MYSQL logs is 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 folder under Debian, other Linux releases may be different, and the MYSQL server in this file has the following examples:
# * 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 is not enabled for 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。 Let's say you don't want to say the error log to the syslog for some reason. Note the above line in the/etc/mysql/conf.d/mysqld_safe_syslog.cnf file, or delete the file directly. Then add the following line to the/ETC/MYSQL/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, the related rows can be canceled (or added) to:
General_log_file =/var/log/mysql/mysql.log
General_log = 1
Slow query log to enable the slow query log, the related rows can be removed from staring (or joining) will be:
log_slow_queries =/var/log/mysql/mysql-slow.log
Long_query_time = 2
Log-queries-not-using-indexes
Configuration modification After restarting MYSQL server The above methods require service restart talent to take effect:
Service MySQL Restart
or use SYSTEMD:
Systemctl Restart Mysql.service
Log on at execution time after MySQL 5.1 We can enable or disable the log at execution time.
Enable logging at execution time. Log in to the MYSQL client (mysql-u root-p) and then execute:
SET GLOBAL general_log = ' on ';
SET GLOBAL slow_query_log = ' on ';
Disable log on execution, log in to 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: Assume that you have not configured the error log file. MYSQL will save the error log in a file named {Host_name}.err, under the Data folder (typically/var/lib/mysql).
The normal query log is set after the above method. You can display the normal log by using the following command:
tail-f/var/log/mysql/mysql.log
Note: Suppose you do not have a normal log file configured. MYSQL will save the normal log in the Data folder (typically/var/lib/mysql) under a file named {Host_name}.log.
The slow query log is set later by 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 folder (typically/var/lib/mysql) under a file named {Host_name}-slow.log.
Cycle logs don't forget to scroll through the logs. 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. Some modifications may be required:
#-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 variables related to official documents 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 very 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 execution 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 the short term, for example, 2-3 days)
Demo sample Below is a demo sample of a MySql normal 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?
