MySQL Got fatal error 1236 cause and resolution "go"

This article from:http://blog.itpub.net/22664653/viewspace-1714269/

A preface
MySQL's master-slave replication as a high-availability feature for synchronizing the data from the main library to the slave library, as a full-time MySQL DBA when maintaining a master copy database cluster, I believe most people will encounter "Got fatal error 1236 from master when Reading data from binary log "This type of error/alarm. This article has sorted out several common error 1236 error, and gives the corresponding solution, there are shortcomings, of course, I hope you readers to correct me.

Two common error 1236 error
2.1 LogEvent over max_allowed_packet Size

1. Got fatal error 1236 from master when reading data from binary log: ' Log event entry exceeded Max_allowed_packet; Increase max_allowed_packet on Master; The start event position from ' mysql-bin.006730 ' in 290066246, the last event is read from '/u01/my3309/log/mysql-bin.006 730

" cause "
This type of error is related to Max_allowed_packet. First Max_allowed_packet control the master-slave copy process, a statement produced by the binary Binlog event size, its value must be a multiple of 1024. A common cause of this type of error is
1 This parameter is different in the configuration size of the main repository, the configuration value of the main library is greater than the configuration value from the library. The Binlog event size that is passed from the main library to the standby is larger than the max_allowed_packet size of the main library or the standby library.
2 The main library has a large number of data writes, such as executing laod data,insert into ... in the main library. Select statement, which produces large transactions.
When the main library passes a packet from the library that is larger than the max_allowed_packet from the library, it fails to receive the packet from the library and reports "Log event entry exceeded Max_allowed_packet".
" How to fix "
You need to ensure that the primary and standby configuration is the same, and then try to increase the value of this parameter.

1. Set global Max_allowed_packet =1*1024*1024*1024;
2. Stop slave;
3. Start slave

In addition, the slave_max_allowed_packet_size parameter in version 5.6 controls the maximum packet size that slave can receive, which is typically greater than and can override the Max_allowed_packet configuration. This reduces the master-slave replication interruption due to the above problems.

2.2 Slave cannot find the Binlog file in the main library

1. Got fatal error 1236 from master when reading data from binary log:

" cause "
This error occurs when a log is taken from the repository's IO process and the first file in the Mysql_bin.index file of the main library is found to be absent. This type of error may be due to your slave for some reason stopped for a long time, when you restart slave replication, the main library can not find the corresponding Binlog, will report such errors. Or because some settings on the main library are binlog, resulting in the deletion of the corresponding Binglog file from the library.
" How to fix "
1 To avoid data loss, the slave needs to be re-built.
2 Note the cleanup policy for the main library Binlog, whether the time-based deletion method is selected or based on how the space utilization is deleted.
Do not use the RM-FR command to delete the Binlog file, which does not synchronize the Binlog entries that modify Mysql_bin.index records. When deleting Binlog, make sure that the main library retains the Binlog file corresponding to Relay_master_log_file from the library show slave status.

2.3 Main Library space issues, log truncated

1. Got fatal error 1236 from master when reading data from binary log: ' Binlog truncated in the middle of event; Consider out of disk space on master; The start event position from ' mysql-bin.006730 ' in 290066434, the last event is read from '/u01/my3309/log/mysql-bin.006 730

" cause "
This error and the main library of the space problem and Sync_binlog configuration, when the main library sync_binlog=n is not equal to 1 and disk space is full, MySQL writes n binary log each time, the system will be synchronized to the disk, but because the storage log disk space is full, resulting in MySQL The log is not fully written to disk and the Binlog event is truncated. Slave reads the Binlog file, it will error "Binlog truncated in the middle of event;"
When the default value of Sync_binlog is 0, like the mechanism of the operating system to brush other files, MySQL does not sync to disk but relies on the operating system to flush binary logs.
When Sync_binlog =n (n>0), MySQL synchronizes its binary log binary logs to disk using the Fdatasync () function when it writes N binary log binary logs every nth time.
" How to fix "
The next available Binlog file is re-directed from the library to the main library and starts at the location where the Binlog file is initialized

1. Stop slave;
2. Change Master to master_log_file= ' mysql-bin.006731 ', master_log_pos=4;
3. Start slave;

2.4 Main Library Abnormal power off, read the wrong position from the library

1. 120611 20:39:38 [ERROR] Error reading packet from Server:client requested master to start replication from impossible Pos Ition (server_errno=1236)
2. 120611 20:39:38 [ERROR] Slave I/o: Got fatal ERROR 1236 from master if reading data from binary log: ' Client requested M Aster to start replication from impossible position ', error_code:1236
3. 120611 20:39:38 [Note] Slave I/O thread exiting, read up to log ' mysql-bin.000143 ', position 664526789

Reason
This problem is also related to sync_binlog=n not equal to 1, more than the current host abnormal crash, such as disk damage, RAID card corruption, or the host abnormal power-down caused binlog not in time to sync to disk. The non-existent binlog position in the main library Binlog file is read from the library and is generally larger than the value of Binlogfile end position.
" How to fix "
1 the next available Binlog file is re-directed from the library to the main library and starts at the location where the Binlog file is initialized

1. Stop slave;
2. Change Master to master_log_file= ' mysql-bin.000144 ', master_log_pos=4;
3. Start slave;

2 The main repository is set to Sync_binlog=1, but when set to 1, performance degrades.

Three related reading

MySQL Replication: ' Got fatal error 1236 ' causes and cures

1 Max_allowed_packet Official Introduction
2 Percona MySQL features Max_binlog_files
Analysis of Sync_binlog Innodb_flush_log_at_trx_commit 3

4 "Main Library abnormal power off, read the wrong position case" from the library  
5 "max_allowed_packet and binary log corruption in MySQL"

MySQL Got fatal error 1236 cause and resolution "go"