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!)