Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

Important things to think about when picking a backup strategy:

  • How long is an acceptable amount of downtime while having to recover data from a backup
  • Is recovery going to always mean All the database? or is there a use case for a more frequent backup of specific, more critical tables?
  • What acceptable load on the server making the backup is accepted? meaning, are you gonna have a replica dedicated to backups? or are you tight on money as an org and will have to run this against your only slave (I HOPE you don't say there only one and only DB server :) )
  • Is paying for a backup solution an option? or free is more preferred?

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.

share|improve this answer
    
@technie we have already setup a slave now is in place ready and is working accordingly.So based on that what is your advice backup from the slave is it? Should we install xtrabackup by Percona there. –  newbie14 Aug 16 '12 at 18:40
    
i insteall percona xtrabackup on all my machines. You need it to prepare a snapshot made by xtrabackup anyway. I usually do a daily full snapshot with Xtrabackup and an hourly incremental snapshot.You could look into code.google.com/p/xtrabackup-manager for how to automate this –  TechieGurl Aug 27 '12 at 21:25
    
@Techigurl it say it is still in beta version according to the wiki guide page. So is it ok to use then? –  newbie14 Sep 1 '12 at 5:31
    
yes xtrabackup manager is in beta so use it at your own risk. In our shop we went for writing a cron based manager script internally for better or worse. –  TechieGurl Sep 4 '12 at 18:55
    
@technigurl so any other option for newbies like me to do have the backups done on regular basis? –  newbie14 Sep 5 '12 at 17:05
show 2 more comments

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:

  • Hot backups (no locks placed on database)
  • Incremental backups (done by backing up pages as opposed to statements)
  • I/O throttling (allowing to limit backup I/O as as to alleviate the load from the machine/disks)
  • Backup is consistent at time of end of backup (as opposed to time when backup started).

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 mysqldump. It really depends on your database size and traffic. mysqldump, event with --single-transaction, places a high load on the DB server, and makes for accumulating locks. It is not as hot as you might think (see this post of mine regarding backup temperatures).

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 mylvmbackup so as to catch the point in time (DB-wise) for which backup is done.

share|improve this answer
    
so are you suggesting for me to explore xtracbackup is it? I guess mysqldump is quite slow is it? So how about LVM is even better for huge data is it? –  newbie14 Aug 17 '12 at 17:55
    
Yes yes and yes. You may also check out MyDumper which is a parallel application of mysqldump. –  Shlomi Noach Aug 18 '12 at 17:42
    
Between mydumper and xtrabackup which will you suggest for me? So I keep taking the snapshots manually or let a backup engine to run on its own? –  newbie14 Aug 19 '12 at 6:16
    
Xtrabackup makes for a binary backup. I suggest you start with this. Next you can try snapshots. If you want logical backup try out MyDumper. I have no knowledge about your filesystem, operating system, disks etc. so I can't possibly recommend you which snapshot to take and how... –  Shlomi Noach Aug 19 '12 at 8:01
    
I would like to know the different in binary backup and logical.Is it that binary will be those binary log? I am using centos and fully using innodb and set innodb_file_per_table –  newbie14 Aug 19 '12 at 10:28
show 1 more comment

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.

share|improve this answer
    
how to get LVM snapshots is it via some softwares? Give me some time to digest the links you gave looks full of knowledge thx. –  newbie14 Aug 17 '12 at 17:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.