Comments on: 10 things you need to know about backup solutions for MySQL

By: Donald V Hirst

Donald V Hirst — Sun, 03 Jul 2016 20:12:34 +0000

In reply to BERNIMONT Thomas. As of 3 July 2016, the above link is broken; it leads to a null page.

By: BERNIMONT Thomas

BERNIMONT Thomas — Thu, 23 Jul 2015 05:50:33 +0000

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 !

By: Baron Schwartz

Baron Schwartz — Fri, 18 Dec 2009 12:54:37 +0000

Start here: http://catb.org/~esr/faqs/smart-questions.html

By: soni

soni — Fri, 18 Dec 2009 09:12:10 +0000

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

By: soni

soni — Fri, 18 Dec 2009 09:11:10 +0000

i have a problem and its master database cannot produce backup. how do i go about it? thank you

By: Maurizio Di Ianni

Maurizio Di Ianni — Tue, 17 Mar 2009 07:36:11 +0000

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

By: Baron Schwartz

Baron Schwartz — Tue, 10 Mar 2009 15:25:04 +0000

/me unsubscribes from spammer list that aforesaid backup provider subscribed him to after downloading the whitepaper. 🙁

By: rgmarcha

rgmarcha — Mon, 09 Mar 2009 16:57:06 +0000

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.

By: Eric Brunson

Eric Brunson — Mon, 09 Mar 2009 16:40:11 +0000

I’m working on a backup strategy for my company, so this set of questions is both timely and appreciated.

By: Baron Schwartz

Baron Schwartz — Thu, 05 Mar 2009 15:18:23 +0000

Great point. But 10 is such a nice number for linkbait headlines! Heh.

By: Jens-Petter Salvesen

Jens-Petter Salvesen — Thu, 05 Mar 2009 10:17:13 +0000

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!

By: Baron Schwartz

Baron Schwartz — Thu, 05 Mar 2009 00:35:45 +0000

Ronald, I haven’t evaluated the backup functionality in MySQL 6 well enough to have an informed opinion about it, sorry 🙁

By: William Newton

William Newton — Wed, 04 Mar 2009 16:05:21 +0000

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.

By: Ronald Bradford

Ronald Bradford — Wed, 04 Mar 2009 15:24:10 +0000

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.

By: Baron Schwartz

Baron Schwartz — Wed, 04 Mar 2009 13:47:49 +0000

With InnoDB, yes there are. You should read the MySQL manual for details.

By: http://moonbingbing.blogspot.com/

http://moonbingbing.blogspot.com/ — Wed, 04 Mar 2009 13:45:44 +0000

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

By: Baron Schwartz

Baron Schwartz — Wed, 04 Mar 2009 12:45:17 +0000

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.

By: Jay

Jay — Wed, 04 Mar 2009 10:51:58 +0000

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.

By: Shlomi Noach

Shlomi Noach — Wed, 04 Mar 2009 10:13:14 +0000

@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

By: Arjen Lentz

Arjen Lentz — Wed, 04 Mar 2009 08:01:25 +0000

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