GTID实现主从复制数据同步
GTID是一个基于原始mysql服务器生成的一个已经被成功执行的全局事务ID,它由服务器ID以及事务ID组成,这个全局事务ID不仅仅在原始服务器上唯一,在所有主从关系的mysql服务器上也是唯一的。正式因为这样一个特性使得mysql主从复制变得更加简单,以及数据库一致性更可靠。
介绍
GTID的概念
全局事务标识:global transaction identifiers
GTID是一个事务一一对应,并且全局唯一ID
一个GTID在一个服务器上只执行一次,避免重复执行导致数据混乱不一致
不再使用传统的MASTER_LOG_FILE+MASTER_LOG_POS开启复制,而是采用MASTER_AUTO_POSTION=1的方式开启复制。
从MYSQL-5.6.5及后续版本开始支持
GTID的组成
GTID = server_uuid:transaction_id
server_uuid:mysql服务器的唯一标识,查看方法mysql客户端内:show variables like '%server_uuid%';
transaction_id:此id是当前服务器中提交事务的一个序列号,从1开始自增长,一个数值对应一个事务
GTID号示例:c9fba9e2-db3b-11eb-81d4-000c298d8da1:1-5
GTID的优势
实现主从更简单,不用像以前一样寻找log_file和log_pos
比传统的主从更加安全
GTID是连续没有空洞的,保证数据一致性,零丢失。
GTID工作原理
master更新数据时,会在事务前产生GTID,一同记录到binlog日志中
slave端的I/O线程将变更的binlog,写入到本地的relay log中
SQL线程从relay log中获取GTID,然后对比slave端的binlog是否有记录(所以MySQL5.6 slave端必须开启binlog)
如果有记录,说明该GTID的事务已经执行,slave会忽略
如果没有记录,slave就会从relay log中执行该GTID的事务,并记录到binlog
在解析过程中会判断是否有主键,如果没有就用二级索引,如果没有就用全部扫描
开始配置GTID复制
主:192.168.152.253 Centos7
从:192.168.152.252 Centos8
测试数据库:vfan
测试表:student
1、修改mysql服务配置文件,添加以下参数,随后重启:
server-id=100 #server idlog-bin=/var/lib/mysql/mysql-bin #开启binlog并指定存储位置
expire_logs_days=10#日志保存时间为10天
gtid_mode=on #gtid模块开关
enforce_gtid_consistency=on #启动GTID强一致性,开启gtid模块必须开启此功能。
binlog_format=row #bin_log日志格式,共有三种STATEMENT、ROW、MIXED;默认为STATEMENT
skip_slave_start=1 #防止复制随着mysql启动而自动启动
主服务器和从服务器的配置一致即可,server-id更改一下
2、在主服务器中创建从服务器连接的用户
CREATE USER 'copy'@'192.168.152.252' IDENTIFIED BY 'copy';
GRANT REPLICATION SLAVE ON*.* TO 'copy'@'192.168.152.252';
flush privileges;
创建完毕记得要测试下slave机是否能登录成功
3、使用mysqldump使两数据库数据同步
主mysql执行:
mysqldump-uroot -proot1 vfan >dump2.sqlscp dump2.sql 192.168.152.252:/data/从mysql执行:
mysql> source /data/dump2.sql
当前主、从服务器数据内容一致,都是以下数据:
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、开启主从复制
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.04sec)
mysql>start slave;
Query OK,0 rows affected (0.01sec)
## 查看slave状态
mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event
Master_Host:192.168.152.253Master_User: copy
Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000014Read_Master_Log_Pos:897Relay_Log_File: kubenode2-relay-bin.000002Relay_Log_Pos:416Relay_Master_Log_File: mysql-bin.000014Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5、检查是否同步
主服务器中插入数据:
mysql> INSERT INTO student(name,age) VALUES('gogoo',50),('zhazha',25);
Query OK,2 rows affected (0.03sec)
Records:2 Duplicates: 0 Warnings: 0从服务器中读取:
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)
数据已经同步,基础的主从复制已经搭建完成
现在模拟一个主从复制架构中,从服务器中途复制失败,不再同步主服务器的场景,并要求不停业务进行数据同步修复,恢复一致。(或者主服务器宕机,从服务器代替主服务器提供服务,现在要恢复主服务器的数据,使它与从服务器的数据同步)
1、首先先模拟一个数据插入的场景
vim insert.sh
#!/usr/bin/envbash
values=(`find /usr/ -type d | awk -F '/' '{print $NF}' | sort -u`)while true
doage=$(( $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
运行脚本,数据在随机插入(插入时间间隔 < 5s)
2、数据还在陆续插入,此时模拟slave节点宕机或异常(在此就直接stop slave;)
mysql>stop slave;
Query OK,0 rows affected (0.01 sec)
3、此时主库数据还在增加,而从库已经不同步,以下是从库数据:
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)
目前主mysql数据:
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)
很明显已经比从库多出很多数据
4、开始从库恢复数据
思路:
先通过mysqldump全量备份当前的数据,由于不能影响业务,所以在mysqldump数据时不能造成锁表。要保持数据写入
由于mysqldump时数据还在写入,所以有一部分数据还是会同步不全,所以导入mysqldump的数据后,跳过dump中包含的GTID事务,再重新建立一次主从配置,开启slave线程,恢复数据并同步。
(1)mysqldump不锁表备份数据
mysqldump -uroot -proot1 --single-transaction --master-data=2 -R vfan | gzip > dump4.sql
主要起作用参数:--single-transaction
(2)查看当前mysqldump导出数据的GTID号
[root@TestCentos7 data]# grepGLOBAL.GTID_PURGED dump4.sql
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'c9fba9e2-db3b-11eb-81d4-000c298d8da1:1-228';
以上的 c9fba9e2-db3b-11eb-81d4-000c298d8da1:1-228表示MASTER机执行到的GTID事务号
(3)去从数据库导入
scp dump4.sql 192.168.152.252:/data
mysql客户端内:
mysql> source /data/dump4.sql
此时从库数据:
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.00sec)
主库数据:| 454 | _ | 46 |
| 455 | 03modsign | 59 |
| 456 | 00systemd-bootchart | 77 |
| 457 | 03modsign | 6 |
| 458 | 0.33.0 | 88 |
+-----+---------------------+-----+
458 rows in set (0.00 sec)
从库数据恢复一部分到234行,主库数据依然在增加,已经是458条
(4)由于我们mysqldump的数据已经包含了在MASTER执行的 1-228 个事务,所以我们在SLAVE进行同步的时候,要忽略这些事务不再进行同步,不然会出现类似于这种报错:
mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event
Master_Host:192.168.152.253Master_User: copy
Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos:137827Relay_Log_File: kubenode2-relay-bin.000002Relay_Log_Pos:417Relay_Master_Log_File: mysql-bin.000002Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno:1062Last_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
要想跳过某些GTID,SLAVE必须保证 gtid_purged 参数为空才能正确跳过,查看当前的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)
当前gtid_purged不为空,所以我们要先设置它为空,执行:
mysql>reset master;
Query OK,0 rows affected (0.05sec)
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_purged为空后,开始重置SLAVE
mysql>stop slave;
Query OK,0 rows affected (0.00sec)
mysql>reset slave all;
Query OK,0 rows affected (0.02 sec)
(6)重置后,设置跳过的GTID,并重新同步MASTER
mysql> SET @@GLOBAL.GTID_PURGED='c9fba9e2-db3b-11eb-81d4-000c298d8da1:1-228';
Query OK,0 rows affected (0.01sec)
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)开启SLAVE进程,查看同步状态
mysql>start slave;
Query OK,0 rows affected (0.01sec)
mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event
Master_Host:192.168.152.253Master_User: copy
Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos:137827Relay_Log_File: kubenode2-relay-bin.000002Relay_Log_Pos:84993Relay_Master_Log_File: mysql-bin.000002Slave_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:0Last_Error:
Skip_Counter:0Exec_Master_Log_Pos:137827Relay_Log_Space:85206Until_Condition: None
Until_Log_File:
Until_Log_Pos:0Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:0Last_IO_Error:
Last_SQL_Errno:0Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:100Master_UUID: c9fba9e2-db3b-11eb-81d4-000c298d8da1
Master_Info_File: mysql.slave_master_info
SQL_Delay:0SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waitingfor moreupdates
Master_Retry_Count:86400Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: c9fba9e2-db3b-11eb-81d4-000c298d8da1:229-519Executed_Gtid_Set: c9fba9e2-db3b-11eb-81d4-000c298d8da1:1-519Auto_Position:1Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key:0Network_Namespace:1 row in set (0.00 sec)
可以看到,同步正常!
(8)最后,查看master与slave数据是否一致
MASTER数据: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.00sec)
SLAVE数据: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)
在我们修过程中插入的数据也已经全部同步。数据完全一致,主从复制修复完成。