What is best practice for backing up a production MySQL database (i.e. on a live website and potentially being updated)?
Some goals I have in mind are:
One way is to run replication onto a second server
• Stop the replication
• Do the backup on the second server
• Restart the replication
This gives you the added advantage that if your main server dies you have a live copy of the data
MySQL has a tool to take online backups: MySQL HotCopy.
Unfortunately this only works for MyISAM, and ARCHIVE tables.
Mysqldump can handle everything except incremental backups.
For InnoDB tables, mysqldump provides a way of making an online backup:
shell> mysqldump --all-databases --single-transaction > all_databases.sql
If you need incremental backups you may want to look into MySQL's Binary logging: Binary Log.
Backing up the binary logs will only record those queries which modify data, and you can do point in time restores with this as well. Backup Recovery.
Nathan Coffield, Dedicated Server Engineer, [email protected], www.hostmysite.com
The DBs are stored in /usr/local/mysql/var on my server. I just have a cronjob to trigger a shell script:
#!/bin/bash
cd /usr/local/mysql/var
x=`*` #What to backup
f=`date +%y%m%d`
f=`echo "$f-mysql-backup.tgz"` #what to name backups
tar -czf /var/WWW/$f $x
##end backup script
I would suggest you to setup Replication in MySQL. You will have to setup a Master and Slave server. All read-writes to the DB could go to your Master Server.
The DB backup can then be taken from the Slave server without interrupting the Master. Even if you pause/stop the Replication process during the backup process, the Master wont get affected. Only the Slave will be affected during that time. But since the read-writes are directed to the Master, your website wont be locked out.
To enable incremental backups, with the Master-Slave setup, you will have to enable Binary Logging on the Slave too (Master will always need Binary Logging for Replication).
To do a secure replication, you will have to connect Master-Slave with an SSL connection.
You can find all the information here
If you only want incremental changes, you can take copies of MySQL's binary logs.
It's not really a best practice, though. mysqldump and mysqlhotcopy (only for MyISAM and ARCHIVE tables) should be used for regular backups.
In one of the stackoverflow.com blog casts Jeff and Joel mentioned using SVN to backup databases. This seems like a great idea and my company plans on changing our backup system to this approach.
I have not done my research on the subject as of late but Jeff seemed pretty adamant about having version backups of databases.