You may not be outstanding, but you must be different
MYSQL common log files
The log file records various types of activities that affect the mysql database, including common log files
- Error log
- Binary log
- Slow query log
- Query log
Error log
The error log file records the startup, operation and shutdown processes of MYSQL. When MYSQL DBA encounters a problem, it should first check this file to locate the problem. This file records not only all error information, but also some warning information or other correct information. You can use SHOW VARIABLES LIKE 'log_error 'to locate the file address
mysql> SHOW VARIABLES LIKE 'log_error'
-> ;
+---------------+-----------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------+
| log_error | C:\web\mysql-8.0.17-winx64\Data\LAPTOP-9GF1412J.err |
+---------------+-----------------------------------------------------+
1 row in set, 2 warnings (0.06 sec)
mysql>
It is better to find the error log file. For example, the type command on window and the tail command on linux
C:\web\mysql-8.0.17-winx64\Data>TYPE LAPTOP-9GF1412J.err
2019-09-15T05:54:18.632334Z 0 [System] [MY-010116] [Server] C:\web\mysql-8.0.17-winx64\bin\mysqld (mysqld 8.0.17) starting as process 19776 2019-09-15T05:54:18.634382Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 2019-09-15T05:54:20.249870Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2019-09-15T05:54:20.288563Z 0 [System] [MY-010931] [Server] C:\web\mysql-8.0.17-winx64\bin\mysqld: ready for connections. Version: '8.0.17' socket: '' port: 3306 MySQL Community Server - GPL. 2019-09-15T05:54:20.340626Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060 2019-09-16T00:44:29.111088Z 0 [System] [MY-010116] [Server] C:\web\mysql-8.0.17-winx64\bin\mysqld (mysqld 8.0.17) starting as process 6636 2019-09-16T00:44:29.127072Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 2019-09-16T00:44:31.680776Z 0 [System] [MY-010229] [Server] Starting crash recovery... 2019-09-16T00:44:31.694672Z 0 [System] [MY-010232] [Server] Crash recovery finished. 2019-09-16T00:44:32.974658Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2019-09-16T00:44:33.164741Z 0 [System] [MY-010931] [Server] C:\web\mysql-8.0.17-winx64\bin\mysqld: ready for connections. Version: '8.0.17' socket: '' port: 3306 MySQL Community Server - GPL. 2019-09-16T00:44:33.184007Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060 2019-09-19T09:13:39.751537Z 0 [System] [MY-013105] [Server] C:\web\mysql-8.0.17-winx64\bin\mysqld: Normal shutdown. 2019-09-19T09:13:40.430804Z 0 [System] [MY-010910] [Server] C:\web\mysql-8.0.17-winx64\bin\mysqld: Shutdown complete (mysqld 8.0.17) MySQL Community Server - GPL. 2019-09-19T09:14:12.127306Z 0 [System] [MY-010116] [Server] C:\web\mysql-8.0.17-winx64\bin\mysqld (mysqld 8.0.17) starting as process 6944 2019-09-19T09:14:12.147239Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 2019-09-19T09:14:15.728404Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2019-09-19T09:14:15.771856Z 0 [System] [MY-010931] [Server] C:\web\mysql-8.0.17-winx64\bin\mysqld: ready for connections. Version: '8.0.17' socket: '' port: 3306 MySQL Community Server - GPL. 2019-09-19T09:14:15.873162Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060 2019-09-20T13:28:09.102441Z 0 [System] [MY-010116] [Server] C:\web\mysql-8.0.17-winx64\bin\mysqld (mysqld 8.0.17) starting as process 7028 2019-09-20T13:28:09.116426Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 2019-09-20T13:28:10.970669Z 0 [System] [MY-010229] [Server] Starting crash recovery... 2019-09-20T13:28:10.984508Z 0 [System] [MY-010232] [Server] Crash recovery finished. 2019-09-20T13:28:12.200807Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2019-09-20T13:28:12.260367Z 0 [System] [MY-010931] [Server] C:\web\mysql-8.0.17-winx64\bin\mysqld: ready for connections. Version: '8.0.17' socket: '' port: 3306 MySQL Community Server - GPL. 2019-09-20T13:28:12.539551Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060 2019-09-20T22:11:24.815229Z 0 [System] [MY-010116] [Server] C:\web\mysql-8.0.17-winx64\bin\mysqld (mysqld 8.0.17) starting as process 7052 2019-09-20T22:11:24.829678Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
Slow query log
Slow query log can help DBA locate SQL statements that may have problems, so as to optimize the SQL statement level. For example, a threshold can be set at MYSQL startup to record all SQL statements that run longer than this value in the slow query log file. DBA will check them every day or after a period of time to confirm whether there are SQL statements that need to be optimized
The threshold can be through long_query_time is set. The default value is 10, representing 10 seconds
By default, MYSQL database does not start slow query. You need to manually set a parameter to ON
mysql> show variables like '%quer%'; +----------------------------------------+----------------------------------------------------------+ | Variable_name | Value | +----------------------------------------+----------------------------------------------------------+ | binlog_rows_query_log_events | OFF | | ft_query_expansion_limit | 20 | | have_query_cache | NO | | log_queries_not_using_indexes | OFF | | log_throttle_queries_not_using_indexes | 0 | | long_query_time | 10.000000 | | query_alloc_block_size | 8192 | | query_prealloc_size | 8192 | | slow_query_log | OFF | | slow_query_log_file | C:\web\mysql-8.0.17-winx64\Data\LAPTOP-9GF1412J-slow.log | +----------------------------------------+----------------------------------------------------------+ 10 rows in set, 2 warnings (0.00 sec)
Modify my Ini configuration file and restart mysql
slow_query_log=TRUE slow_query_log_file= C:/web/mysql-8.0.17-winx64/Data/LAPTOP-9GF1412J-slow.log long_query_time=5
mysql> show variables like '%quer%'; +----------------------------------------+----------------------------------------------------------+ | Variable_name | Value | +----------------------------------------+----------------------------------------------------------+ | binlog_rows_query_log_events | OFF | | ft_query_expansion_limit | 20 | | have_query_cache | NO | | log_queries_not_using_indexes | OFF | | log_throttle_queries_not_using_indexes | 0 | | long_query_time | 5.000000 | | query_alloc_block_size | 8192 | | query_prealloc_size | 8192 | | slow_query_log | ON | | slow_query_log_file | C:/web/mysql-8.0.17-winx64/Data/LAPTOP-9GF1412J-slow.log | +----------------------------------------+----------------------------------------------------------+ 10 rows in set, 2 warnings (0.01 sec)
At this time, the slow query log has been started
Attention
- Set long_ query_ After the threshold of time, the mysql database will record all SQL statements whose running time exceeds this value, but the case where the running time is exactly equal to will not be recorded, that is, mysql will judge that it is greater than long_query_time. At the same time, the situation is recorded in subtle ways
- Another parameter related to slow query log is log_queries_not_using_indexes. If the running sql statement does not use an index, mysql will also record this statement in the slow query log, but first you need to turn on this parameter.
log_throttle_queries_not_using_indexes is mysql5 A parameter added in version 6.5 is used to indicate the number of SQL statements allowed to be recorded to the slow log without using the index per minute. The default value is 0. In the production environment, if the index is not used, such SQL statements will be frequently recorded to the slow log, resulting in the continuous increase of the size of the slow log file. Therefore, this parameter can be adjusted.
Slow query logs can grow rapidly when logging queries that do not use indexes. You can set log_throttle_queries_not_using_indexes The system variable sets rate limits for these queries. By default, this variable is 0, which means there is no limit. Positive values impose a per minute limit on the logging of queries that do not use indexes. The first such query opens a 60 second window in which the server records that the query reaches the given limit, and then suppresses other queries. If there are suppressed queries at the end of the window, the server records a summary indicating how many queries there are and the total time spent in these queries. When the server records the next query that does not use the index, the next 60 second window begins.
The server uses control parameters to determine whether to write queries to the slow query log in the following order:
- The query must not be a management statement, or log_slow_admin_statements Must be enabled.
- The query must take at least long_query_time A few seconds, or log_queries_not_using_indexes Must be enabled and the query does not use an index for row lookup.
- The query must have at least checked min_examined_row_limit that 's ok.
- Not based on log_throttle_queries_not_using_indexes Set suppress query.
The server does not record queries processed by the query cache.
Slow query log content
When the slow query log is enabled, the server writes the output to the log_output Any destination specified by the system variable. If logging is enabled, the server opens the log FILE and writes a startup message to it. However, queries are not further logged to the FILE unless the log target is selected for FILE. If the target is NONE, the server will not write any queries even if slow query logging is enabled. If FILE is not selected as the output destination, setting the log FILE name has no effect on logging.
If slow query log is enabled and FILE is selected as the output target, each statement written to the log is preceded by a line beginning with # characters and has the following fields (all fields in one line):
-
Query_time: *duration*
Statement execution time in seconds.
-
Lock_time: *duration*
Time to acquire the lock in seconds.
-
Rows_sent: *N*
The number of rows sent to the client.
-
Rows_examined:*``*
Number of rows checked by the server tier (excluding any processing inside the storage engine).
Each statement written to the slow query log file is preceded by a SET A statement that contains a timestamp indicating when a slow statement was recorded (occurs after the statement completes execution)
Query log
The query log records the information of all requests to MYSQL database, no matter whether these requests are executed correctly or not. The default file is the hostname log
Show variables like '% general'_ Log% 'can be used to check whether the query log is enabled. It is not enabled here
mysql> show variables like '%general_log%'; +------------------+-----------------------------------------------------+ | Variable_name | Value | +------------------+-----------------------------------------------------+ | general_log | OFF | | general_log_file | C:\web\mysql-8.0.17-winx64\Data\LAPTOP-9GF1412J.log | +------------------+-----------------------------------------------------+ 2 rows in set, 2 warnings (0.01 sec)
Via set global general_log = on enable query log
mysql> set global general_log = on; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%general_log%'; +------------------+-----------------------------------------------------+ | Variable_name | Value | +------------------+-----------------------------------------------------+ | general_log | ON | | general_log_file | C:\web\mysql-8.0.17-winx64\Data\LAPTOP-9GF1412J.log | +------------------+-----------------------------------------------------+ 2 rows in set, 2 warnings (0.00 sec)
The following are some records of the query log file, including the tables in the database data used, and even some requests for Access denied, including the failure to execute SQL statements correctly
C:\web\mysql-8.0.17-winx64\bin\mysqld, Version: 8.0.17 (MySQL Community Server - GPL). started with: TCP Port: 3306, Named Pipe: MySQL Time Id Command Argument 2021-06-24T07:44:51.426012Z 8 Query show variables like '%general_log%' 2021-06-24T07:46:19.304059Z 9 Connect root@localhost on using TCP/IP 2021-06-24T07:46:19.304324Z 9 Query /*!40101 set @@session.wait_timeout=28800 */ 2021-06-24T07:46:19.469827Z 10 Connect root@localhost on using TCP/IP 2021-06-24T07:46:19.470064Z 10 Query /*!40101 set @@session.wait_timeout=28800 */ 2021-06-24T07:46:19.645373Z 9 Query use `yddatabase` 2021-06-24T07:46:31.140310Z 9 Query show databases 2021-06-24T07:46:31.164709Z 11 Connect root@localhost on using TCP/IP 2021-06-24T07:46:31.164970Z 11 Query /*!40101 set @@session.wait_timeout=28800 */ 2021-06-24T07:46:31.167136Z 11 Query show databases 2021-06-24T07:46:31.173601Z 11 Query show full tables from `charroom` where table_type = 'BASE TABLE' 2021-06-24T07:46:31.180121Z 11 Query show create table `charroom`.`chatmessage` 2021-06-24T07:46:31.186464Z 11 Query show create table `charroom`.`user` 2021-06-24T07:46:31.187838Z 11 Query show full tables from `charroom` where table_type = 'VIEW' 2021-06-24T07:46:31.188995Z 11 Query SHOW TRIGGERS FROM `charroom` 2021-06-24T07:46:31.192482Z 11 Query show full tables from `checkdatabase` where table_type = 'BASE TABLE' 2021-06-24T07:46:31.193914Z 11 Query show create table `checkdatabase`.`collection_list` 2021-06-24T07:46:31.196756Z 11 Query show create table `checkdatabase`.`comment_list` 2021-06-24T07:46:31.199021Z 11 Query show create table `checkdatabase`.`explanation_list` 2021-06-24T07:46:31.200749Z 11 Query show create table `checkdatabase`.`letter_list` 2021-06-24T07:46:31.202090Z 11 Query show create table `checkdatabase`.`like_list` 2021-06-24T07:46:31.203227Z 11 Query show create table `checkdatabase`.`subject_list` 2021-06-24T07:46:31.204429Z 11 Query show create table `checkdatabase`.`user_list` 2021-06-24T07:46:31.205574Z 11 Query show full tables from `checkdatabase` where table_type = 'VIEW' 2021-06-24T07:46:31.206539Z 11 Query SHOW TRIGGERS FROM `checkdatabase` 2021-06-24T07:46:31.209658Z 11 Query show full tables from `comment` where table_type = 'BASE TABLE' 2021-06-24T07:46:31.210672Z 11 Query show create table `comment`.`comment` 2021-06-24T07:46:31.213173Z 11 Query show full tables from `comment` where table_type = 'VIEW' 2021-06-24T07:46:31.214167Z 11 Query SHOW TRIGGERS FROM `comment` 2021-06-24T07:46:31.217083Z 11 Query show full tables from `dachuang` where table_type = 'BASE TABLE' 2021-06-24T07:46:31.217909Z 11 Query show create table `dachuang`.`dc_race_list` 2021-06-24T07:46:31.219818Z 11 Query show create table `dachuang`.`dc_user_list` 2021-06-24T07:46:31.221181Z 11 Query show full tables from `dachuang` where table_type = 'VIEW' 2021-06-24T07:46:31.222128Z 11 Query SHOW TRIGGERS FROM `dachuang` 2021-06-24T07:46:31.224448Z 11 Query show full tables from `hoteldatabase` where table_type = 'BASE TABLE' 2021-06-24T07:46:31.225324Z 11 Query show create table `hoteldatabase`.`house_layout_list` 2021-06-24T07:46:31.227504Z 11 Query show create table `hoteldatabase`.`house_list` 2021-06-24T07:46:31.229187Z 11 Query show create table `hoteldatabase`.`order_list` 2021-06-24T07:46:31.230373Z 11 Query show create table `hoteldatabase`.`sys_log` 2021-06-24T07:46:31.231522Z 11 Query show create table `hoteldatabase`.`user_list` 2021-06-24T07:46:31.232863Z 11 Query show full tables from `hoteldatabase` where table_type = 'VIEW' 2021-06-24T07:46:31.233827Z 11 Query SHOW TRIGGERS FROM `hoteldatabase` 2021-06-24T07:46:31.237496Z 11 Query show full tables from `infodata` where table_type = 'BASE TABLE' 2021-06-24T07:46:31.238327Z 11 Query show full tables from `infodata` where table_type = 'VIEW'
Binary log
The binary log records all operations that make changes to the mysql database, but does not include operations such as select and show. Because such operations as like as two peas do not modify the database itself, but if the operation itself does not cause changes in the database itself, it is possible to import binary data to modify the table data of the database, but it is exactly the same as before.
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000626 | 155 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'binlog.000626'
-> ;
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| binlog.000626 | 4 | Format_desc | 1 | 124 | Server ver: 8.0.17, Binlog ver: 4 |
| binlog.000626 | 124 | Previous_gtids | 1 | 155 | |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.01 sec)
mysql> show binlog events in 'binlog.000626';
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| binlog.000626 | 4 | Format_desc | 1 | 124 | Server ver: 8.0.17, Binlog ver: 4 |
| binlog.000626 | 124 | Previous_gtids | 1 | 155 | |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.00 sec)
Binary files have the following functions
-
recovery
Some data recovery requires binary logs. For example, after a database full backup file is recovered, users can recover point in time through binary logs
-
copy
The principle is similar to recovery. A remote database (slave or standby) and a MYSQL database (master or primary) are synchronized in real time by copying and executing binary logs
-
audit
Users can audit the information in the binary log to determine whether there is an injection attack on the database
The following configuration file parameters affect the information and behavior of binary logging
- max_binlog_size
- binlog_cache_size
- sync_binlog
- binlog-do-db
- binlog-ignore-db
- binlog_format
Parameter max_binlog_size specifies the maximum value of a single binary log file. If it exceeds the modified value, a new binary log file will be generated with the suffix + 1 and recorded to index file
binlog_cache_size is based on session, that is, when a thread starts a transaction, it will automatically allocate a binlog_cache_size cache
mysql> show variables like 'binlog_cache_size'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | binlog_cache_size | 32768 | +-------------------+-------+ 1 row in set, 2 warnings (0.00 sec) mysql> show global status like 'binlog_cache%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | +-----------------------+-------+ 2 rows in set (0.00 sec)
By default, binary logs are not synchronized to the disk every time they are written (buffer write). Therefore, when mysql goes down, some data may not be written to the disk, which will cause problems in recovery and replication. Parameter sync_binlog = N indicates how many times the write buffer is synchronized to the disk, sync_binlog = 1 means that binary logs are written synchronously to the disk
The parameters binlog do dB and bin ignore DB indicate which library logs need to be written or ignored. The default value is empty, indicating that the logs of all libraries need to be synchronized to binary logs
The server uses a variety of log formats to record information in binary logs. The exact format used depends on the MySQL version used. There are three log formats:
- The replication function in MySQL was originally based on the propagation of SQL statements from source to copy. This is called statement based logging. You can use --binlog-format=STATEMENT.
- In row based logging, the source writes events to the binary log, indicating how individual table rows are affected. You can make the server use row based logging to --binlog-format=ROW.
- There is a third option: mixed logging. For mixed logging, statement based logging is used by default, but in some cases, the logging mode is automatically switched to row based logging, as described below. You can start by using the option mysqld Make MySQL explicitly use mixed logging --binlog-format=MIXED.
In MySQL 5.6, the default binary log format is state
The log format can also be set or limited by the storage engine being used. This helps eliminate problems when copying certain statements between sources and replicas using different storage engines.
For statement based replication, copying non deterministic statements can be problematic. When deciding whether a given statement is safe for statement based replication, MySQL determines whether it can ensure that statement based logging can be used to copy statements. If MySQL cannot make this guarantee, it will mark the statement as possibly unreliable and issue a warning. Statement may not be safe to log in statement format.
You can avoid these problems by using MySQL's row based replication.
For more in-depth understanding, you can view the official documents https://dev.mysql.com/doc/refman/5.6/en/binary-log.html