Here's the problem - I have two large boxes who will share the problem
of keeping my database up and responsive. The priority is to maximize
uptime, with boosting performance being a distant second (we currently
have no db performance issues even on one box running MySQL).
I've read through several messages on the list regarding replication, as
well as the online documentation. I have some ideas of how to proceed,
but wanted to get the advice of the replication veterans on the list.
I currently have one box set up as the master, and the new box as a
slave. The slave is correctly receiving all updates from the master.
The question is what to do next.
I believe I can:
1. Split reads between both, and keep writes on the "master",
and use a monitoring process on the slave to detect the master going
down and move all writes to itself - probably at the application layer.
2. Set up the two in a circular relationship so updates on
either will be replicated to the other. The problem with this approach
is handling the problem of auto_increment fields, of which we have
_plenty_. I'd probably have to drop all auto_inc fields and handle the
id generation in the application layer.
Re: #2 above, has anyone considered allowing another type of field -
akin to auto_increment, but with a hook which allows calling an external
module which returns the value for the field? This would allow me to
cleanly implement a circular replication setup without having to change
the application...
What else can I do? Can I get some suggestions?
Thanks!
