热门标签
热门文章
- 1学Python该看什么书？10本Python好书推荐，10年程序员倾囊相授！_python书籍推荐
- 2flink CDC相关问题Q&A_flink cdc server-time-zone
- 3python 速度 write_videofile_moviepy音视频剪辑基类VideoClip的write_videofile方法codec、bitrate、preset、audio_bitrat...
- 4Windows系统下安装mujoco环境的教程【原创】
- 5SpringBoot+Actuator，打造一款强大的监控和管理系统
- 6这18个网站能让你的页面背景炫酷起来_酷炫的网站
- 7How to covert HEIF to JPG with Java_java heif 转码 jpg
- 8MuJoCo 入门教程（三）Python 绑定_mujoco.mjmodel
- 9【鸿蒙实战开发】基于@ohos/crypto-js实现加解密工具箱_鸿蒙 cryptojs
- 10Java之并发编程_java并发编程
当前位置: article > 正文
mysql的GTID同步原理介绍及详细配置过程_mysql gtid
作者：黑客灵魂 | 2024-08-10 16:05:35
赞
踩
mysql gtid
什么是GTID？
GTID (Global Transaction ID) 是对于一个已提交事务的编号，并且是一个全局唯一的编号。 GTID 实际上 是由UUID+TID 组成的。
其中 UUID 是一个 MySQL 实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。
下面是一个GTID的具体形式：03a1eb63-c21a-11ec-b07f-000c2987bea6:1-25，冒号分割前边为UUID，后边为TID。
GTID 集合可以包含来自多个 MySQL 实例的事务，它们之间用逗号分隔。
GTID的工作原理
①当一个事务在主库端执行并提交时，产生GTID，一同记录到binlog日志中。
②binlog传输到slave,并存储到slave的relaylog后，读取这个GTID的这个值设置GTID_next变量，即告诉Slave，下一个要执行的GTID值。
③sql线程从relay log中获取GTID，然后对比slave端的binlog是否有该GTID。
④如果有记录，说明该GTID的事务已经执行，slave会忽略。
⑤如果没有记录，slave就会执行该GTID事务，并记录该GTID到自身的binlog，在读取执行事务前会先检查其他session持有该GTID，确保不被重复执行。
⑥在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。
数据库角色 IP 系统与MySQL版本 有无数据
主数据库 192.168.121.164 RHEL7 MySQL5.7 无数据
从数据库 192.168.121.133 RHEL7 MySQL5.7 无数据
mysql> grant replication slave on \*.* to 'slave'@'192.168.121.%' identified by 'MySql@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
配置主库文件
[root@localhost ~]# vim /etc/my.cnf [mysqld] port=3306 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-name-resolve symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid skip-name-resolve log-bin=mysql-bin #开启二进制日志 server-id=1 #服务器ID，必须唯一 gtid-mode=on #开启gtid模式 enforce-gtid-consistency=on #强制gtid一致性，开启后对特定的create table不支持 binlog-format=row #默认为mixed混合模式，更改成row复制，为了数据一致性 log-slave-updates=1 #从库binlog记录主库同步的操作日志 skip-slave-start=1 #跳过slave复制线程 binlog-ignore-db=mysql
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
然后重启一下数据库
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# systemctl status mysqld
- 1
- 2
查看GTID模式状态
[root@localhost ~]# mysql -uroot -pMySql@123 -e 'show variables like "%GTID%";'
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
配置从库文件
[root@localhost ~]# vim /etc/my.cnf [mysqld] port=3306 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid skip-name-resolve log-bin=mysql-bin server-id=2 gtid-mode=on enforce-gtid-consistency=on binlog-format=row log-slave-updates=1 skip-slave-start=1
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
然后重启一下数据库
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# systemctl status mysqld
- 1
- 2
查看GTID模式状态
[root@localhost ~]# mysql -uroot -pMySql@123 -e 'show variables like "%GTID%";'
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
开启主从同步
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to
master_host='192.168.121.164',
master_user='slave',
master_password='MySql@123',
master_auto_position=1;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
使用GTID方式同步的时候，必须指定master_auto_position=1
GTID使用master_auto_position=1代替了基于binlog和position号的主从同步方式,更便于主从同步的搭建
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.11 Master_User: Jaking Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 154 Relay_Log_File: server12-relay-bin.000002 Relay_Log_Pos: 367 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: 154 Relay_Log_Space: 577 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: 11 Master_UUID: 03a1eb63-c21a-11ec-b07f-000c2987bea6 Master_Info_File: /var/lib/mysql/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: Executed_Gtid_Set: Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
然后进行数据测试
create table aa(nid int,stuname char(15),sex int);
insert into aa value (2001,'tom',1),(2002,'mary',0),(2003,'cak',1);
select * from aa;
- 1
- 2
- 3
- 4
- 5
然后去从库查看数据
发现库已经有了，到这里已经可以差不多了
当然还是要看下表了，结果还是正常的
这时候我们可以看下两个库的状态
查看主库状态
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 | 1620 | | | 03a1eb63-c21a-11ec-b07f-000c2987bea6:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
查看从库状态
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 | 1584 | | | 03a1eb63-c21a-11ec-b07f-000c2987bea6:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
到这里就完成辣
还有可以测试一下从库停机后，主库仍在写入数据，从库恢复后能否自动同步数据
从库停掉主从同步
mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.10.11 Master_User: Jaking Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 1620 Relay_Log_File: server12-relay-bin.000002 Relay_Log_Pos: 1833 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: No Slave_SQL_Running: No 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: 1620 Relay_Log_Space: 2043 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: NULL 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: 11 Master_UUID: 03a1eb63-c21a-11ec-b07f-000c2987bea6 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 03a1eb63-c21a-11ec-b07f-000c2987bea6:1-6 Executed_Gtid_Set: 03a1eb63-c21a-11ec-b07f-000c2987bea6:1-6 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
然后去主库添加数据
查看从库
是没有同步信息过来的，因为我们没有开启同步
然后我们开启同步
mysql> stop slave;
mysql> reset slave all;
mysql> change master to
master_host='192.168.121.164',
master_user='slave',
master_password='MySql@123',
master_auto_position=1;
mysql> start slave;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
所有测试就完成了。
祝大家都能成功。
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/黑客灵魂/article/detail/959240
推荐阅读
相关标签
