A well thought backup saved my skin last Saturday.
It's a simple setup: many copies. Using MySQL replication, the master is for writes.
Four slaves for reads. One slave for backups only (*). In a different server room. In a different building. (**)
The backup slave has a cron job, which stops the slave, makes a dump, removes the oldest one, and resumes replication.
The same job works hourly (keeps 30 dumps), daily (keeps 7 dumps), and weekly (keeps 8 dumps).
The disaster occurred yesterday. A colleague who was working too much (***) made a destructive query on the wrong server. He thought he was using the development server, but it turned out to be the master. Fortunately, nobody else was working on a Saturday, so there weren't any changes, besides his. I zeroed the database on the master and reloaded the latest hourly dump. No suffering. No bad blood, and my morning coffee is paid till the end of the quarter!
(*) Replication is not a backup.
In this particular case, if you rely only on replication, your data is gone forever.
You have five copies, all of them FUBAR.
(**) Keep the backup slave in the same room, and a flooding will make both master and slave useless at once.
(***) Seriously, Mel! You should have been holding a beer in a pub downtown instead of being in the office.
10 comments:
That's the way to go.
By the way, take a look at "rsnapshot" which can efficiently (using rsync and hard links) do daily/weekly/monthly backups. It also has all the hooks to run commands pre/post the backup, to deal with MySQL, etc.
This assumes that the slave has the same data as the master, which is often not the case in practice.
But fortunately there's Maatkit :-)
Would you mind posting the portion of your script which takes the slave down, and auto restarts it at the right position?
That would help me so much to see a working example.
Mike,
you don't need to worry about the position.
The backup script does
call_sql('SLAVE STOP');
# dump database using mysqldump
call_sql('SLAVE START');
Replication is asynchronous. When you restart the slave, it picks up from the last position, without any manual intervention from the DBA.
Amelia
You could wrap maatkit's (great toolset) parallel-dump around 'mysqladmin stop-slave'.
It also creates a file containing bin log data too - handy for ref, should you need to change the master log pos on slaves.
Having slaves for backups is only any use if you're monitoring them for replication errors AND checking that they're really in sync with the master (e.g. with maatkit's table-checksum).
If you're not doing both of those things, your slaves are almost guaranteed to either fail and go unnoticed, or become gradually further and further out of sync with the master (again unnoticed).
Slaves can get out of sync for various reasons - either hardware (power, duff ram, etc, particularly if you're using MyISAM) or software (Master issues update queries which don't replicate correctly). Both have happened to us in production but don't happen often (and not always to all slaves, so we have good backups)
Mark
Thanks for everyone's concern. There is no need to assume that I am not monitoring my slaves. I am, actually, although I am not using Maatkit for it. I have a customized monitor that has been keeping an eye on my replicas for almost three years now. There was no Maatkit available then, and so I have been using something less elegant but effective.
My slaves, especially the backup slave, are in sync.
Amelia
benefit, but anyone else's too.
It's good to hear that it's being monitored properly. Out of interest, how often do they go out of sync, and what is the typical cause?
Amelia, welcome to Planet MYSQL. I am excited to see you writing about Sun. Is your shop primarily a Sun shop, Linux shop or do you use both?
And you can use M**tkit. Lol, I am just kidding :)
Frank
Frank,
Thanks. I am glad to be here.
I am mostly running a Linux shop.
No need to change right now. Just looking around. If Solaris turns out to be good I may reconsider. Have you seen my request for a Solaris equivalent to apt?
Amelia
Post a Comment