Master-slave synchronization error caused by max_allowed_packet restriction in Mysql

MySQL Master run for some time, nothing has gone wrong. But recently went on two times problem, the record is convenient behind the investigation!

Slave_io_running and slave_sql_running are yes, master-slave sync error

First, confirm the status of each server. View the main library state is normal, binlog position has been changing, the process state is also normal.

mysql> show master status; +------------------+-----------+--------------+------------------+ | file              | Position  | Binlog_Do_DB | 

binlog_ignore_db | +------------------+-----------+--------------+------------------+ | mysql-bin.000364 |  232554068 |              | 

                 |

+------------------+-----------+--------------+------------------+   mysql> show processlist; +-------------+----------+-----------------------------------------------------------------------------+ |  command     | time     | state                                                                      

    | +-------------+----------+-----------------------------------------------------------------------------+ |  Connect     | 14536445 | slave has read all relay

 log; waiting for the slave i/o thread to update it | | binlog dump |    22459 | master has sent all  binlog to slave; waiting for binlog to be updated  

     | +-------------+----------+-----------------------------------------------------------------------------+ 

Look at the library state, the overall value of the library is only a delay.

mysql> show slave status\g;   master_log_file: mysql-bin.000364 read_master_log_pos: 246924389 relay_log_file:  mysql-relay-bin.3831269 relay_log_pos: 244389572 relay_master_log_file: mysql-bin.000363 Slave_IO_ Running: yes Slave_sql_running: yes seconds_behind_master: 23423   mysql> show 

Processlist; +---------+-------+-----------------------------------------------------------------------------+-------------- ----+ | command | time  | state                                                                           | info             | +---------+-------+-----------------------------------------------------------------------------+-------------- ----+ | connect | 22800 | waiting for master to send event                                               | NULL        

     | | connect |    99 | slave has read all relay  log; waiting for the slave i/o thread to update it | 

null             | +---------+-------+-----------------------------------------------------------------------------+------------------+ 

But wait for a while to view the library has not been updated, after the restart Seconds_behind_master for 0,slave_io_running and slave_sql_running states are yes. Confirmed the Master_host, Master_user and other parameters, also matched the master_server_id are normal. Sql_slave_skip_counter was also found on the Internet to skip one step, but because of the high requirements for data integrity, fear of generating data abnormalities and dare not operate. So there's basically no more.

Waiting for a day can not find the intention to redo, but redo is not the way, gotta find the problem, the data more can not be repeated every time. Previously looked at Binlog no obvious discovery, so still have to go to see the next binlog see what can be found?

Mysqlbinlog mysql-relay-bin.3831269--start-position=244389572--stop-position=246924461 | More

mysqlbinlog mysql-relay-bin.3831269--start-datetime= "2014-08-07 21:30:00"--stop-datetime= "2014-08-07 21:35:00 "--base64-output=decode-rows-v | More

Binlog based on row replication with the--base64-output=decode-rows-v parameter.

Slowly also really found something, found that there are many DELETE statements executed, when through the WC statistics found unexpectedly there are more than 700,000. In the view of the business is there to execute a SQL, delete all the records in the table, the data is too much, this time to see the master and subordinate this table records, the main library is empty, the database records are all in, that may be caused by this reason. The operation can be skipped, so try skipping it:

Mysql>slave stop;

Mysql>set GLOBAL sql_slave_skip_counter = 1;

Mysql>slave start;

After jumping MySQL restore normal, and finally manually empty the database of the data of the table. As to why this large deletion caused the heavy storage to stop, still need to delve into.

Max_allowed_packet restrictions cause master-Slave synchronization errors

The cause is also implemented a large update to the database to update a few 10 megabytes of data (visible update unreasonable), resulting in the master-slave synchronization error, view the status of the library display

Last_io_error:got fatal Error 1236 from master when reading the data from binary log:
' Log event entry exceeded Max_allowed_packet; Increase max_allowed_packet on master '

There are obvious error descriptions to check a lot, the description says to increase the max_allowed_packet of the main library.

Max_allowed_packet

MySQL service is through the network packet to transmit data (communication packet refers to the MySQL server is sent to a single SQL statement or sent to the client of a single line), the MySQL protocol can recognize the size of the packet is controlled by Max_allowed_packet. When a MySQL client or mysqld server receives a packet larger than Max_allowed_packet byte, the log event entry exceeded Max_allowed_packet is issued. Error, and close the connection. As this master-slave replication encounters, the IO process gets the log from the main library, but the SQL size in a single log exceeds the Max_allowed_packet limit, and the IO thread process stops and SQL thread is displayed as Yes. For clients, if the communication packet is too large, a "lost connection to the MySQL server" error may be encountered during query execution.

Stop the Heavy library, the master and subordinate are adjusted, and then start the heavy library can!

stop slave;

Set global max_allowed_packet=1035543552;

Start slave;