Summary of Binlog usage of MySQL database (must read)

I won't go into details about how important binlog binary log is to MySQL database. Based on my daily operation experience and combined with online reference materials, I will sort out the use of binlog logs:

1. Introduction to binlog

1) What is binlog?
The binlog log is used to record all statements that update data or have potentially updated data (for example, a DELETE that does not match any rows). Statements are saved in the form of "events", which describe changes to data.

2) Binlog Function
Because of the binlog of data updates, it can be used for real-time backup and combined with master/slave replication.

3) Parameters related to binlog
log_bin

Setting this parameter means enabling the binlog function and specifying the path name

log_bin_index

Setting this parameter specifies the path and name of the binary index file

binlog_do_db

This parameter means that only the binary log of the specified database is recorded

binlog_ignore_db
This parameter indicates that the binary log of the specified database is not recorded.

max_binlog_cache_size

This parameter indicates the maximum size of the memory used by binlog

binlog_cache_size

This parameter indicates the memory size used by binlog, which can be tested with the help of status variables binlog_cache_use and binlog_cache_disk_use.

binlog_cache_use: The number of transactions using the binary log cache

binlog_cache_disk_use: The number of transactions that used the binary log cache but exceeded the binlog_cache_size value and used a temporary file to save the statements in the transaction

max_binlog_size

The maximum and default value of Binlog is 1GB. This setting cannot strictly control the size of Binlog, especially when Binlog is close to the maximum value and encounters a relatively large transaction. In order to ensure the integrity of the transaction, it is impossible to switch the log. All SQL statements of the transaction can only be recorded in the current log until the transaction ends.

sync_binlog

This parameter directly affects the performance and integrity of MySQL

sync_binlog=0

When the transaction is committed, Mysql only writes the data in binlog_cache to the Binlog file, but does not execute disk synchronization instructions such as fsync to notify the file system to refresh the cache to disk. Instead, it lets the Filesystem decide when to synchronize. This is the best performance.

sync_binlog=n , after n transactions are committed, Mysql will execute a disk synchronization instruction such as fsync, and the comrade file system will refresh the Binlog file cache to disk.

The default setting in Mysql is sync_binlog=0, which means that no mandatory disk refresh instructions are made. This gives the best performance, but also the greatest risk. Once the system crashes, all Binlog information in the file system cache will be lost.

4) Deleting binlog

Binlog can be deleted manually or automatically:

a) Automatically delete binlog

Use binlog parameter (expire_logs_days) to realize mysql automatic deletion of binlog

mysql> show binary logs;
mysql> show variables like 'expire_logs_days'; //This parameter indicates the number of days after which binlog logs are automatically deleted/expired. The default value is 0, indicating that they are not automatically deleted.
mysql> set global expire_logs_days=3; // means that the logs are retained for 3 days and expire automatically after 3 days.

b) Manually delete binlog

mysql> reset master; //Delete the master's binlog, that is, manually delete all binlog logs
mysql> reset slave; //Delete the slave's relay log
mysql> purge master logs before '2012-03-30 17:20:00'; //Delete the binlog log files in the log index before the specified date
mysql> purge master logs to 'binlog.000002'; //Delete the binlog log file in the log index of the specified log file

mysql> set sql_log_bin= 1/0 ; //If the user has super privileges, he can enable or disable binlog records for the current session
mysql> show master logs; //View the master's binlog log list
mysql> show binary logs; //View the size of the master's binlog log file
mysql> show master status; //Used to provide status information of master binary log file
mysql> show slave hosts; //Display the list of currently registered slaves. Slaves that do not begin with the --report-host=slave_name option will not be displayed in this list.

mysql> flush logs; //Generate a new binlog log file

MySQL binlog log automatic cleanup and manual deletion case description:

When you enable the MySQL database master-slave, a large number of files such as mysql-bin.00000* log will be generated, which will consume a lot of your hard disk space. 
mysql-bin.000001 
mysql-bin.000002 
mysql-bin.000003 
mysql-bin.000004 
mysql-bin.000005 
… 
 
