The mysql GTID master-slave replication fails and the synchronization process is resumed without stopping

GTID realizes master-slave replication data synchronization

GTID is a successfully executed global transaction ID generated based on the original mysql server. It consists of server ID and transaction ID. this global transaction ID is unique not only on the original server, but also on all mysql servers with master-slave relationship. Officially, such a feature makes mysql master-slave replication easier and database consistency more reliable.

 

introduce

Concept of GTID

  1. global transaction identifiers
  2. GTID is a transaction with one-to-one correspondence and globally unique ID
  3. A GTID is executed only once on a server to avoid data confusion and inconsistency caused by repeated execution
  4. No longer use the traditional MASTER_LOG_FILE+MASTER_LOG_POS starts replication, but uses master_ AUTO_ Start replication with position = 1.
  5. Supported from MYSQL-5.6.5 and later versions

 

Composition of GTID

GTID = server_uuid:transaction_id

server_uuid: the unique identifier of the mysql server. Viewing method: Show variables like '% server' in the mysql client_ uuid%';

transaction_id: this id is a serial number of the transaction submitted in the current server, increasing from 1. A value corresponds to a transaction

GTID number example: c9fba9e2-db3b-11eb-81d4-000c298d8da1:1-5

 

Advantages of GTID

  1. It's easier to implement master-slave. You don't have to look for log as before_ File and log_pos
  2. Safer than traditional master-slave
  3. GTID is continuous without holes, ensuring data consistency and zero loss.

 

How GTID works

  1. When a transaction is executed and committed at the primary database end, the GTID is generated and recorded in the binlog log together;
  2. After the binlog is transferred to the slave and stored in the slave's relaylog, the GTID is read

 

Start configuring GTID replication

Main: 192.168.152.253 Centos7

From: 192.168.152.252 Centos8

Test database: vfan

Test form: student

 

1. Modify the mysql service configuration file, add the following parameters, and then restart:

server-id=100    #server id
log-bin=/var/lib/mysql/mysql-bin  #Open binlog and specify the storage location
expire_logs_days=10  #The log is saved for 10 days
gtid_mode=on  #gtid module switch
enforce_gtid_consistency=on  #To start gtid strong consistency, this function must be enabled to start gtid module.
binlog_format=row  #bin_log log format, there are three types: state, ROW and MIXED; The default is state
skip_slave_start=1  #Prevent replication from starting automatically with mysql startup

The configuration of the master server and the slave server can be the same, and the server ID can be changed

 

2. Create users connected from the server in the master server

CREATE USER 'copy'@'192.168.152.252' IDENTIFIED BY 'copy';
GRANT REPLICATION SLAVE ON *.* TO 'copy'@'192.168.152.252';
flush privileges;

After creation, remember to test whether the slave machine can log in successfully

 

3. Use mysqldump to synchronize the data of the two databases

main mysql Execution:
mysqldump -uroot -proot1 vfan > dump2.sql
scp dump2.sql 192.168.152.252:/data/

from mysql Execution:
mysql> source /data/dump2.sql

 

The current master and slave server data contents are the same, which are the following data:

mysql> select * from student;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Tony |  18 |
|  2 | Any  |  17 |
|  3 | Goy  |  20 |
|  4 | Baly |  18 |
|  5 | Heg  |  19 |
|  6 | hhh  | 100 |
|  7 | lll  |  99 |
+----+------+-----+
7 rows in set (0.01 sec)

 

4. Enable master-slave replication

mysql> CHANGE MASTER TO MASTER_HOST='192.168.152.253',MASTER_USER='copy',MASTER_PASSWORD='copy',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

## View slave status
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.152.253
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000014
          Read_Master_Log_Pos: 897
               Relay_Log_File: kubenode2-relay-bin.000002
                Relay_Log_Pos: 416
        Relay_Master_Log_File: mysql-bin.000014
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

 

5. Check for synchronization

Insert data into primary server:
mysql> INSERT INTO student(name,age) VALUES('gogoo',50),('zhazha',25);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

