Several log and pos details in mysql master-slave replication Construction

Master-slave replication is an old topic. I will not talk about the details of master-slave replication here. I will focus on the differences between the logs and locations in show slave status \ G;

First, cut a graph to facilitate the explanation:

As shown in the figure, the more important parameter is * log and * pos. How can we differentiate them and what do they mean?

Let's first talk about the principle of master-slave replication:

I. Master-slave Principle

Replication thread

MySQL replication is an asynchronous replication process, from one MySQL instace (we call it the master) to another MySQL instance (we call it the slave ). The entire replication process between the master and slave is mainly completed by three threads, two of which (SQL thread and IO thread) are on the slave side, and the other (IO thread) on the master side.

To implement MySQL replication, you must first enable the master

Binary log (mysql-bin.xxxxxx) functionality, otherwise it cannot be implemented. The whole replication process is actually because slave obtains the log from the master end and then executes the operations recorded in the log in full order on itself. To enable the binary log of MySQL, you can use the "-log-bin" parameter option during MySQL server startup, or. the "Log-bin" parameter item is added to the mysqld parameter group (the parameter section marked by [mysqld]) in the CNF configuration file.

The basic process of replication is as follows:

1. The IO thread on the Slave connects to the Master, and requests the log content after the specified location of the specified log file (or from the beginning of the log;

2. After the Master receives a request from the Slave IO thread, it reads the log information at the specified location of the specified Log Based on the Request Information and returns it to the Slave IO thread. In addition to the information contained in the Log, the returned information also includes the name of the Binary Log file on the Master end and its location in the Binary Log;

3. after the Slave IO thread receives the information, it writes the received Log content to the end of the Relay Log file (mysql-relay-bin.xxxxxx) at the Slave end in sequence, and record the file name and location of the bin-log on the Master end to the master-info file, so that the next read can clearly show the High-Speed Master "I need to start from the location of a bin-log, please send it to me"

4. after the Slave SQL thread detects the newly added content in the Relay Log, it will immediately parse the content in the Log file into the executable Query statements during actual execution on the Master end, and execute these queries on your own. In this way, the same Query is actually executed on the Master and Slave ends, so the data at both ends is exactly the same.

Simply put, the slave database first reads the binary file (Master_Log_File) and location (Read_Master_Log_Pos) of the master database through the io thread and then caches the Relay_Log_File to the local (slave Database Server) relay_Log_Pos, and then read the relay file (Relay_Log_File) from the SQL thread of the database ), the execution of this SQL thread records the file (Relay_Master_Log_File) and location (Exec_Master_Log_Pos) that has been executed ).

Illustration:

In this case, we will be surprised that the replication process is very simple. It seems that three log files are not used, but only two log files are displayed;

Since three log files and their locations are displayed in the slave status, let's take a look at their definitions and explain them later;

Ii. Log Interpretation

L Master_Log_File and Read_Master_Log_Pos record the current master binlog file and location read by the IO thread, corresponding to the binlog file and location of the master.

L Relay_Log_File, Relay_Log_Pos records the file and location where the SQL thread executes the relay log, which corresponds to the relay log file and location on the slave.

L Relay_Master_Log_File: Exec_Master_Log_Pos records the files and locations where the SQL thread executes the binlog on the master node and the binlog files and locations on the master node.

After reading the definition, you can better understand the master-slave replication process above.

Iii. Log details

1. Let's take a look at the common binlog files, text files parsed using mysqlbinlog:

Here we mainly use the row-based binlog.

We can see that the start position of the BINLOG event statement is the byte offset of the binary BINLOG file. You can find the starting position of the next event based on the end_log_pos of the previous event, as shown in.

2. Let's look at relay_log, which can also be parsed using mysqlbinlog tool (not the same machine ):

The relay_log and BINLOG record methods are basically the same. The biggest difference is that end_log_pos records the location of the event in the BINLOG file of the master, rather than the location of the relay log event ., The end_log_pos of the previous event is different from the start position of the next relay log event.

Why do I need to set it like this? The reason is very simple, that is, to easily locate the location of the master BINLOG, it is of little significance to record the start offset of the next event of the relay log on the slave, but if the offset of the master BINLOG is recorded, in SQL thread, we can determine the location of a BINLOG on the master node. Which BINLOG column is used. We can find the front of relay_log.

· IO thread records all binlogs read from the master to the local BINLOG, so the end log_pos of the last event of the relay log is read_master_log_pos.

· The SQL thread is executed according to the transaction. Therefore, exec_master_log_pos corresponds to the end_log_pos of the last transaction event in the relay log, which corresponds to the BINLOG location of the master.

· Relay_log_pos records the end position of the event executed by the SQL thread in the relay log. This is the offset of the relay log.

From the database information obtained by other servers, we only need relay_master_log_file & exec_master_log_pos to create a new slave database.