There are three solutions to delete these binlog logs: 
1. Close MySQL master and slave, and close binlog; 
The example operation is as follows: 
[root@huqniupc ~]# vim /etc/my.cnf //Comment out log-bin and binlog_format 
# Replication Master Server (default) 
# binary logging is required for replication 
# log-bin=mysql-bin 
# binary logging format - mixed recommended 
# binlog_format=mixed 
Then restart the database 2. Enable MySQL master-slave and set expire_logs_days; 
The example operation is as follows: 
[root@huqniupc ~]# vim /etc/my.cnf //Modify expire_logs_days, x is the number of days for automatic deletion, usually x is set to a shorter point, such as 10 
expire_logs_days = x //Number of days after which binary logs are automatically deleted. The default value is 0, which means "no automatic deletion" 
This method requires restarting MySQL 
 
Of course, you can also start MySQL master-slave without restarting MySQL, and set expire_logs_days directly in MySQL 
> show binary logs; 
> show variables like '%log%'; 
> set global expire_logs_days = 10; 
 
 
3. Manually clear the binlog file (for example, Mysql> PURGE MASTER LOGS TO 'MySQL-bin.010';) 
The example operation is as follows: 
[root@huqniupc ~]# /usr/local/mysql/bin/mysql -u root -p 
> PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY); //Delete MySQL binlog logs from 10 days ago. Appendix 2 contains instructions and examples for manual deletion of PURGE MASTER LOGS. > show master logs; 
  
You can also reset the master and delete all binlog files: 
# /usr/local/mysql/bin/mysql -u root -p 
> reset master; //Appendix 3: Description of the impact on mysql when clearing binlog--------------------------------------------------------------- 
PURGE MASTER LOGS Manual deletion usage and examples, MASTER and BINARY are synonyms> PURGE {MASTER | BINARY} LOGS TO 'log_name'
> PURGE {MASTER | BINARY} LOGS BEFORE 'date'
Deletes all binary logs in the log index before the specified log or date. These logs are also removed from the list of MySQL BIN-LOG logs recorded in the log index file, so that the given log becomes the first one. 
 
Examples: 
> PURGE MASTER LOGS TO 'MySQL-bin.010'; //Purge MySQL-bin.010 logs> PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00'; //Purge binlog logs before 2008-06-22 13:00:00> PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY); //Purge binlog logs before 3 days ago BEFORE, the date argument of the variable can be in the format of 'YYYY-MM-DD hh:mm:ss'. 
-----------------------------------------------------

5) Impact on MySQL when clearing binlog

If there is an active slave server that is currently reading one of the logs you are trying to delete, this statement will have no effect and will fail with an error; however, if the slave server is shut down (or the master-slave relationship is down) and happens to clean up one of the logs it wants to read, the slave server will not be able to replicate after it is started; this statement can be run safely while slave servers are replicating, without stopping them.

6) View binglog

You can view the contents of binlog through the mysqlbinlog command

[root@localhost ~]# mysqlbinlog /home/mysql/binlog/binlog.000003 | more
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#120330 16:51:46 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.45-log created 120330 1
6:51:46
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
# at 196
#120330 17:54:15 server id 1 end_log_pos 294 Query thread_id=3 exec_time=2 error_code=0
SET TIMESTAMP=1333101255/*!*/;
insert into tt7 select * from tt7/*!*/;
# at 294
#120330 17:54:46 server id 1 end_log_pos 388 Query thread_id=3 exec_time=28 error_code=0
SET TIMESTAMP=1333101286/*!*/;
alter table tt7 engine=innodb/*!*/;

Parsing binlog format:

Location

The position in the file. "at 196" indicates the starting point of the "event", which starts at the 196th byte; "end_log_pos 294" indicates the end at the 294th byte

Timestamp

Timestamp of the event: "120330 17:54:46"

Event execution time

The time taken for the event to execute: "exec_time=28"

Error Code

The error code is: "error_code=0"

Server ID

Server ID: "server id 1"

Note the following points:

1. It is impossible to imagine that the MySQL log can be restored to the state at any time. This restoration has a prerequisite!
There must be at least one database backup from the beginning of log recording. Restoring the database through logs is actually just a process of replaying previous operations. Don't think too much about it.

Since it is a playback operation, you must be careful. If you execute the recovery twice, it is equivalent to playing it back twice, and the consequences can be imagined.

so:

1) Be sure to back up your data before restoring.

2) Since there are many binary files and the data span to be restored is large, you can consider merging the log files during the recovery.

2. Enable binlog logging

