Mysql slave master-Slave synchronization exception handling error:1872

1, the cause of the error:

Today received Zabbix disk alarm, through the du command to find the MySQL directory is too large, found that the relay log does not set the expiration time, so manually remove the relay log from the library, after restarting MySQL main from the replication state exception:

 du  -h --max-depth=1 / |sort   #查看根目录下各目录大小，并进行排序

2. Error message:

    [ERROR] Slave SQL: Slave failed to initialize relay log info structure from the repository, Error_code: 1872  

3. View Mstaer Log Logging point

  mysql> Show Master Status \g*************************** 1. Row *************************** File: mysql-bin.000033 position:199787569 binlog_do_db:binlog_ignore_db:executed_gtid_set:950c9b3d-737f-11e7-8 956-000c29fb7e00:1-381889421 row in Set (0.00 sec) mysql> Show Master status;+------------------+-----------+------- -------+------------------+-------------------------------------------------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+------------------+-----------+--------------+------------------+------- ------------------------------------------+| mysql-bin.000033 |              199787569 |                  | | 950c9b3d-737f-11e7-8956-000c29fb7e00:1-38188942 |+------------------+-----------+--------------+--------------- ---+-------------------------------------------------+1 row in Set (0.00 sec)  

4, because it is RM Delete relay log, cause MySQL Unable to further process the related log, at this time need to stop slave stop and then reset initialization

Through the above error and relay log introduction, it is easy to know that because the Mysql.slave_relay_log_info table retains the previous replication information, so that the new from the library can not be found when the corresponding file, then we clean up the records in the table is not enough. Again, do not manually delete the table data, MySQL has provided us with tools: RESET Slave:

mysql>reset slave   mysql>change master to master_log_file=‘mysql-bin.000033‘,master_log_pos=199787569 ;   #与masterbinlog点进行关联mysql>start slave;  

4.1 Reset slave Feature Description:

1、删除slave_master_info ，slave_relay_log_info两个表中数据；2、删除所有relay log文件，并重新创建新的relay log文件；3、不会改变gtid_executed 或者 gtid_purged的值

5. Verify Master-slave status

Mysql> Show slave status\g*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos t:192.168.0.37 #master主库 master_user:rep master_port:3306 Connect_ret Ry:60 master_log_file:mysql-bin.000033 read_master_log_pos:199850445 Relay_log_file : db03-relay-bin.000003 relay_log_pos:25581 relay_master_log_file:mysql-bin.000033 Sla Ve_io_running:yes #主要看这里 Slave_sql_running:yes #主要这里 Replicate_do_db:replicate_igno Re_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignor e_table:last_errno:0 last_error:skip_counter:0 Exec_mas ter_log_pos:199850445 relay_log_space:25958 Until_conditioN:none until_log_file:until_log_pos:0 Master_ssl_allowed:no master_s SL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Ma                Ster_ssl_key:seconds_behind_master:0master_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:1 master_uuid:950c9b3d-737f-11e7-8956-000c29fb7e00 Master_info_file: /application/mysql-5.6.35/data/master.info sql_delay:0 Sql_remaining_delay:null Slave_S Ql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it master_retry_count:86400 Master_bind:last _io_error_timestamp:last_sql_error_timestamp:master_ssl_crl:master_ssl_crlpath:retrieved_gtid_set:950c9b3d-737f-11e7-8956-000c29fb7e00:38188963-38188981 Executed_ gtid_set:291d8a91-75f5-11e7-995f-000c29ea7194:1-8,950c9b3d-737f-11e7-8956-000c29fb7e00:1-38188981 Auto_Pos Ition:11 row in Set (0.00 sec)

Mysql Slave master-Slave synchronization exception handling error:1872