mysql master_log_file_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 相互交流