（5.7）mysql高可用系列——MySQL中的GTID复制（理论篇）【转】

转自：https://blog.csdn.net/wmq880204/article/details/53160078

一、GTID的概述：

1、全局事物标识：global transaction identifieds。

2、GTID事物是全局唯一性的，且一个事务对应一个GTID。

3、一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。

4、GTID用来代替classic的复制方法，不在使用binlog+pos开启复制。而是使用master_auto_postion=1的方式自动匹配GTID断点进行复制。

5、MySQL-5.6.5开始支持的，MySQL-5.6.10后开始完善。

6、在传统的slave端，binlog是不用开启的，但是在GTID中，slave端的binlog是必须开启的，目的是记录执行过的GTID（强制）。

 

二、GTID的组成部分：

前面是server_uuid：后面是一个序列号

例如：server_uuid：sequence number

7800a22c-95ae-11e4-983d-080027de205a:10

UUID：每个mysql实例的唯一ID，由于会传递到slave，所以也可以理解为源ID。

Sequence number：在每台MySQL服务器上都是从1开始自增长的序列，一个数值对应一个事务。

 

三、GTID比传统复制的优势：

1、更简单的实现failover，不用以前那样在需要找log_file和log_Pos。

2、更简单的搭建主从复制。

3、比传统复制更加安全。

4、GTID是连续没有空洞的，因此主从库出现数据冲突时，可以用添加空事物的方式进行跳过。

 

四、GTID的工作原理：

1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
6、在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。
 

要点：

　　1、slave在接受master的binlog时，会校验master的GTID是否已经执行过（一个服务器只能执行一次）。

　　2、为了保证主从数据的一致性，多线程只能同时执行一个GTID。

 

五、使用GTID的限制

　　

GTID的限制：

（1）不支持非事务引擎（从库报错，stopslave; start slave; 忽略）

（2）不支持create table … select 语句复制（主库直接报错，原因是该语句其实是一个DDL+DML）

（3）不允许在一个SQL同时更新一个事务引擎和非事务引擎的表

（4）在一个复制组中，必须要求统一开启GTID或是关闭GTID

（5）开启GTID需要重启（5.7中可能不需要）

（6）开启GTID后，就不在使用原来的传统的复制方式

（7）对于createtemporary table 和drop temporary table语句不支持

（8）不支持sql_slave_skip_counter

 

六、使用GTID搭建mysql的主从复制的主要参数

：

[mysqld]
#GTID:
gtid_mode=on
enforce_gtid_consistency=on
server_id=2003306    #每天实例的server_id都要不一样
 
#binlog
log-bin=mysqlbin
log-slave-updates=1   #允许下端接入slave
binlog_format=row      #强烈建议，其他格式可能造成数据不一致
 
#relay log
skip_slave_start=1
注意：建议使用mysql-5.6.5以上的最新版本。

 

 

（二）、启动GTID的两种方法：

方法一、

1、如果是在已经跑的服务器，你需要重启一下mysql server。

2、启动之前，一定要先关闭master的写入，保证所有slave端都已经和master端数据保持同步。

3、所有slave需要加上skip_slave_start=1的配置参数，避免启动后还是使用老的复制协议。

 

方法二、

1、如果是新搭建的服务器，直接启动就行了。

 

七、master-slave搭建的注意事项

（一）、使用GTID的方式，把salve端挂载master端：

1、启动以后最好不要立即执行事务，而是先change master上。

2、然后在执行事务，当然知不是必须的。

3、使用下面的sql切换slave到新的master。

stop slave;
change master to
master_host = 192.168.100.200,
master_port = 3306,
master_user = abobo,
master_password=123,
master_auto_position = 1;

 

 

(二)、如果给已经运行的GTID的master端添加一个新的slave

 有两种方法：

方法一、适用于master也是新建不久的情况。

1、如果你的master所有的binlog还在。可以选择类似于上面的方法，安装slave，直接change master to到master端。

2、原理是直接获取master所有的GTID并执行。

3、优点：简单方便。

4、缺点：如果binlog太多，数据完全同步需要时间较长，并且master一开始就启用了GTUD。

 

 

方法二、适用于拥有较大数据的情况。（推荐）

1、通过master或者其他slave的备份搭建新的slave。（看第三部分）

2、原理：获取master的数据和这些数据对应的GTID范围，然后通过slave设置@@global.gtid_purged跳过备份包含的gtid。