Read from server:
mysql> select * from student;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | Tony   |  18 |
|  2 | Any    |  17 |
|  3 | Goy    |  20 |
|  4 | Baly   |  18 |
|  5 | Heg    |  19 |
|  6 | hhh    | 100 |
|  7 | lll    |  99 |
|  8 | gogoo  |  50 |
|  9 | zhazha |  25 |
+----+--------+-----+
9 rows in set (0.00 sec)

The data has been synchronized, and the basic master-slave replication has been completed

 

Now simulate a master-slave replication architecture in which the slave server fails to replicate halfway and the master server is no longer synchronized. It is required to keep the business for data synchronization repair and restore consistency.

1. First, simulate a scenario of data insertion

vim insert.sh

#!/usr/bin/env bash

values=(`find /usr/ -type d | awk -F '/' '{print $NF}' | sort -u`)

while true
do
age=$(( $RANDOM%100 ))
name=${values[$(( $RANDOM%6 ))]}

mysql -h127.1 -P3306 -uroot -proot1 -e "INSERT INTO vfan.student(name,age) VALUES('"${name}"',${age});" &> /dev/null 
sleep $(( $RANDOM%5 ))
done

Run the script, and the data is inserted randomly (insertion interval < 5S)

Current primary mysql data:

mysql> select * from student;
+----+---------------------+-----+
| id | name                | age |
......
|  97 | _                   |   2 |
|  98 | 00bash              |  15 |
|  99 | 00bash              |  52 |
| 100 | 00bash              |  43 |
| 101 | _                   |  65 |
| 102 | 00                  |  67 |
+-----+---------------------+-----+
102 rows in set (0.01 sec)

 

2. The data is still being inserted one after another. At this time, simulate the downtime or abnormality of the slave node (stop slave directly here)

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

 

3. At this time, the master database data is still increasing, while the slave database is not synchronized. The following is the slave database data:

mysql> select * from student;
+----+---------------------+-----+
| id | name                | age |
......
| 82 | 00bash              |  50 |
| 83 | 00systemd-bootchart |  36 |
| 84 | 00bash              |  48 |
| 85 | 00systemd-bootchart |  41 |
| 86 | 00                  |  72 |
+----+---------------------+-----+
86 rows in set (0.00 sec)

 

4. Start restoring data from library

Idea:

First, back up the current data in full through mysqldump. Since the business cannot be affected, the table cannot be locked when mysqldump data. To keep data written

Because the data is still being written during mysqldump, some data will still be incomplete synchronization. Therefore, after importing the data of mysqldump, skip the GTID transaction contained in the dump, re-establish the master-slave configuration, start the slave thread, recover the data and synchronize.

 

(1) mysqldump does not lock table backup data

mysqldump -uroot -proot1 --single-transaction --master-data=2 -R vfan | gzip > dump4.sql

Main parameters: -- single transaction

 

(2) View the GTID number of the current mysqldump export data

[root@TestCentos7 data]# grep GLOBAL.GTID_PURGED dump4.sql 
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'c9fba9e2-db3b-11eb-81d4-000c298d8da1:1-228';

The above c9fba9e2-db3b-11eb-81d4-000c298d8da1:1-228 indicates the GTID transaction number executed by the MASTER

 

(3) Import from database

scp dump4.sql 192.168.152.252:/data

mysql In client:
mysql> source /data/dump4.sql

At this time, from the library data:
mysql> select * from student;
| 230 | 00                  |  53 |
| 231 | 00bash              |  66 |
| 232 | _                   |  18 |
| 233 | 0.33.0              |  98 |
| 234 | 00bash              |  14 |
+-----+---------------------+-----+
234 rows in set (0.00 sec)

Master database data:
| 454 | _                   |  46 |
| 455 | 03modsign           |  59 |
| 456 | 00systemd-bootchart |  77 |
| 457 | 03modsign           |   6 |
| 458 | 0.33.0              |  88 |
+-----+---------------------+-----+
458 rows in set (0.00 sec)

After recovering part of the database data to 234 rows, the main database data is still increasing, which is 458

 

