When and how do I enable MySql logs ?, Enable mysql Log
If you are a web developer and want to debug your application or improve its performance, you need to refer to various log files. Logs are the best choice for troubleshooting. For the famous MySql database server, you need to refer to the following log files:
- Error Log: It contains the error information generated when the server is running (of course, when the service is started and stopped ).
- Common query log: This is a general log that records what mysqld is doing (connection, disconnection, query ).
- Slow query log: As its name indicates, it records SQL statements for "slow" queries.
Binary logs are not involved in this article. Binary logs require extremely high server hardware configurations and are only useful in specific scenarios (such as master-slave replication, master-slave installation, and recovery of some data. Otherwise, it is a real "performance Killer ".
For more information about MySql logs, see http://dev.mysql.com/doc/refman/5.7/en/server-logs.html.
Use MySql configuration to enable the location of log-related parameters
[Mysqld].
Edit the MySql configuration file:
Nano/etc/mysql/my. cnf
The above is the default installation directory In Debian. Other Linux versions may not be the same. The MySql Server parameters in this file are as follows:
# * 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. 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 = 100 M
# Binlog_do_db = include_database_name
# Binlog_ignore_db = include_database_name
By default, MySql installation does not enable all log files (except for error logs on Windows ). When MySql is installed in Debian, the error log is sent to syslog by default.
The error log is configured according to/etc/mysql/conf. d/mysqld_safe_syslog.cnf. The error log is pushed to syslog:
[Mysqld_safe]
Syslog
This is a recommended practice. If you do not want to push the error log to syslog for some reason, push/etc/mysql/conf. d/mysqld_safe_syslog.cnf file, or delete the file directly, and then in/etc/mysql/my. add the following lines to cnf:
[Mysqld_safe]
Log_error =/var/log/mysql/mysql_error.log
[Mysqld]
Log_error =/var/log/mysql/mysql_error.log
Generally, to query logs, you must enable general query logs and uncomment (or add) the relevant lines:
General_log_file =/var/log/mysql. log
General_log = 1
To enable the slow query log for slow query, uncomment (or add) the related lines:
Log_slow_queries =/var/log/mysql/mysql-slow.log.
Long_query_time = 2
Log-queries-not-using-indexes
After the configuration is modified, restart the MySql server:
Service mysql restart
Or use systemd:
Systemctl restart mysql. service
After MySql 5.1 is enabled during runtime, we can enable or disable logs during runtime.
Log on to the MySql client (mysql-u root-p) and run the following command:
Set global general_log = 'on ';
Set global slow_query_log = 'on ';
Disable logs during running. log on to the Mysql client (mysql-u root-p) and run the following command:
Set global general_log = 'off ';
Set global slow_query_log = 'off ';
This method is applicable to all platforms and does not require service restart.
After setting the error log in the preceding way, you can run the following command to display the error log:
Tail-f/var/log/syslog
Note: If you have not configured the error Log File, MySql will save the error log in the data directory (usually/var/lib/mysql) named {host_name }. err file.
After setting common query logs in the preceding way, you can use the following command to display common logs:
Tail-f/var/log/mysql. log
Note: If you do not configure a common log file, MySql will save the common log in the data directory (usually/var/lib/mysql) named {host_name }. log File.
After setting the slow query log in the preceding way, you can use the following command to display the slow query log:
Tail-f/var/log/mysql/mysql-slow.log
Note: If you have not configured the slow query log file, MySql will save the common log in the data directory (usually/var/lib/mysql) named {host_name}-slow. log File.
Do not forget to scroll logs in cyclic logs; otherwise, log files may become very large.
In Debian (and Debian-derived series such as Ubuntu) systems, after the initial installation of MySql, the loop log has 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 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 the server configuration using show variables like '% log %'; to check the variables related to the server and 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,201 3, 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 |
| Bin log_cache_size | 32768 |
| Binlog_direct_non_transactional_updates | OFF |
| Binlog_format | STATEMENT |
| Binlog_stmt_cache_size | 1, 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 | 1, 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)
For more information about server variables, see http://dev.mysql.com/doc/refman/5.7/en/server-options.html.
When to enable log MySql by default, all log files will not be enabled (except for the error log on Windows ). By default, the error log is sent to syslog when Debian is installed.
In fact, in many cases, log files can provide solutions to key problems:
- Always enable error Log
- In these cases, enable normal query logs (preferably at runtime ): check whether your application correctly processes the MySql database connection (a common mistake is to connect to MySql multiple times from a single script); monitor the query execution from your application; test memcached (or similar software) and check whether a query is executed by the database or processed by memcached.
- When your application causes performance degradation for some reason and you want to find these slow queries, enable the slow query log (MySql is best configured in a short period of time, such as 2-3 days)
The following is an example of a MySql common 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 <= '20140901'
43 Query SELECT * FROM posts WHERE date_published is not null AND date_published <= '000000' order by date_published desc limit 20131021144350
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 <= '000000' 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 <'000000' order by date_published desc limit 20131020150000
45 Query SELECT url, post_title FROM posts WHERE date_published is not null and date_published> '000000' order by date_published asc limit 20131020150000
45 Query SELECT * FROM posts WHERE date_published is not null AND date_published <= '000000' AND date_published> = '000000' order by impressions desc limit 20131021144428, 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 <= '000000' order by date_published desc limit 0, 10
46 Quit
45 Quit
Original article: http://www.pontikis.net/blog/how-and-when-to-enable-mysql-logs.
