change master 未指定master_log_file

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.

MASTER_LOG_FILE=''為空的意思是找所能找到的最早的binlog

轉載請註明源出處
QQ 273002188 歡迎一起學習
QQ 群 236941212
oracle,mysql,mongo 相互交流

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25099483/viewspace-2055676/，如需轉載，請註明出處，否則將追究法律責任。

change master 未指定master_log_file

相關文章