Repair solution for inconsistent MySQL GTID master and slave

Solution 1: Rebuild Replicas

Prerequisites
advantage
shortcoming
Procedure

Master
Slave

Solution 2: Use percona-toolkit for data repair

Prerequisites

advantage

shortcoming

Procedure

Background Example
Verify consistency

Solution 1: Rebuild Replicas

MySQL 5.6 and above introduces new global transaction ID (GTID) support in replication. When performing backups of MySQL and MySQL 5.7 with GTID mode enabled, Percona XtraBackup automatically stores the GTID value in xtrabackup_binlog_info. This information can be used to create new (or repair damaged) GTID-based replicas.

Prerequisites

Percona xtrabackup needs to be installed on the MySQL machine

advantage

Relatively safe and easy to operate

shortcoming

When the amount of data is large, the backup time will be longer.
When the database has read-write separation, the read requests undertaken by the Slave need to be transferred to the Master

Procedure

Master

Use the xtrabackup tool on the Master to back up the current database. The user who executes this command needs to have permission to read the MySQL data directory.

innobackupex --default-file=/etc/my.cnf --user=root -H 127.0.0.1 --password=[PASSWORD] /tmp

Copy the backup file to the Slave machine

Slave

Execute this command on the Slave machine to prepare the backup file

innobackupex --default-file=/etc/my.cnf --user=root -H 127.0.0.1 --password=[PASSWORD] --apply-log /tmp/[TIMESTAMP]

Back up and delete the Slave data directory

systemctl stop mysqld
mv /data/mysql{,.bak}

Copy the backup to the target directory, grant the corresponding permissions, and then restart the Slave

innobackupex --default-file=/etc/my.cnf --user=root -H 127.0.0.1 --password=[PASSWORD] --copy-back /tmp/[TIMESTAMP]
chmod 750 /data/mysql
chown mysql.mysql -R /data/mysql
systemctl start mysqld

View the last GTID of the current backup that has been executed, as shown in the following example

$ cat /tmp/[TIMESTAMP]/xtrabackup_binlog_info
mysql-bin.000002 1232 c777888a-b6df-11e2-a604-080027635ef5:1-4

This GTID will also be printed out after the innobackupex backup is completed.

innobackupex: MySQL binlog position: filename 'mysql-bin.000002', position 1232, GTID of the last change 'c777888a-b6df-11e2-a604-080027635ef5:1-4'

NewSlave > RESET MASTER;
NewSlave > SET GLOBAL gtid_purged='c777888a-b6df-11e2-a604-080027635ef5:1-4';
NewSlave > CHANGE MASTER TO
       MASTER_HOST="$masterip",
       MASTER_USER="repl",
       MASTER_PASSWORD="$slavepass",
       MASTER_AUTO_POSITION = 1;
NewSlave > START SLAVE;

Check whether the replication status of the slave is normal

NewSlave > SHOW SLAVE STATUS\G
     [..]
     Slave_IO_Running: Yes
     Slave_SQL_Running: Yes
     [...]
     Retrieved_Gtid_Set: c777888a-b6df-11e2-a604-080027635ef5:5
     Executed_Gtid_Set: c777888a-b6df-11e2-a604-080027635ef5:1-5

We can see that the replica has retrieved the new transaction numbered 5, so transactions from 1 to 5 are already on this replica. In this way, we have completed the construction of a new replica.

Solution 2: Use percona-toolkit for data repair

The PT toolkit contains two tools, pt-table-checksum and pt-table-sync, which are mainly used to detect whether the master and slave are consistent and to repair data inconsistencies.

Prerequisites

The percona-toolkit tool needs to be installed on the MySQL machine

advantage

The repair speed is fast and there is no need to stop the slave library

shortcoming

The operation is complicated. Back up the database before the operation. The table to be repaired needs to have a unique constraint.

Procedure

Background Example

IP relationship mapping

| IP | Role |
| ---- | ---- |
| 192.168.100.132 | Master |
| 192.168.100.131 | Slave |

Assume that the table structure to be restored is as follows

