MySQL Community-Server 5.6.39 RHEL7 Scenraio: Trying to process binary logs to restore the incremental backup and receive max_allowed_packet error.

Turned off binary logs,restarted the database. Backed up the current schema using.

**mysqldump -u root -p --single-transaction --databases nsb > /backup/nsb_backup.sql**

Turned on binary logging and restarted the database.

Now I created a new schema called abcd, created two tables abc and xyz in it. Populated the tables with 1M rows.

insert into abc values (100000, 'ABCDBDHDBFFFL'); and then insert into abc select * from abc; If I understand it these all changes are recorded in the binary logs. There were binary logs in the data directory from MYSQLTEST_binlog.000001 to MYSQLTEST_binlog.000010.

Now I dropped the new schema abcd hoping it to restore using binary logs.

Restarted the database: Tried to restore using

$ mysqlbinlog MYSQLTEST_binlog.0000{01..10} > /tmp/test.sql

Now I tried to reload the test.sql dump.

$ mysql -u root -p < /tmp/test.sql

It goes on for some time and I receive the irritating error in the log file Aborted connection 1 to db: 'abcd' user: 'root' host: 'localhost' (Got a packet bigger than 'max_allowed_packet' bytes)

I confirmed the same in the my.cnf file which was already set to 1G in the [mysqld] section. The maximum permissible value for this variable is 1G. I can't raise it further.

Could anyone help me understand what is happening. Is it the repeated inserts causing this issue or anything else? I checked some forums but all suggest me to check the my.cnf file for max_allowed_packet and they suggest to restart the database. https://stackoverflow.com/questions/93128/mysql-error-1153-got-a-packet-bigger-than-max-allowed-packet-bytes

Mysql - Got a packet bigger than 'max_allowed_packet' bytes) when importing

Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table

Restore (PITR) of CloudSQL (MySQL) fails with Got a packet bigger than 'max_allowed_packet' bytes

MySQL "max_allowed_packet" error -- how do you profile the max packet size?

MySQL max_allowed_packet greater than 1024 MB

Also most other questions on the web are related to replication and max_allowed_packet. My question is not about replication.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.