To restore the database through logs, you must first define log-bin=mysql-bin in the my.cnf file . The generated binlog log name is named after mysql-bin.

3. When will a new binlog file be generated?

1) Add --flush-logs during backup

2) When restarting the mysql service

Special reminder: every time mysql is started, a file similar to mysql-bin.00000n will be regenerated. If your mysql needs to be restarted once a day, you must be careful not to choose the wrong log file.

2. Introduction to binlog log format

(1) MySQL binlog has three formats: Statement, MiXED, and ROW

1) Statement: Every SQL statement that modifies data will be recorded in the binlog

Advantages: No need to record changes in each row, reducing the amount of binlog logs, saving IO, and improving performance. (Compared with row, how much performance and log volume can be saved depends on the SQL situation of the application. Normally, the amount of log generated by modifying or inserting the same record in row format is still less than the amount of log generated by statement. However, considering that if there are conditional update operations, whole table deletion, alter table and other operations, ROW format will generate a large amount of logs. Therefore, when considering whether to use ROW format logs, you should consider the actual situation of the application, how much the amount of logs generated will increase, and the IO performance issues it will bring.)

Disadvantages: Since only the executed statements are recorded, in order for these statements to run correctly on the slave, some relevant information of each statement during execution must also be recorded to ensure that all statements can get the same results on the slave as when executed on the master. In addition, MySQL replication, such as some specific functions, the slave can be consistent with the master, which will cause many related problems (such as sleep() function, last_insert_id(), and user-defined functions (udf)).

Statements using the following functions also cannot be copied:

* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (unless the --sysdate-is-now option was enabled at startup)

At the same time, INSERT ...SELECT will generate more row-level locks than RBR

2) Row: does not record the context-related information of the SQL statement, only saves which record is modified

Advantages: Binlog does not need to record the context-related information of the executed SQL statement, but only needs to record what the record has been modified to. Therefore, the rowlevel log content will clearly record the details of each row of data modification. And there will be no problem that the stored procedure, function, or trigger call and trigger cannot be correctly copied in certain situations

Disadvantages: When all executed statements are recorded in the log, they will be recorded as changes to each row of records, which may generate a large amount of log content. For example, if an update statement modifies multiple records, each modification will be recorded in the binlog, which will cause a large amount of binlog logs. Especially when executing statements such as alter table, each record will be changed due to the modification of the table structure, so each record of the table will be recorded in the log.

3) Mixedlevel: It is a combination of the above two levels . General statement modifications use the statement format to save binlog. For example, some functions and statements cannot complete the master-slave replication operation, so the row format is used to save binlog. MySQL will distinguish the log format to be recorded according to each specific SQL statement executed, that is, choose between Statement and Row. The row level mode of the new version of MySQL has also been optimized. Not all modifications will be recorded at the row level. For example, when the table structure changes, it will be recorded in statement mode. As for statements that modify data, such as update or delete, changes to all rows will still be recorded.

Mixed log description:

During the slave log synchronization process, for time functions such as now, the MIXED log format will generate a corresponding unix_timestamp()*1000 time string in the log. When the slave completes the synchronization, it uses the time when the sqlEvent occurs to ensure data accuracy. In addition, for some functional functions, the slave can complete the corresponding data synchronization. For some UDF functions specified above, which cause the slave to be unaware of the situation, these Binlogs will be stored in ROW format to ensure that the generated Binlog can be used by the slave to complete data synchronization.

(2) Basic configuration and format setting of binlog

1) Basic preparation

The binlog log format can be specified by the attribute binlog_format of the mysql my.cnf file. Such as the following:
binlog_format = MIXED //binlog log format
log_bin = directory/mysql-bin.log //binlog log name
expire_logs_days = 7 //binlog expiration cleanup time
max_binlog_size 100m //Size of each binlog log file

binlog-do-db=The name of the database to be backed up. If you want to back up multiple databases, just set this option repeatedly.
binlog-ignore-db=Databases that do not need to be backed up are killed. If you back up multiple databases, just set this option repeatedly.

2) Binlog log format selection

Mysql uses the Statement log format by default, and MIXED is recommended.
Due to some special uses, you can consider using ROWED, such as synchronizing data modifications through binlog logs, which will save a lot of related operations. Binlog data processing becomes very easy, and parsing is also very easy compared to mixed data (of course, the premise is that the IO overhead caused by the increased log volume is within the tolerable range).