3、优点：是可以避免第一种方法的不足。

4、缺点：相对来说有点复杂。

 

（三）、通过备份搭建新的slave：（方法二的扩展）

两种方法：

方法一、mysqldump的方式：

1、在备份的时候指定--master-data=2（来保存binlog的文件号和位置的命令）。

2、使用mysqldump的命令在dump文件里可以看到下面两个信息：

SET @@SESSION.SQL_LOG_BIN=0;

SET @@GLOBAL.GTID_PURGED='7800a22c-95ae-11e4-983d-080027de205a:1-8';

3、将备份还原到slave后，使用change master to命令挂载master端。

 

注意：在mysql5.6.9以后的命令才支持这个功能。

 

方法二、percona Xtrabackup

1、Xtrabackup_binlog_info文件中，包含global.gtid_purged='XXXXXX:XXXX'的信息。

2、然后到slave去手工的 SET GLOBAL.GTID_PURGED='XXXXXX:XXXX'。

3、恢复备份，开启change master to 命令。

 

注意：如果系统运行了很久，无法找到GTID的变好了，可以通过上面的方式进行查找。

 

八、GTID如何跳过事务冲突

1、这个功能主要跳过事务，代替原来的set global sql_slave_skip_counter = 1。

2、由于在这个GTID必须是连续的，正常情况同一个服务器产生的GTID是不会存在空缺的。所以不能简单的skip掉一个事务，只能通过注入空事物的方法替换掉一个实际操作事务。

3、注入空事物的方法：

stop slave;
set gtid_next='xxxxxxx:N';
begin;commit;
set gtid_next='AUTOMATIC';
start slave;

 

4、这里的xxxxx:N 也就是你的slave sql thread报错的GTID，或者说是你想要跳过的GTID。

 

 

九、GTID的参数注释

[master]>show global variables like '%gtid%';
1、enforce_gtid_consistency：开启gtid的一些安全限制（介意开启）。
 
2、gtid_executed：全局和seeeion级别都可以用。用来保存已经执行过的GTIDs。
贴士：show  master status\G;输出结果中的Executed_Gtid_Set和gitd_executed一致。reset master时，此值会被清空。
 
3、gtid_owned：全局和session级别都可用，全局表示所有服务器拥有GTIDs，session级别表示当前client拥有所有GTIDs。（此功能用的少）
 
4、gtid_mode：是否开启GTID功能。
 
5、gtid_purged：全局参数，设置在binlog中，已经purged的GTIDs，并且purged掉的GTIDs会包含到gtid_executed中。
贴士：从而导致slave不会再去master请求这些GTIDs，并且Executed_Gtid_Set为空时，才可以设置此值。
 
6、gtid_next：这个时session级别的参数：
[master]>show session variables like '%gtid_next%';

十、关于GTID的一些功能限制

（一）、更新非事务引擎：
1、Case重现：
master：对一个innodb表做一个多sql更新的事物，效果是产生一个GTID。
slave：对应的表是MYISAM引擎，执行这个GTID的第一个语句后就会报错，因为非事务引擎一个sql就是一个事务。
 
2、错误编号：
last_Errno:1756
 
3、异常恢复方案：
（1）、简单的stop slave; start slave;就能够忽略错误。但是这个时候主从的一致性已经出现问题。需要手工的把slave差的数据补上。
（2）、首先将引擎调整为一样的，slave也改为事务引擎。
 
 
（二）、create table ....select statements
1、case重现：
 master：直接执行一个create table select * from table;的sql
 
2、报错：
error 1786
 
3、原理：
由于create table ...select语句会生成两个sql，一个是DDL创建表SQL，一个是insert into 插入数据的sql。
由于DDL会导致自动提交，所以这个sql至少需要两个GTID，但是GTID模式下，只能给这个sql生成一个GTID，如果强制执行会导致和上面更新非事务引擎一样的结果。
 
 
（三）、一个sql同事操作innodb引擎和myisam引擎：
case重现：t1表是innodb，t2表是myisam
1、update t1,t2 set t1.id=1000,t2.id=1000 where t1.id=t2.id;
2、报错：1785
3、原理和第二个相同。
 
 
 
