MySQL主从复制（基于GTID--事务ID方式）

一、GTID相关概念

1.GTID 是什么？

关于gitd的介绍

• MySQL-5.6.5开始支持的，MySQL-5.6.10后开始完善；
• mysql数据库从5.6.5开始新增一种基于GDIT的复制方式。通过GDIT保证每个主库上提交的事务在集群中有一个唯一的ID.这种方式强化了数据库的主备一致性，故障恢复以及容错能力。
• GTID (Global Transaction ID) 在整个事务流程中每一个事务 ID 是全局唯一的，且在整个主从复制架构中该 ID 都不会相同。
• GTID 实际上 是由 UUID+TID 组成的。其中 UUID 是一个 MySQL 实例的唯一标识。TID 代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

• server_uuid：server_uuid 是在 Mysql 首次启动过程中自动生成的一个uuid(128位) 随机值，生成后会将该值存储到数据目录的auto.cnf 中。因为是随机值，所以不同服务器的 Mysql 的server_uuid 都是不相同的。
• transaction_id（tid）：代表了该实例上已经提交的事务数量，是一个整数，初始值是 1 ，每次提交事务的时候分配给这个事务并加1 。

• GTID是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置。

2.GTID主从复制方式概念

• 基于 GTID 的主从复制方式的出现，主要是用于替换传统的日志点 复制方式。通过GTID 可以保证每个主库提交的事务在集群中都有 唯一 的一个事务 ID。
• 强化了数据库主从的一致性和故障恢复数据的容错能力，在主库 宕机发生主从切换 的情况下，GTID 方式可以让其他从库自动找到新主库复制的位置。
• 而且 GTID 可以忽略已经执行过的事务，减少了数据发生错误的概率。
• 一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致；
• GTID用来代替传统复制方法，不再使用MASTER_LOG_FILE+MASTER_LOG_POS开启复制。而是使用MASTER_AUTO_POSTION=1的方式开始复制；
• 在GTID中【slave】端的binlog是必须开启的，目的是记录执行过的GTID（强制）。

3.GTID的优缺点

优点：

• 根据 GTID 可以快速的确定事务最初是在哪个实例上提交的。
• 更简单的搭建主从复制，确保每个事务只会被执行一次。
• 一个事务对应一个唯一ID，一个GTID在一个服务器上只会执行一次
• GTID是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置
• 简单的实现 failover故障转移，不用以前那样在需要找 log_file 和 log_pos。
• 减少手工干预和降低服务故障时间，当主机挂了之后通过软件从众多的备机中提升一台备机为主机

缺点：

• 主从库的表存储引擎必须是一致的

• 主从库的表存储引擎不一致，就会导致数据不一致。如果主从库的存储引擎不一致，例如一个是事务存储引擎，一个是非事务存储引擎，则会导致事务和 GTID 之间一对一的关系被破坏，结果就会导致基于 GTID 的复制不能正确运行；
• master：对一个innodb表做一个多sql更新的事物，效果是产生一个GTID。
• slave：假设对应的表是MYISAM引擎，执行这个GTID的第一个语句后就会报错，因为非事务引擎一个sql就是一个事务。
• 当从库报错时简单的stop slave; start slave;就能够忽略错误。
• 但是这个时候主从的一致性已经出现问题，需要手工的把slave差的数据补上，这里要将引擎调整为一样的，slave也改为事务引擎。

• 不允许一个SQL同时更新一个事务引擎和非事务引擎的表

• 事务中混合多个存储引擎，就会产生多个 GTID。当使用 GTID 时，如果在同一个事务中，更新包括了非事务引擎（如 MyISAM）和事务引擎（如 InnoDB）表的操作，可能会导致产生多个 GTID、主从复制数据不一致、从库复制中断等情况
• 在一个复制组中，必须要求统一开启GTID或是关闭GTID

• 不支持create table….select 语句复制（主库直接报错）;
• create table xxx as select的语句，会被拆分为两部分，create语句和insert语句，但是如果想一次搞定，MySQL会抛出如下的错误。

