Necessary knowledge points of MySQL log file development
Reference source: juejin.im/post/5b7c0aabf265da438415b9eb
preface
Log files record all kinds of activities that affect MySQL database. Common log files in MySQL database include error log, binary log, slow query log and query log. They are introduced below.
Error log
The error log file records the start-up, operation and shutdown process of MySQL.
mysql> show variables like 'log_error'; +---------------+---------------------+ | Variable_name | Value | +---------------+---------------------+ | log_error | /var/log/mysqld.log | +---------------+---------------------+ 1 row in set (0.03 sec)
You can see the path and file name of the error log. By default, the file name of the error file is the host name of the server, that is: hostname.err . But I set / var / Log here/ mysqld.log You can modify the error log address in / etc/my.cnf Add
# Recommended in standard MySQL setup sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
When MySQL database fails to start normally, the first file to be searched is the error log file, which records the error information and can help us find the problem.
Slow query log
The slow query log is used to record the SQL statements whose response time exceeds the threshold value, so we can set a threshold to record all SQL statements whose run time exceeds the threshold value to the slow query log file. The threshold can be set by the parameter long_query_time. The default is 10 seconds.
Start slow query log
By default, MySQL database does not start slow query log. You need to manually set this parameter to ON, and then start
mysql> show variables like "%slow%"; +---------------------------+-------------------------------------------------+ | Variable_name | Value | +---------------------------+-------------------------------------------------+ | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/iz2zeaf3cg1099kiidi06mz-slow.log | +---------------------------+-------------------------------------------------+ 5 rows in set (0.00 sec) mysql> set global slow_query_log='ON'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like "slow_query_log"; +---------------------------+-------------------------------------------------+ | Variable_name | Value | +---------------------------+-------------------------------------------------+ | | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/iz2zeaf3cg1099kiidi06mz-slow.log | +---------------------------+-------------------------------------------------+ 2 rows in set (0.00 sec)
But use set global slow_query_log = 'ON' enables the slow query log, which is only valid for the current database. If the MySQL database is restarted, it will be invalid. So if you want to take effect permanently, you have to modify the configuration file my.cnf (the same is true for other system variables), as follows:
[mysqld] slow_query_log=1
Then restart MySQL to enable slow query logging. The path of the log file is slow above_ query_ log_ File.
Set threshold
mysql> show variables like 'long_query_time'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec)
The default threshold is 10 seconds. We can modify the threshold size. For example (this is still valid for the current database)
mysql> set global long_query_time=0.05; Query OK, 0 rows affected (0.00 sec)
Set long_ query_ After the threshold of time, the MySQL database records all SQL statements whose run time exceeds this value, but the runtime is exactly equal to long_query_time is not recorded. Set long_query_time is 0 to capture all queries
Parameter log_queries_not_using_indexes
Another parameter related to slow query logs is log_queries_not_using_indexes,
If the running SQL statement does not use an index, the MySQL database will also record the SQL statement to the slow query log file. First, make sure that log is turned on_ queries_ not_ using_ indexes;
mysql> show variables like 'log_queries_not_using_indexes'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | ON | +-------------------------------+-------+ 1 row in set (0.12 sec)
For example, the index is not used for query:
mysql> explain select * from vote_record_memory where vote_id = 323; +----+-------------+--------------------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | vote_record_memory | ALL | NULL | NULL | NULL | NULL | 149272 | Using where | +----+-------------+--------------------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (1.56 sec)
You can see that a full table scan has been performed. Then go to the log log file to see that this SQL has been marked as slow SQL because it does not use an index.
# Time: 180817 11:42:59 # User@Host: root[root] @ [117.136.86.151] Id: 2625 # Query_time: 0.016542 Lock_time: 0.000112 Rows_sent: 142 Rows_examined: 149272 SET timestamp=1534477379; select * from vote_record_memory where vote_id = 323;
Put the log record into the table
mysql 5.1 can start to put the log records of slow queries into a table called slow in mysql database_ Log
| slow_log | CREATE TABLE `slow_log` ( `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `user_host` mediumtext NOT NULL, `query_time` time NOT NULL, `lock_time` time NOT NULL, `rows_sent` int(11) NOT NULL, `rows_examined` int(11) NOT NULL, `db` varchar(512) NOT NULL, `last_insert_id` int(11) NOT NULL, `insert_id` int(11) NOT NULL, `server_id` int(10) unsigned NOT NULL, `sql_text` mediumtext NOT NULL, `thread_id` bigint(21) unsigned NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' |
Parameter log_output specifies the output format of slow query. It is file by default. You can set it to table, which will become slow above_ Log
mysql> show variables like "log_output"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | FILE | +---------------+-------+ 1 row in set (0.19 sec)
However, in most cases, it is unnecessary to do so, which not only has a great impact on performance, but also supports microsecond level information when logging slow queries to files. However, recording slow queries to tables will lead to time granularity degradation to seconds, while the log of second level slow queries is not of great significance
Slow query log analysis tool
mysqldumpslow command
When more and more SQL queries are recorded in slow query log files, it is not easy to directly view the log files. MySQL provides the mysqldumpslow command to solve this problem
[root@iz2zeaf3cg1099kiidi06mz mysql]# mysqldumpslow iz2zeaf3cg1099kiidi06mz-slow.log Reading mysql slow query log from iz2zeaf3cg1099kiidi06mz-slow.log Count: 1 Time=60.02s (60s) Lock=0.00s (0s) Rows=149272.0 (149272), root[root]@[117.136.86.151] select * from vote_record_memory Count: 1 Time=14.85s (14s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[117.136.86.151] CALL add_vote_memory(N) Count: 1 Time=1.72s (1s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[117.136.86.151] INSERT into vote_record SELECT * from vote_record_memory Count: 1 Time=0.02s (0s) Lock=0.00s (0s) Rows=142.0 (142), root[root]@[117.136.86.151] select * from vote_record_memory where vote_id = N
PT query digest tool
PT query digest is the most powerful tool for analyzing MySQL query logs. It can analyze binlog, Generallog and slowlog, and can also analyze MySQL protocol data captured by show processlist or tcpdump. It is more specific and perfect than mysqldumpslow. The following is an example of using Pt query Digest:
//Direct analysis of slow query files pt-query-digest slow.log > slow_report.log
The tool can print out the analysis report of query and output the analysis results to the file. The analysis process is to parameterize the conditions of query statements, and then group statistics the queries after parameterization, and count the execution time, times and proportion of each query, so as to find out the problems and optimize them with the help of the analysis results.
For more information on the installation and use of Pt query digest, please refer to:
www.ywnds.com/?p=8179
Query log
The view log records all requests to the MySQL database, regardless of whether they were executed correctly or not. The default is host name. Log
mysql> show variables like "general_log%"; +------------------+--------------------------------------------+ | Variable_name | Value | +------------------+--------------------------------------------+ | general_log | OFF | | general_log_file | /var/lib/mysql/iz2zeaf3cg1099kiidi06mz.log | +------------------+--------------------------------------------+ 2 rows in set (0.24 sec)
By default, the query log is not started. It must be enabled first.
mysql> set global general_log='ON'; Query OK, 0 rows affected (0.05 sec) mysql> show variables like "general_log%"; +------------------+--------------------------------------------+ | Variable_name | Value | +------------------+--------------------------------------------+ | general_log | ON | | general_log_file | /var/lib/mysql/iz2zeaf3cg1099kiidi06mz.log | +------------------+--------------------------------------------+ 2 rows in set (0.11 sec)
Binary log
Binary log records all operations that make changes to the database, but does not include operations such as select and show, because these operations do not modify the data itself. If you want to record select and show operations, you can only use query logs instead of binary logs.
In addition, the binary also includes information such as the time and execution time of the database change operation. Binary log has the following functions:
- **Recovery: * * binary logs are required for the recovery of some data. For example, after a database full backup file is recovered, we can use the binary log for point in time recovery
- Replication: by copying and executing binary logs, a remote MySQL database (usually slave or standby) can synchronize with a MySQL database (usually master or primary) in real time
- **Audit: * * users can audit the information in the binary log to determine whether there is an injection attack on the database
Turn on binary log
The binary log can be started by configuring the parameter log bin [= name]. If no name is specified, the default binary log file name is the host name, and the suffix name is the serial number of the binary log
[mysqld] log-bin
mysql> show variables like 'datadir'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+ 1 row in set (0.00 sec)
mysqld-bin.000001 is the binary log file, and mysqld-bin.index It is a binary index file. In order to manage all binlog files, MySQL creates an additional index file, which records all binlog files used by MySQL in sequence. If you want to customize the name of the index file, you can set the log_bin_index=file parameter.
-rw-rw---- 1 mysql mysql 120 Aug 21 16:42 mysqld-bin.000001 -rw-rw---- 1 mysql mysql 20 Aug 21 16:42 mysqld-bin.index
View binary log file
For binary log files, unlike the error log files and slow query log files, which can be viewed with cat, head, tail and other commands, it needs to use the MySQL tool mysqlbinlog. For example:
[root@iz2zeaf3cg1099kiidi06mz mysql]# mysqlbinlog mysqld-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #180821 16:42:53 server id 1 end_log_pos 120 CRC32 0x3e55be40 Start: binlog v 4, server v 5.6.39-log created 180821 16:42:53 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' jdB7Ww8BAAAAdAAAAHgAAAABAAQANS42LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAACN0HtbEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAUC+ VT4= '/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
Binary log file configuration parameters
The following is a brief introduction to several important configuration parameters of binary log file
- max_binlog_size
You can go through max_binlog_size parameter to limit the size of a single binlog file (1g by default)
- binlog_cache_size
When a transaction table storage engine (such as InnoDB storage engine) is used, all uncommitted binary logs will be recorded into a buffer. When the transaction is committed, the binary logs in the cache will be directly written to the binary log file, and the size of the buffer is determined by binlog_ cache_ The default size is 32K.
In addition, binlog_ cache_ The size is based on the session. When each thread starts a transaction, MySQL will automatically allocate a binlog size_ cache_ Cache of size
mysql> show variables like 'binlog_cache_size'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | binlog_cache_size | 32768 | +-------------------+-------+ 1 row in set (0.00 sec)
- sync_binlog
By default, binary logs are not synchronized to disk every time they are written. Parameter sync_binlog = [N] indicates that every write buffer is synchronized to disk. If N is set to 1, it is sync_binlog = 1 means that the binary log is written synchronously to the disk. At this time, the write operation does not need to use the operating system buffer to write the binary log
- binlog_format
binlog_ The format parameter is very important. It affects the format of binary log. There are three formats:
1. Statement: records the logical SQL statement of the log
2. Row: record the row changes of the table
3. mixed: in this format, mysql uses statement format to record binary log files by default. However, ROW format is used in some cases, including the following situations:
- The storage engine of the table is NDB. At this time, DML operations on the table will be recorded in ROW format.
- UUID(), user(), current are used_ USER(),FOUND_ROW(),ROW_COUNT() and other uncertain functions.
- The INSERT DELAY statement is used.
- User defined functions (UDF s) are used.
- temporary table is used.
reference resources https://book.douban.com/subject/24708143/http / / search.dangdang.com/?key=%E9%AB%98%E6%80%A7%E8%83%BDMySQL