preface
When a DDL or DML operation is performed successfully and the number of rows returned is greater than 0, the operation will be recorded to the Binlog file.
Let’s start with two basic numbers
DDL(Data Definition Language)
, a data definition language for creating, modifying, and deleting the structure of a library or table, that is, representing structural changes to a library or table. For example,CREATE, ALTER, DROP...
DML(Data Manipulation Language)
, a data manipulation language for inserting, modifying, deleting, and querying table data. For example,INSERT, UPDATE, DELETE, SELECT...
BINLOG file
A binary file, which records all changes to the database and saves them on disk in binary form.
The main purpose
- Master-slave replication. After MySQL uses the master-slave architecture, after the connection between the slave library and the master library is established, the master library will send Binlog to the slave library, and the slave library performs Binglog file content by parsing and playing back to achieve the effect of consistency between the master and slave. All data subscription, synchronization, and migration tools are based on this principle.
- The Mysqlbinlog tool can be used to restore the data to a certain time before the operation.
Record mode (based on MySQL8.0)
Statement
Binlog stores raw SQL statements.- Advantages: No need to record every row change, and
Row
Compared with reducing the number of Binlog logs, it saves I/O and improves performance. - Disadvantages: Since only executing statements are recorded, playback on slave libraries can cause master/slave inconsistencies in some cases, such as in SQL
UUID(),USER()
Etc. Function.
- Advantages: No need to record every row change, and
Row
Binlog stores the values of each row before and after all field changes.- Advantages: Does not appear because the values before and after each field change are recorded for each row
Statement
Problems with patterns. - Disadvantages: A single SQL can cause 100 rows to change, so Binlog will record the value of each field in those 100 rows before and after the change.
Statement
Schema only needs to record this one SQL, so andStatement
Compared to modelA large amount of log content can be generated.
- Advantages: Does not appear because the values before and after each field change are recorded for each row
Mixed
The mixed mode will analyze each SQL entry when usedStatement
Used when pattern recording can cause problemsRow
Schema records, such as those in SQLUUID(),USER()
And so onRow
Mode.
When do I write a Binlog file? Will I write a Binlog if the operation does not change the table data?
Conclusion (based on MySQL8.0)
Statement
In this mode, all successful DDL and DML operations are recorded, includingUPDATE and DELETE
The operation affects the case of zero rows.Row
In this mode, all DDL and DML operations that are successfully executed and whose number of affected rows is greater than 0 are recordedUPDATE and DELETE
If the number of rows affected by an operation is 0, the operation will not be recorded in the Binlog.Mixed
In mode, when an SQL parsed should be usedStatement
Mode write Binlog according toStatement
Rule, when an SQL analysis should be usedRow
Mode write Binlog according toRow
The rules.- If the USER() function is used in an UPDATE and the operation is successful, the number of affected rows returned by the operation is 0
Row
Rule, so this is not recorded in Binlog. - A SQL
UPDATE SET NAME = 'kd' WHERE ID = 1
, and the data with ID 1 does not exist, although the execution succeeds and the number of impact rows returned is 0, this SQL will be usedStatement
Rule, so the operation will still be recorded in the Binlog.
- If the USER() function is used in an UPDATE and the operation is successful, the number of affected rows returned by the operation is 0
validation
View the version and service status
mysql> SELECT VERSION()\G
*************************** 1. row ***************************
VERSION(): 8.027.
1 row in set (0.00 sec)
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: binlog000003.
Position: 5423
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
Copy the code
Ready to store table
mysql> CREATE DATABASE KD;
Query OK, 1 row affected (0.01 sec)
mysql> USE KD
Database changed
mysql> CREATE TABLE `TEST`(`id` int, `name` varchar(100), PRIMARY KEY(`id`));
Query OK, 0 rows affected (0.03 sec)
Copy the code
validationStatement
mysql> SET BINLOG_FORMAT='STATEMENT';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'BINLOG_FORMAT'\G
*************************** 1. row ***************************
Variable_name: binlog_format
Value: STATEMENT
1 row in set (0.01 sec)
Copy the code
steps
- Insert:
INSERT INTO
TESTVALUES(1,'1');
Record to Binlog - Modification:
UPDATE
TESTSET
NAME='kd' WHERE
ID= 1;
Record to Binlog - Modification:
SQL - > execute successfully but the number of affected rows is 0
Record to Binlog
mysql> INSERT INTO `TEST` VALUES(1.'1');
Query OK, 1 row affected (0.01 sec)
mysql> UPDATE `TEST` SET `NAME`='kd' WHERE `ID`=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE `TEST` SET `NAME`='kd' WHERE `ID`=1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
SHOW BINLOG EVENTS IN 'binlog.000003';
Copy the code
Although the third SQL operation affects 0 rows, it is still recorded in the Binlog
validationRow
mysql> SET BINLOG_FORMAT='ROW';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'BINLOG_FORMAT'\G
*************************** 1. row ***************************
Variable_name: binlog_format
Value: ROW
1 row in set (0.00 sec)
Copy the code
steps
- Insert:
INSERT INTO
TESTVALUES(3,'3');
Record to Binlog - Modification:
UPDATE
TESTSET
NAME='kd' WHERE
ID= 3;
Record to Binlog - Modification:
SQL - > execute successfully but the number of affected rows is 0
[Not recorded in Binlog]
mysql> INSERT INTO `TEST` VALUES(3.'3');
Query OK, 1 row affected (0.01 sec)
mysql> UPDATE `TEST` SET `NAME`='kd' WHERE `ID`=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE `TEST` SET `NAME`='kd' WHERE `ID`=3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
SHOW BINLOG EVENTS IN 'binlog.000003';
Copy the code
- for
Row
In SQL mode, a Binlog is recorded only when the number of rows returned is greater than 0. Row
In this mode, you need to use the Mysqlbinlog tool to parse the Binlog to see the detailed changes for each row.
Binlog details in Row mode
// Use the mysqlbinlog tool to parse the Binlog file, because Binlog is a binary fileroot# mysqlbinlog --base64-output=decode-rows -v binlog.000003
Copy the code
INSERT INTO TEST
VALUES(3,’3′); Details of the Binlog
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/ *! * /;
# at 6730
#220220 8:28:26 server id 1 end_log_pos 6803 CRC32 0x90515f14 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1645345706/ *! * /;
BEGIN
/ *! * /;
# at 6803
#220220 8:28:26 server id 1 end_log_pos 6859 CRC32 0xe8817f41 Table_map: `KD`.`TEST` mapped to number 97
# at 6859
#220220 8:28:26 server id 1 end_log_pos 6902 CRC32 0x94a36e8f Write_rows: table id 97 flags: STMT_END_F
### INSERT INTO `KD`.`TEST`
### SET# # #@ 1=3# # #@ 2='3'
# at 6902
#220220 8:28:26 server id 1 end_log_pos 6933 CRC32 0xfc598006 Xid = 137
COMMIT/ *! * /;
Copy the code
UPDATE TEST
SET NAME
=’kd’ WHERE ID
= 3; Details of the Binlog
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/ *! * /;
# at 7012
#220220 8:28:34 server id 1 end_log_pos 7094 CRC32 0xf9220b3c Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1645345714/ *! * /;
BEGIN
/ *! * /;
# at 7094
#220220 8:28:34 server id 1 end_log_pos 7150 CRC32 0x7f47ccbc Table_map: `KD`.`TEST` mapped to number 97
# at 7150
#220220 8:28:34 server id 1 end_log_pos 7203 CRC32 0xbddcd2d7 Update_rows: table id 97 flags: STMT_END_F
### UPDATE `KD`.`TEST`
### WHERE# # #@ 1=3# # #@ 2='3'# # #SET# # #@ 1=3# # #@ 2='kd'
# at 7203
#220220 8:28:34 server id 1 end_log_pos 7234 CRC32 0xd1959d3f Xid = 138
COMMIT/ *! * /;
Copy the code
validationMIXED
mysql> SET BINLOG_FORMAT='MIXED';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'BINLOG_FORMAT'\G
*************************** 1. row ***************************
Variable_name: binlog_format
Value: MIXED
1 row in set (0.01 sec)
Copy the code
steps
- Insert:
INSERT INTO
TESTVALUES(4,'4');
[Is usedStatement
Mode, record to Binlog】 - Modification:
UPDATE
TESTSET
NAME='kd' WHERE
ID= 4;
[Is usedStatement
Mode, record to Binlog】 - Modification:
SQL - > execute successfully but the number of affected rows is 0
[Is usedStatement
Mode, record to Binlog】 - Modification:
UPDATE
TESTSET
NAME=USER() WHERE
ID= 4;
[Is usedRow
Mode, record to Binlog】 - Modification:
SQL - > execute successfully but the number of affected rows is 0
[Is usedRow
Mode, not recorded in Binlog】
mysql> INSERT INTO `TEST` VALUES(4.'4');
Query OK, 1 row affected (0.01 sec)
mysql> UPDATE `TEST` SET `NAME`='kd' WHERE `ID`=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE `TEST` SET `NAME`='kd' WHERE `ID`=4;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> UPDATE `TEST` SET `NAME`=USER(a)WHERE `ID`=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE `TEST` SET `NAME`=USER(a)WHERE `ID`=4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
SHOW BINLOG EVENTS IN 'binlog.000003';
Copy the code
Mixed
In mode, when an SQL parsed should be usedStatement
Mode write Binlog according toStatement
Rule, when an SQL analysis should be usedRow
Mode write Binlog according toRow
The rules.- Article 1, article 2 using SQL uses
Statement
Four or five SQL SQL usesRow
. - Because the fifth SQL uses
Row
And the operation succeeds but the number of affected rows is 0. Therefore, no Binlog is recorded.
Article 4 the UPDATETEST
SET NAME
=USER() WHERE ID
= 4; Details of the Binlog
root# mysqlbinlog --base64-output=decode-rows -v binlog.000003
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/ *! * /;
# at 8248
#220220 8:52:12 server id 1 end_log_pos 8330 CRC32 0xfd4b0743 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1645347132/ *! * /;
BEGIN
/ *! * /;
# at 8330
#220220 8:52:12 server id 1 end_log_pos 8386 CRC32 0xf9725844 Table_map: `KD`.`TEST` mapped to number 97
# at 8386
#220220 8:52:12 server id 1 end_log_pos 8452 CRC32 0x9049fa91 Update_rows: table id 97 flags: STMT_END_F
### UPDATE `KD`.`TEST`
### WHERE# # #@ 1=4# # #@ 2='kd'# # #SET# # #@ 1=4# # #@ 2='root@localhost'
# at 8452
#220220 8:52:12 server id 1 end_log_pos 8483 CRC32 0xc51d1b7a Xid = 147
COMMIT/ *! * /;
Copy the code
conclusion
- Due to limited space, the test of DELETE is not included in the article, but DELETE and UPDATE are consistent.
Statement
In this mode, all successful DDL and DML operations are recorded, includingUPDATE and DELETE
The number of rows affected by the operation is zero.Row
In this mode, all DDL and DML operations that are successfully executed and whose number of affected rows is greater than 0 are recordedUPDATE and DELETE
If the number of rows affected by an operation is 0, the operation will not be recorded in the Binlog.Mixed
Mode, when an SQL should be usedStatement
Mode write Binlog according toStatement
Rule when an SQL should be usedRow
Mode write Binlog according toRow
The rules.- If the USER() function is used in an UPDATE and the operation is successful, the number of affected rows returned by the operation is 0
Row
Rule, so this is not recorded in Binlog. - A SQL
UPDATE SET NAME = 'kd' WHERE ID = 1
, and the data with ID 1 does not exist, although the execution succeeds and the number of impact rows returned is 0, this SQL will be usedStatement
Rule, so the operation will still be recorded in the Binlog.
- If the USER() function is used in an UPDATE and the operation is successful, the number of affected rows returned by the operation is 0