3) mysqlbinlog format selection

MySQL's selection principle for log format: If you use INSERT, UPDATE, DELETE and other direct table operations, the log format is recorded according to the setting of binlog_format. If you use management statements such as GRANT, REVOKE, SET PASSWORD, then SBR mode is used for recording anyway.

(3) MySQL Binlog log analysis

View the specific mysql log through the MysqlBinlog command, as follows:

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

SET TIMESTAMP=1350355892/*!*/;

BEGIN

/*!*/;

# at 1643330

#121016 10:51:32 server id 1 end_log_pos 1643885 Query thread_id=272571 exec_time=0 error_code=0

SET TIMESTAMP=1350355892/*!*/;

Insert into T_test….)

/*!*/;

# at 1643885

#121016 10:51:32 server id 1 end_log_pos 1643912 Xid = 0

COMMIT /*!*/;

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

1. Time to start things:

SET TIMESTAMP=1350355892/*!*/;

BEGIN

2.sqlevent starting point

#at 1643330: The starting point of the event, starting at 1643330 bytes.

3. The time when sqlevent occurs

#121016 10:51:32: is the time when the event occurred.

4.serverId

server id 1: the serverId of the master

5.sqlevent endpoint and time spent, error code

end_log_pos 1643885: is the end point of the event, ending with 1643885 bytes.

execTime 0: time spent

error_code=0: error code

Xid: event indicates the committed XA transaction

3. Optimization description of MySQL log (focus on binlog log)

The MySQL system is highly scalable and can run efficiently in an environment with sufficient hardware resources, and can also run well in an environment with very few resources.
But no matter what, sufficient hardware resources are always helpful to improve MySQL performance.

The following focuses on analyzing the impact of MySQL logs (mainly Binlog) on ​​system performance, and derives corresponding optimization ideas based on the relevant characteristics of the logs.

1) Performance impact of logs
The direct performance loss caused by log recording is the most expensive IO resource in the database system .

MySQL logs mainly include error log (ErrorLog), update log (UpdateLog) , binary log (Binlog), query log (QueryLog), slow query log (SlowQueryLog), etc.
Special note: The update log is only available in older versions of MySQL and has been replaced by the binary log .

By default, the system only opens the error log and closes all other logs to minimize IO loss and improve system performance.
However, in general slightly more important practical application scenarios, you need to at least open the binary log, because this is the basis for incremental backup of many MySQL storage engines and the basic condition for MySQL to achieve replication.
Sometimes, in order to further optimize MySQL performance and locate SQL statements that execute slowly, many systems will also open slow query logs to record SQL statements whose execution time exceeds a specific value (set by ourselves).

Generally speaking, query logging is rarely enabled in production systems. Because after the query log is turned on, every query executed in MySQL will be recorded in the log, which will bring a relatively large IO burden to the system, but the actual benefit it brings is not very large. Generally, the log is opened for a short period of time for analysis only in a development and testing environment in order to locate which SQL statements are used for certain functions.
Therefore, in the MySQL system, the MySQL log that will affect performance (excluding the logs of each storage engine itself) is mainly Binlog.

2) Binlog related parameters and optimization strategies

Let's first look at the relevant parameters of Binlog. You can get the relevant parameters of Binlog by executing the following command.
Of course, it also shows the "innodb_locks_unsafe_for_binlog" parameter, which is unique to the Innodb storage engine and related to Binlog:

mysql> show variables like '%binlog%'; 
+-----------------------------------------+----------------------+ 
| Variable_name | Value | 
+-----------------------------------------+----------------------+ 
| binlog_cache_size | 16777216 | 
| binlog_checksum | CRC32 | 
| binlog_direct_non_transactional_updates | OFF | 
| binlog_error_action | IGNORE_ERROR | 
| binlog_format | MIXED | 
| binlog_gtid_simple_recovery | OFF | 
| binlog_max_flush_queue_time | 0 | 
| binlog_order_commits | ON | 
| binlog_row_image | FULL | 
| binlog_rows_query_log_events | OFF | 
| binlog_stmt_cache_size | 32768 | 
| binlogging_impossible_mode | IGNORE_ERROR | 
| innodb_api_enable_binlog | OFF | 
| innodb_locks_unsafe_for_binlog | OFF | 
| max_binlog_cache_size | 18446744073709547520 | 
| max_binlog_size | 1073741824 | 
| max_binlog_stmt_cache_size | 18446744073709547520 | 
| simplified_binlog_gtid_recovery | OFF | 
| sync_binlog | 1 | 
+-----------------------------------------+----------------------+ 
19 rows in set (0.00 sec)

