mysql报错1593_主从复制报1593错误的处理

由于模拟一主二从场景下主从切换的需要，在增加第二个从库时，启动第二个从库报如下错误：

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: 10.16.24.107

Master_User: repl

Master_Port: 3376

Connect_Retry: 60

Master_Log_File: mysql-bin.000068

Read_Master_Log_Pos: 120

Relay_Log_File: relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000068

Slave_IO_Running: No

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_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: 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.

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1073376

Master_UUID:

Master_Info_File: /data/mysql/mysql3376/data/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: 160508 17:26:22

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)

上面错误发生在IO线程，错误码为1593，报错很明显是UUID与主库冲突:

查看主库的UUID：

(product)root@localhost [(none)]> show variables like 'server_uuid';

+---------------+--------------------------------------+

| Variable_name | Value                                |

+---------------+--------------------------------------+

| server_uuid   | 36af7e42-f4fc-11e5-8b08-0050568a0bcb |

+---------------+--------------------------------------+

查看从库的UUID:

mysql> show variables like 'server_uuid';

+---------------+--------------------------------------+

| Variable_name | Value                                |

+---------------+--------------------------------------+

| server_uuid   | 36af7e42-f4fc-11e5-8b08-0050568a0bcb |

+---------------+--------------------------------------+

1 row in set (0.00 sec)

上面比较UUID，确实完全相同，哦，原来第二个从库是主库冷备过来的，同时有将atuo.conf文件一同copy过来。

查看从库的auto.cnf文件内容如下：

[mysql@mvxl0784 data]$ cat auto.cnf

[auto]

server-uuid=36af7e42-f4fc-11e5-8b08-0050568a0bcb

[mysql@mvxl0784 data]$ pwd

/data/mysql/mysql3376/data

删除auto.cnf，并重启mysql后，查看auto.cnf文件，发现重新自动生成后内容不同了。

[mysql@mvxl0784 data]$ cat auto.cnf

[auto]

server-uuid=5548c382-1501-11e6-9bd4-0050568a3cbf

再启动slave后，查看从库状态，这时正常了。

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.16.24.107

Master_User: repl

Master_Port: 3376

Connect_Retry: 60

Master_Log_File: mysql-bin.000068

Read_Master_Log_Pos: 120

Relay_Log_File: relay-bin.000003

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000068

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_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: 450

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_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1073376

Master_UUID: 36af7e42-f4fc-11e5-8b08-0050568a0bcb

Master_Info_File: /apps/mysql/mysql3376/data/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)