MySQL backups for InnoDB, as an Oracle DBA

When I started, I found the section on backups in the manual a bit confusing. I wanted to know, what’s available for InnoDB backups that would correspond to “exports” and “hot backups” for Oracle. I had trouble extracting a clear answer from the manual.

Here’s what I’ve found over time, to the best of my knowledge.

Assuming you don’t want to buy extra software (eg, Zmanda), you can use mysqldump (“export”) and/or LVM (“hot backup”) for your backup needs. (mysqlhotcopy is not for InnoDB.) Replication is nice, too. Here’s a bit about each method.

1. mysqldump (think “export”)
You can get a full backup, even for point-in-time recovery, using mysqldump. Mysqldump is sort of like Oracle’s export. Dumps the database, and you can use it to import it back. It creates a bunch of text commands that you can redirect into a mysql client to do the import. And mysql has a tool for applying changes from binary logs, so if you record the log position for the mysqldump, you can “roll forward” from there if you have your binary logs. (Think archived redo logs.) Here’s what I might recommend:

take your mysqldump:
mysqldump ––routines ––triggers ––single-transaction ––master-data=2 -p databasename > databasename.dmp

––routines and –triggers are to export your stored procedures and triggers
––single-transaction says to get a point-in-time, consistent backup (works for InnoDB)
––master-data=2 says to record the binary log position at the beginning of the mysqldump
-p is because I assume you have a password; you’ll be prompted for it

If you need to recover, you can redirect this file into a mysql client so:
mysql -p < databasename.dmp

If you need point-in-time recovery, look inside the dump file for the binary log position (near the beginning) and use that, and mysqlbin ––start-position to replay the changes in your binary logs from that point. (See the documentation for more on that.)

The drawback is that importing from a dump file can take a long time. We have a database that’s about 55G and it can take 2 days to restore it with mysqldump dump files! (Most of that is because our one biggest table is about 35G with lots of indexing included.)

2. LVM

