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.
