Mysql数据库管理-MySQL】MySQL5.7传统复制和GTID复制转换

1.  GTID优缺点

    MySQL传统点位复制在5.7版本前是主要的主从复制模式，而随着MySQL5.6版本引入GTID，并且MySQL5.7进行各方面的优化以后，在mySQL5.7(尤其是MySQL5.7.6）版本后GTID模式的主从复制方式成为一个新的选择方式。要使用GTID模式，首先也需知其优缺点，其主要的优缺点如下：

1.1  优点

a) 更简单的实现failover，无需找log_file和log_Pos。
b) 更简单的搭建主从复制。
c) 复制集群有一个统一的方式识别复制位置，给集群管理带来了便利。
d) 正常情况下，GTID是连续没有空洞的，因此主从库出现数据冲突时，可以用添加空事物的方式进行跳过

1.2 限制

a)  在一个事务里面混合使用引擎如Innodb(支持事务)、MyISAM（不支持事务）， 造成多个GTIDs和同一个事务相关联出错
b)  CREATE TABLE…..SELECT 不能使用，该语句产生的两个event在某一情况 会使用同一个GTID（同一个GTID在slave只能被使用一次），使用时会报如下错误：

错误代码： 1786
Statement violates GTID consistency: CREATE TABLE ... SELECT.

     需改为 create table like  tb; insert into tb ... select  方式处理
c)  CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE 不能在事务内使用 （启用了--enforce-gtid-consistency参数)

2 在线调整的条件

a)   要求MySQL 5.7.6及以后版本。

b） 所有组中节点的gtid_mode 为off状态。

一、参数解析

下面对GTID_MODE变量如下解释：

值	解释
OFF	新事务是非GTID, Slave只接受不带GTID的事务，传送来GTID的事务会报错
OFF_PERMISSIVE	新事务是非GTID, Slave即接受不带GTID的事务也接受带GTID的事务
ON_PERMISSIVE	新事务是GTID, Slave即接受不带GTID的事务也接受带GTID的事务
ON	新事务是GTID, Slave只接受带GTID的事务

我们在更改GTID_MODE时不可直接跳跃更改，否则会提示报错

root@localhost [xucl]>set global gtid_mode=ON;

ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.

二、在线更改

1.将enforce_gtid_consistency设置为WARNING，主库从库都要设置，无先后顺序。

set @@global.enforce_gtid_consistency=warn;

以下语法GTID都不支持：

1.CREATE TABLE ...SELECT ..

该语句会被拆分为create table和insert table两个事务且分配同一个GTID，传到备库后，insert操作会被忽略

ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

2.在事务内部的CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE

ERROR 1787 (HY000): Statement violates GTID consistency:CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context.

These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.

3.在一个事务内部同时更新非事务引擎表和事务引擎表

ERROR 1785 (HY000): When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions,and never in the same statement as updates to transactional tables.

注意：执行完这条语句后，如果出现GTID不兼容的语句用法，在错误日志会记录相关信息，那么需要调整应该程序避免不兼容的写法，直到完全没有产生不兼容的语句，可以通过应用程序去排查所有的sql，也可以设置后观察错误日志一段时间，建议观察过，这一步非常重要。

2.将ENFORCE_GTID_CONSISTENCY设置为ON，主库从库都要设置，无先后顺序。

set @@global.enforce_gtid_consistency=on;

3.设置GTID_MODE为off_permissiv,主库从库都要设置，无先后顺序。

SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

4.设置GTID_MODE为on_permissiv,主库从库都要设置，无先后顺序。

SET @@GLOBAL.GTID_MODE = on_permissive;

5.主从检查变量ONGOING_ANONYMOUS_TRANSACTION_COUNT

SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT'; ？？

MySQL status变量是一种用于传达MySQL服务器执行状态或者MySQL服务器性能数据的状态变量，你可以调整这些变量来调整MySQL的性能，它能用来分析MySQL服务器当前的运行状态，分析MySQL的性能等MySQL系统的状态。

Ongoing_anonymous_transaction_count 是 MySQL Status 变量之一，用来表示MySQL当前正在运行的无名事务的数量。 这个值会在MySQL中执行 Begin 命令后增加，随着对应的 Commit 或 Rollback 命令的执行而减少。因为没有指定的事务名，因此称为无名事务，也就是没有 begin transaction来定义事务的场景

需要等到该变量为0

6.确保所有的匿名事务（非GTID事务）已经被完全复制到所有的server上。

主库

show master status;

从库

show slave status\G

确保事务在从库都会放完毕
或者：

SELECT MASTER_POS_WAIT('mysql-bin.00000x', xxx);

7.确认整个拓扑结构中已经没有匿名事务的存在，如之前产生的所有匿名事务已经全部被执行完毕，甚至二进制日志中也不要有匿名事务，可以通过flush logs，并让mysql来自动清理旧的二进制日志文件。

8.设置GLOBAL.GTID_MODE为ON,主库从库都要设置，无先后顺序。

SET @@GLOBAL.GTID_MODE = ON;

9.修改my.cnf的配置文件，重启数据库服务，避免数据库重启后失效。

#开启GTID复制功能
gtid_mode=on
enforce_gtid_consistency=1

把传统复制停掉，执行 stop slave, 再执行change master to master_auto_position=1,然后start slave,开启主从复制。
stop slave;

change master to master_auto_position=1;

start slave;