MySQL – Large Dataset Dump and Restore

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.

Restoring Data

** 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

Cleanup

• 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