MySQL — Master/Slave Switchover Protocol based on Site orGTID

The primary library is responsible for all writes and some reads, while other read requests are shared by the secondary library.

Master/standby switchover after the master library fails in a master/multiple slave architecture. There is more process of the slave library pointing to the new master library than one master and one standby.

Location-based active/standby switchover synchronization

To set node B as the slave library of node A ‘, run the change master command:

CHANGE MASTER TO
MASTER_HOST = $host_name
MASTER_POST = $port
MASTER_USER = $user_name
MASTER_PASSWORD = $password
MASTER_LOG_FILE = $master_log_name
MASTER_LOG_POS = $master_log_pos
Copy the code

• MASTER_HOST, MASTER_PORT, MASTER_USER, and MASTER_PASSWORD meet the requirements of the IP address, port, user name, and password of the master library A ‘respectively
• MASTER_LOG_FILE and MASTER_LOG_POS are the file name and log offset for the master library, which is the synchronization point

For node B to be set as A ‘slave, the locus parameter must be set.

Originally, node B is the slave library of A, and the loci of A are also recorded locally. But for the same log, the locus of A is different from that of A ‘. Therefore, it is necessary to find the synchronization point before switching.

(One of the reasons for this discrepancy is that the secondary database has enabled parallel replication, such as group commit parallelism. In this case, the transaction that was executed first on the primary database may not be executed first on the secondary database, and the binlog will be different.)

Methods to find synchronization sites:

1. Wait for the new master database A ‘to complete the synchronization of all the relay logs

2. Run the show master status command on A ‘to obtain the latest file and position on A’

3. Take the fault time T of the original master library A

4. Use mysqlbinlog to parse the file of A ‘and obtain the location 123 at time T

But that’s not exact:

Suppose that at time T, primary library A has completed an INSERT statement to insert A row of data R, and has passed binlog to A ‘and B, and then the host of primary library A is powered down immediately after the transfer.

The system status is as follows:

1, on the slave library B, the row R already exists because the binlog has been synchronized

2, on the new master library A ‘, the line R also exists, after the log uninstalls 123 position

3, execute the change master command on the secondary library B, point to file 123 of A ‘, the binlog inserted into R will be synchronized to the secondary library B, the primary key conflict will occur, and then stop the synchronization

Therefore, when switching the master library, we should actively skip some errors caused by “repeated operations” to avoid switching task blocking.

SQL > execute transaction skip command on db

set global sql_slave_skip_counter = 1;
start slave;
Copy the code

Stop each time you encounter an error and execute the skip command until no more errors are reported.

2. Skip specified errors by setting the slave_skip_errors parameter

During the active/standby switchover, two types of errors are commonly encountered:

1062Unique key conflict while inserting data1032Rows not found while deleting dataCopy the code

Set slave_skip_errors to “1032,1062” and skip these two errors.

Note that these two errors can be skipped without loss during a master/slave switchover. After the synchronization is complete, set all parameters to null to prevent the synchronization from being skipped even when the master and slave are inconsistent.

Synchronization of active/standby switchover based on GTID

GTID = server_uuid:gno
Copy the code

GTID = source_id:transaction_id
Copy the code

CHANGE MASTER TO 
MASTER_HOST=$host_name 
MASTER_PORT=$port 
MASTER_USER=$user_name 
MASTER_PASSWORD=$password 
master_auto_position=1 
Copy the code