mysql gtid

MySQL 5.7 GTID在线切换

Posted by Sunday on 2018-05-09

在线切换的版本要>=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的事务
环境
MySQL版本为5.7.18
Master：192.168.1.41 3306 GTID
Slave：192.168.1.42 3306 GTID

实验一 将传统复制切换到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;
主从库执行 set global master_info_repository = ‘TABLE’;
主从库执行 set global relay_log_info_repository = ‘TABLE’;
在从库执行
stop slave;
change master to master_auto_position=1;
start slave;
记得更改配置文件
主

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
server_id = 41
log-bin = mysql-bin
binlog_format = row
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 1G
expire_logs_days = 30

gtid-mode = on
enforce-gtid-consistency = 1 
master-info-repository = TABLE
relay-log-info-repository = TABLE
sync-master-info = 1 
slave-parallel-workers = 2 
binlog-checksum = CRC32
master-verify-checksum = 1 
slave-sql-verify-checksum = 1 
binlog-rows-query-log_events = 1

从

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
server_id = 42
log-bin = mysql-bin
binlog_format = row
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 1G
expire_logs_days = 30

log-slave-updates = 1 
gtid-mode = on
enforce-gtid-consistency = 1 
master-info-repository = TABLE
relay-log-info-repository = TABLE
sync-master-info = 1 
slave-parallel-workers = 4 
binlog-checksum = CRC32
master-verify-checksum = 1 
slave-sql-verify-checksum = 1 
binlog-rows-query-log_events = 1 
relay_log_recovery=1

实验二 将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;
记得配置文件修改

链接：https://www.jianshu.com/p/5931eaffa437

CATALOG
    FEATURED TAGS
    mysql gtid
    FRIENDS