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
- Prepare the directory where the mysql container is mounted and the configuration file information
- Start the mysql container and check whether bin_log is enabled
- 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 code
The three formats of binlog
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
Insert a piece of data into the database
As 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);
As 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.
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)
For 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:
Update 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
Look again at the ALTER statement
mysqlbinlog mysql-bin.000004 -vv --base64-output=decode-rows
The current timestamp 1639963483 is recorded in binlog.
Look again at statements that use system functions
In binlog, the value returned by sysdate() is directly recorded in the UPDATE statement.
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)
Insert statement 1
Records in ROW mode, because the updated field is obtained with CURRENT_TIMESTAMP
The update statement 1
Using 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:
The STATMENT mode is used to store the binlog.
What if I use a system function
The type of each ROW is different. It seems that this can only be recorded in ROW mode. Look at the binlog
And so it was.
|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.