• ERROR 1786 (HY000)： Statement violates GTID consistency： CREATE TABLE … SELECT.
• create table xxx as select 的方式可以拆分成两部分，如下：
• create table xxxx like data_mgr;
• insert into xxxx select *from data_mgr;

二、GTID工作原理

1. 当一个事务在主库端执行并提交时，产生 GTID，一同记录到 binlog 日志中。
2. binlog 传输到 slave，并存储到 slave 的 relaylog 后，读取这个 GTID 的这个值设置 gtid_next 变量，即告诉 Slave，下一个要执行的 GTID 值。
3. sql 线程从 relay log 中获取 GTID，然后对比 slave 端的 binlog 是否有该 GTID。
4. 如果有记录，说明该 GTID 的事务已经执行，slave 会忽略。
5. 如果没有记录，slave 就会执行该 GTID 事务，并记录该 GTID 到自身的 binlog。

如何判断复制方式GTID 还是 pos
Show slave status查看Auto_Position字段：0是pos 方式， 1是gtid方式。

三、部署主从复制

架构图

1、准备环境两台机器，关闭防火墙和selinux；两台机器环境必须一致；时间也得一致！！！
192.168.221.136 mysql-master
192.168.221.138 mysql-slave

对时：
[root@localhost ~]# timedatectl set-timezone Asia/Shanghai
//查看和ntp server的时间差异(需要外网访问，如果内网有ntpd服务器，自行替换域名为该服务的地址)
[root@localhost ~]# yum -y install ntpdate
[root@localhost ~]# ntpdate -d cn.pool.ntp.org
//和 ntp 服务器同步时间
[root@localhost ~]# ntpdate cn.pool.ntp.org
1
2
3
4
5
6
7

​2、两台机器安装mysql5.7(略)建议使用相同的安装方式

注意：
对主库已有的数据库不会进行自动同步。
主从同步之前，主库上已有数据库备份，需要在从库上手动导入同步。
开启 GTID 后的导出导入数据的注意点
Warning： A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions， even those that changed suppressed parts of the database. If you don’t want to restore GTIDs， pass --set-gtid-purged=OFF. To make a complete dump， pass --all-databases --triggers --routines --events
#意思是： 当前数据库实例中开启了 GTID 功能， 在开启有 GTID 功能的数据库实例中， 导出其中任何一个库， 如果没有显示地指定–set-gtid-purged参数， 都会提示这一行信息。 意思是默认情况下， 导出的库中含有 GTID 信息， 如果不想导出包含有 GTID 信息的数据库， 需要显示地添加–set-gtid-purged=OFF参数。
mysqldump -uroot -p'' --set-gtid-purged=OFF --single-transaction --all-databases > /path/dbname.bak
mysqldump -uroot -p'' --set-gtid-purged=OFF --single-transaction -B 库名1 库名2 > /path/dbname.bak
在从库上导入单个数据库。
mysql -uroot -p'' 库名 < /path/dbname.bak
在从库上导入多个数据库。
mysql -uroot -p'' < /path/dbname.bak

master操作：

[root@mysql-master ~]# vim /etc/my.cnf    //在[mysqld]下添加如下内容
server-id=1
log-bin = mylog                 //开启binlog日志，不指定绝对路径的话，yum安装方式产生在/var/lib/mysql/,二进制安装方式在/usr/local/mysql/data/
gtid_mode = on                  //在主从服务器上都打开gtid模式
enforce_gtid_consistency=1      //强制使用GTID一致性 consistency：一致性
sync_binlog = 1                 //强制gtid
[root@mysql-master ~]# systemctl restart mysqld
1
2
3
4
5
6
7

主服务器创建账户：

mysql> grant replication slave,reload,super on *.*  to 'slave'@'%' identified by 'JiannLt@123';
//注：生产环境中密码采用高级别的密码，实际生产环境中将'%'换成slave的ip
mysql> flush privileges;
1
2
3

​注意：如果不成功删除以前的binlog日志
replication slave权限：这个权限用来给从服务器账号授予复制权限，从服务器可以从主服务器中读取二进制日志。
super权限：允许用户使用修改全局变量的SET语句以及CHANGE MASTER语句
reload权限：必须拥有reload权限，才可以执行flush [tables | logs | privileges]

slave操作：

[root@mysql-slave ~]# vim /etc/my.cnf    //[mysqld]添加如下配置
server-id=2    //server-id每台服务器都不能一样，用于标识不同的MySQL服务器实例
gtid_mode = ON    //打开gtid
enforce_gtid_consistency=1        //强制使用GTID一致性
master-info-repository=TABLE    //将主服务器信息保存在mysql.slave_master_info表中
relay-log-info-repository=TABLE    //将中继日志信息保存到mysql.slave_relay_log_info表中
[root@mysql-slave ~]# systemctl restart mysqld
[root@mysql-slave ~]# mysql -uroot -p'JiannLt@123'   //登陆mysql
mysql> change master to            #设置主从关系
master_host='192.168.221.136',     #指定同步的主机
master_user='slave',               #指定同步的账号
master_password='JiannLt@123', #指定slave的密码
master_auto_position=1;            #开启自动同步位置模式
Query OK， 0 rows affected， 2 warnings (0.02 sec)

mysql> start slave;   #启动slave角色
Query OK， 0 rows affected (0.00 sec)
mysql> show slave status\G  #查看状态，验证sql和IO是不是yes。
说明同步成功
Slave_IO_Running： Yes
Slave_SQL_Running： Yes
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

四、测试同步

1.主库上新建数据库

#查看本机IP
mysql> select substring_index(host,':',1) as ip , count(*) from information_schema.processlist group by ip;
+----------------+----------+
| ip             | count(*) |
+----------------+----------+
| 192.168.221.136|        1 |
| localhost      |        1 |
+----------------+----------+
2 rows in set (0.00 sec)
​
mysql> create database testdb;
Query OK， 1 row affected (0.00 sec)
​
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
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

2.从库上查看是否同步成功

mysql>  show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

五、重设从库

#全在从库执行
mysql> stop slave;
mysql> reset slave;
mysql> reset master; 
​
#从库的binlog已经无效了，所以要执行这个命令清空binlog
mysql> change master to 
master_host='192.168.221.136',
master_port=3306,
master_user='slave',
master_password='JiannLt@123',
master_auto_position=1;
​#master_auto_position=1;表示是否自动获取主库的binary log坐标点，设置为1表示是。
​
mysql> start slave;   #启动slave角色
Query OK， 0 rows affected (0.00 sec)
​
mysql> show slave status\G  #查看状态，验证sql和IO是不是yes。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

六、常见故障

常见故障1：
Slave has more GTIDs than the master has，using the master’s SERVER_UUID
该问题代表从库获取到的GTID超过了主库，比如主库在未指定binlog文件名的同时修改了系统主机名，导致binlog全部被修改，从库就会判断失败；
解决方法如下：

#重设从库
mysql> stop slave;
mysql> reset slave;
mysql> reset master; 
mysql> change master to
master_host='192.168.221.136',
master_port=3306,
master_user='slave',
master_password='JiannLt@123',
master_auto_position=1;

mysql> start slave;
1
2
3
4
5
6
7
8
9
10
11
12

​常见故障2
如果从库未指定relaylog的同时修改了系统主机名，只需要在从库重新执行一次同步

mysql> stop slave;
mysql> reset slave;
mysql> change master to
master_host='192.168.221.136',
master_port=3306,
master_user='slave',
master_password='JiannLt@123',
master_auto_position=1;

mysql> start slave;
1
2
3
4
5
6
7
8
9
10

​常见故障3
Master_has_purged_require_gtids
主库提前删除了还未同步完成的binlog
解决方法如下：

#在主库上查看master信息
mysql> show master status\G;
*************************** 1. row ***************************
             File: mylog.000001
         Position: 605
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: cc9a32c5-4bc7-11ee-bdae-000c295e8b83:1-2
1 row in set (0.00 sec)

