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