MySQL Nightly Backups of a large database


I am sure we are all aware of mysqldump, a great tool for backing up your database. It is included in the /bin directory of any mysql install. It is also useful that the backup file it creates is a series of insert statements in a .sql file, so it is easy to restore your database.

The basic command line syntax for backing up your database is:
mysqldump --user=dbuser -p dbname>mybackup.sql

This creates the file mybackupfile.sql which is a database backup.

Usually when we make a database backup, we want to copy the backup to a tape or across the network. And what if the file we backed up is huge? 10GB or more? Then it starts to take a while to do nightly backups. The backup process will still be quick but copying a file of that size anywhere is prone to failure. Then... what about rsync? Could we use rsync for incremental backups? No we can't. Rsync doesn't incrementally transfer portions of files, only entire files, so it wouldn't work because the database backup is one .sql file.

What if we can break up the database into multiple files? Then we might have something here. While most large databases grow over time, the older data doesn't usually change very often and can be referenced with a date field or an 'id' field. Based on that assumption we can use mysqldump for incremental backups.
mysqldump --user=dbu --where="id <=1000" -p mydb tn>mybackup1.sql
mysqldump --user=dbu --where="1000 < id AND id <=2000" -p mydb tn>mybackup2.sql
mysqldump --user=dbu --where="2000 < id AND id <=3000" -p mydb tn>mybackup3.sql
REM (where tn is the tablename, mydb is the database name, and dbu is db user)

So... if all the new data in the db is happening between ids 2000 and 3000, we can expect that records 0 to 2000 won't change very much. So when we back up the records 0 to 1000, we can use rsync for incrementally backups, saving valuable backup time.

You can even use a batch file to generate the mysqldump statements.

backup.bat
set DMP="C:\\Program Files\\MySQL\\MySQL Server 5.0\\bin\\mysqldump.exe"
FOR /L %%A IN (2000,1,2010) DO %DMP%  --user=dbu --where="date>='%%A-01-01' and date<='%%A-12-31'" mydb tn>myback%%A.sql

This will generate a backup file for each year.

Note: If you do use the batchfile you will need to add --password=mypass to avoid the password prompts for each backup.
code snippets are licensed under Creative Commons CC-By-SA 3.0 (unless otherwise specified)