Nov 9, 2009

Should MySQL backup be equal to system downtime?

I had a short conversation with my mate, Romi Kuntsman regarding a common issue in current systems: the database backup.
Since database systems are very active, the database backup process takes relatively long time and the whole system performance is going down (or in some cases the whole system stops responding and going down...)

Should I backup or Should I don't?
Well, first of all, keep backup your system. You never know when your system will corrupt due to hardware failure, when a hacker will decide to "check" your system, or when the regulator will visit your offices.

So how should I keep my system responding to users?
Well... lets take a look at storage systems: when you have a large storage machine (SAN) you usually do not backup the primary site machine, but rather backup the secondary storage machine by splitting it from the main site (or by using a snapshot of the secondary site). This way your primary machine keeps serving clients with interference, while the secondary machine is taking care of the backup. When backup is finished, the sync between the machines is being restored.

So it works for storage systems, how can it work for databases?
Well lets implement a similar design to your database system:
  1. Install another MySQL instance
  2. Configure this instance to be a slave of your master database
  3. Schedule a job to bring down the sync, backup the slave and bring back the sync

And what about the hot backup?
Well, if you still want to maintain high availability during backup (and you should), implement a two slaves configuration, where the first slave is used for high availability and the second for backup.

Keep Performing,
Moshe Kaplan. RockeTier. The Performance Experts.

2 comments:

Omer Gertel said...

A hot backup is not a replacement to periodical snapshot. If a corruption creeps into the data, it will quickly be replicated into the slaves.

I think a periodical snapshot is still a good idea.

Moshe Kaplan said...

Hi Omer,

You are absolutely right.
Hot backup is not a replacement for cold backup.
However, hot backup can be used in order to generate a cold backup without the performance side effects of backup the master instance

Moshe

ShareThis

Intense Debate Comments

Ratings and Recommendations