Shulou(Shulou.com)05/31 Report--

This article introduces what is the role of binlog and relay-log structure in MySQL. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Binlog action

The main function of binlog is to record the changes of the tables in the database, it only records the sql that changes the data, and the sql that does not change the data will not be written. For example, select statements are generally not recorded, because they will not make any changes to the data.

Take a look at the binlog generation process with an actual scenario, and prepare the sql:

Create table test (text varchar (20)); insert into test values ('test_text'); select * from test; flush logs

View binlog

Show binlog events in 'binlog.000029'

The results displayed are as follows:

Binlog

Alternatively, you can use the mysqlbinlog tool to view the contents of binlog:

Show variables like 'log_%'; # View the log directory mysqlbinlog-- short-form-- force-if-open-- base64-output=never / usr/local/var/mysql/binlog.000029

From the log we can see that the statement to create the table and a Format_ header and Ratate rotation event are executed, which we will talk about later, let's first look at the meaning of several fields.

Log_name represents the name of the log file. For example, my query here is to query binlog.000029 directly, and the default is show binlog events, but this will only query the first binlog, not the currently active binlog. If you don't know what binlog has, you can use the command:

Show binary logs; # View binlog list show master status; # View the latest binlog

Pos represents the location where the file begins.

Event_type represents the type of event.

Server_id is the server ID that creates the event.

End_log_pos represents where the event ends in the file. For example, the first query ends at 723, and after the second insert, the file starts at 723.

Info stands for event information and is a piece of readable text.

Binlog log structure

The structure of the binlog log is about like this. It consists of an index file and a binlog file, in which the binlog event consists of a general header, a commit header and an event body.

First of all, let's talk about the index file. Each line of the index file contains the full file name of a binlog file (similar to host-bin.001). Some commands such as flush logs writing all logs to disk will affect the index file.

Each binlog file consists of several binlog events, with the format description event (Format_description) as the file header (the binlog picture Format_desc event above) and the log rotation event (rotate) as the end of the file.

Format_description contains server information of binlog files, key information of file status, and so on. If the server shuts down or restarts, a new binlog file is created and a new format_description is written. His format is roughly as follows.

2 binlog-version string [50] mysql-server version 4 create timestamp 1 event header length string [p] event type header lengths

The log rotation event contains the file name of the next binlog and the location to start reading. It is added to the end of the file by the server after writing the binlog. The rotation event does not exist every time. The format is as follows.

If binlog-version > 1 {8 position} string [p] name of the next binlog

The binlog event consists of groups (group) of several transactions, each corresponding to a transaction. If the create alter statement is not a transaction statement, then they are themselves a group, and each group either executes all or none of them.

Binlog event structure

Each binlog event consists of three parts:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

A generic header that contains the basic information that all events in binlog have.

Submission header, the content of the submission header is also different for different types of events.

The event body, which stores the main data of the event, is also different for different types of events.

Binlog rotation and cleanup

We can also see from the above example that there is not only one binlog, and based on the real scenario, it is definitely not advisable to write a binlog file all the time, while there are three main scenarios for binlog rotation:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

The server starts, and each time the server starts, a new binlog file is generated.

The maximum size can be controlled by binlog-cache-size, and will be replaced when the maximum size is reached.

Display refresh, flush logs writes all logs to disk, and a new file write is created. You can see from the first example that a new log binlog.000030 file is generated after execution and starts at 4.

As we get more and more binlog files over time, there are two ways to clear binlog:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

By setting expire-logs-days to control the number of days you want to keep binlog log files, the system will clean up automatically.

Manual cleanup through PURGE BINARY LOGS

Relay-log structure

The relay-log relay log is the core of connecting master and slave. Let's take a closer look at its structure and use.

Image-20200909161115718

The structure of relay-log is very similar to that of binlog, except that he has an extra file for master.info and relay-log.info.

Master.info records the location of the binlog that was last read to master synchronized, as well as all the information necessary to connect to master and start replication.

Relay-log.info records the progress of the file copy, and the SQL thread is responsible for updating where the next event starts.

In the last article, we mentioned that the whole replication process looks something like this:

Once we know the structure of binlog and relay-log, let's revisit the flow of the entire link, and here we assume that both master.info and relay-log.info exist:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Master receives a client request statement and writes a record to the binary log before the end of the statement, which may contain multiple events.

At this point, a Slave thread connected to Master,Master reads the log from binlog and sends it to Slave's IO thread.

The IO thread reads from the master.info to the last location of the last write.

The IO thread writes the log to the relay-log relay log, and if the specified relay-log size is exceeded, it writes a rotation event and creates a new relay-log.

Update the last location of the master.info

The SQL thread reads from relay-log.info to the location of the last read

SQL thread reads log events

Execute sql in the database

Update the last location of the relay-log.info

Slave records its own binlog log

But here IO and SQL threads have the problem of generating duplicate events. Take a scenario:

Record the relay log first, and then update the master.info location

The server crashed and failed to write to master.info

The server resumes and synchronizes the last location obtained from master.info again, which will cause the event to be executed repeatedly.

About what the role of binlog and relay-log structures in MySQL is shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.