mysqldump packet bigger than max_allowed_packet
When backing up a MySQL database using mysqldump command, got following error
|
1 2 3 |
[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
|
1 |
vi /etc/my.cnf |
Add following under [mysqld] section
|
1 |
max_allowed_packet=2G |
Now restart MySQL with
|
1 |
systemctl restart mysql |
You can verify the value is changed by running following SQL command in MySQL prompt
|
1 |
show variables like 'max_allowed_packet'; |
Or my running following command in command line
|
1 |
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.
|
1 |
mysqldump --max_allowed_packet=2G DB_NAME > DB_NAME.sql |