"binlog_cache_size" : The size of the cache that holds binary log SQL statements during a transaction. The binary log cache is the memory allocated to each client when the server supports the transactional storage engine and the server enables binary logging (--log-bin option). Note that each client can allocate a binlogcache space of a set size. If multi-statement transactions often occur in your system, you can try to increase the value to get better performance. Of course, we can determine the current binlog_cache_size status through the following two status variables of MySQL: Binlog_cache_use and Binlog_cache_disk_use.

"max_binlog_cache_size" : Corresponding to "binlog_cache_size", but represents the maximum cache memory size that binlog can use. When we execute a multi-statement transaction, if max_binlog_cache_size is not large enough, the system may report an error "Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage".

"max_binlog_size" : The maximum value of the Binlog log, generally set to 512M or 1G, but cannot exceed 1G. This size cannot strictly control the size of the Binlog, especially when the Binlog is close to the end and encounters a large transaction. In order to ensure the integrity of the transaction, the system cannot switch logs and can only record all SQL statements of the transaction into the current log until the transaction ends. This is a little different from Oracle's Redo log, because Oracle's Redo log records the changes in the physical location of the data file, and it also records information related to Redo and Undo. Therefore, whether the same transaction is in the same log is not critical to Oracle. What MySQL records in Binlog is the database logic change information, which MySQL calls Event, which is actually the Query statement such as DML that brings about database changes.

"sync_binlog" : This parameter is crucial for the MySQL system. It not only affects the performance loss of Binlog to MySQL, but also affects the integrity of the data in MySQL. The following are descriptions of the various settings for the "sync_binlog" parameter:

sync_binlog=0 , after the transaction is committed, MySQL does not perform disk synchronization instructions such as fsync to refresh the information in binlog_cache to disk, but lets Filesystem decide when to synchronize, or synchronize to disk only when the cache is full.

sync_binlog=n , after every n transactions are committed, MySQL will execute a disk synchronization instruction such as fsync to force the data in binlog_cache to be written to disk.

In MySQL, the system default setting is sync_binlog=0, which means that no mandatory disk refresh instructions are performed. The performance is the best at this time, but the risk is also the greatest. Because once the system crashes, all binlog information in binlog_cache will be lost. When set to "1", it is the safest setting but has the greatest performance loss. Because when it is set to 1, even if the system crashes, at most one unfinished transaction in binlog_cache will be lost, which has no substantial impact on the actual data. Based on past experience and related tests, for systems with high concurrent transactions, the write performance difference between "sync_binlog" set to 0 and set to 1 may be as high as 5 times or more.

Other:

MySQL replication is actually achieved by copying the Binlog on the Master side to the Slave side through the network using the IO thread, and then parsing the logs in the Binlog through the SQL thread and applying them to the database. Therefore, the size of the Binlog will have a direct impact on the IO thread and the network between the Master and Slave ends.

The amount of Binlog generated in MySQL cannot be changed. As long as our Query changes the data in the database, the Event corresponding to the Query must be recorded in the Binlog. So is there no way for us to optimize replication? Of course not. In the MySQL replication environment, there are actually 8 parameters that allow us to control the DB or Table that needs to be replicated or ignored, which are:

Binlog_Do_DB : Set which databases (Schemas) need to record Binlog;

Binlog_Ignore_DB : Set which databases (Schemas) should not record Binlog;

Replicate_Do_DB : Set the database (Schema) to be replicated. Multiple DBs are separated by commas (",").

Replicate_Ignore_DB : Set the database (Schema) that can be ignored;

Replicate_Do_Table : Set the Table to be replicated;

Replicate_Ignore_Table : Set the table that can be ignored;

Replicate_Wild_Do_Table : Same function as Replicate_Do_Table, but can be set with wildcards;

Replicate_Wild_Ignore_Table : Same function as Replicate_Ignore_Table, but can be set with wildcards;

