Exporting large databases with MySQL dump

I had to export a 7GB database recently using mysqldump.

I started out with the usual export call

mysqldump -u admin -p sugar > sugar_28_may.sql

However, the users complained that they couldn’t access the system while it was being exported.

The culprit was mysqldump’s habit of locking tables to export them. The solution was this

mysqldump -u admin -p --lock-tables=false sugar > sugar_28_may.sql

The other problem I got was this message

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

That message meant that I had to wait 15 minutes while the backup went up to 2GB and then the error kicked in.

I checked the max_allowed_packet by running

mysqldump --help
max_allowed_packet                25165824

It turned out the server had a 24MB packet limit. I increased it to 1GB.

mysqldump -u admin -p --max_allowed_packet=1073741824 --lock-tables=false sugar >sugar_28may12.sql

Note that the –max_allowed_packet option on MySQL 5.0xx seems to be max-allowed-packet on MySQL 5.5x. Running mysqldump –help will sort out the correct syntax to use.