slave have equal MySQL Server UUIDs原因及解決
最近在部署MySQL主從複製架構的時候,碰到了"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." 這個錯誤提示。即主從架構中使用了相同的UUID。檢查server_id系統變數,已經是不同的設定,那原因是?接下來為具體描述。
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 |
+-----------+------+------+-----------+--------------------------------------+
### Author : Leshami
### Blog :