[lug] mysqldump versus snapshot versus..

[Sean Reifschneider]

On Thu, Aug 02, 2007 at 10:19:13AM -0600, Jason Vallery wrote:
>"slave" instance of MySQL in sync with the "master" instance.  Useful
>for providing hot spares or in some cases load balancing your select
>queries across multiple servers (assuming your data doesn't change

Yes, but you can also just set up the binary log files and *NOT* do the
replication, and replay the log files in the event of database corruption.
Of course, that means that when you want to recover you have to take the
time that is required to replay all the changes made to your database.

As far as using the replication server for selects, you have to be awfully
careful with that.  For one thing, you may not get that much additional
performance by doing that.  See the discussions related to memcached for
what livejournal saw when they tried to do that.  Another issue is that you
need to make SURE no updates are happening to those read-only machines, or
that you've set up things so that updates to those machines don't break the
database.

I say this because we're currently working with a client who over the last
2 weeks has had their application mysteriously start writing to the
read-only slaves.  Big, hairy, mess.

But, yeah, a replication slave is a good place to run backups against.
There was recently a blog entry about doing this that I saw on one of the
blogs I read, but I can't seem to locate it now.

Sean
-- 
 Language is the most important .. uh..  I think you know what I'm trying
 to say.  -- Steve Martin
Sean Reifschneider, Member of Technical Staff <jafo at tummy.com>
tummy.com, ltd. - Linux Consulting since 1995: Ask me about High Availability