mysql> show create table test.t;
+-------+-------------------------------------
| Table | Create Table |
+-------+-------------------------------------
| t | CREATE TABLE `t` (
 `id` int(11) NOT NULL,
 `content` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------

In the case of normal master-slave consistency, the data of Master and Slave are as follows

mysql> select * from test.t;
+----+---------+
| id | content |
+----+---------+
| 1 | a |
| 2 | b |
+----+---------+
2 rows in set (0.00 sec)

In extreme cases, if the following master-slave inconsistency occurs, the situation is as follows:

The Master adds a record with id 3, as shown below, but it is not synchronized to the Slave and automatically fails over to the Slave.
After the Old Slave has served as the New Master for a period of time, new records are added to the table.

After restarting the Old Master, the Old Master data is as follows:

old_master> select * from test.t;
+----+---------+
| id | content |
+----+---------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+---------+
3 rows in set (0.00 sec)

The data of New Master is as follows:

new_master> select * from test.t;
+----+---------+
| id | content |
+----+---------+
| 1 | a |
| 2 | b |
| 3 | cc |
| 4 | dd |
+----+---------+
4 rows in set (0.00 sec)

At this time, if the old master is configured as the slave of the new master, an error will be reported, such as the following error

...Last_IO_Error: binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID.

You can see that the GTID of the Old Master has reached 255

Executed_Gtid_Set: 5b750c75-86c2-11eb-af71-000c2973a2d5:1-10,
60d082ee-86c2-11eb-a9df-000c2988edab:1-255

The GTID of the New Master is only 254

mysql> show master status\G
*************************** 1. row ***************************
       File:mysql-bin.000001
     Position: 4062
   Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 5b750c75-86c2-11eb-af71-000c2973a2d5:1-2,
60d082ee-86c2-11eb-a9df-000c2988edab:1-254
1 row in set (0.00 sec)

At this point we configure the Old Master to skip the error and restore the Old Master to a state where it can replicate normally from the New Master

old_master> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

old_master> set gtid_next='60d082ee-86c2-11eb-a9df-000c2988edab:254'; --Specify the version of the next transaction, the GTID you want to skip
Query OK, 0 rows affected (0.00 sec)

old_master> begin;
Query OK, 0 rows affected (0.00 sec)

old_master> commit; -- Inject an empty transaction
Query OK, 0 rows affected (0.00 sec)

old_master> set gtid_next='AUTOMATIC'; -- Restore to automatic GTID
Query OK, 0 rows affected (0.00 sec)

old_master> start slave;
Query OK, 0 rows affected (0.13 sec)

Then we can see that replication is proceeding normally on the Old Master

mysql> show slave status\G
      ...
       Slave_IO_Running: Yes
      Slave_SQL_Running: Yes
      ...
      Executed_Gtid_Set: 5b750c75-86c2-11eb-af71-000c2973a2d5:1-10,
60d082ee-86c2-11eb-a9df-000c2988edab:1-255
        Auto_Position: 1
     Replicate_Rewrite_DB:
         Channel_Name:
      Master_TLS_Version:

Finally, we clear slave_master_info on the New Master

new_master> reset slave all for channel '';
Query OK, 0 rows affected (0.00 sec)

new_master> show slave status\G;
Empty set (0.01 sec)

Verify consistency

Next, we need to verify the master-slave consistency. Execute pt-table-checksum on the New Master. ROWS is 4 and there is a DIFFS

[root@localhost ~]# pt-table-checksum h='127.0.0.1',u='mha',p='[PASSWORD]',P=3306 --no-check-binlog-format --databases test
Checking if all tables can be checksummed ...
Starting checksum ...
      TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
03-29T19:24:18 0 1 4 1 1 0 0.322 test.t

Two-way synchronization (synchronization operation will modify data, data backup is performed before the operation)

During the synchronization process, pt-table-sync will modify data on the Master. The parameters of pt-table-sync are as follows:

pt-table-sync --databases test --bidirectional --conflict-column='*' --conflict-comparison 'newest' h='192.168.100.132',u='mha',p='[PASSWORD]',P=3306 h='192.168.100.131' --print
--database specifies the database to be executed --bidirectional is bidirectional synchronization --conflict-column compares the column when a conflict occurs --conflict-comparison conflict comparison strategy --print outputs comparison results --dry-run test run --execute execute test # The DSN on the left is the Slave
# The DSN on the right is the Master

Here we specify —conflict-name='content' as the comparison column, and the business primary key is generally used as this column. You can see that the statements to be executed are printed out

[root@localhost ~]# pt-table-sync --databases test --bidirectional --conflict-column='content' --conflict-comparison 'newest' h='192.168.100.132',u='mha',p='[PASSWORD]',P=3306 h='192.168.100.131' --print
/*192.168.100.132:3306*/ UPDATE `test`.`t` SET `content`='cc' WHERE `id`='3' LIMIT 1;
/*192.168.100.132:3306*/ INSERT INTO `test`.`t`(`id`, `content`) VALUES ('4', 'dd');

Next, execute the statement

[root@localhost ~]# pt-table-sync --databases test --bidirectional --conflict-column='content' --conflict-comparison 'newest' h='192.168.100.132',u='mha',p='[PASSWORD]',P=3306 h='192.168.100.131' --execute

Then perform data comparison again on the Master, and you can see that the data is normal.

[root@localhost ~]# pt-table-checksum h='127.0.0.1',u='mha',p='[PASSWORD]',P=3306 --no-check-binlog-format --databases test
Checking if all tables can be checksummed ...
Starting checksum ...
      TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
03-30T12:09:57 0 0 4 0 1 0 0.330 test.t

The above is the detailed content of the repair solution for MySQL GTID master-slave inconsistency. For more information about MySQL GTID master-slave inconsistency repair, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in: