mysql在线切换 GTID模式

在线切换的版本要>=5.7.6

gitd_mode解释

OFF不产生GTID,Slave只接收不带GTID的事务

OFF_PERMISSIVE不产生GTID,Slave接收不带GTID的事务也接收带GTID的事务

ON_PERMISSIVE产生GTID,Slave接收不带GTID的事务也接收带GTID的事务

ON产生GTID,Slave只接收带GTID的事务

需要注意的是，这几个值的改变是有顺序的，即

off<--->OFF_PERMISSIVE<--->ON_PERMISSIVE<--->ON不能跳跃执行，会报错

实验一  在线将GTID的环境切换到非GTID

从库执行  stop slave;
从库执行  change master to master_auto_position=0;
从库执行  start slave;
主从库执行  set @@GLOBAL.GTID_MODE = ON_PERMISSIVE
主库执行  set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE
这个时候已经从gtid转到传统复制了,不过还是中间状态
在从库执行  set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE
主从库执行  select @@GLOBAL.GTID_OWNED;是不是为空
主库执行  set global gtid_mode=0;
主库执行  set global enforce_gtid_consistency=off;

----永久生效,记得配置文件修改

实验二  在线将传统复制切换到GTID复制

在主从库执行  set global enforce_gtid_consistency=WARN;
在主从库执行  set global enforce_gtid_consistency=on;
主从库执行  set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
在从库执行  set @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
在主库执行  set @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
查看show status like 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';是否为0,等于0是表示所有连接都转为gtid复制

主从库执行  set global gtid_mode=on;
stop slave;
change master to master_auto_position=1;
start slave;

--永久生效,记得更改配置文件


在线修改GTID模式相关命令

1、 在每一台机器上执行命令

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;

这是很重要的一步，必须确保服务器上没有违反GTID规范的SQL，否则当设置为GTID模式后，这些业务SQL会被拒绝执行，建议设置上面变量值间隔一天后errorLog没有警告，才可进行下一步。

2、 在每一台服务器上执行

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;

上面参数表示GTID模式下一些SQL会被拒绝执行，有：create table ...select ....,CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE,在一个事务中更新事务表和非事务表。

3、 在每一台服务器上执行

SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

表示采用匿名事务记录binlog，slave可以兼容GTID事务和匿名事务，执行该命令没有顺序可言，但是必须每个机器都要执行。

4、在每一台服务器上执行

SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

表示采用GTID事务记录binlog，slave可以兼容GTID事务和匿名事务，执行该命令没有顺序可言，但是必须每个机器都要执行。

5、等待步骤5前产生的事务（匿名事务）全部在slave都已应用完成

5.1）在master上执行：show master status;记录file和pos

5.2) 在所有slave执行：SELECT MASTER_POS_WAIT(‘file‘, pos);如果执行结果都为0表示都已经应用完成，可以进行下一步，否则需要一直等待完成。

方法二：在每一台slave上都执行show master status;查看Executed_Gtid_Set是否有值，如果不为空了，说明已经开始使用GTID复制了

6、在每一台服务器上执行

SET @@GLOBAL.GTID_MODE = ON;

7、在所有slave上执行

stop slave;

 CHANGE MASTER TO MASTER_AUTO_POSITION = 1;

 START SLAVE;

8、在每一台服务器上修改my.cnf

gtid_mode = on

enforce_gtid_consistency = 1


案例:

一、环境

创建 MySQL 实例，我搭建3个MySQL实例(一主2从)


我在同一台机器上搭建的3个mysql实例，角色说明如下


3 台实例创建完毕后，在master上创建复制账号

/usr/local/mysql5.7/bin/mysql -uroot -p --socket=/data/mysql/mysql3307/tmp/mysql3307.sock

mysql> create user repl@'%'  identified by '123456';

Query OK, 0 rows affected (0.04 sec)


mysql> grant  replication slave on *.* to repl@'%';

Query OK, 0 rows affected (0.00 sec)


mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)


将主库的数据全量导出（mysqldump或xtrabackup工具），因为是测试环境，数据量小，因此就使用mysqldump进行导出。

/usr/local/mysql5.7/bin/mysqldump  --skip-add-locks  --default-character-set utf8   --master-data=2 --single-transaction  --all-databases  -E -R --triggers -uroot -p123456 --socket=/data/mysql/mysql3307/tmp/mysql3307.sock >all.sql

二、传统复制

1. 导入数据

2.查看备份文件里的信息

注：备份时设置了--master-data=2因此备份文件里有备份时刻的主库的binlog日志信息


3.在从库上配置主从

