MySQL GTID 主从错误
2023-12-28 12:14:38
错误
搭建主从出现以下错误
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 status\G *************************** 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 status\G
发现 Slave_IO_Running 和 Slave_SQL_Running 均为 yes。主从同步正常
文章来源:https://blog.csdn.net/weixin_68243500/article/details/135264975
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!