Bi-directional replication for the same MySQL table

Question

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

1. Is this even possible to setup bi-directional replication, so that the AppA, AppB view the same data at any point in time.

2. 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.

Answer 1

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.

Answer 2

This answer assumes you are running both databases/schemas on the same mysqld. If you are running on different mysqlds please see Hexist's answer

Off course there is a chance of collision: who's to say id 7 isn't created in dbA and BEFORE the replication happened a different id 7 is created in dbB? Especially when using auto increment

You could try a number of things, amongst:

• If your id is a signed int the max value is 2147483647. In dbB try setting the initial auto increment to 1073741824 (set it half way the max). That way all ids that are created in dbB are in a higher id zone and collision is less likely.
• Try guids in stead of auto increment ids ( How should I store GUID in MySQL tables? )

If both dbA and dbB are on the same mysql server, you can simply achieve 'replication' by running these queries on a schedule or with a trigger (on insert)

Insert into dbA.tableA values (select b.* from dbB.tableA b left join dbA.tableA a on b.id = a.id where a.id is null)
Insert into dbB.tableA values (select a.* from dbA.tableA a left join dbB.tableA b on a.id = b.id where b.id is null)

edit : Sorry, You cannot use auto-increment. The auto increment will always be based on the highest id already existing. So you cant force the auto-increment on 1 machine to stay low after it got a higher id.

Answer 3

I would suggest to give store/retrieve control to a lets say AppC, which would have access to both dbA and dbB. Also AppC would create the primary key.