rsnapshot is a filesystem snapshot utility for making backups of local and remote systems, based on rsync. Rather than just doing a complete copy every time, it uses hardlinks to create incrementals (which are from a local perspective a full backup also). You can specify how long to keep old backups, and all the other usual jazz. You’d generally have it connect over ssh. You’ll want/need to run it on a filesystem that supports hardlinks, so that precludes NTFS.
In the context of MySQL, you can’t just do a filesystem copy of your MySQL data/logs, that would be inconsistent and broken. (amazingly, I still see people insisting/arguing on this – but heck it’s your business/data to gamble with, right?)
Anyway, if you do a local mysqldump also, or for instance use XtraBackup to take a binary backup of your InnoDB tablespace/logs, then rsnapshot can be used to automate the transfer of those files to a different geographical location.
Two extra things you need to do:
- Regularly test your backups. They can fail, and that can be fatal. For XtraBackup, run the prepare command and essentially start up a MySQL instance on it to make sure it’s all happy. Havint this already done also saves time if you need to restore.
- For restore time, you need to include the time needed to transfer files back to the target server.
Yes, rsnapshot is neat, I just find it a bit difficult to configure/set up. There is a similar tool named “rsnap”, which uses a similar approach:
http://daniel.lorch.cc/projects/rsnap/
FWIW, mylvmbackup supports rsnap as well 🙂
Sorry for arguing again, but:
> In the context of MySQL, you can’t just do a filesystem copy of your MySQL data/logs, that would be inconsistent and broken.
Even if you stop all write activity and issue “flush tables”? Doesn’t it ensure the database integrity on disk?
> Even if you stop all write activity and issue “flush tables”?
> Doesn’t it ensure the database integrity on disk?
Only if you are using MyISAM tables only. If you use InnoDB and MyISAM in parallel, your backup will be inconsistent.