Shulou(Shulou.com)06/01 Report--

Preface: as a database administrator of MySQL, he often encounters the problem of master-slave synchronization, and the more common one is the error report of error 1236. Just recently I ran into this error report again. Here are the documents sorted out.

[2] problem description

1. Environmental information

Mysql version: 5.7.20

Operating system version: centeros 6.5

2. Error message

Click (here) to collapse or open

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State:

Master_Host: 192.168.1.19

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000123

Read_Master_Log_Pos: 221693972

Relay_Log_File: DB-02-relay-bin.000011

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000123

Slave_IO_Running: No

Slave_SQL_Running: Yes

Replicate_Do_DB: DB02

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 221693972

Relay_Log_Space: 535

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

Last_IO_Error: 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 first event' mysql-bin.000123' at 221693972, the last event read from'/ data/binlog/mysql-bin.000123' at 123, the last byte read from'/ data/binlog/mysql-bin.000123' at 221693991.'

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 11

Master_UUID: b688e640-f5d3-11e7-9275-005056a675fe

Master_Info_File: / data/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp: 180705 23:22:44

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: b688e640-f5d3-11e7-9275-005056a666fe:1

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version: [3] cause of the problem

Slave_IO_Running: No

Slave_SQL_Running: Yes

As you can see from the above two parameters, the current binlog master library reported an error when it was transferred to the slave library. The binlog that has been transmitted can be executed normally.

Last_IO_Error:max_allowed_packet shows that the current parameter is too small and needs to be increased.

Max_allowed_packet controls the size of the binglog event transmission in the master-slave replication process. Generally speaking, the master-slave will report an error in the following two cases.

1. The configuration size of the parameters in the master / slave database is different, and the configuration value of the master database is greater than that of the slave database. The binlog event size passed from the primary library to the standby database exceeds the max_allowed_packet size of the primary or standby database.

2. When there is a large amount of data written in the main database, such as insert into. Select statement, which produces a large transaction.

When the master library passes a packet larger than the max_allowed_packet of the slave library, it fails to receive the packet from the slave library and reports "log event entry exceeded max_allowed_packet".

For this parameter, you can check the official documentation of mysql:

Https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet

PropertyValueCommand-Line Format--max-allowed-packet=#System Variablemax_allowed_packetScopeGlobal, SessionDynamicYesTypeintegerDefault Value4194304Minimum Value1024Maximum Value1073741824. This parameter can be modified in session, with a minimum of 1k and a maximum of 1GB. Default is 4m.

[4] solution

Click (here) to collapse or open

The method of viewing parameters

Mysql > show VARIABLES like'% max_allowed_packet%'

+-+ +

| | Variable_name | Value |

+-+ +

| | max_allowed_packet | 1073741824 | |

| | slave_max_allowed_packet | 1073741824 | |

+-+ +

2 rows in set (0.00 sec)

Do not restart the database modification method, but after the modification, log in to mysql again

Set global max_allowed_packet = 51210241024

The modification method of restarting database

Or modify the my.cnf parameter [server]

Slave_max_allowed_packet = 512m

After the modification, re-stop slave, and then start slave

But this is just one of the cases. Recently, a database found that the max_allowed_packet parameter had been set, but the master and slave would not synchronize anyway.

Finally, it is found that due to the misoperation of the user, the master_log_pos location is wrong and needs to be readjusted.

Change master to master_log_file='mysql-bin.006738', master_log_pos=107

Conclusion: after a toss, it is finally done, sometimes the wrong message is very obvious, but how to solve it can not be dealt with, at this time the experience of the administrator is reflected. There is also an open source product like mysql that needs to be clearer about the subsequent error reports, and it will be easier to manage.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.