物理备份后主从复制出现的问题

物理备份：表空件(共享和独立)、数据和表结构文件、重做日志文件、配置文件my.cnf(需要修改)

启动mysql服务器，

CHANGE MASTER TO MASTER_HOST='192.168.26.142',MASTER_USER='repl',MASTER_PASSWORD='lei66',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=426,master_port=3307;

start slave

show slave status 

（1）出现了下面问题

           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: 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: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind:

原因：直接把原有的datadir直接拷贝过去，导致两个服务器的UUID相同

解决办法：在datadir目录下auto.cnf删去，重启数据库会自动产生一个新的auto.cnf，文件中包含UUID

官方文档：The auto.cnf file has a format similar to that used for my.cnf or my.ini files. In MySQL 5.7, auto.cnf has only a single [auto]section containing a single server_uuid setting and value;

（2）复制时从出现问题（gtid_purged）：

            Executed_Gtid_Set: 0cf841b0-65ad-11e7-80af-000c2962c60e:61-70,

396800e4-657f-11e7-91a7-000c29062839:1-8,6c336913-52b2-11e7-971a-000c2962c60e:12-138

(2)reset master(把主服务器的binlog清除并从头开始创建)

(3)set gtid_purged='0cf841b0-65ad-11e7-80af-000c2962c60e:61-71,396800e4-657f-11e7-91a7-000c29062839:1-8,6c336913-52b2-11e7-971a-000c2962c60e:12-138

ps:reset master和reset slave谨慎使用。