Common repair methods for MySQL master-slave replication disconnection

01 Problem Description

In a production environment, we often encounter situations where MySQL master-slave replication is disconnected. When master-slave replication is disconnected, usually the steps to solve the problem are as follows:

1. Use show slave status on the database to view the intuitive reason for the replication disconnection and record the current replication location

2. Check the error log to analyze the reasons for the replication disconnection in more detail

3. Repair the master-slave replication relationship

4. If the replication relationship cannot be repaired, you need to rebuild the slave database

02 Solution

There are various reasons why the master-slave replication relationship is broken. Sometimes, we don’t have time to objectively analyze the cause because the application is unusable and needs to be restored immediately. In this case, we must make a trade-off between the replication break problem and service availability and then deal with it accordingly.

Common methods for solving master-slave replication breaks are as follows:

1. Find other slave libraries and replace them quickly

This method requires that your application has an architecture of at least one master and two slaves. If a problem occurs in one of the slave libraries, the other slave library can be quickly brought online to restore application access, and the specific cause of the problem in the failed slave library can be investigated later.

2. Skip the error of failed copy

In some cases, we can determine the reason for the master-slave replication break. For example, if there is one more database db_1 on the master than on the slave, then when we execute drop database db_1 on the master, the replication of the slave will definitely be disconnected. In this case, we can solve it by skipping a transaction.

Method 1: (directly skip the current transaction)

In GTID mode, this can be solved by the following command:

mysql> STOP SLAVE;
mysql> SET GTID_NEXT='xxxxxx:yyy'; ----- Set the gtid event to be skipped
mysql> BEGIN;COMMIT;
mysql> SET GTID_NEXT='AUTOMATIC';
mysql> START SLAVE;

In non-GTID mode, this can be solved by the following command:

stop slave;
set sql_slave_skip_counter=1;
start slave;

Method 2: (Specify a new location)

If we know the specific location of the next transaction through binlog analysis, we can also solve it by specifying the specific location of the next transaction:

In GTID mode:

mysql> STOP SLAVE;
mysql> RESET MASTER;
mysql> SET @@GLOBAL.GTID_PURGED ='xxxxxxx:yyyyyy' ----- indicates that these gtid events have been executed mysql> START SLAVE;

Note that GTID_PURGED must be GLOBAL. The above command can also be written as set global gtid_purged='xxx:yyy'

In non-GTID mode:

stop slave;
change master to master_log_file='mysql-bin.001360',master_log_pos=676383371;
start slave;

Method 3: pt-slave-restart tool

If we skip a transaction and there is still a disconnection (for example, we deleted 100 data on the slave database, but the master database wants to update these 100 data), we can use the pt-slave-restart tool, which can continuously skip the disconnected position.

Here’s how to use it:

pt-slave-restart -h 10.xxx.xxx.xxx -P port -u user -p password

When we use parallel replication, pt-slave-restart may report an error. At this time, we can change the parallel replication to single-threaded replication, and then use the pt-slave-restart tool. You can refer to this article:

pt-slave-restart tool

Method 4: Set the parameter slave_exec_mode

This parameter can modify the slave library execution mode during the master-slave replication process. If it is set to strict mode, all replication will stop once an error is reported. If it is set to idempotent mode, errors with specific error numbers will be skipped. The command is as follows:

set global slave_exec_mode = idempotent

For details, please refer to the previous article:

Introduction to three parameters of MySQL replication problem

There are two other parameters for skipping replication errors in this article, namely slave_skip_errors and sql_slave_skip_counter.

3. Use backup to rebuild the slave library

This method is not used in many scenarios. Usually, this method is considered only when the slave library is unavailable or cannot be synchronized with the master library. For example, the reset master operation is executed on the master library, causing all binlogs to be cleared. In this case, the slave library cannot obtain and read the correct binlog, and the replication will be disconnected. In this case, rebuilding the slave library may be the only way.

The above is the details of the commonly used repair methods for MySQL master-slave replication disconnection. For more information on repairing MySQL master-slave replication disconnection, please pay attention to other related articles on 123WORDPRESS.COM!