Through the above eight parameters, we can easily control the amount of Binlog from the Master to the Slave as small as possible according to actual needs, thereby reducing the network traffic from the Master to the Slave, reducing the IO volume of the IO thread, and reducing the number of SQL threads parsing and applying SQL, ultimately improving the data delay problem on the Slave.

In fact, the first two of the above eight parameters are set on the Master side, while the last six parameters are set on the Slave side. Although the first two parameters and the last six parameters are not directly related in terms of function, they can enable similar functions for optimizing MySQL Replication. Of course, there are some differences, the main differences are as follows:

If the first two parameters are set on the Master side, not only will the IO volume caused by the Binlog records on the Master side be reduced, but the IO thread on the Master side will also be able to reduce the amount of Binlog reads, and the amount of Binlog passed to the IO thread on the Slave side will naturally be less. The advantage of doing this is that it can reduce network IO, reduce the IO volume of the Slave IO thread, and reduce the workload of the Slave SQL thread, thereby optimizing the replication performance to the greatest extent. Of course, there are some disadvantages in setting it on the Master side, because MySQL determines whether to replicate an event based on the data changed by the query that generated the event.

The database in which the query is executed is not based on the default schema, but on the default schema in which the query is executed, that is, the DB specified when we log in or the DB specified when running "USE DATABASE". Only when the current default DB and the DB set in the configuration are completely consistent will the IO thread read the Event to the Slave's IO thread. Therefore, if the default DB and the DB to be replicated are different in the system, and the data of a certain Table in the DB to be replicated is changed, the Event will not be replicated to the Slave, which will cause the data on the Slave side to be inconsistent with the data on the Master. Similarly, if the data in a schema that does not need to be replicated is changed under the default schema, it will be replicated to the Slave side. If the Slave side does not have this schema, the replication will fail and stop.

If the following six parameters are set on the Slave side, the performance optimization may be slightly inferior to that on the Master side, because all Events that need to be replicated or not will be read to the Slave side by the IO thread, which not only increases the network IO volume, but also increases the RelayLog write volume for the Slave side's IO thread. However, the amount of log application by the Slave SQL thread on the Slave side can still be reduced. Although the performance is slightly inferior, setting up a replication filtering mechanism on the Slave side can ensure that there will be no inconsistency in the Slave and Master data or replication errors caused by problems with the default Schema.

3) Slow query log Query Log related parameters and usage suggestions
Let's take a look at the relevant parameter configuration of SlowQueryLog. Sometimes, in order to locate the less efficient Query statements in the system, we need to open the slow query log, that is, SlowQueryLog. We can view the relevant settings of the system slow query log as follows:

mysql> show variables like 'log_slow%'; 
+------------------+-------+ 
| Variable_name | Value | 
+------------------+-------+ 
| log_slow_queries | ON | 
+------------------+-------+ 
1 row in set (0.00 sec) 
 
mysql> show variables like 'long_query%'; 
+-----------------+-------+ 
| Variable_name | Value | 
+-----------------+-------+ 
| long_query_time | 1 | 
+-----------------+-------+ 
1 row in set (0.01 sec) 

The "log_slow_queries" parameter shows whether the system has turned on the SlowQueryLog function, and the "long_query_time" parameter tells us how long the execution time of the query exceeds the current system setting SlowQuery record. In the MySQL version released by MySQL AB, the shortest slow query time that SlowQueryLog can set is 1 second, which may not fully meet our requirements in some cases. If you want to further shorten the time limit for slow queries, you can use the microslow-patch (called mslPatch) provided by Percona to break through this limitation. mslpatch can not only reduce the slow query time to milliseconds, but also filter the recorded SQL through some specific rules, such as only recording the SlowQuery involving a certain table and other additional functions.

The overall impact of turning on the SlowQueryLog function on system performance is not as great as that of Binlog. After all, the amount of data in SlowQueryLog is relatively small, so the IO loss it brings is also relatively small. However, the system needs to calculate the execution time of each query, so there will always be some consumption, mainly CPU consumption. If your system has sufficient CPU resources, you don't have to worry about this little loss. After all, it may bring us greater performance optimization. However, if our CPU resources are relatively tight, we can completely turn off this function most of the time, and only need to intermittently turn on the SlowQueryLog function to locate possible slow queries.

Other MySQL logs are rarely used (QueryLog) or have little impact on performance, so we will not analyze them in detail here.

The above summary of the use of Binlog log in MySQL database (must read) is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.