(4) Since our mysqldump data already contains 1-228 transactions executed by the MASTER, we should ignore these transactions and no longer synchronize them when we synchronize SLAVE, otherwise an error similar to this will appear:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.152.253
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 137827
               Relay_Log_File: kubenode2-relay-bin.000002
                Relay_Log_Pos: 417
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
                   Last_Errno: 1062
                   Last_Error: Could not execute Write_rows event on table vfan.student; Duplicate entry '87' for key 'student.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000002, end_log_pos 10588

 

To skip certain gtids, SLAVE must guarantee gtids_ Only when the purged parameter is empty can it be skipped correctly. Check the current gtid_purged:

mysql> show global variables like '%gtid%';
+----------------------------------+-------------------------------------------------------------------------------------+
| Variable_name                    | Value                                                                               |
+----------------------------------+-------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                                                                  |
| enforce_gtid_consistency         | ON                                                                                  |
| gtid_executed                    | b30cb2ff-32d4-11eb-a447-000c292826bc:1-2,
c9fba9e2-db3b-11eb-81d4-000c298d8da1:1-80 |
| gtid_executed_compression_period | 1000                                                                                |
| gtid_mode                        | ON                                                                                  |
| gtid_owned                       |                                                                                     |
| gtid_purged                      | c9fba9e2-db3b-11eb-81d4-000c298d8da1:1-70                                           |
| session_track_gtids              | OFF                                                                                 |
+----------------------------------+-------------------------------------------------------------------------------------+
8 rows in set (0.02 sec)

 

Current gtid_purged is not empty, so we need to set it to be empty first and execute:

mysql> reset master;
Query OK, 0 rows affected (0.05 sec)

mysql> show global variables like '%gtid%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| binlog_gtid_simple_recovery      | ON    |
| enforce_gtid_consistency         | ON    |
| gtid_executed                    |       |
| gtid_executed_compression_period | 1000  |
| gtid_mode                        | ON    |
| gtid_owned                       |       |
| gtid_purged                      |       |
| session_track_gtids              | OFF   |
+----------------------------------+-------+
8 rows in set (0.00 sec)

 

(5)gtid_ Reset SLAVE after purged is empty

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> reset slave all;
Query OK, 0 rows affected (0.02 sec)

 

(6) After reset, set the skipped GTID and resynchronize the MASTER

mysql> SET @@GLOBAL.GTID_PURGED='c9fba9e2-db3b-11eb-81d4-000c298d8da1:1-228';
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.152.253',MASTER_USER='copy',MASTER_PASSWORD='copy',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

 

(7) Start the SLAVE process and check the synchronization status

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.152.253
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 137827
               Relay_Log_File: kubenode2-relay-bin.000002
                Relay_Log_Pos: 84993
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 137827
              Relay_Log_Space: 85206
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 100
                  Master_UUID: c9fba9e2-db3b-11eb-81d4-000c298d8da1
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: c9fba9e2-db3b-11eb-81d4-000c298d8da1:229-519
            Executed_Gtid_Set: c9fba9e2-db3b-11eb-81d4-000c298d8da1:1-519
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

You can see that the synchronization is normal!

 

(8) Finally, check whether the master and slave data are consistent

MASTER Data: SELECT * FROM student;
| 520 | 00systemd-bootchart |  18 |
| 521 | 00systemd-bootchart |  44 |
| 522 | 03modsign           |  98 |
| 523 | 00systemd-bootchart |  45 |
| 524 | 00                  |  90 |
| 525 | 03modsign           |  21 |
+-----+---------------------+-----+
525 rows in set (0.00 sec)

SLAVE Data: SELECT * FROM student;
| 519 | 0.33.0              |  99 |
| 520 | 00systemd-bootchart |  18 |
| 521 | 00systemd-bootchart |  44 |
| 522 | 03modsign           |  98 |
| 523 | 00systemd-bootchart |  45 |
| 524 | 00                  |  90 |
| 525 | 03modsign           |  21 |
+-----+---------------------+-----+
525 rows in set (0.00 sec)

The data inserted during our repair has also been synchronized. The data is completely consistent, and the master-slave replication and repair are completed.

Tags: Database MySQL

Posted by dharprog on Thu, 05 May 2022 06:29:39 +0300