MySQL主从架构GTID复制

GTID（全局事务标识符）,MySQL 5.6开始支持，MySQL 5.7以上版本建议启用GTID功能

GTID优点：

  • 保证事务全局统一
  • 截取日志更加方便。跨多文件，判断起点终点更加方便
  • 判断主从工作状态更加方便
  • 传输日志，可以并发传输。SQL回放可以更高并发
  • 主从复制构建更加方便

相关选项：

1
2
gtid_mode 						## gtid模式
enforce_gtid_consistency 		## 保证GTID安全的参数

配置案例：

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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
##  主节点配置
[root@Rocky8-2 ~]# vim /etc/my.cnf.d/mysql.cnf 
[mysqld]
server-id=12
gtid_mode=ON
enforce_gtid_consistency
log-bin=/binlog/mysql-bin

##  从节点配置
[root@Rocky8-3 ~]# vim /etc/my.cnf.d/mysql.cnf
[mysqld]
server-id=13
gtid_mode=ON
enforce_gtid_consistency
log-bin=/binlog/mysql-bin
read-only

##  如果数据不一至，需要备份主库数据并还原到从库，
[root@Rocky8-2 ~]# mysqldump -A -F --master-data=1 --single-transaction > /data/all.sql
[root@Rocky8-2 ~]# scp /data/all.sql  10.0.0.13:/data/

[root@Rocky8-3 ~]# mysql
mysql> set sql_log_bin=off;
mysql> source /data/all.sql;
mysql> set sql_log_bin=on;

##  执行change master to
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.12',
    ->     MASTER_USER='repluser',
    ->     MASTER_PASSWORD='123456',
    ->     MASTER_PORT=3306,
    ->     MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 8 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.04 sec)

##  检查复制状态
##  观察 Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes

mysql> show  slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.0.0.12
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 157
               Relay_Log_File: Rocky8-3-relay-bin.000002
                Relay_Log_Pos: 373
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

##  主节点新建一个数据库：
mysql> create database test1;
Query OK, 1 row affected (0.02 sec)

##  从节点同步
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
6 rows in set (0.01 sec)

##  主节点更新数据，观察同步状态，主要以观以下两个状态值
##  Retrieved_Gtid_Set
##  Executed_Gtid_Set

##  主节点观察状态
mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000006
         Position: 345
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 18bbcaa5-1fb3-11ee-bcd2-000c295891d3:1
1 row in set (0.01 sec)

##  从节点观察状态
mysql> show  slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.0.0.12
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 345
               Relay_Log_File: Rocky8-3-relay-bin.000002
                Relay_Log_Pos: 561
        Relay_Master_Log_File: mysql-bin.000006
             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: 345
              Relay_Log_Space: 774
              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: 12
                  Master_UUID: 18bbcaa5-1fb3-11ee-bcd2-000c295891d3
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 18bbcaa5-1fb3-11ee-bcd2-000c295891d3:1
            Executed_Gtid_Set: 18bbcaa5-1fb3-11ee-bcd2-000c295891d3:1
                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, 1 warning (0.00 sec)