我是 PHP 程序员

change 时未指定master_log_file,master_log_pos那么会从MASTER_LOG_FILE='' and MASTER_LOG_POS=4开始

同事要实现一个东西：

原来是 A->B

现在要改变A的ip地址。

在B上 直接指定了A的新ip,   主从状态就出错了。

change master master_host='10.19.140.142',master_user='ucloudbackup',master_password='kv6kFqqaDW';

start slave后

Slave_IO_State: Waiting for master to send event

               Master_Host: 10.19.140.142

               Master_User: ucloudbackup

               Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: mysql-bin.000003

         Read_Master_Log_Pos: 2385

             Relay_Log_File: mysql-relay.000002

              Relay_Log_Pos: 25128

       Relay_Master_Log_File: mysql-bin.000001

           Slave_IO_Running: Yes

          Slave_SQL_Running: No

            Replicate_Do_DB:

         Replicate_Ignore_DB:

         Replicate_Do_Table:

      Replicate_Ignore_Table:

     Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                Last_Errno: 1062

                Last_Error: Error 'Duplicate entry '%-test-' for key 'PRIMARY'' on query. Default database: 'mysql'. Query: 'INSERT INTO db SELECT * FROM tmp_db WHERE @had_db_table=0'

              Skip_Counter: 0

         Exec_Master_Log_Pos: 24982

            Relay_Log_Space: 1065343

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

              Last_IO_Error:

             Last_SQL_Errno: 1062

             Last_SQL_Error: Error 'Duplicate entry '%-test-' for key 'PRIMARY'' on query. Default database: 'mysql'. Query: 'INSERT INTO db SELECT * FROM tmp_db WHERE @had_db_table=0'

  Replicate_Ignore_Server_Ids:

           Master_Server_Id: 169053326

看起来像是 只指定了 ip 没有指定log_file的名称和位置造成的。

测试：

mysql> change master to  master_host='10.19.140.142',master_user='ucloudbackup',master_password='kv6kFqqaDW';

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G

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

             Slave_IO_State:

               Master_Host: 10.19.140.142

               Master_User: ucloudbackup

               Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File:

         Read_Master_Log_Pos: 4

             Relay_Log_File: mysql-relay.000001

              Relay_Log_Pos: 4

       Relay_Master_Log_File:

           Slave_IO_Running: No

          Slave_SQL_Running: No

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

            Relay_Log_Space: 107

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

              Last_IO_Error:

             Last_SQL_Errno: 0

             Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

           Master_Server_Id: 169031254

1 row in set (0.00 sec)

看起来是没有指定文件名和位置造成的。

help change master to可以看到：

  If you specify the MASTER_HOST or MASTER_PORT option, the slave

  assumes that the master server is different from before (even if the

  option value is the same as its current value.) In this case, the old

  values for the master binary log file name and position are

  considered no longer applicable, so if you do not specify

  MASTER_LOG_FILE and MASTER_LOG_POS in the statement,

  MASTER_LOG_FILE='' and MASTER_LOG_POS=4 are silently appended to it.