MySQL5.7在线开启/关闭GTID
环境介绍
Part1:写在最前
截止本文撰写当日,MySQL5.7.16是官网的最新稳定版,本文将用MySQL5.7.16来进行演示。从MySQL5.6开始,支持了GTID复制模式,这种模式其实是把双刃剑,虽然容易搭建主从复制了,但使用不当,就容易出现一些错误,例如error 1236。在MySQL5.6如果开启GTID模式,需要在my.cnf中加入以下几个参数:
①log-bin=mysql-bin
②binlog_format=row
③log_slave_updates=1
④gtid_mode=ON
⑤enforce_gtid_consistency=ON
Warning:警告这里的一些参数不是动态参数,也就是需要重启mysql才能生效。
Part2:环境[root@HE1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.16-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.16-log |
+------------+
1 row in set (0.00 sec)
mysql> show global variables like 'log_slave%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| log_slave_updates | OFF |
+-------------------+-------+
1 row in set (0.00 sec)
MySQL5.6开启GTID需要开启log_slave_updates参数,否则启动报错,而5.7不在需要开启该参数。
实战
Part1:搭建传统复制模式
本文环境HE1为从,HE3为主库
[root@HE1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server v