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

MySQL主从复制：GTID模式详解与实战操作

最新推荐文章于 2025-09-23 19:27:03 发布

原创最新推荐文章于 2025-09-23 19:27:03 发布 · 2.5k 阅读

13 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #服务器 #linux

本文详细介绍了GTID复制在MySQL主从同步中的作用，包括GTID复制的工作原理、配置参数调整以及一个完整的环境搭建与测试案例。通过实例演示了如何开启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 中；

3、GTID复制开启参数

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

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/\*
//上方语句末尾\*表示将*注释，否则优快云会视为注释，复制至Linux系统需要删除“\”

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

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

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)

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		#关闭防火墙

#启动从库同步开关
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)

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)

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)

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)

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

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)