mysqldump: Error 2020: Got packet bigger than ‘max_allowed_packet’ bytes when dumping table

I got this error when I was dumping a huge table which size is more than 26GB in size.

mysqldump: Error 2020: Got packet bigger than ‘max_allowed_packet’ bytes when dumping table `QPR` at row: 5659

The server’s default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns). The largest possible packet that can be transmitted to or from a MySQL 5.1 server or client is 1GB.

edit your my.cnf file and add
max_allowed_packet=1024M then restart

[root@db01 ~]# service mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

mysql> show GLOBAL variables like 'max_allowed_packet%';
+--------------------+------------+
| Variable_name | Value |
+--------------------+------------+
| max_allowed_packet | 1073740800 |
+--------------------+------------+
1 row in set (0.00 sec)
mysql>

NB: I need huge blog type data to be fetched from db. We can also avoid this restart buy
Make sure that if the parameter values are changed by executing this command (ex.)show parameters like ‘max_allowed_packet’ from MySQL client(default).

Suppose if you dumping huge blob data from another host, you also need to change this mysql variables to get it worked. Here I’m setting this to 1GB

mysql> <strong>set global max_allowed_packet=100000000000;</strong>
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'max_allowed_packet%';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.10 sec)
mysql> <strong>set max_allowed_packet=100000000000;</strong>
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'max_allowed_packet%';
+--------------------+------------+
| Variable_name | Value |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set (0.00 sec)
mysql>

Finally the real solution is you need to add the “max_allowed_packets” parameter along with the mysqldump. mysqldump is notorious for ignoring this value in my.cnf, but setting it as the command line parameter always works.

#mysqldump -u root -p –max_allowed_packet=512M -B database –tables tblblogdb > dump.sql

I think it’s always better to run large mysqldump from another server from the same network which help to reduce disk resource and cpu power during the peak hours. Here is the one sample,
#$MYSQLDUMP –max_allowed_packet=1G -u $MyUSER -h $MyHOST -p$MyPASS -B $db | bzip2 > $FILE