Shulou(Shulou.com)06/01 Report--

Recently, when deploying the MySQL master-slave replication architecture on a virtual machine, I encountered

"Last_IO_Error: Fatal error: The slave Iram O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work."

This error prompt.

That is, the same UUID is used in the master-slave architecture. Check the server_id system variable, which is already a different setting. What is the reason?

1. Error prompt:

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State:

Master_Host: 192.168.30.138

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000023

Read_Master_Log_Pos: 331

Relay_Log_File: mysql2-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: binlog.000023

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: 331

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: 1

Master_UUID:

Master_Info_File: / data/mysql/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: 160520 10:54:33

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 has an error when viewing the status of slave.

2. Check server_id and UUID

Master:

Mysql > show variables like'% server%'

+-+

| | Variable_name | Value |

+-+

| | character_set_server | utf8 |

| | collation_server | utf8_general_ci |

| | innodb_ft_server_stopword_table |

| | server_id | 1 | |

| | server_id_bits | 32 | |

| | server_uuid | e5fa0e64-0c48-11e6-a2f7-000c292545f2 |

+-+

6 rows in set (0.00 sec)

Slave:

Mysql > show variables like'% server%'

+-+

| | Variable_name | Value |

+-+

| | character_set_server | utf8 |

| | collation_server | utf8_general_ci |

| | innodb_ft_server_stopword_table |

| | server_id | 2 | |

| | server_id_bits | 32 | |

| | server_uuid | e5fa0e64-0c48-11e6-a2f7-000c292545f2 |

+-+

6 rows in set (0.00 sec)

From the above, you can see that the server_id on both sides is inconsistent, but the UUID is the same.

Check the auto.cnf file

This file is in the datadir directory

[root@mysql1 data] # cat auto.cnf

[auto]

Server-uuid=e5fa0e64-0c48-11e6-a2f7-000c292545f2

[root@mysql2 data] # cat auto.cnf

[auto]

Server-uuid=e5fa0e64-0c48-11e6-a2f7-000c292545f2

3. Solution

Because this is a virtual machine operation, the whole system uses cloning to build the slave library, which leads to the consistency of the master and slave UUID.

1) Delete the original auto.cnf file from the library

[root@mysql2 data] # mv auto.cnf auto.cnf.bak

2) restart the MySQL process

[root@mysql2 data] # service mysqld restart

Shutting down MySQL.. [OK]

Starting MySQL.... [OK]

[root@mysql2 data] #

3) check the auto.cnf file. UUID has changed.

[root@mysql2 data] # cat auto.cnf

[auto]

Server-uuid=0f7d1af5-1e37-11e6-97e2-000c29a23ba9

4) restart slave

[root@mysql2 data] # mysql-uroot-p

Enter password:

.

Mysql > start slave

Query OK, 0 rows affected, 1 warning (0.00 sec)

5) check the slave status.

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.30.138

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000023

Read_Master_Log_Pos: 410

Relay_Log_File: mysql2-relay-bin.000003

Relay_Log_Pos: 359

Relay_Master_Log_File: binlog.000023

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: 410

Relay_Log_Space: 533

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: 1

Master_UUID: e5fa0e64-0c48-11e6-a2f7-000c292545f2

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

So far, the problem has been solved and the synchronization is normal.

When building master and slave in the way of virtual machine cloning, you need to pay attention, otherwise it is easy to cause the above problems.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.