Bi-directional replication for the same MySQL table
mysql, replication
Solution
Yes, master-master replication is pretty well supported in mysql, so you can do what you're looking to do.
You'll want to have dbA and dbB have different auto_increment_offsets, and to set the auto_increment_increment to greater than the default of 1 though. See
http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html
In summary, you'll learn you'll want to add to your respective my.cnf files something like:
dbA:
[mysqld]
server-id = 1
auto_increment_increment = 10
auto_increment_offset = 1
dbB:
[mysqld]
server-id = 2
auto_increment_increment = 10
auto_increment_offset = 2
then when server A inserts values it will use values like 1, 11, 21, 31 for it's primary key values.. server B will use 2, 12, 22, 32, etc.. that way they'll never conflict.
Obviously you can use lower values for your auto_increment_increment, for example, 2, but depending on how you want to grow your cluster later you may want to give yourself some room.
Problem
AppA stores/retrieves data from `dbA.tableA`. AppB stores/retrieves data from `dbB.tableA`. tableA definition is the same across these databases. To start with `dbB.tableA` was copied from `dbA.tableA` (assuming both had 5 rows). `row6` was created by AppA (say primary key 6) `row7` was created by AppB (say primary key 7). I would like `row7` to be copied to `dbA.tableA` and `row6` to `dbB.tableA` Is this even possible to setup bi-directional replication, so that the AppA, AppB view the same data at any point in time. If the primary key is an auto-increment, would it be possible to maintain integrity of data or is there a possibility that there would be collisions on the primary key.