« Shibboleth Overview | Main | 5.8 Days Building UMLS Index with DBIx::FullTextSearch »

January 30, 2004

MySQL Replication Up and Running (and I'm Impressed)

I've been wanting to get into MySQL replication for awhile. We've had conversations here for at least a year about having a slave running on a separate machine. Having the database replicated is good for several reasons, the two most important to us:
1) In instance of a hardware problem on master, the application can be pointed at the slave.
2) Aggregating, or indexing data (large, long SQL selects) against the production database can cause interruptions in service, which is rarely a good thing.

We're not quite to the point where we have a dedicated machine for replicating the database (coming summer 2004), but I thought it good to get something set up and let it run for a few months to get a feel for it, so I installed MySQL (4.0.17) on a semi-free box, changed configuration on our production MySQL (3.23.54a) server to enable it as a master, and got a slave up and running. Really wasn't terribly difficult, the MySQL docs are well done (as always).

The most impressive thing to me is the answer to this question: How closely in sync is the slave to the master? I scoured the MySQL docs but could not find a good answer to the question (probably looked right at it, but didn't see it).

I put two terminal windows side by side, with a tail -f on the bin-log for both machines. Although I know in theory there must be a delay between a write on the master and a write on the slave, when tailing the files there is no difference. I'm not forcing flushing to the logs, so there is some incosistent delay between when the update happens in the database and the log file gets written. This would explain why sometimes I see the update on the slave before the master. In most cases, the difference between the master and slave is fractions of a second. I would guess that under heavy load that the updates aren't quite as quick.

I think that's impressive . . . makes me think differently about how a MySQL slave could be used. Maybe I'm easy to impress, haven't spent much time around the "other" databases (other than a short, employer-mandated, stint with Oracle).

Posted by mike at January 30, 2004 2:47 PM