MySQL5.6从库修改主机名同步停止的解决办法

  环境:MySQL5.6 + CentOS6.5

  问题描述:从库修改主机名后,从库的同步没有自动启动,用start slave命令开启失败

  从库修改主机名后,从库的同步没有自动启动,查看状态如下:

  mysql> show slave statusG

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

  Slave_IO_State:

  Master_Host: 192.168.1.2

  Master_User: manager_slave

  Master_Port: 3306

  Connect_Retry: 10

  Master_Log_File: logbin.000053

  Read_Master_Log_Pos: 588641410

  Relay_Log_File: zzstep-relay-bin.000015

  Relay_Log_Pos: 38778474

  Relay_Master_Log_File: logbin.000053

  Slave_IO_Running: No

  Slave_SQL_Running: No

  Replicate_Do_DB:

  Replicate_Ignore_DB: mysql,information_schema,performance_schema

  Replicate_Do_Table:

  Replicate_Ignore_Table:

  Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table: hn.sphinx%

  Last_Errno: 0

  Last_Error:

  Skip_Counter: 0

  Exec_Master_Log_Pos: 588641410

  Relay_Log_Space: 0

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

  Master_UUID: 8ce09c46-a7be-11e4-8e06-0050569f4b5b

  Master_Info_File: /opt/mysql_data/master.info

  SQL_Delay: 0

  SQL_Remaining_Delay: NULL

  Slave_SQL_Running_State:

  Master_Retry_Count: 86400

  Master_Bind:

  Last_IO_Error_Timestamp:

  Last_SQL_Error_Timestamp:

  Master_SSL_Crl:

  Master_SSL_Crlpath:

  Retrieved_Gtid_Set:

  Executed_Gtid_Set:

  Auto_Position: 0

  1 row in set (0.00 sec)

  使用start slave命令开启失败:

  mysql> start slave;

  ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

  使用reset slave命令解决:

  mysql> reset slave;

  Query OK, 0 rows affected (0.01 sec)

  mysql> start slave;

  Query OK, 0 rows affected (0.03 sec)

  mysql> show slave statusG

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

  Slave_IO_State: Queueing master event to the relay log

  Master_Host: 192.168.1.2

  Master_User: manager_slave

  Master_Port: 3306

  Connect_Retry: 10

  Master_Log_File: logbin.000021

  Read_Master_Log_Pos: 11215004

  Relay_Log_File: db_mysql_02-relay-bin.000007

  Relay_Log_Pos: 22097

  Relay_Master_Log_File: logbin.000005

  Slave_IO_Running: Yes

  Slave_SQL_Running: Yes

  Replicate_Do_DB:

  Replicate_Ignore_DB: mysql,information_schema,performance_schema

  Replicate_Do_Table:

  Replicate_Ignore_Table:

  Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table: hn.sphinx%

  Last_Errno: 0

  Last_Error:

  Skip_Counter: 0

  Exec_Master_Log_Pos: 21937

  Relay_Log_Space: 13113813

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

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

  Master_UUID: 8ce09c46-a7be-11e4-8e06-0050569f4b5b

  Master_Info_File: /opt/mysql_data/master.info

  SQL_Delay: 0

  SQL_Remaining_Delay: NULL

  Slave_SQL_Running_State: creating table

  Master_Retry_Count: 86400

  Master_Bind:

  Last_IO_Error_Timestamp:

  Last_SQL_Error_Timestamp:

  Master_SSL_Crl:

  Master_SSL_Crlpath:

  Retrieved_Gtid_Set:

  Executed_Gtid_Set:

  Auto_Position: 0

  1 row in set (0.00 sec)

  mysql>

  总结:

  由于修改主机名后,relay-log的名称发生变化,导致文件IO失败,reset slave可以重新定位。

    文章来源:http://huangfuff.blog.51cto.com/2632203/1610429

发表评论

邮箱地址不会被公开。 必填项已用*标注