（四）、在一个replication grouop 中，所有的mysql必须要统一开启或者关闭GTID功能。
1、case重现：
将一个未开启gtid的slave通过原始的binlog和pos方式连接到开启GTID的master。
 
2、报错：
The slave IO thread stops because the master has @@GLOBAL.GTID_MODE ON and this server has @@GLOBAL.GTID_MODE OFF。
 
 
 
（五）、在一个replication group中，如果开启GTID以后，就不再允许使用classic的复制方式：
1、case重现：
将一个开启gtid的slave通过原始的binlog和pos方式连接到开启GTID的master。
 
2、报错：
ERROR 1776(HY000)：Parameters MASTER_LOG_FILE，MASTER_LOG_POS，RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active。
 
 
 
（六）、GTID_MODE是not online的：
需要重启才能生效，官方暂时不支持平滑的从classic replication切换到GTID replication。
贴士：
　　由于GTID开启需要重启系统，一个复制组中所有的实例必须统一开启或者关闭GTID,开启GTID以后不能在使用classic复制。
问题：
　　也就是说在线业务必须统一关闭，然后再启动，会导致服务中断。
 
解决方案：
1、针对这种情况，社区有两种对应的平滑升级的方案：
　　一种是booking.com出品，这两个差别在淘宝9月份数据库月报里有说明，加了一个桥接的服务器，既可以运行GTID模式下，也可以运行classic模式下。
　　另外一种是facebook.com出品。所有的slave可以在开启GTID模式的情况下，可以连接到没有开启GTID模式的master。
 
2、可以关闭一个部分，停止写操作，但是读不用，将另一部分改成GTID模式。
 
 
（七）、Temporary tables。
1、create temporary table和drop temporary table语句一样在GTID环境下不支持。
　　如果--enforce_gtid_consistency参数开启，并且autocommit=1，那么可以使用。
 
 
（八）、关于Errant transaction
1、Errant transaction：所谓的errant transaction也就是没有规范的从master执行，而是直接从slave执行的事务。
2、由于GTID协议的原因，最开始已经提过（参见GTID architecture）。
3、如果slave有errant transaction产生，由于GTID协议中的规则，很容易导致failover失败。主要有两种情况：
　　a、在slave上做了无用的或者临时的errant transaction操作，如果该slave升级成为master的话，连接到它的所有数据库都会获取到这个事务。如果一样就会产生冲突。
　　b、由于做了这个errant transaction这个事务以后，其他的slave还没有获取这个errant transaction的GTID，需要从master上发同步给其他的slave，但是主的binlog又被删掉了，这时将会报错。
 
4、总之：尽量避免产生errant transaction。可以通过：set sql_log_bin=off的方式在slave执行sql，但是也要考虑到数据一致性。
 

【必看】gtid 的全局变量 gtid_execute 与 gtid_purged

该部分转自：https://www.cnblogs.com/gaogao67/p/10684090.html

 

参数定义

　　gtid_executed，全局参数，GTID集合包含所有在该服务器上执行过的事务编号和使用set gtid_purged语句设置过的事务编号，使用 SHOW MASTER STATUS 和 SHOW SLAVE STATUS 命令得到的 Executed_Gtid_Set 列值就取自于全局参数gitd_executed。

　　gtid_purged，全局参数，GTID集合包含从binlog中purged掉的事务ID，该集合是全局参数gtid_executed的子集。

官方参数解释：
gtid_executed
　　When used with global scope, this variable contains a representation of the set of all transactions executed on the server and GTIDs that have been set by a SET gtid_purged statement. This is the same as the value of the Executed_Gtid_Set column in the output of SHOW MASTER STATUS and SHOW SLAVE STATUS.
 
gtid_purged
　　The set of all transactions that have been purged from the binary log. This is a subset of the set of transactions in gtid_executed.


参数更新机制
当复制主库关闭binlog时：
1. 事务提交不会生成GTID，mysql.gtid_executed表/gtid_executed变量/gtid_purged变量均不更新。
 