Install your (linux) O/S with LVM and use it to take snapshots of the database while it’s running. This takes a point-in-time snapshot of your files. And with InnoDB, you don’t have to flush or lock the tables. If you use a snapshot to recover, to InnoDB it’s like your system crashed, and it will recover. (At least, that’s the theory, and it’s worked for me.) Nice, but you have to configure your system correctly to have the space for the LVM snapshots, and taking a big snapshot can slow down your system. (No numbers here, sorry, but it was noticeable to the developers.) So we use a combination of mysqldump and replication instead. (But there is a nice perl script to manage your lvm backups, if you’re interested in one, at http://lenz.homelinux.org/mylvmbackup/)

Three pages I found talk about how to do this. But I think there was another one I can’t find now that was most useful to me. (Please leave a comment if you think you have a better page to reference.) google LVM if it’s new to you, then check out the following:
MySQL Performance Blog – Using LVM for MySQL Backup and Replication setup
Mike Krukenberg’s blog – MySQL Backups using LVM Snapshots
nixcraft – Consistent backup with Linux Logical Volume Manager (LVM) snapshots

3. replication
set up replication and replicate your database to another server. Then you can take file copies whenever you want by shutting down your replication slave and copying all the files somewhere. Very nice! If you need to use your file copy to recover, you’ll have to start up from the copy and promote the database to master. Or, instead, just switch over to your slave and promote it to master, and don’t worry about the file copies. (But file copies are nice to have, too.)

That’s a very quick overview. Hopefully it’ll help someone wondering what their choices are and how each work. You can take it from there…

(update: just found this great article from xaprb (?) on “how to make MySQL replication reliable“.  Recommended reading!  Thanks, xaprb.   Also, just found related “MySQL toolkit“, also from “xaprb” which will also help you make replication reliable, among other things!)

About these ads

17 Responses to “MySQL backups for InnoDB, as an Oracle DBA”

  1. Kolbe Says:

    Don’t forget that there’s a big difference between “backups” and “mirrors”. Replication will apply that errant DROP DATABASE command just as readily as all the INSERTs and UPDATEs you meant to send! So, “file copies” are not just “nice to have”, they’re critical!

  2. Manuel Padilha Says:

    Another option could be DRBD. It’s a distributed remote block device on top of which you can create a filesystem to store your databases. That is something you could probably also use with Oracle, probably even with raw partitions.

    I’ve never tried with Oracle but it works great with MySQL and, as far as I know, it’s the best way to achieve synchronous replication, essential for high availability scenarios.

    Good article. Seen through planet mysql.

  3. Xaprb Says:

    If you use replication, you can’t blithely assume the slave has the same data as the master. If you check, you’re (in my opinion) likely to find some tables are different. Try the table checksum tool I wrote: http://mysqltoolkit.sourceforge.net/

  4. Baron Says:

    you should add –opt and more important: –no-autocommit
    After that your dump will be imported in less than a day.

  5. Ky Cheng Says:

    For quicker mysqldump restore, you can try the followings:

    Add the following options to the mysqldump statement:
    –extended-insert
    Major speed gain + smaller dump file – generate multiple-row insert statements rather than single row insert.
    For example, 100 x 50ms single row insert queries = 5sec vs 1 x 55ms 100 rows insert query (the overhead of multi-row insert is tiny)
    –net_buffer_length=size
    Use in conjunction with extended-insert, mysqldump will read number of rows up to net_buffer_length size for each insert. The more rows you insert in one single statement, the quicker the restoration process. However, ensure your MySQL net_buffer_length variable matches when loading the data back.
    –quick
    Recommend in the reference manual to be use with single-transaction, suppose to speed up the dump process.
    –add-locks
    lock table for faster inserts when restoring the data
    –disable-keys
    disable index creation until after rows are inserted, only relevant for non-unique index for MyISAM tables

  6. ben Says:

    Wow! Thanks for all the great comments. I will try out all of the suggestions, and thanks for the additions and corrections!

    As for some of the mysqldump options, I will test things out. According to the manual, –opt is on by default, and that includes –extended-insert and –add-locks (which corresponds to what I’ve seen in my dump files – I do have multi-row inserts and lock statements). And –quick seems to be to speed up the dump, not the inserts. (But that’s good too!) I will try adding –quick, and also –no-autocommit right away. Thanks again!

  7. Xaprb Says:

    Perhaps also mylvmbackup (by Lenz Grimmer I think) will be useful to you.

  8. Ken Jacobs Says:

    And don’t forget the InnoDB Hot Backup utility, available directly from Innobase. Check out the website: http://www.innodb.com/hot-backup. As that site says:

    InnoDB Hot Backup allows you to backup a running MySQL database, including InnoDB and MyISAM tables, with minimal disruption to operations. In addition, InnoDB Hot Backup supports creating compressed backup files, and performing backups of subsets of InnoDB tables. In conjunction MySQL’s binlog, users can perform point in time recovery.

    There is a presentation available that gives an overview of the features and functions. See: http://www.innodb.com/hot-backup/info.

    Yes, it is not free, but it is quite reasonable at $520 for a 1-year license or $1,320 for a perpetual license. Note that free trials are available.

  9. Jeremy Cole Says:

    Hi,

    I think you confuse two issues:

    * Actually taking a backup, of which cp, rsync, mysqldump, mysqlhotcopy are all methods.

    * Not impacting production, of which replication and LVM (and even combining them) are possible methods.

    You can for instance use replication+mysqldump, replication+shutdown, LVM+rsync, replication+LVM+rsync, and many other combinations. Our preference is generally replication(dual-master)+LVM+rsync, failing over between the masters once a week, and taking backups always on the inactive side.

    Sorry Ken, a closed-source non-free ibbackup is just not that interesting when LVM is so much more flexible.

    Regards,

    Jeremy

  10. ben Says:

    Jeremy – I didn’t *feel* confused, but no, I didn’t explicitly mention that we need to keep our system up for the backups. Otherwise it’s obviously easier.

    Thanks for your clarifications and additions!

    Ben

  11. Log Buffer #60: a Carnival of the Vanities for DBAs · Steve Karam · The Oracle Alchemist Says:

    […] in keeping your MySQL database safe in the case of a disaster (I sure hope so)? Have a look at this post from a MySQL DBA that was once an Oracle […]

  12. Sam’s Updates » Blog Archive » links for 2007-09-13 Says:

    […] MySQL backups for InnoDB, as an Oracle DBA « from Oracle to MySQL (tags: MySQL backup innodb) […]

  13. zmanda Says:

    Quick clarification: While Zmanda Recovery Manager is certainly available as a paid-for enterprise edition (which comes with support, GUI and other goodies.), it’s fully functional community edition cal also be downloaded from here:

    http://www.zmanda.com/download-zrm.php

    Among other things, it collates snapshot based full backups with binary log based incremental backups, to do automated point-in-time recovery.

  14. Idetrorce Says:

    very interesting, but I don’t agree with you
    Idetrorce

  15. dbametrix Says:

    Hi,

    Usefull article. But can you tell me upto what size MySQL is best then Oracle?

  16. Ingvar Says:

    For point-in-time recovery by spooling binlogs, make sure that your data does not have blobs in them. The mysqlbinlog tool generates text based SQL, and feeds that to the server. Blobs (typically images, raw data) breaks that.

    See http://ingvar.blog.linpro.no/2008/10/27/point-in-time-recovery-of-mysql-with-binlog-and-blogs-not-possible/ and http://bugs.mysql.com/bug.php?id=13265

  17. Fourat Says:

    A simple tutorial for a clear way to get LVM hotbackup: http://bit.ly/aFKoEB

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: