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

一、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_uuidserver_uuid 是在 Mysql 首次启动过程中自动生成的一个uuid(128位) 随机值生成后会将该值存储到数据目录的auto.cnf 中。因为是随机值所以不同服务器的 Mysql 的server_uuid 都是不相同的。
• transaction_idtid代表了该实例上已经提交的事务数量是一个整数初始值是 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

​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

主服务器创建账户

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

​注意如果不成功删除以前的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.主库上新建数据库

#查看本机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)

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

mysql>  show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

五、重设从库

#全在从库执行
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
Slave has more GTIDs than the master hasusing 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;

​常见故障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;

​常见故障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;

七、故障切换

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

mysql> stop slave
mysql> reset master

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

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

八、GTID的一些疑问

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

疑问为什么基于gtid的主从复制也需要将bin-log日志打开不是基于gtid的复制么怎么还需要打开bin-log呢
在 MySQL 中全局事务 IDGTID确实用于主从复制以确保主服务器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以上版本。二者可以配合使用发挥各自优势。