mysql 1593_解决配mysql集群时报"The slave I/O thread...slave have equal MySQL server UUIDs; these UUIDs .. E...

一、异常说明

在安装mysql主从cluster集群时报" [ERROR] Slave I/O for channel '': 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. Error_code: 1593"错误异常，详情如下>>[root@centos-cluster-s19423 ~]# tail  -f  /var/log/mysqld.log @b@2019-05-14T09:43:58.083624Z 0 [Note] Server socket created on IP: '::'.@b@2019-05-14T09:43:58.086731Z 0 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=centos-cluster-s19423-relay-bin' to avoid this problem.@b@2019-05-14T09:43:58.094486Z 2 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.@b@2019-05-14T09:43:58.095035Z 0 [Note] /usr/sbin/mysqld: ready for connections.@b@Version: '5.7.26-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)@b@2019-05-14T09:43:58.095050Z 1 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0@b@2019-05-14T09:43:58.095076Z 1 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000001' at position 120, relay log './centos-cluster-s19423-relay-bin.000003' position: 4@b@2019-05-14T09:43:58.096045Z 2 [Note] Slave I/O thread for channel '': connected to master 'root@192.168.1.204:3306',replication started in log 'mysql-bin.000003' at position 154@b@2019-05-14T09:43:58.099760Z 2 [ERROR] Slave I/O for channel '': 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. Error_code: 1593@b@2019-05-14T09:43:58.099772Z 2 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000003', position 154

且查看mysql slave备机状态Slave_IO_Running=false,具体如下mysql> show  slave  status;@b@+----------------+---------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+@b@| Slave_IO_State | Master_Host   | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File                         | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error                                                                                                                                          | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File           | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |@b@+----------------+---------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+@b@|                | 192.168.1.204 | root        |        3306 |            60 | mysql-bin.000003 |                 154 | centos-cluster-s19423-relay-bin.000004 |             4 | mysql-bin.000001      | No               | Yes               | 401_it00        |                     |                    |                        |                         |                             |          0 |            |            0 |                 120 |             154 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |          1593 | 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. |              0 |                |                             |                1 |             | /var/lib/mysql/master.info |         0 |                NULL | Slave has read all relay log; waiting for more updates |              86400 |             | 190514 17:43:58         |                          |                |                    |                    |                   |             0 |                      |              |                    |@b@+----------------+---------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+@b@1 row in set (0.00 sec)

二、解决方法

1.由于通过虚拟机vm克隆之前已安装有mysql的centos6.7的系统，造成mysql server-uuid重复(通过下面方式分别登录两台服务器查看mysql的UUID是否一样)[root@centos-cluster-s19423 ~]# mysql  -uroot  -p123456;@b@mysql: [Warning] Using a password on the command line interface can be insecure.@b@Welcome to the MySQL monitor.  Commands end with ; or \g.@b@Your MySQL connection id is 13@b@Server version: 5.7.26-log MySQL Community Server (GPL)@b@@b@Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.@b@@b@Oracle is a registered trademark of Oracle Corporation and/or its@b@affiliates. Other names may be trademarks of their respective@b@owners.@b@@b@Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.@b@@b@mysql> select  uuid();@b@+--------------------------------------+@b@| uuid()                               |@b@+--------------------------------------+@b@| 0eec5819-78a2-11e9-84d0-525400534aff |@b@+--------------------------------------+@b@1 row in set (0.04 sec)

2. 修改其中一个mysql的auto.cnf配置文件已区分server-uuid[root@centos-cluster-s19423 ~]# vi  /var/lib/mysql/auto.cnf @b@[auto]@b@server-uuid=b1bfa5f4-6a95-11e9-8049-525400534aee@b@~@b@~@b@ @b@"/var/lib/mysql/auto.cnf" 2L, 56C written@b@mysql> exit;@b@Bye

3. 重启mysql，再查看slave状态显示 Slave_IO_Running=true了[root@centos-cluster-s19423 ~]# service  mysqld  restart@b@停止 mysqld： [确定]@b@正在启动 mysqld： [确定]@b@@b@[root@centos-cluster-s19423 ~]# mysql  -uroot  -p123456;@b@mysql: [Warning] Using a password on the command line interface can be insecure.@b@Welcome to the MySQL monitor.  Commands end with ; or \g.@b@Your MySQL connection id is 13@b@Server version: 5.7.26-log MySQL Community Server (GPL)@b@@b@Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.@b@@b@Oracle is a registered trademark of Oracle Corporation and/or its@b@affiliates. Other names may be trademarks of their respective@b@owners.@b@@b@Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.@b@mysql> show  slave status;@b@+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+@b@| Slave_IO_State                   | Master_Host   | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File                         | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID                          | Master_Info_File           | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |@b@+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+@b@| Waiting for master to send event | 192.168.1.204 | root        |        3306 |            60 | mysql-bin.000003 |                 154 | centos-cluster-s19423-relay-bin.000006 |           320 | mysql-bin.000003      | Yes              | Yes               | 401_it00        |                     |                    |                        |                         |                             |          0 |            |            0 |                 154 |             543 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |                1 | b1bfa5f4-6a95-11e9-8049-525400534aff | /var/lib/mysql/master.info |         0 |                NULL | Slave has read all relay log; waiting for more updates |              86400 |             |                         |                          |                |                    |                    |                   |             0 |                      |              |                    |@b@+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+@b@1 row in set (0.00 sec)