物理備份後主從複製出現的問題

物理備份：表空件(共享和獨立)、數據和表結構文件、重做日誌文件、配置文件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謹慎使用。