MySQL case 09:last_io_error:got fatal Error 1236 from master when reading data from binary log

Just finished processing "mining" event, in the last MySQL Nbu backup, found that there is a problem from the library, curious is how the master-slave state abnormal no alarm? Regardless of so many, deal with this problem and then improve the alarm content.

First, error message

The error message that you see from the Library show slave status \g is as follows:

Slave_IO_Running:NoSlave_SQL_Running:YesLast_IO_Errno:1236Last_io_error:got Fatal Error1236  fromMaster whenReading data from binary Log:'Client requested Master to start replication from impossible position; the first event'Mysql-Bin.000081'at 480141113, the last event in read from'./Mysql-Bin.000081'at 4, the last byte read from'./Mysql-Bin.000081'At 4.'

Ii. Causes of Errors

Here see the io_thread from the library has been terminated, the error number is 1236, specifically due to reading the main library's Binlog log location (the firstevent ' MySQL-bin. 000081' at 480141113, the last event read from '. /mysql-bin. 000081' at 4) does not cause master-slave failures to fail.

Third, the solution

1. Check the Binlog information from the library status and read and execute

Mysql>Show slave status \g*************************** 1. Row***************************Slave_IO_State:Master_Host:xx.xx.xx.xx Master_user:username Master_port:3306Connect_retry: -Master_log_file:mysQL-bin. 000081  read_master_log_pos: 480141113relay_log_file:mysql9017-Relay-Bin.000163Relay_log_pos:480141259Relay_master_log_file:mysql-Bin.000081Slave_io_running:no Slave_sql_running:yes Replicate_do_db:replicate_ Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_I Gnore_table:last_errno:0Last_error:skip_counter:0 exec_master_log_pos: 480141113Relay_log_space:480141462until_condition:none Until_log_file:until_log_pos:0Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SS L_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:NULLmaster_ssl_verify_server_cert:no Last_io_errno:1236Last_io_error:got Fatal Error1236  fromMaster whenReading data from binary Log:'Client requested Master to start replication from impossible position; the first event'Mysql-Bin.000081'at 480141113, the last event in read from'./Mysql-Bin.000081'at 4, the last byte read from'./Mysql-Bin.000081'At 4.'Last_sql_errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: -1Rowinch Set(0.00Sec

2. View the Binlog content of the main library

[backup]# mysqlbinlog mysql-bin.000081 >mysql-bin.log

See the main Library binlog log mysql-bin.000081 the largest pos is 480140557, but from the library to read is ' MySQL-bin. 000081' at 480141113, it is clear that the POS value from the library is larger than the POS value that exists in the main library itself, resulting in a failure to read.

You can view Binlog's POS information and log content by using the following statement
Mysql> show Binlog events in ' mysql-bin.000081 ' from 480140557 limit 10;
Empty Set (0.04 sec)
3. Change the sync location from the library to complete data resynchronization

Main Library:

Mysqlbinlog mysql-bin.000082 |more

From library:

 to Master_host='xx.xx.xx.xx', master_user='  Username', master_port=3306, Master_password=' Password ', master_log_file='mysql-bin.000082', Master_log_pos = 4;

Start slave;

Show slave status \g

Master-Slave synchronization is normal

4. Main Library parameter improvement

This cause is largely due to master-slave synchronization in the process, the main library abnormal power loss, resulting in memory data transfer to the Binlog log from the library but not submitted to the Sync_binlog, that is, the main library settings may be problematic in the main library check parameter settings:

Mysql>Show global variables like '%sync_binlog%'; +---------------+-------+|Variable_name|Value|+---------------+-------+|Sync_binlog| 0     |+---------------+-------+1Rowinch Set(0.00Sec

Sure enough, the value is 0, do not actively synchronize the Binlog cache data to disk, and rely on the operating system itself does not periodically flush the contents of the file to disk. Set to 1 The safest, synchronize a binary log once per statement or transaction, even if it crashes, it loses at most one statement or transaction log, but is therefore also the slowest. This is set to 0, the case of power outage causes Binlog cache data loss not written to the binlog of the master library, but the Binlog information is synchronized to the slave library. This situation is prone to inconsistent master-slave data, so even after the master-slave data recovery, still need to verify the consistency of data through master-slave data comparison.

mysql> set global sync_binlog=1;
Query OK, 0 rows Affected (0.00 sec)

Change configuration file my.cnf settings sync_binlog=1

5. Master-Slave data verification

6.innodb_flush_log_at_trx_commit parameter extension

The innodb_flush_log_at_trx_commit parameter specifies how often the InnoDB log is written after the transaction commits. In fact, it is not rigorous, and look at the significance and performance of the different values.     At 0 , the  write operation of log log buffer is not related to the transaction commit operation. In this case, MySQL performs best, but if the mysqld process crashes, it usually results in the last 1s of log loss.     At 1 ,log buffer is written to the log file and written to disk each time the transaction commits. This is also the default value. This is the safest configuration, but it is also the slowest because each transaction requires disk I/O.     At 2 , each transaction commit writes to the log file, but does not immediately write to the disk, and the log file is brushed once per second to disk. At this point, if the mysqld process crashes, the data is not lost because the log has been written to the system cache, and in the case of an operating system crash, it usually results in the last 1s of log loss.

MySQL case 09:last_io_error:got fatal Error 1236 from master when reading data from binary log