当复制主库开启binlog时：
1. 事务提交需要生成Binlog，GTID在Binlog的ordered_commit flush阶段生成。
2. 表mysql.gtid_executed在实例重启或flush log或binlog文件写满等造成binlog发生切换是保存上一个binlog执行过的全部gtid，属于非实时更新。
3. 全局变量gtid_executed在事务commit阶段更新,属于实时更新。
4. 全局变量gtid_purged在执行purge binary log命令或binlog超过保持期进行清理binlog时更新，属于非实时更新。
 
 
当复制从库关闭binlog或关闭log_slave_update时：
1. 在从库上应用主库binlog时不会生成新的GTID，也不会写入复制从库的binlog文件。
2. 表mysql.gtid_executed在应用主库binlog事务时更新，并与事务一起提交，属于实时更新。
3. 全局变量gtid_executed在主库binlog事务commit阶段更新，属于实时更新。
4. 全局变量gtid_purged在主库binlog事务commit阶段更新，属于实时更新。
 
当复制从库开启binlog或开启log_slave_update时：
1. 在从库上应用主库binlog时不会生成新的GTID，但会写入复制从库的binlog文件。
2. 表mysql.gtid_executed在实例重启或flush log或binlog文件写满等造成binlog发生切换是保存上一个binlog执行过的全部gtid，属于非实时更新。
3. 全局变量gtid_executed在事务commit阶段更新,属于实时更新。
4. 全局变量gtid_purged在执行purge binary log命令或binlog超过保持期进行清理binlog时更新，属于非实时更新。

 
参数重置机制
在某些场景下，需要修改全局变量gtid_purged和gtid_executed的值，执行对全局变量gtid_purged进行赋值时，会报以下错误：
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

设置@@GLOBAL.GTID_EXECUTED需要通过RESET MASTER命令，该命令会情况当前服务器上的Binlog文件，并将@@GLOBAL.GTID_EXECUTED和@@GLOBAL.GTID_PURGED的值重置为空，重新初始化binlog文件序号，重新初始化GTID的事务ID起始值.

 

参数恢复机制
参数binlog_gtid_simple_recovery用于控制在实例重启时，如何计算全局变量gtid_executed和gtid_purged。在MySQL 5.7.7及之后版本中该参数默认为True

binlog_gtid_simple_recovery=FALSE
• To initialize gtid_executed, binary log files are iterated from the newest file, stopping at the first binary log that has any Previous_gtids_log_event. All GTIDs from Previous_gtids_log_event and Gtid_log_events are read from this binary log file. This GTID set is stored internally and called gtids_in_binlog. The value of gtid_executed is computed as the union of this set and the GTIDs stored in the mysql.gtid_executed table.
This process could take a long time if you had a large number of binary log files without GTID events, for example created when gtid_mode=OFF.
• To initialize gtid_purged, binary log files are iterated from the oldest to the newest, stopping at the first binary log that contains either a Previous_gtids_log_event that is non-empty (that has at least one GTID) or that has at least one Gtid_log_event. From this binary log it reads Previous_gtids_log_event. This GTID set is subtracted from gtids_in_binlog and the result stored in the internal variable gtids_in_binlog_not_purged. The value of gtid_purged is initialized to the value of gtid_executed, minus gtids_in_binlog_not_purged.

binlog_gtid_simple_recovery=TRUE
which is the default in MySQL 5.7.7 and later, the server iterates only the oldest and the newest binary log files and the values of gtid_purged and gtid_executed are computed based only on Previous_gtids_log_event or Gtid_log_event found in these files. This ensures only two binary log files are iterated during server restart or when binary logs are being purged.

在实例重启后，全局变量gtid_executed和gtid_purged的值取决于：

1、从binlog文件头读取Previous_gtids_log_event获取该binlog之前的gtid_set

2、从binlog文件的所有事件中获取该binlog中包含的gtid_set
3、从mysql.gtid_executed表获取到执行过的gtid_set
通过上面三个集合算出当前实例执行过的gtid_set和当前BINLOG中包含的gtid_set。
 
 
mysql.gtid_executed表更新机制
在MySQL 5.6版本中，开启GTID模式必须打开参数log_slave_updates，即从库必须在记录一份binlog，以保证在从库重启后，可以通过扫描最后一个二进制日志获得当前从库执行过的GTID集合。
在MySQL 5.7.5版本中引入mysql.gtid_executed表来存放gtid信息，因此在GTID模式下不要求开启log_slave_update参数。
 
在MySQL 5.7版本中，对mysql.gtid_executed表的更新策略分为：

