[MySQL] Fetal 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.

 

 

---

 

 

1. 오류 발생

---

• replication 구성 시 mysqldump 를 통해 데이터를 그대로 복사하여 리플리케이션을 구성 시 발생하는 오류이다.

mysql> show slave status\G
*********************************1. row ************************************
Slave_IO_State:
Master_Host: 12.345.678
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 120
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Relpicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 120
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1593
Last_IO_Error: Fetal 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.
Last_SQL_Errono: 0
Last_SQL_Error:
Replicate_Ignore_Server_Id:
Master_Server_Id: 1
Master_UUID:
Mater_Info_File: /var/lib/mysql/master.info
SQL Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

 

 

 

2. 오류 발생 원인

---

• auto.cnf 라는 파일에 UUID 정보가 저장되어 있는데 master DB 의 데이터를 dump 해올때 해당 파일도 함께 복사해서 발생한다.

 

 

3. 오류 해결

---

• Master 서버의 UUID 가 들어있는 auto.cnf 파일을 삭제해주면 된다.

(1) auto.cnf 의 내용 확인

[root@db02 oper]# cat /var/lib/mysql/auto.cnf
[auto]
server-uuid=8d2102f9-9d83-23dz-b3j3-928d7a78ew32

 

(2) 서비스 중지

[root@db02 oper]# systemctl stop mysqld

 

(3) auto.cnf 파일 삭제

[root@db02 oper]# rm -rf /var/lib/mysql/auto.cnf

 

(4) 서비스 재시작

[root@db02 oper]# systemctl start mysqld

 

(5) slave 시작

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

 

(6) slave 상태 조회

mysql> show slave status\G
*********************************1. row ************************************
Slave_IO_State: Waiting for master to send event
Master_Host: 12.345.678
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 34276
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 34439
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Relpicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 34276
Relay_Log_Space: 34613
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errono: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 8d2102f9-9d83-23dz-b3j3-928d7a78ew32
Mater_Info_File: /var/lib/mysql/master.info
SQL Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

 

Last_IO_Error 없이 Replication 이 제대로 작동하는 것을 확인할 수 있다.