Description: Binlog Records log information about write operations performed by the database. It is saved in binary format on the disk. It is logged by the Server layer and binlog is generated using any storage engine.
The experiment to prepare
We did a series of studies on MySQL binlog using Docker. Some preparations need to be made:
- Pull mysql 5.7 version image
$Docker pull mysql: 5.7
Copy the code- Prepare the directory where the mysql container is mounted and the configuration file information
$ mkdir -p infra/mysql
$ cd infra/mysql
#Mount the directory
$ mkdir data
#The configuration file
$ mkdir mysql.conf.d && cd mysql.conf.d
$ echo "[mysqld]
#Set the format of the binlog
binlog_format = statement
#Example Set the log storage path
log-bin=/var/lib/mysql/mysql-bin
server-id=12345" > mysqld.cnf
Copy the code- Start the mysql container and check whether bin_log is enabled
#Start the container
$docker run -p3306:3306 -d --name mysql57 \ -v /Users/zioyi/infra/mysql/mysql.conf.d:/etc/mysql/conf.d \ -v / Users/zioyi/infra/mysql/data: / var/lib/mysql \ - e MYSQL_ROOT_PASSWORD = root - restart = always mysql: 5.6
#Into the container
$ docker exec -it mysql57 sh
$ [mysql57] mysql -uroot -prootmysql > show variable like "%log_bin%"; +---------------------------------+--------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/mysql-bin | | log_bin_index | /var/lib/mysql/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 6 rows in the set (0.00 SEC)Copy the code- Create databases and tables
mysql > create database mall character set utf8mb4 collate utf8mb4_unicode_ci; Query OK, 1 row affected (0.01sec) mysql > use mall; Database changed mysql > CREATE TABLE `item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 'name' varchar(100) DEFAULT 'COMMENT ',' price 'int(10) unsigned DEFAULT '0' COMMENT ', PRIMARY KEY (' id ') ENGINE=InnoDB DEFAULT CHARSET= utf8MB4 COMMENT=' InnoDB ';Copy the codeThe three formats of binlog
STATMENT
Statment-based Replication (SBR). Each SQL statement that modifies data is copied to a binlog.
We set the bin_log mode to STATMENT and restart the container
$ echo "[mysqld]
#Set the format of the binlog
binlog_format = statement
#Example Set the log storage path
log-bin=/var/lib/mysql/mysql-bin
server-id=12345" > mysqld.cnf
Copy the codeInsert a piece of data into the database
mysql> insert into item(name, price) values("book", 10);Query OK, 1 row affected (0.01sec)
#Know the latest binlog file to viewshow binlog events in 'mysql-bin.000004'; +------------------+-----+-------------+-----------+-------------+------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------+ | mysql-bin.000004 | 4 | Format_desc | 12345 | 120 | Server ver: 5.6.51 - log, Binlog ver: 4 | | mysql-bin.000004 | 120 | Query | 12345 | 263 | create database mall character set utf8mb4 collate utf8mb4_unicode_ci | | mysql-bin.000004 | 263 | Query | 12345 | 446 | create database blog_service default character set utf8mb4 default collate utf8mb4_general_ci | | mysql-bin.000004 | 446 | Query | 12345 | 758 | use `mall`; CREATE TABLE `item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT '' COMMENT '', `price` int(10) unsigned DEFAULT '0' COMMENT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='' | | mysql-bin.000004 | 758 | Query | 12345 | 837 | BEGIN | | mysql-bin.000004 | 837 | Intvar | 12345 | 869 | INSERT_ID=1 | | mysql-bin.000004 | 869 | Query | 12345 | 991 | use `mall`; insert into item(name, price) values("book", 10) | | mysql-bin.000004 | 991 | Xid | 12345 | 1022 | COMMIT /* xid=15 */ | +------------------+-----+-------------+-----------+-------------+------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------+Copy the codeAs you can see, our insert has already been recorded. Let’s insert a few more entries into the table:
mysql > insert into item(name, price) values("fruit", 12);
mysql > insert into item(name, price) values("rice", 2);
#Add a new field to a table
mysql > alter table item add column type smallint default 0;
mysql > update item set type = 1 where type = 0;
#Let's look at the binlogmysql > show binlog events in 'mysql-bin.000004'; | mysql-bin.000004 | 1022 | Query | 12345 | 1101 | BEGIN | | mysql-bin.000004 | 1101 | Intvar | 12345 | 1133 | INSERT_ID=2 | | mysql-bin.000004 | 1133 | Query | 12345 | 1256 | use `mall`; insert into item(name, price) values("fruit", 12) | | mysql-bin.000004 | 1256 | Xid | 12345 | 1287 | COMMIT /* xid=23 */ | | mysql-bin.000004 | 1287 | Query | 12345 | 1366 | BEGIN | | mysql-bin.000004 | 1366 | Intvar | 12345 | 1398 | INSERT_ID=3 | | mysql-bin.000004 | 1398 | Query | 12345 | 1519 | use `mall`; insert into item(name, price) values("rice", 2) | | mysql-bin.000004 | 1519 | Xid | 12345 | 1550 | COMMIT /* xid=25 */ | | mysql-bin.000004 | 1550 | Query | 12345 | 1675 | use `mall`; alter table item add column type smallint default 0 | | mysql-bin.000004 | 1675 | Query | 12345 | 1754 | BEGIN | | mysql-bin.000004 | 1754 | Query | 12345 | 1867 | use `mall`; update item set type = 1 where type = 0 | | mysql-bin.000004 | 1867 | Xid | 12345 | 1898 | COMMIT /* xid=31 */ |Copy the codeAs you can see, THE INSERT, alert, update, and other SQL statements are recorded in the binlog and are exactly the same as when they were executed.
To summarize the pros and cons of STATEMENT:
- Advantages: If a change such as ALTER or UPDATE affects the modification of multiple rows, only one SQL ROW needs to be recorded (compared with ROW mode). This reduces the amount of binlog logs, saves IO, and improves performance.
- q
ROW
Row-based replication (RBR) does not record the context of daily SQL statements, only which statements were modified. Change the mode of binlog to ROW:
mysql > set global binlog_format = ROW;
mysql > select @@global.binlog_format;
+------------------------+
| @@global.binlog_format |
+------------------------+
| ROW |
+------------------------+
1 row in set (0.00 sec)
#updatemysql > update item set type = 1 where type = 2; Query OK, 3 rows affected (0.01sec) Rows matched: 3 Changed: 3 Warnings: 0Copy the codeFor STATEMENT binlogs, all DML operations are recorded in QUERY_EVENT, so show binlog events in ‘mysql-bin.000004’; You can see it;
For binlogs in ROW format, all DML operations are recorded in rows_events, which are classified into three types: WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, and DELETE_ROWS_EVENT correspond to insert, UPDATE, and DELETE operations respectively. We need to use the mysqlbinlog tool to view this
Take a look at the contents of the ROW:
$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows
#211219 15:07:12 server id 12345 end_log_pos 2162 CRC32 0xb0fc9915 Update_rows: table id 73 flags: STMT_END_F
### UPDATE `mall`.`item`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='book' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=10 /* INT meta=0 nullable=1 is_null=0 */
### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='book' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=10 /* INT meta=0 nullable=1 is_null=0 */
### @4=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
### UPDATE `mall`.`item`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='fruit' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=12 /* INT meta=0 nullable=1 is_null=0 */
### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='fruit' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=12 /* INT meta=0 nullable=1 is_null=0 */
### @4=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
### UPDATE `mall`.`item`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=2 /* INT meta=0 nullable=1 is_null=0 */
### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=2 /* INT meta=0 nullable=1 is_null=0 */
### @4=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
# at 2162
#211219 15:07:12 server id 12345 end_log_pos 2193 CRC32 0x44ee46b2 Xid = 44COMMIT/*! * /. DELIMITER ;Copy the codeUpdate item set type = 1 where type = 2; The statement records changes to three records, separate UPDATE statements for each record that meets the WHERE condition.
The same is true for delete statements
mysql> update item set type = 1 where id = 3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from item ;+----+-------+-------+------+ | id | name | price | type | +----+-------+-------+------+ | 1 | book | 10 | 2 | | 2 | Fruit 12 | | | 2 | 3 | rice | 2 | | + 1 - + -- -- -- -- -- -- -- + -- -- -- -- -- -- - + -- -- -- -- -- - + 3 rows in the set (0.00 SEC)
mysql> delete from item where type = 2;Query OK, 2 rows affected (0.01sec)Copy the code$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows
### DELETE FROM `mall`.`item`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='book' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=10 /* INT meta=0 nullable=1 is_null=0 */
### @4=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
### DELETE FROM `mall`.`item`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='fruit' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=12 /* INT meta=0 nullable=1 is_null=0 */
### @4=2 /* SHORTINT meta=0 nullable=1 is_null=0 */
# at 2612
#211220 1:18:03 server id 12345 end_log_pos 2643 CRC32 0x435b7a4b Xid = 55COMMIT/*! * /.Copy the codeLook again at the ALTER statement
mysql> alter table item add column updated datetime default CURRENT_TIMESTAMP;
Copy the codemysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows
# at 2612
#211220 1:18:03 server id 12345 end_log_pos 2643 CRC32 0x435b7a4b Xid = 55COMMIT/*! * /.# at 2643
#211220 1:24:43 server id 12345 end_log_pos 2795 CRC32 0x68a196c8 Query thread_id=2 exec_time=0 error_code=0SET TIMESTAMP=1639963483/*! * /. SET @@session.time_zone='SYSTEM'/*! * /. alter table item add column updated datetime default CURRENT_TIMESTAMP /*! * /.Copy the codeThe current timestamp 1639963483 is recorded in binlog.
Look again at statements that use system functions
mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2021-12-20 01:31:26 |
+---------------------+
1 row in set (0.00 sec)
mysql> update item set updated = sysdate() where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from item;+----+------+-------+------+---------------------+ | id | name | price | type | updated | +----+------+-------+------+---------------------+ | 3 | rice | 2 | 1 | 2021-12-20 01:32:15 | + - + -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows
# at 2875
#211220 1:32:15 server id 12345 end_log_pos 2929 CRC32 0x1f777374 Table_map: `mall`.`item` mapped to number 74
# at 2929
#211220 1:32:15 server id 12345 end_log_pos 3009 CRC32 0x312870a3 Update_rows: table id 74 flags: STMT_END_F
### UPDATE `mall`.`item`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=2 /* INT meta=0 nullable=1 is_null=0 */
### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 01:24:43' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=2 /* INT meta=0 nullable=1 is_null=0 */
### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 01:32:15' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
# at 3009
#211220 1:32:15 server id 12345 end_log_pos 3040 CRC32 0xb49295bc Xid = 59COMMIT/*! * /.Copy the codeIn binlog, the value returned by sysdate() is directly recorded in the UPDATE statement.
MIXED
Mixed-based replication (MBR) is based on STATMENT and ROW modes. The general replication uses STATMENT mode to save binlogs. For operations that cannot be copied in STATEMENT mode, use ROW mode to save binlogs.
mysql > set global binlog_format = MIXED;
mysql > select @@global.binlog_format;
+------------------------+
| @@global.binlog_format |
+------------------------+
| ROW |
+------------------------+
1 row in set (0.00 sec)
#updatemysql > update item set type = 1 where type = 2; Query OK, 3 rows affected (0.01sec) Rows matched: 3 Changed: 3 Warnings: 0Copy the codeInsert statement 1
mysql> insert into item(name, price, type) values("pencil", 1, 3);
Query OK, 1 row affected (0.00 sec)
Copy the code$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows
#211220 1:39:45 server id 12345 end_log_pos 3233 CRC32 0xfcb23be0 Write_rows: table id 74 flags: STMT_END_F
### INSERT INTO `mall`.`item`
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=3 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 01:39:45' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
# at 3233
#211220 1:39:45 server id 12345 end_log_pos 3264 CRC32 0x1ee1856c Xid = 62COMMIT/*! * /.Copy the codeRecords in ROW mode, because the updated field is obtained with CURRENT_TIMESTAMP
The update statement 1
mysql> update item set type = 4;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
Copy the code$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows
### UPDATE `mall`.`item`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=2 /* INT meta=0 nullable=1 is_null=0 */
### @4=1 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 01:32:15' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=2 /* INT meta=0 nullable=1 is_null=0 */
### @4=4 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 01:32:15' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### UPDATE `mall`.`item`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=3 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 01:39:45' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=4 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 01:39:45' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### UPDATE `mall`.`item`
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=3 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 10:00:00' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### SET
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=4 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @5='2021-12-20 10:00:00' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
# at 3782
Copy the codeUsing the ROW mode again, it seems that this is true whenever there is a default CURRENT_TIMESTAMP field in the table.
Delete the updated field and try again:
mysql> alter table item drop column updated;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from item;+----+--------+-------+------+ | id | name | price | type | +----+--------+-------+------+ | 3 | rice | 2 | 4 | | 4 | Pencil 1 4 | | | | | 5 pencil | | | 4 + 1 - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + 3 rows in the set (0.00 SEC)
mysql> update item set type = 5;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
Copy the codemysql> show binlog events in 'mysql-bin.000004';
| mysql-bin.000004 | 3813 | Query | 12345 | 3923 | use `mall`; alter table item drop column updated |
| mysql-bin.000004 | 3923 | Query | 12345 | 4002 | BEGIN |
| mysql-bin.000004 | 4002 | Query | 12345 | 4100 | use `mall`; update item set type = 5 |
| mysql-bin.000004 | 4100 | Xid | 12345 | 4131 | COMMIT /* xid=82 */ |
Copy the codeThe STATMENT mode is used to store the binlog.
What if I use a system function
mysql> update item set type = ceil(rand() * 10);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from item;+----+--------+-------+------+ | id | name | price | type | +----+--------+-------+------+ | 3 | rice | 2 | 4 | | 4 | Pencil 1 10 | | | | | 5 pencil | | | 8 + 1 - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + 3 rows in the set (0.00 SEC)Copy the codeThe type of each ROW is different. It seems that this can only be recorded in ROW mode. Look at the binlog
$ mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows
#211220 1:57:57 server id 12345 end_log_pos 4401 CRC32 0x95bf290e Update_rows: table id 75 flags: STMT_END_F
### UPDATE `mall`.`item`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=2 /* INT meta=0 nullable=1 is_null=0 */
### @4=5 /* SHORTINT meta=0 nullable=1 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='rice' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=2 /* INT meta=0 nullable=1 is_null=0 */
### @4=4 /* SHORTINT meta=0 nullable=1 is_null=0 */
### UPDATE `mall`.`item`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=5 /* SHORTINT meta=0 nullable=1 is_null=0 */
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=10 /* SHORTINT meta=0 nullable=1 is_null=0 */
### UPDATE `mall`.`item`
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=5 /* SHORTINT meta=0 nullable=1 is_null=0 */
### SET
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2='pencil' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4=8 /* SHORTINT meta=0 nullable=1 is_null=0 */
# at 4401
Copy the codeAnd so it was.
contrast
| model | advantages | disadvantages |
|---|---|---|
| STATMENT | If a change such as ALTER or UPDATE affects the modification of multiple rows, you only need to record one SQL row. This reduces the amount of binlog logs, saves IO, and improves performance. | In some cases, it is impossible to restore the current data, such as using system functions sysdate(), rand(), and so on |
| ROW | There will be no problems with certain stored procedures, functions, trigger surveys, and triggers not being copied correctly | A large number of logs are generated, for example, update affects a large number of functions. |
| MIXED | It combines the advantages and disadvantages of STATMENT and ROW | – |
Before MySQL 5.7.7, the default format was STATEMENT. After MySQL 5.7.7, the default value was ROW.
Binlog Flush time
For the InnoDB storage engine, binlogs are only recorded when a transaction commits, while the records are still in memory. When is the binlog flushed to disk? MySQL uses the sync_binlog parameter to control the flush time of binlog. The value ranges from 0 to N:
- 0: the system determines when to write data to the disk.
- 1: Write the binlog to the disk every commit time.
- N: The binlog is written to the disk every N transactions.
As you can see from the above, sync_binlog is safest set to 1, which is also the default for MySQL versions after 5.7.7. However, setting a larger value can improve database performance, so it is possible to increase the value appropriately and sacrifice some consistency for better performance.
Binlog usage scenarios
In practical applications, binlog is used in the following scenarios:
- Data recovery: Restore data by using the mysqlbinlog tool.
- Master/Slave replication: Enable the binlog function on the Master and send the binlog function to each Slave. The Slave replays the binlog function to achieve data consistency between the Master and Slave.
- Data synchronization: Similar to master-slave replication, when we want to synchronize MySQL data to other databases suitable for data analysis, we do so by consuming binlog.