On 26/05/2005 6:40 p.m., Cecil Brand wrote:
Hi,
I was wandering if anyone know of a stable and reliabile way to sync 2
live mysql databases, both ways.
I know I can use a master and slave, and yes have setup a few without
any problem, but as all
of us know this is just a one way downstream sync. I need to sync both
ways and basicly live, the every
5 or 10 min cronjob sync with a perl/php/c++ app just won't cut it.
Unfortunately, the simple answer is you can't.
While it is possible to set up multi-master replication with MySQL, this
is not useful for the vast majority of applications. If you are sending
write queries to both masters then you have the possibility of the two
servers becoming out of sync. This is particularly true with
auto_increment primary keys - if you have a table and you send one
insert statement to each server, both at the same time, then they might
both assign the same auto_increment number to each row, and then they
will both be forced to ignore the replicated inserts they receive from
each other - screwing your table.
Your options are to use MySQL Cluster (not an option for a lot of people
as your entire database has to be stored in RAM), or if you're just
after high availability, set up one-way replication with dynamic master
failover (which I am currently trying to figure out how to do myself).
-Simon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]