0

I try to take a backup of a huge MySQL database via mysqldump. The database has millions of rows with a total size of 1.3TB.

CREATE TABLE PageContents
(
ID int(11) unsigned,
Added date,
Text MEDIUMTEXT,
FOREIGN KEY(ID) REFERENCES AllPages(ID) ON DELETE CASCADE,
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci KEY_BLOCK_SIZE=1;

but I receive an error after 8 hours of dumping:

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `PageContents` at row: 20946782

I had increased max_allowed_packet in mysql.cnf

max_allowed_packet  = 16G
  • 1
    Documentation claims that max value for max_allowed_packet is 1 Gb... you may try to use structure dump + CSV data backup. Structure backup must be divided by 2 parts. While restore, you restore structures with indices only, then load data, then restore FKs and triggers. – Akina Feb 3 at 12:55
  • 1
    Have you considered LVM snapshots or a replication solution? Also, have you looked at Percona's XtraBackup instead of mysqldump? – Vérace Feb 3 at 15:43
1

First, make sure you have set max_allowed_packet in the appropriate section [mysqldump] of the config file my.cnf and that it does not exceed the maximal value of 1G:

[mysqldump]
max_allowed_packet=1G

Or you can also specify it at runtime (without having to change my.cnf):

mysqldump --max-allowed-packet=1G ...
| improve this answer | |
1

You can add max_allowed_packet=512M to your mysqldump command.

Or add max_allowed_packet=512M to [mysqldump] section of your my.cnf

Note: It will not work if it is not under the [mysqldump] section

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.