根据第2步的位置信息，配置主从，然后开启主从同步，开启后查看主从同步的状态，如果IO线程(Slave_IO_Running)和SQL线程(Slave_SQL_Running:)均为Yes，则说明主从同步正常。

-- 配置主从
mysql> change master to master_host='10.163.78.121',master_port=3307,master_user='repl',master_password='123456',master_log_file='mysql_binlog.000006',master_log_pos=194;

Query OK, 0 rows affected, 2 warnings (0.06 sec)

-- 开启主从同步
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

-- 查看主从同步状态
mysql> show slave status\G


三、基于GTID的主从


1. 导入数据

2. 查看备份信息里的的GTID，配置起始GTID


从节点:

mysql> reset master;

Query OK, 0 rows affected (0.05 sec)

设置从节点GTID初始值

mysql> SET @@GLOBAL.GTID_PURGED='29a1d089-7a79-11e8-8164-525400ad5460:1-103';

Query OK, 0 rows affected (0.00 sec)


3. 配置主从同步，开启主从同步并查看同步状态

mysql> change master to master_host='10.163.78.121',master_port=3307,master_user='repl',master_password='123456',master_auto_position=1;

-- 开启主从同步
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

查看状态
mysql> show slave status\G

注意：本次配置就不需要写binlog文件名及位置信息，而是写master_auto_position=1即可。


在主库（master）上能查看到从库信息
mysql> show slave hosts;

==================================================================================================================================================================================

GTID模式在线切换成传统模式

切换之前查看下主从gitd_mode参数值主服务器：gtid_mode值

show variables like '%gtid_mode%';
gtid_mode  ON

从服务器gitd_mode值

show variables like '%gtid_mode%';
gtid_mode  ON


先在从库上关闭slave，停止主从复制，然后修改复制模式为传统模式，master_auto_position=0
stop slave;
change master to master_host='172.16.2.186', master_user='repuser', master_password='repuser123',
master_log_file='wm_binlog.000004', master_log_pos=1541,master_auto_position=0;

start slave;

从库：

set global gtid_mode=on_permissive;

set global gtid_mode=off_permissive;

set global gtid_mode=off;

set global enforce_gtid_consistency=off;

show variables like '%gtid_mode%';

 gtid_mode = OFF


主库：

show variables like '%gtid_mode%';

set global gtid_mode=on_permissive;

set global gtid_mode=off_permissive;

set global gtid_mode=off;

set global enforce_gtid_consistency=off;

show variables like '%gtid_mode%';

gtid_mode = OFF

两边gtid复制模式方式现已关闭，同时开始传统模式

==================================================================================================================================================================================

传统主从模式在线切换GTID模式

在主从库上修改enforce_gtid_consistency=warn,确保在error.log中不出现警告信息，如果有需要先修复。

Step：1

主库：

set global enforce_gtid_consistency=warn;

show variables like '%enforce_gtid_consistency%';


从库：

set global enforce_gtid_consistency=warn;

show variables like '%enforce_gtid_consistency%';


Step：2

在主从库上调整enforce_gtid_consistency=on,保证GTID的一致性

主库：

set global enforce_gtid_consistency=on;

show variables like '%enforce_gtid_consistency%';


从库：

set global enforce_gtid_consistency=on;

show variables like '%enforce_gtid_consistency%';

然后在主从库按照前面的顺序再一次修改回去，直至gtid_mode=on为止


主库操作：

set global gtid_mode=off_permissive;

set global gtid_mode=on_permissive;

set global gtid_mode=on;

show variables like '%gtid_mode%';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | ON |
+---------------+-------+


从库操作：

set global gtid_mode=off_permissive;

set global gtid_mode=on_permissive;

set global gtid_mode=on;

show variables like '%gtid_mode%';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | ON |
+---------------+-------+


上述gtid_mode参数值修改必须严格安装顺序依次修改，否则会报错。

查看从库的参数Ongoing_anonymous_transaction_count参数值是否为0，如果为0，意味着没有等待的事务，可以直接进行后面的操作

show global status like '%ongoing_anonymous%';

show variables like '%gtid%';


Step：3

把传统的复制模式改为GTID模式要把传统的复制停掉，然后执行stop slave，然后执行change master to master_auto_position=1

停止slave，查看当前主从状态：

stop slave;

change master to master_auto_position=1;

start slave;

show slave status\G

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,189评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,577评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,857评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,703评论 1赞 276
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,705评论 5赞 366
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,620评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,995评论 3赞 396
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,656评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,898评论 1赞 298
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,639评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,720评论 1赞 330
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,395评论 4赞 319
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,982评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,953评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,195评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 44,907评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,472评论 2赞 342