The MySQL Binlog is a binary log format. The Binlog records all DDL and DML statements (except SELECT, SHOW, etc.) in the form of EVENTS, along with the statement execution time.
Binlog has two main uses:
Data recovery
Because the Binlog keeps a detailed record of all SQL changes to the data, it can be used to play back the historical data if there is a problem with data manipulation at some point, or when the database is down and data is lost.
A master-slave replication
For businesses that want to do multi-machine backups, they can listen to the current write library’s Binlog and synchronize all changes to the write library.
Binlog contains two types of files:
Binary log index file (.index) : logs all binaries. Binary log file (.00000*) : Log all DDL and DML statement events. The Binlog function is enabled by default and can grow rapidly online. The MySQL configuration file my.cnf provides some parameters to set the Binlog function.
Copy sets this parameter to enable the binlog function, and specifies the binary log storage directory log-bin=/home/mysql/binlog/
Mysql -bin.* mysql-bin.* mysql-bin.* mysql-bin.
Set a maximum of 100MB
max_binlog_size=104857600
Set to hold only 7 days BINLOG
expire_logs_days = 7
The binlog logs only updates to the specified library
binlog-do-db=db_name
The binlog log does not record updates to the specified library
binlog-ignore-db=db_name
How many times write buffer, a disk brush, default 0
Sync_binlog =0 Note that:
Max_binlog_size: The Binlog maximum and default value is 1G. This setting does not strictly control the size of the Binlog, especially if the Binlog is close to the maximum and there is a large transaction, so it is not possible to switch the log to ensure the integrity of the transaction. Only all SQL for the transaction can be logged into the current log until the transaction ends. So the real file is sometimes larger than the max_binlog_size set value. Expire_logs_days: Binlog delete is not performed on a service schedule, but is triggered by an event, including:
The server was restarted and the server was updated log reached the maximum log length max_binlog_size log was flushed. The binary log is enabled by the log-bin option of the configuration file. The MySQL server will create two new files in the data root directory mysql-bin.000001 and mysql-bin.index. If the configuration option does not give the file name, MySQL will use the host name for the two files. The.index file contains a list of all log files.
SYNC_BINLOG: This parameter determines how often the Binlog log should be updated. The default is 0, which means that the operation is up to the operating system to determine how often to write to disk based on its own load.
SYNC_BINLOG = 1 means that each transaction commit is written to disk immediately. SYNC_BINLOG = N means that n transactions are committed before the disk is written.
According to the MySQL documentation, a Binlog is written when the SQL transaction has finished executing, but before any of the associated Locks have been freed or the transaction has finally committed. This ensures that the timing of operations recorded by the Binlog is consistent with the actual data change order in the database.
Check if the Binlog file is open:
Copy
| mysql> show variables like ‘%log_bin%’; | |
|---|---|
| Variable_name | Value |
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/data/binlog |
| log_bin_index | /usr/local/mysql/data/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
6 rows in set (0.00 SEC) MySQL records all changes to the contents and structure of the database in the mysql-bin.n file instead of SELECT and UPDATE statements that are not actually updated.
If you don’t know which Binlog files are available, you can use the following command:
Copy show binary logs; # show master status; # Check the latest binlog
| mysql> show binary logs; | ||
|---|---|---|
| Log_name | File_size | Encrypted |
| mysql-bin.000001 | 179 | No |
| mysql-bin.000002 | 156 | No |
2 rows in set (0.00 SEC) Binlog = ‘rows in set (0.00 SEC)’;
Copy mysqlbinlog mysql – bin. 000002 | more mysqlbinlog command can look at it.
1
There is a trace to what looks messy. Binlog manages log information in the form of events. You can use the syntax of show Binlog events in to see the details of the current Binlog file.
Copy
| mysql> show binlog events in ‘mysql-bin.000001’; | |||||
|---|---|---|---|---|---|
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
| mysql-bin.000001 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| mysql-bin.000001 | 125 | Previous_gtids | 1 | 156 | |
| mysql-bin.000001 | 156 | Stop | 1 | 179 |
3 rows in set (0.01 SEC) This is a Binlog file with no data written to it.
Binlog is version V4, and you can see that the log ends at Stop. A Stop event occurs in two cases:
If the master shuts down, it will appear at the end of the Binlog file. If the backup machine shuts down, it will say Relay Log at the end, or RESET SLAVE.
Normally a normal Binlog file ends with a Rotate event. Rotate Event is written at the end of the file when the Binlog file exceeds the specified size, pointing to the next Binlog file.
Let’s take a look at what a Binlog file with data operations looks like.
Copy
| mysql> show binlog events in ‘mysql-bin.000002’; | |||||
|---|---|---|---|---|---|
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
| mysql-bin.000002 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| mysql-bin.000002 | 125 | Previous_gtids | 1 | 156 |
2 rows in set (0.00 SEC) 2 rows in set (0.00 SEC) Next we insert a piece of data and look at the Binlog event.
Copy
| mysql> show binlog events in ‘mysql-bin.000002’; | |||||
|---|---|---|---|---|---|
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
| mysql-bin.000002 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| mysql-bin.000002 | 125 | Previous_gtids | 1 | 156 | |
| mysql-bin.000002 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| mysql-bin.000002 | 235 | Query | 1 | 323 | BEGIN |
| mysql-bin.000002 | 323 | Intvar | 1 | 355 | INSERT_ID=13 |
| mysql-bin.000002 | 355 | Query | 1 | 494 | use test_db; INSERT INTO test_db.test_db(name) VALUES (‘xdfdf’) |
| mysql-bin.000002 | 494 | Xid | 1 | 525 | COMMIT / xid=192 / |
7 rows in set (0.00 SEC) This is the BinLog event after adding a data item.
Let’s explain the key fields of the data row in the Event query:
Pos: The start position of the current event, each event occupies a fixed byte size. The end position (End_log_position) minus Pos is the number of bytes the event occupies.
As we can see from the log above, the first event position does not start at 0, but at 4. MySQL uses the first four bytes of the file to determine if this is a Binlog file. This is very common. Many files, such as PDF, DOC, JPG, etc., use the first few characters to determine if they are valid.
EVENT_TYPE: Represents the type of the event
Server_id: Represents the MySQL Server_id that produced this event, configured by setting the server-id option in my.cnf
End_log_position: The starting position of the next event
Info: Contains specific information about the event
Binlog also provides a customizable service for different usage scenarios, providing three modes to provide different levels of detail for logging content.
Statement mode: copy based on SQL Statement (Statement – -based replication – SBR) Row pattern: based on the copy (Row – -based replication – RBR) Mixed mode: The Mixed-Based Replication-MBR Statement schema holds the SQL for each piece of modified data.
This mode only holds a normal SQL statement and does not involve context information for execution.
Because the local environment of each MySQL database may be different, the same statement may not be executed on different machines for processing logical SQL that relies on functions or contextual processing in the local environment.
Things like sleep(), last_insert_id(), and so on depend on the local environment at a particular time.
MySQL V5.1.5 now supports a BINLOG in ROW mode. It differs from the Statement mode in that it does not store specific SQL statements. Instead, it records specific information that has been modified.
For example, an UPDATE Statement updates 10 items of data. In Statement mode, a single SQL is sufficient, but a ROW mode stores what was updated in each Row. There are 10 items.
The pros and cons of the ROW pattern are obvious. Preserving the details of each change is bound to result in a rapid expansion of storage space in return for detailed records of event operations. So the higher the demand, the higher the cost.
Mixed mode Mixed mode is a combination of these two modes. Since the above two modes go to the extremes of minimalism and meticulousness, is it possible to combine them in different usage scenarios?
In Mixed mode, the Statement mode is used to store the Binlog in a normal update Statement, but some function operations that may affect the accuracy of the data are saved in ROW mode. This approach requires you to distinguish which schema to choose based on each specific SQL statement.
MySQL has provided Mixed mode since V5.1.8. Prior to V5.7.7, the default mode is Statement mode. Since then, the default mode is Row mode, but the default mode has been used since versions 8.0 and above.
The format of the current Binlog query is:
Copy
| mysql> show global variables like ‘%binlog_format%’; | |
|---|---|
| Variable_name | Value |
| binlog_format | MIXED |
| default_week_format | 0 |
| information_schema_stats_expiry | 86400 |
| innodb_default_row_format | dynamic |
| require_row_format | OFF |
MySQL > select * from mysqlbinlog where rows in set (0.01sec) MySQL > select * from mysqlbinlog where rows in set (0.01sec); So how do you use Binlog to restore the data state before deletion?
If an error is first detected, stop the MySQL service to prevent further updates.
The binaries are then analyzed using the mysqlbinlog command to see where the locus information was before the misoperation.
The next step is to restore the data. The data in the current database is already wrong, so the data from the beginning position to the site before the misoperation must be correct. If normal data also comes in after the misoperation, the site data of this period of time should be backed up.
Such as:
The misoperation site started with a value of 501, and the misoperation ended with a value of 705. After that, all the correct data were obtained from the site up to 800.
So 0 to 500, 706 to 800 are valid data, and then we can do data recovery.
Start by backing up and clearing the database.
Then use mysqlbinlog to restore the data:
0-500 data:
Copy mysqlbinlog –start-position=0 –stop-position=500 bin-log.000003 > /root/back.sql; The function of the above command is to restore the data from loci 0-500 to a custom SQL file. The same is true for the data of 706-800. Then we execute the two SQL files.
As we mentioned above, different operations can correspond to different types of events, and different modes of Binlog can have different types of events for the same operation. Let’s take a look at some common types of events.
First let’s look at the event type definition in the source code:
Source location: / libbinlogevents/include/binlog_event. H
Copy
enum Log_event_type
{
/**
Every time you update this enum (when you add a type), you have to
fix Format_description_event::Format_description_event().*/
UNKNOWN_EVENT= 0,
START_EVENT_V3= 1,
QUERY_EVENT= 2,
STOP_EVENT= 3,
ROTATE_EVENT= 4,
INTVAR_EVENT= 5,
LOAD_EVENT= 6,
SLAVE_EVENT= 7,
CREATE_FILE_EVENT= 8,
APPEND_BLOCK_EVENT= 9,
EXEC_LOAD_EVENT= 10,
DELETE_FILE_EVENT= 11,
/**
NEW_LOAD_EVENT is like LOAD_EVENT except that it has a longer
sql_ex, allowing multibyte TERMINATED BY etc; both types share the
same class (Load_event)*/
NEW_LOAD_EVENT= 12,
RAND_EVENT= 13,
USER_VAR_EVENT= 14,
FORMAT_DESCRIPTION_EVENT= 15,
XID_EVENT= 16,
BEGIN_LOAD_QUERY_EVENT= 17,
EXECUTE_LOAD_QUERY_EVENT= 18,
TABLE_MAP_EVENT = 19,
/ * *
The PRE_GA event numbers were used for 5.1.0 to 5.1.15 and are
therefore obsolete.*/
PRE_GA_WRITE_ROWS_EVENT = 20,
PRE_GA_UPDATE_ROWS_EVENT = 21,
PRE_GA_DELETE_ROWS_EVENT = 22,
/ * *
The V1 event numbers are used from 5.1.16 until mysql-trunk-xx*/
WRITE_ROWS_EVENT_V1 = 23,
UPDATE_ROWS_EVENT_V1 = 24,
DELETE_ROWS_EVENT_V1 = 25,
/ * *
Something out of the ordinary happened on the master*/
INCIDENT_EVENT= 26,
/ * *
Heartbeat event to be send by master at its idle time
to ensure master's online status to slave*/
HEARTBEAT_LOG_EVENT= 27,
/ * *
In some situations, it is necessary to send over ignorable
data to the slave: data that a slave can handle in case there
is code for handling it, but which can be ignored if it is not
recognized.*/
IGNORABLE_LOG_EVENT= 28,
ROWS_QUERY_LOG_EVENT= 29,
/* Version 2 of the Row events /
WRITE_ROWS_EVENT = 30,
UPDATE_ROWS_EVENT = 31,
DELETE_ROWS_EVENT = 32,
GTID_LOG_EVENT= 33,
ANONYMOUS_GTID_LOG_EVENT= 34,
PREVIOUS_GTIDS_LOG_EVENT= 35,
TRANSACTION_CONTEXT_EVENT= 36,
VIEW_CHANGE_EVENT= 37,
/ Prepared XA transaction terminal event similar to Xid /
XA_PREPARE_LOG_EVENT= 38,
/**
Add new events here - right above this comment!
Existing events (except ENUM_END_EVENT) should never change their numbers*/ ENUM_END_EVENT / end marker / }; There are so many event types that we will not cover them all, but pick out some commonly used ones to look at.
FORMAT_DESCRIPTION_EVENT
Format_description_event was introduced in Binlog V4 to replace the START_EVENT_V3 event in the previous version. It is the first event in the Binlog file, and it occurs only once in the Binlog. MySQL resolves the other events according to the definition of FORMAT_DESCRIPTION_EVENT.
It usually specifies the MySQL version, the Binlog version, and the creation time of the Binlog file.
QUERY_EVENT
An event of type QUERY_EVENT is usually used in the following situations:
A DML operation in STATEMENT format can be performed at the start of a transaction. A DDL operation in ROW format can be performed at the start of a transaction.
Copy
| mysql> show binlog events in ‘mysql-bin.000002’; | |||||
|---|---|---|---|---|---|
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
| mysql-bin.000002 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| mysql-bin.000002 | 125 | Previous_gtids | 1 | 156 | |
| mysql-bin.000002 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| mysql-bin.000002 | 235 | Query | 1 | 323 | BEGIN |
| mysql-bin.000002 | 323 | Intvar | 1 | 355 | INSERT_ID=13 |
| mysql-bin.000002 | 355 | Query | 1 | 494 | use test_db; INSERT INTO test_db.test_db(name) VALUES (‘xdfdf’) |
| mysql-bin.000002 | 494 | Xid | 1 | 525 | COMMIT / xid=192 / |
7 rows in set (0.00 sec)
XID_EVENT
When a transaction commits, whether it is a STATEMENT or a ROW Binlog, an XID_EVENT event is added to the end to indicate the end of the transaction. This event records the ID of the transaction and decides whether to commit the prepared transaction in the storage engine based on the transaction committed in the Binlog during MySQL crash recovery.
ROWS_EVENT
For a ROW formatted Binlog, all DML statements are logged in ROWS_EVENT.
ROWS_EVENTS are divided into three types:
WRITE_ROWS_EVENT
UPDATE_ROWS_EVENT
DELETE_ROWS_EVENT
For INSERT, UPDATE, and DELETE operations, respectively.
For INSERT operations, WRITE_ROWS_EVENT contains the data to be inserted.
For UPDATE operations, UPDATE_ROWS_EVENT contains not only the modified data, but also the pre-modified value.
For the DELETE operation, you only need to specify the primary key to delete (in the absence of a primary key, all columns are given).
Contrast the QUERY_EVENT event, which records the DML operation in text form. For ROWS_EVENT events, which are not textual, you need to specify -vv –base64-output=decode-rows when viewing a row-based Binlog via mysqlbinlog.
To test this, first change the log format to “Rows” :
Copy
mysql> set binlog_format=row;
Query OK, 0 rows affected (0.00 sec)
mysql> mysql> flush logs; Query OK, 0 rows affected (0.01sec)
Then refresh the log file and start a new Binlog. Let’s look at the log after we insert a piece of data:
Copy
| mysql> show binlog events in ‘binlog.000008’; | |||||
|---|---|---|---|---|---|
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
| binlog.000008 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| binlog.000008 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000008 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| binlog.000008 | 235 | Query | 1 | 313 | BEGIN |
| binlog.000008 | 313 | Table_map | 1 | 377 | table_id: 85 (test_db.test_db) |
| binlog.000008 | 377 | Write_rows | 1 | 423 | table_id: 85 flags: STMT_END_F |
| binlog.000008 | 423 | Xid | 1 | 454 | COMMIT / xid=44 / |
In this article, we will learn more about what a Binlog is, what is in it, what is in it, and how to retrieve data from a Binlog. The most important use of Binlog today is for master-slave synchronization, so the next article will look at how to implement master-slave synchronization using Binlog.