注意, 这里又有一个坑了
即使启动 start slave 成功了, 主从复制任然是失败的
1, 错误消息
- MySQL> show slave staus;
- Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;
these UUIDs must be different for replication to work.
2, 查看主从的 server_id 变量
- master_mysql> show variables like 'server_id';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | server_id | 33|
- +---------------+-------+
- slave_mysql> show variables like 'server_id';
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | server_id | 11|
- +---------------+-------+
-- 从上面的情形可知, 主从 MySQL 已经使用了不同的 server_id
3, 解决故障
- ### 查看 auto.cnf 文件
- [root@dbsrv1 ~] cat /data/mysqldata/auto.cnf ### 主上的 uuid
- [auto]
- server-uuid=62ee10aa-b1f7-11e4-90ae-080027615026
- [root@dbsrv2 ~]# more /data/mysqldata/auto.cnf ### 从上的 uuid, 果然出现了重复, 原因是克隆了虚拟机, 只改 server_id 不行
- [auto]
- server-uuid=62ee10aa-b1f7-11e4-90ae-080027615026
- [root@dbsrv2 ~]# mv /data/mysqldata/auto.cnf /data/mysqldata/auto.cnf.bk ### 重命名该文件
- [root@dbsrv2 ~]# service MySQL restart ### 重启 MySQL
- Shutting down MySQL.[ OK ]
- Starting MySQL.[ OK ]
- [root@dbsrv2 ~]# more /data/mysqldata/auto.cnf ### 重启后自动生成新的 auto.cnf 文件, 即新的 UUID
- [auto]
- server-uuid=6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9
- ### 再次查看 slave 的状态已经正常
- [root@dbsrv1 ~]# MySQL -uroot -pxxx -e "show slave status\G"|grep Running
Warning: Using a password on the command line interface can be insecure.
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
- ### 主库端查看自身的 uuid
- master_mysql> show variables like 'server_uuid';
- +---------------+--------------------------------------+
- | Variable_name | Value|
- +---------------+--------------------------------------+
- | server_uuid | 62ee10aa-b1f7-11e4-90ae-080027615026 |
- +---------------+--------------------------------------+
- 1 row in set (0.00 sec)
- ### 主库端查看从库的 uuid
- master_mysql> show slave hosts;
- +-----------+------+------+-----------+--------------------------------------+
- | Server_id | Host | Port | Master_id | Slave_UUID |
- +-----------+------+------+-----------+--------------------------------------+
- |33 | | 3306 |11 | 62ee10aa-b1f7-11e4-90ae-080027615030 |
- |22 | | 3306 |11 | 6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9 |
- +-----------+------+------+-----------+--------------------------------------+
来源: http://www.linuxidc.com/Linux/2018-09/154521.htm