#在从库上手动指定二进制日志文件master_log_file和位置master_log_pos与master上的一致
mysql > stop slave;
mysql > change master to 
master_host='192.168.221.136',
master_user='slave',
master_password='JiannLt@123',
master_log_file='mylog.000001',
master_log_pos=605,
master_auto_position=0;

mysql > start slave;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

七、故障切换

mysql主从，主机故障或者宕机，如何进行切换？
1)在salve执行：

mysql> stop slave；
mysql> reset master；
1
2

2)查看是否只读模式，如果是只读需要将只读关闭：show variables like 'read_only';
只读模式需要修改my.cnf文件，注释read-only=1并重启mysql服务。
或者不重启使用命令关闭只读，但下次重启后失效：set global read_only=off;

3)查看

show slave status \G
show master status \G
1
2

4)在程序中将原来主库IP地址改为现在的从库IP地址，测试应用连接是否正常

八、GTID的一些疑问

1.为什么基于GTID的同步也要打开bin-log？

疑问：为什么基于gtid的主从复制也需要将bin-log日志打开，不是基于gtid的复制么？怎么还需要打开bin-log呢？
在 MySQL 中，全局事务 ID（GTID）确实用于主从复制，以确保主服务器（master）上的每个事务在所有从服务器（slave）上只被执行一次。然而，GTID 并不是日志记录本身，而是一个在主服务器上为每个事务生成的唯一标识符。
即使在使用 GTID 的情况下，我们也需要二进制日志（binlog）。下面是主要的原因：

• GTID本身并不保存binlog事件的具体内容，它只记录了每个event的全局唯一标识编号。这些修改内容还是记录在二进制日志中的。
• 主库也需要打开binlog，因为GTID信息是存放在binlog中的。如果主库关闭了binlog，就不能记录GTID，从而无法进行基于GTID的复制。
• GTID只是增加了一个全局事务ID，本质上还是基于二进制日志日志进行位置(基于日志偏移量)的复制。
• 从库想要真正进行同步复制，还需要根据GTID从主机拉取对应的binlog事件。
• 所以主库上binlog日志的记录和传递是基于GTID复制的基础。master需要将binlog写入磁盘，并通过binlog协议传递给slave。
• 基于GTID的优势在于，即使binlog日志被清理，也能通过GTID标识从任意位置恢复同步。
• 一些用来恢复容灾的操作，如从机POS(position)，也需要在binlog中定位恰当的位置。
• 某些数据库操作如切换读写节点，也需要基于binlog中实际事件来完成数据一致性。

因此，即使在使用基于 GTID 的主从复制的情况下，也需要开启二进制日志，以便记录和复制数据更改，支持故障恢复，以及存储 GTID 信息。

2.GTID和Bin-log的方式对比

MySQL的二进制日志（binlog）和全局事务标识符（GTID）是MySQL复制中的两种不同机制，它们具有不同的特点和适用场景：

记录方式不同：

• binlog 通过 log file + log pos 来定位数据事件。
• GTID 使用全局唯一的事务ID来标识事件。

分配方式不同：

• binlog 是顺序分配的。
• GTID 可以随机分配,不依赖服务启动顺序。

故障恢复不同：

• binlog 需要使用备份+重放所有日志的方式进行恢复。
• GTID 可以只重放丢失的事务段的日志。

引用关系不同：

• binlog 无法判断事件顺序和依赖关系。
• GTID 可以明确事件顺序和事务之间的依赖。

重复数据处理不同：

• binlog 可能会重放重复数据。
• GTID 可以识别重复数据并自动跳过。

使用场景：

• Binlog 支持所有版本MySQL，更加兼容，适合常规的主从复制。
• GTID 可以更方便地进行故障恢复，分库分表也更简单，适合对数据一致性要求更高的场景。

综上，GTID 在事务管理上更强大，但需要MySQL 5.6以上版本。二者可以配合使用，发挥各自优势。