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
- global transaction identifiers
- GTID is a transaction with one-to-one correspondence and globally unique ID
- A GTID is executed only once on a server to avoid data confusion and inconsistency caused by repeated execution
- No longer use the traditional MASTER_LOG_FILE+MASTER_LOG_POS starts replication, but uses master_ AUTO_ Start replication with position = 1.
- 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
- It's easier to implement master-slave. You don't have to look for log as before_ File and log_pos
- Safer than traditional master-slave
- GTID is continuous without holes, ensuring data consistency and zero loss.
How GTID works
- When a transaction is executed and committed at the primary database end, the GTID is generated and recorded in the binlog log together;
- 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.