Whats the best mySQL backup solution out there?

Portal Home > Knowledgebase > Articles Database > Whats the best mySQL backup solution out there?

Posted by iTom, 08-16-2009, 04:35 PM
Hi everyone. Ok here goes, I am looking for peoples experience with the best way to backup databases that are always changing. We have a client that has an ever changing database at the moment we are looking at 2-5 read/writes a second. needless to say its a pretty important aspect to their site/business and we are looking for a backup solution. Requrements: Real time backup - we need it to be kept up-to date all day long 24/7 for minimal data loss. Intelligent - There have been times when one of the tables has been dropped entirely or gone from 4,000 records to 2, now we need to make sure that the replication software doesn't copy this, unless its a legit drop. eMail alearts would be a plus, if it noticed a large change in the database etc... Thinking stuff like email i can scritp myself, need more help with the first two. Got a few ideas but thought will see what everyone else has to say first. Thanks for your time!
Posted by DJMizt73, 08-16-2009, 06:42 PM
Just curious - you've looked at MySQL replication right? (slave or multiple slaves)
Posted by iTom, 08-17-2009, 02:53 AM
Yes but if someone was to issue the "delete from table where name=*" I take it that would also be replicated? This is the kind of stuff we need to protect from.
Posted by eth10, 08-17-2009, 03:06 AM
mysqladmin databasename > database.sql 24 hours may not be possible. You can write a script to do this
Posted by larry2148, 08-17-2009, 03:13 AM
I think with any sort of live backup solution you'll find the problem of it removing the data on the backup system also. How long does it take to make a manual backup? Eth10's suggestion would probably be a good one, you could script in a date stamp so it would keep a history too.
Posted by iTom, 08-17-2009, 03:25 AM
Yeh at the moment I have a script doing that every 15min thought the day, still you have the chance of 15min of data lost. Wanted something a bit better, if it exists.
Posted by larry2148, 08-17-2009, 03:29 AM
I guess if you had it replicated that would take care of the problem of a down server, and the only time you would then lose data is if the tables actually dropped and you had to restore from the last backup. I've looked into mysql clustering etc in the past and it was quite involved though, it might not be worth it depending on how much they are willing to pay.
Posted by tim2718281, 08-17-2009, 04:45 AM
You should hire an experienced DBA to develop a custom restore system for your application. Anything else will fail.
Posted by StevenG, 08-17-2009, 04:53 AM
I'd run a replica server, and do the backups on that one as often as you need, space permits. Can't see why you'd need an experienced DBA for that specific purpose, unless you have budget for it.. a jack of all trades admin should be able to set that up easily.
Posted by Thomas Manning, 08-17-2009, 07:10 AM
You can better use R1soft for this. Please check the link provided below. http://www.r1soft.com/
Posted by StevenG, 08-17-2009, 07:13 AM
Or just use LVM on the replica or the master or both, then you can get snapshot backups in a blink, that only use up changes in space on the file system. R1soft has some questionable issues still or have they all been fixed? - Never used it.
Posted by iTom, 08-17-2009, 07:42 AM
Yeh but the problem I am having is twice before something wipped out the database using the delete command, i need backups to ignore this command when used to wipe the entire table. Looks like shell scripting time
Posted by StevenG, 08-17-2009, 08:06 AM
You could use mysql proxy for database connections and rewrite deletes.. although I haven't tested this works, I've been wanting to find a use for mysql proxy to try it. Can't you also be a bit more selective with mysql grants on the tables? Snapshot backups could be done a lot more regular, than conventional backups. Anyway, I'm sure you have enough info to put something into place.
Posted by TQ Mark, 08-17-2009, 10:50 AM
Check out MySQL binary logs which is one way to get better incremental backups apart from doing full DB dumps http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

Add to Favourites Print this Article