错误
搭建主从出现以下错误
Last_IO_Error: The replication receiver thread cannot start because the master has GTID_MODE = OFF and this server has GTID_MODE = ON.
原因
MySQL主从的 Master 和 Slave 必须 同时开启或者关闭 enforce-gtid-consistency和 gtid-mode 功能，即要保持一致。
主节点
mysql> show variables like '%GTID_MODE%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | OFF |
+---------------+-------+
1 row in set (0.01 sec)
关闭状态
从节点
mysql> show variables like '%GTID_MODE%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | ON |
+---------------+-------+
1 row in set (0.00 sec)
开启状态
解决
开启主节点的 GTID
mysql> set global enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> 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.
mysql>
在更改GTID_MODE时不可直接跳跃更改，否则会提示报错
mysql> set @@global.enforce_gtid_consistency=warn;
Query OK, 0 rows affected (0.00 sec)
mysql> set @@global.enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Query OK, 0 rows affected (0.02 sec)
mysql> SET @@GLOBAL.GTID_MODE = on_permissive;
Query OK, 0 rows affected (0.01 sec)
mysql> set global gtid_mode=ON;
Query OK, 0 rows affected (0.01 sec)
查看
mysql> show variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | ON |
+---------------+-------+
1 row in set (0.01 sec)
主节点已开启
重新搭建
从库取消主从
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.03 sec)
主库查看 日志 和 位点
mysql> show master statusG
*************************** 1. row ***************************
File: binlog.000028
Position: 157
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
记住 file 文件 和 position 位点
从库搭建
mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='root', MASTER_PASSWORD='123456',MASTER_PORT=3306, MASTER_LOG_FILE='binlog.000028', MASTER_LOG_POS=157;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
查看
mysql> show slave statusG
发现 Slave_IO_Running 和 Slave_SQL_Running 均为 yes。主从同步正常
