MySQL主从复制【基于GTID复制】_mysql基于gtid的主从复制

作者：神奇cpp | 2024-08-10 16:18:54

踩

mysql基于gtid的主从复制

MySQL主从复制【基于GTID复制】

1、GTID复制简介
2、GTID复制运行过程
3、GTID复制开启参数
4、示例

1、GTID复制简介

在主从同步时 GTID_Event 和事务的 Binlog 会一起传递到从库，由中继日志接收，从库在执行的时候使用对应的 GTID 写 binlog；主从同步以后，可以通过 GTID 确定从库目前同步的位置了。
【简单来说：可以通过 GTID 自动找点，无需像之前那样通过 binlog 名和 position 号找点】

2、GTID复制运行过程

1、master 更新数据时，会在事务前产生 GTID 并一同记录到 binlog 日志中；
2、slave 端的 IO 线程将变更的 binlog写入到本地的 relay-log（中继日志）中；
3、sql 线程从 relay-log 中获取对应的 GTID，对比 slave 端的 binlog 的记录 ；
4、如果有记录，说明该 GTID 的事务已执行，slave 会忽略该 GTID；
5、如果没有记录，slave 会从 relay-log 中执行该 GTID 的事务，并记录到 binlog 中；
1
2
3
4
5

3、GTID复制开启参数

//在MySQL配置文件中添加
[mysqld]
gtid-mode=on						//启用GTID
enforce-gtid-consistency=true		//强制GTID的一致性
log-slave-updates=1					//slave更新是否记入日志（1表示记入、0表示不记入）
1
2
3
4
5

4、示例

1）环境准备

三台主机：一主、两从
主库（MySQL master）[ip为192.168.25.131]
从库（MySQL slave1）[ip为192.168.25.133]
从库（MySQL slave2）[ip为192.168.25.134]

2）初始化数据【重置所有数据（方便实验）】

//清空MySQL数据目录，否则无法初始化
[root@localhost ~]# rm -rf /var/lib/mysql/\*
//上方语句末尾\*表示将*注释，否则CSDN会视为注释，复制至Linux系统需要删除“\”

[root@localhost ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr  --datadir=/var/lib/mysql/ 
1
2
3
4
5

3）配置主库

[root@localhost ~]# systemctl stop firewalld	//关闭防火墙，否则主从无法建立连接（有能力者设定防火墙规则放行）

[root@localhost ~]# vim /etc/my.cnf		//修改配置文件（开启binlog参数、设置server-id值）

[mysqld]
log_bin=/var/lib/mysql/mysql-bin	//bin_log指定文件名和文件路径
server_id=131	//MySQL5.5版本后要开启bin_log必须给定一个唯一的服务器id（一般为IPV4地址主机位）

gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

[mysql]
prompt="db01 [\\d] > "

[root@localhost ~]# systemctl restart mysqld
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

db01 [(none)] > grant replication slave on *.* to "rep"@"192.168.25.%" identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

db01 [(none)] > show grants for "rep"@"192.168.25.%";
+--------------------------------------------------------+
| Grants for rep@192.168.25.%                            |
+--------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.25.%' |
+--------------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

4）配置从库

db02 [(none)] > change master to
    -> master_host="192.168.25.131",		#主库IP
    -> master_user="rep",					#同步账号
    -> master_password="123456",			#同步账号的密码
    -> master_auto_position=1;				#自动 position 号（偏移值）【不用填写binlog & position】
Query OK, 0 rows affected, 2 warnings (0.01 sec)

db02 [(none)] > system systemctl stop firewalld		#关闭防火墙

db03 [(none)] > change master to
    -> master_host="192.168.25.131",
    -> master_user="rep",
    -> master_password="123456",
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

db02 [(none)] > system systemctl stop firewalld		#关闭防火墙
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

#启动从库同步开关
db02 [(none)] > start slave;
Query OK, 0 rows affected (0.00 sec)

db02 [(none)] > show slave status\G		#查看结果
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.25.131
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 707
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 920
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes		#此处必须为yes（表示成功）
            Slave_SQL_Running: Yes		#此处必须为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: 707
              Relay_Log_Space: 1131
              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: 131
                  Master_UUID: 4a4ec55f-d078-11ec-b9e8-000c29c1f77e
             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: 4a4ec55f-d078-11ec-b9e8-000c29c1f77e:1-2		//表示收到的事务（重点）
            Executed_Gtid_Set: 4a4ec55f-d078-11ec-b9e8-000c29c1f77e:1-2,
594ea1be-d078-11ec-96d7-000c2967ad99:1										//表示已执行的事务（重点）
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

db03 [(none)] > start slave;
Query OK, 0 rows affected (0.00 sec)

db03 [(none)] > show slave status\G		#查看结果
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.25.131
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 707
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 920
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes		#此处必须为yes（表示成功）
            Slave_SQL_Running: Yes		#此处必须为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: 707
              Relay_Log_Space: 1131
              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: 131
                  Master_UUID: 4a4ec55f-d078-11ec-b9e8-000c29c1f77e
             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: 4a4ec55f-d078-11ec-b9e8-000c29c1f77e:1-2		//表示收到的事务（重点）
            Executed_Gtid_Set: 4a4ec55f-d078-11ec-b9e8-000c29c1f77e:1-2,
5fe99a5e-d078-11ec-956b-000c294d6b8d:1										//表示已执行的事务（重点）
                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
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

5）测试

db01 [(none)] > create database test;
Query OK, 1 row affected (0.00 sec)

db02 [(none)] > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

db03 [(none)] > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows 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

db01 [test] > create table t1(id int);
Query OK, 0 rows affected (0.01 sec)

db01 [test] > insert into t1 values(1),(2);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

db02 [(none)] > use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
db02 [test] > select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

db03 [(none)] > use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
db03 [test] > select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows 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

db01 [test] > drop database test;
Query OK, 1 row affected (0.00 sec)

db02 [test] > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

db03 [test] > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows 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

通过上方测试，通过查看发现所有数据均已同步，主从复制成功

6）查看GTID

db01 [(none)] > show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 |     1443 |              |                  | 4a4ec55f-d078-11ec-b9e8-000c29c1f77e:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

db02 [(none)] > show master status;
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| mysql-bin.000002 |     1678 |              |                  | 4a4ec55f-d078-11ec-b9e8-000c29c1f77e:1-6,
594ea1be-d078-11ec-96d7-000c2967ad99:1 |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

db03 [(none)] > show master status;
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| mysql-bin.000002 |     1678 |              |                  | 4a4ec55f-d078-11ec-b9e8-000c29c1f77e:1-6,
5fe99a5e-d078-11ec-956b-000c294d6b8d:1 |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
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

可以看出三台服务器的GTID是相同的，同时保留自身GTID

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/神奇cpp/article/detail/959290