MySQL – Large Dataset Dump and Restore
By : Josh -
When performing a dump and restore of large datasets in MySQL that take multiple days to perform, I typically use the process outlined here.
Note that it is critical to connect to the server in question and establish a screen session to perform all commands and operations within. This prevents issues with client connections dropping. I have been known to start a multi-hour restore and need to take my laptop to another client and become discouraged when I had not followed this advice.
Dumping Data to Disk
1. Stop replication after noting binary log positions, if applicable:
STOP SLAVE; SHOW SLAVE STATUS\G SHOW MASTER STATUS;
Then edit your my.cnf file and temporarily add the following – just in case the mysql daemon restarts due to corrupt record or server reboot:
# /etc/my.cnf skip-slave-start = 1
2. Set the bind-address parameter to localhost and restart mysql to prevent network access to the server in question – a rogue connection could write or consume resources from the mysql daemon and slow down the dump and/or restore.
# /etc/my.cnf bind-address = 127.0.0.1
3. Dump data
mysqldump [--skip-add-drop-table] [--replace] -u root -pxxxxxxxx $database $table | gzip > /mnt/backups/$dumpfile-0.sql.gz
Dumping Partial Data
Sometimes you export due to a corrupt InnoDB record or the dump might fail at some point. If you know approximately where this occurred, use the following command to dump a partial set:
mysqldump --skip-add-drop-table --replace -u root -pxxxxxxxx $database $table --where="id > XXXXXXXX" | gzip > /mnt/backups/$dumpfile-N.sql.gz
** Note the use of the –replace command to ensure you can over-write existing data. This allows you to estimate conservatively where the previous dump may have stopped.
** Note that you can use the tail n +XX command to skip any drop/create statements in the file if you forgot to tell mysqldump to skip those commands.
gzip -dc /mnt/backups/$backupfile.sql.gz | tail -n +49 | mysql -u root -pxxxxxxxx $database
I will also run this shell script in a screen window to clear up any binary logs that might accumulate, if binary logging is enabled. Note: do not use this command if you are restoring a master which has an active slave during the process.
# while restoring, clear binary logs to prevent disk filling issues: while true; do echo "clearing binary logs"; echo "reset master;" | mysql -u root -pxxxxxxxx; sleep 60; done
- Enable / re-establish replication
- Enable backups
- Enable network access by disabling ‘bind-address’ in my.cnf
- Enable slave start, disable ‘skip-slave-start’ in my.cnf