Backup strategy for frequently changing database

I'm curious about how others are backing up their databases,
especially when it contains frequently changing information, such as a
list of orders and line items from an ecommerce site.

Right now, I'm backing up my mysql database every night, but of course
we can have a few hundred orders that occur during the daytime, so if
something catastrophic were to happen, we'd lose the information from
all the orders that had been placed since the last backup occurred.
But backing up every time a transaction happens is not feasible.

Is this where a read-only mysql replication slave comes in? (read only
in the sense that updates can only happen from the master database)
I'm not much of a database admin, so I'm kind of clueless about this
stuff, was hoping someone could outline some of the different
strategies. Thanks,

Mike

I'm curious about how others are backing up their databases,
especially when it contains frequently changing information, such as a
list of orders and line items from an ecommerce site.

Right now, I'm backing up my mysql database every night, but of course
we can have a few hundred orders that occur during the daytime, so if
something catastrophic were to happen, we'd lose the information from
all the orders that had been placed since the last backup occurred.
But backing up every time a transaction happens is not feasible.

Is this where a read-only mysql replication slave comes in? (read only
in the sense that updates can only happen from the master database)
I'm not much of a database admin, so I'm kind of clueless about this
stuff, was hoping someone could outline some of the different
strategies. Thanks,

Yes. You can configure it to only mirror certain tables if that would help, but this is what you want. Preferably somewhere else on the net, but with low latency. Somewhere else in the data center would probably be enough -- if you figure that if the data center goes belly up the least of your worries is the few hundred orders... If not, put it somewhere else on the planet :slight_smile:

MySQL replication is pretty easy to setup... check the online docs. There's also an OReilly (I think) book on mysql that coveres various strategies.

-philip

Thanks Philip, I figured mysql replication was what I needed, just
wanted to get some confirmation on it.

Mike

Philip Hallstrom wrote:

MySQL replication is pretty easy to setup... check the online docs.
There's also an OReilly (I think) book on mysql that coveres various
strategies.

-philip

Slightly off topic I know, but I'd be interested to hear anyones
comments on how they feel that mysql replication [or postgresql for that
matter] can compare with oracle's dataguard or IBM's DB2 HADR?

I've had two types of db issues:

1. Hardware died: needed to get another system up & running with
restored db.
2. Data polluted: needed to restore to a certain point, fix pollution,
then run all transactions from a log file.

I can see how mysql replication will address issue 1, can it do issue 2?

thanks,
Matt.

http://www.tickex.com
The ticket search engine for concert, sports, and theatre tickets.