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.