MySQL Backups

February 28, 2020 Stanly G Linux, MySQL 0 minutes, 54 seconds

Trouble getting a backup to restore? Does it take forever? The problem might just be in the backup.

Backup without extended inserts (one statement per line):

dbname=wordpress; mysqldump --skip-extended-insert $dbname | gzip > $dbname.sql.gz

This backup will be very large, and it will probably take a long time to restore, but if it crashes, you'll know exactly how far it made it. This type of backup is also very useful for extracting data. It puts each INSERT statement on a separate line. For my sample 3GB database, this took about 3 hours to restore.

Backup with a set of options that will make the restore faster:

dbname=wordpress; mysqldump --net_buffer_length=8K --max-allowed-packet=1M --no-autocommit \
--disable-keys $dbname | gzip > $dbname.sql.gz

I don't know if all 4 of the options are required. (I'll update if I figure it out.) This made a good backup of a 3GB database that I could restore quickly. For my sample 3GB database, this took about 10 minutes to restore.

And to restore that database, regardless of which option you chose:

dbname=wordpress; pv $dbname.sql.gz | gunzip | mysql $dbname