1、主库服务器，未开启log_bin参数，则不对mysql.gtid_executed表进行更新。
2、主库服务器，开启log_bin参数，当二进制文件进行rotation时或者关闭实例时对mysql.gtid_executed进行更新。
3、从库服务器，未开启log_bin参数或未开启log_slave_updates参数，在用户事务执行时对mysql.gtid_executed进行更新，并与用户事务一起进行提交。
4、从库服务器，开启log_bin参数和开启log_slave_updates参数，当二进制文件进行rotation时或者关闭实例时对mysql.gtid_executed进行更新。

当主库开启log_bin参数或从库开启log_slave_update参数时，执行或应用事务所生成的binlog会写入磁盘，当二进制文件进行rotation时或者关闭实例时对mysql.gtid_executed进行更新，表mysql.gtid_executed中不能提供完整的GTID集合数据，需以参数gtid_executed为准，如果实例异常重启，最近的GTID集合数据没有更新到mysql.gtid_executed表中，当实例恢复时，通过扫描最后一个binlog文件来获得最新的GTID集合数据。

当从库未开启log_bin参数或未开启log_slave_updates参数时，应用主库所传递过来的事务不会产生新的binlog，在执行事务开始便可以获取到该事务的GTID，因此可以随着事务一起提交，类似于MySQL 5.6版本中随事务一起更新mysql.slave_master_info表一样。

当执行RESET MASTER时，表mysql.gtid_executed会被清空。

 
mysql.gtid_executed表压缩
当从库未开启log_slave_updates参数时，由于每个事务都会向mysql.gtid_executed表写入记录，为防止mysql.gtid_executed表数据量暴增，MySQL 5.7引入参数gtid_executed_compression_period来控制每执行N个事务后，对mysql.gtid_executed表进行一次压缩，将多个连续的gtid值合并为gtid集合。如果gtid_executed_compression_period被设置为0，则不会进行压缩。
当主库开启log_bin参数或从库开启log_slave_update参数时，参数gtid_executed_compression_period并不会被使用，只有当二进制文件进行Rotation时才会进行GTID压缩。

 

Xtrabackup备份恢复
在使用Xtrabackup热备+BINLOG位点方式搭建主从复制的场景中，会发现从库上存在GTID空隙。

由于所有数据库服务器都配置开启log_bin参数和log_slave_updates参数，因此每次执行事务不会更新mysql.gtid_executed表。
在使用Xtrabackup热备搭建主从复制时，假设：
1、T1时间点开始热备。
2、T2时间点备份到mysql.gtid_executed表。
3、T3时间点mysql.gtid_executed表最后一次更新。
4、T4时间点热备完成

在使用Xtrabackup热备恢复后，新实例上mysql.gtid_executed表为T3时间点的gtid_set，而新实例上没有任何BINLOG文件，因此新实例上全局参数gtid_executed和gtid_purged的值为T3时间点的gtid_set。而使用基于位点的方式搭建复制，新从库从T4时间点后的BINLOG日志开始应用。最终导致T3时间点到T4时间点的gtid_set没有包含在新从库的全局参数gtid_executed中，出现GTID空隙。

 

使用Xtrabackup备份过程中，会执行以下FLUSH操作：

复制代码
2019-07-18T18:17:21.025361+08:00       37 Query    SET SESSION lock_wait_timeout=31536000
2019-07-18T18:17:21.025503+08:00       37 Query    FLUSH NO_WRITE_TO_BINLOG TABLES
2019-07-18T18:17:21.025841+08:00       37 Query    FLUSH TABLES WITH READ LOCK
2019-07-18T18:17:21.069165+08:00       37 Query    SHOW VARIABLES
2019-07-18T18:17:21.072180+08:00       37 Query    SHOW SLAVE STATUS
2019-07-18T18:17:21.072291+08:00       37 Query    SHOW MASTER STATUS
2019-07-18T18:17:21.072340+08:00       37 Query    SHOW VARIABLES
2019-07-18T18:17:21.074537+08:00       37 Query    FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS
2019-07-18T18:17:21.275242+08:00       37 Query    UNLOCK TABLES
2019-07-18T18:17:21.275749+08:00       37 Query    SELECT UUID()
2019-07-18T18:17:21.275851+08:00       37 Query    SELECT VERSION()
复制代码

上面三次FLUSH 操作都不会导致二进制文件进行rotation，因此也不会导致更新mysql.gtid_executed表数据。

posted @ 2019-08-25 14:26  郭大侠1  阅读(549)  评论(0)  编辑  收藏  举报