Are you using someone else’s backup solution for your MySQL data? Do you care a lot about your data? Are you sure you’re getting a reliable, recoverable backup that’ll work for your business and your application, and won’t impact your critical processes while it runs? Here are ten questions you need to be able to answer:
- Does the backup require shutting down MySQL? If not, what is the impact on the running server? Blocking, I/O load, cache pollution, etc?
- What technique is used for the MySQL backup? Is it mysqldump or a custom product that does something similar? Is it a filesystem copy?
- Does the backup system understand that you cannot back up InnoDB by simply copying its files?
- Does the backup use FLUSH TABLES, LOCK TABLES, or FLUSH TABLES WITH READ LOCK? These all interrupt processing.
- What other effects are there on MySQL? I’ve seen systems that do a RESET MASTER, which immediately breaks replication. Are there any FLUSH commands at all, like FLUSH LOGS?
- How does the system guarantee that you can perform point-in-time recovery?
- How does the system guarantee consistency with the binary log, InnoDB logs, and replication?
- Can you use the system to set up new MySQL replication slaves? How?
- Does the system verify that the backup is restorable, e.g. does it run InnoDB recovery before declaring success?
- Does anyone stand behind it with support, and guarantee working, recoverable backups? How strong is the legal guarantee of this and how much insurance do they have?
There are a lot of other things a backup solution needs to provide, but these are some of the things I’d require specifically for MySQL, or more so for MySQL than for other types of backups.
Where is this post coming from? Well, I’ve had a couple of frustrating encounters with vendors who sell backup products with some kind of agent for MySQL database backup. I am almost never able to get any significant technical detail on how their backups are implemented, and often I can’t even get the most basic information such as “dump or file copy.” Sometimes they tell me to register and download a whitepaper that just has a bullet point “Support for MySQL Databases”. Or “I have to transfer you to the salesperson who covers your region” and I never get to talk to anyone.
Some of them even use the word “proprietary” as if it’s a good thing. In reality, a proprietary, secret backup black-box is unacceptable. MySQL backups are complex and difficult to get right. It is very application-dependent and technology-dependent. Unless I know exactly how it works, I cannot tell the client whether it’ll work for them at all.
So I offer the above list as a way to evaluate your backup provider’s suitability for enterprise-ready backup uses. If you can’t answer these questions, it’s a system that you can’t evaluate seriously, in my opinion.
Being able to quantify the costs of your data being unavailable can help offset the costs of backup solutions in many cases as well. Everybody knows that not having the data online is “bad”, but having a dollar amount with that data _and_ it’s availability is really powerful information.
Great list! I enjoy reading your blog, it’s very informative.
Based on the list you’ve created, what would you suggest are good out-of-the-box backup solutions for medium sized MySQL applications? Currently we just dump the sql to a file using a chron job (we’re currently in development), and are looking for a more robust solution for alpha. Of course, we hope to get paid consultation from an expert such as yourself when time comes, but we’re on a *very* tight budget and your suggestion would be useful. Thanks 🙂
I’d look at mylvmbackup. It’s about the most useful broad-brush statement I can make 🙂
Baron, I’m particularly interested in #9 on your list. Could you link me to a place that explains more about how this works? I was not aware that there was a way to automatically verify the backup using InnoDB crash recovery. Or perhaps I am misunderstanding the point.
Karan, I have an excellent backup solution that does not require LVM. It uses Maatkit for taking the backup quickly and Amazon S3 for storage. It makes it easy to restore single tables or even a whole database. I will be releasing it at Percona’s performance conference, but feel free to reach me at myfirstname at squidoo.com if you’d like a copy. If you’re looking for an even more lightweight solution try this: http://jamescrisp.org/2007/12/09/automating-mysql-database-backups/
“Does the backup system understand that you cannot back up InnoDB by simply copying its files?”
Please could anyone confirm something what we’re doing is OK:
LVM, XFS filesystem with InnoDB. We simply create an LVM snapshot and then do a file copy.
Sure, it means that InnoDB has to recover itself from a half-way state if the backup is restored, but it’s ACID so that’s not a problem, right?
A further question if anyone has any insight please:
LVM snapshots have been indicated as severely affecting InnoDB performance – however someone on a thread in this blog suggested that SSDs alleviate most of the performance hit the snapshot causes (as long as it’s a good SSD like the Intel X25-E with high random write performance).
Thanks in advance for any insight!
Yea, the “backup agent” market makes themselves appear quite dodgy. Which is ironic, given that they’re trying to sell safety!
This makes me wonder if they’re just adding MySQL support as a “tickbox item” without having a *proper* implementation (in whatever form suitable).
@2 Karen –
mysql-zrm is also a very popular tool (comes in community and commercial versions). It utilizes mysqldump/raw copy/lvm etc., does full+incremental backups, and more. I’ve had good experience with it (though, to be completely fair, I haven’t had the need to recover a production system backed up with mysql-zrm as yet, only test drives).
Regards
Ops, I did it again. Can someone please clarify;
“Does the backup system understand that you cannot back up InnoDB by simply copying its files?”
We stop the service, copy all files in /data and then start the service. So far it (seems) to work, but I admit the only tests we’ve performed is to take the copy to another server and started it there, with some simple data consistency checks.
So please explain what inconsistencies or errors I should expect to get with this process.
You can take InnoDB backups by copying the files, as long as a) MySQL is shut down, OR b) you’ve taken a filesystem snapshot with something like LVM or a SAN snapshot.
The bad backup software just copies the files while the server is running (which won’t give a usable backup for ANY storage engine!) or does a FLUSH TABLES WITH READ LOCK and copies the files, because whoever wrote the software didn’t read that FLUSH TABLES WITH READ LOCK doesn’t stop InnoDB’s background I/O.
Gil, the way to test that you have a recoverable backup is to start MySQL on the backup, and let InnoDB’s recovery process run. If it completes and the error log looks good, and you can connect to the resulting mysqld instance and execute some selected InnoDB queries with the expected results, then you have a pretty good indication of a sound backup.
Baron,I wanna know why ‘FLUSH TABLES WITH READ LOCK and copies the files ‘ is bad for backup?
are there data files rewrite after executed ‘FLUSH TABLES WITH READ LOCK ‘?
thx
With InnoDB, yes there are. You should read the MySQL manual for details.
Baron,
Do you consider the online backup solution proposed in MySQL 6 to be capable of addressing these points?
It’s amazing that online backup has been the single greatest request from customers and users for many years, yet MySQL (company) has failed to deliver a working solution.
MySQL lacks a single unbreakable backup solution (for example in comparison to Oracle), and I see no solution close to solving this long standing fundamental problem in the near future.
I don’t think I’ve ever learned anything by reading a whitepaper. And if you have to register first before reading it? Even more worthless. I hope someone at Mysql takes not of this fact. They’ve got white pages all over the place all of which require a registration. If you have valuable information, just present it in an easy to use format: like a web page.
Ronald, I haven’t evaluated the backup functionality in MySQL 6 well enough to have an informed opinion about it, sorry 🙁
There is a big item that’s missing: Recovery time. It’s nearly useless to have a consistent backup if it takes two days to restore and you can only afford two hours of downtime.. 🙂
Otherwise a good, useful article as always!
Great point. But 10 is such a nice number for linkbait headlines! Heh.
I’m working on a backup strategy for my company, so this set of questions is both timely and appreciated.
Baron, I’m a customer of R1Soft (www.r1soft.com), and so far my backups are good enough that I use them regularly to spawn new slaves.
Their backup solution works tracking modified blocks in a filesystem (I have used only ext3) with a kernel driver. So, after a FLUSH TABLES WITH READ LOCK, they take a snapshot of the state of the blocks in the filesystem, and those are the blocks’ versions put into the backup. So, it seems it should be equivalent to a LVM snapshot.
The speed of the restore, though fast, is not optimal, but I think it’s because the CPU is the bottleneck decompressing the backup data. I haven’t tested a mysql restore from a non-compressed backup to compare.
/me unsubscribes from spammer list that aforesaid backup provider subscribed him to after downloading the whitepaper. 🙁
A great thing would be that you can transform a mysqldump file with extended inserts to a load data outfile format file. If there is someone who knows something about it… I tell you this taking care about the 10 reasons explained before and thinking on a server with huge databases, with millions of rows per table (100GB).
i have a problem and its master database cannot produce backup. how do i go about it? thank you
i have a problem and its master database cannot produce backup. this is particular for flex language explorer. how do i go about it? thank you
Start here: http://catb.org/~esr/faqs/smart-questions.html
Hello dear friends,
http://www.abmysql.com is a free webservice to run automatic mysql backup and restore your backup too !
Have a nice day and enjoy your databases !
As of 3 July 2016, the above link is broken; it leads to a null page.