We have a innodb mysql database so we are lost with lots of different mysql backup strategies. Some talk about mysqldump some talk about third part tools. Some of the tables we also plan to run partitions. Any good tool to help for this task?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Today there's hardly any discussion (as I see it, of course), with regard to InnoDB backup. Xtrabackup by Percona is a widespread, feature rich & stable solution which offers:
Xtrabackup derives from InnoDB Hot Backup, nowadays called MySQL Enterprise Backup. There's additional wrapping scripts which allow for the extra featureset.
Smaller databases may benefit from the common
I use LVM snapshots quite a lot. The great advantage is that these are DB ignorant, so no DB-related bugs affect this type of backup. The great disadvantage is an overly increased I/O. Check out mylvmbackup.
If you have ZFS or another file system which supports snapshots, that may be a great solution. If you have a storage device which supports snapshots, that's great too. All snapshots are DB ignorant, though you often want to script this a-la
I am sort of an old school DBA, so hear me out anyway...
My Past Articles in the DBA StackExchange on Backups for MySQL
In my earlier posts, I recommend doing mysqldumps in parallel as well as one single file. I even suggest trying out XtraBackup. I do not want to limit you to mysqldump. Some databases are so large that LVM Snapshots are the only possible solution.
Important things to think about when picking a backup strategy:
This is how I would tackle this in order of priorities. and that is then end of my objective answer :)
Now, for the subjective part based on my experience:)
For a large portion of DB setups these days, using mysqldump as the ONLY backup solution is not scalable. It is slow, creates HUGE files, importing mysqldumps takes forever, and it is tricky making mysqldump give you a true 'point in time' recovery with data consistent across the board. I use mysqldump to backup v small tables (less than 200MB each) that are also super critical and more prone to end user foul ups (like user table or user_profile). The common use case when I go back to these is when someone asks "When was this user changed? and what was the value of setting X for them" so it is not really a data is lost rather than a data is messed up situation. For true 'world on fire' backups, I use xtrabackup by Percona. It is free, open sourced, in very active development, and very easy to automate.