mysqldump packet bigger than max_allowed_packet
When backing up a MySQL database using mysqldump command, got following error
[root@vps189 ~]# mysqldump sok_main > sok_main.sql mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' when dumping table `wp_options` at row: 7068 [root@vps189 ~]#
To fix, you need to edit MySQL config file
vi /etc/my.cnf
Add following under [mysqld] section
max_allowed_packet=2G
Now restart MySQL with
systemctl restart mysql
You can verify the value is changed by running following SQL command in MySQL prompt
show variables like 'max_allowed_packet';
Or my running following command in command line
mysqladmin variables | grep max_allowed_packet
On a VPS, editing /etc/my.cnf did not changed the value in MySQL for some reason. I got it fixed by running mysqldump with –max_allowed_packet=2G option.
mysqldump --max_allowed_packet=2G DB_NAME > DB_NAME.sql