3

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.
Rpj
  • 131
  • 1

1 Answers1

1

I'm assuming these are different instances of MySQL (most likely on different servers).

You can set up Master-master replication, where dbA is a master of dbB and vice versa.

Then, to avoid auto-increment collisions, set the following in my.cnf on dbA:

auto_increment_increment=2
auto_increment_offset=1

And the following on dbB:

auto_increment_increment=2
auto_increment_offset=2
Colin 't Hart
  • 8,747
  • 15
  • 33
  • 40
Valerie Parham-Thompson
  • 676
  • 5
  • 8
  • @rpj Please note that the increment config will not solve collisions in the case of a deletes/updates, and it can lead to replication breaking and/or having inconsistent data. – jynus Jul 06 '14 at 16:35
  • @jynus Do you mean deletes/updates on primary key only? – Valerie Parham-Thompson Jul 10 '14 at 10:58
  • No, simultaneous deletes and updates on the same rows (no matter the field) will break replication as there is no procedure for conflict resolution/rollbacking. Check these slides (specially page 10 and 11): http://www.percona.com/live/mysql-conference-2013/sites/default/files/slides/Hazards%20of%20Multi-writing.pdf – jynus Jul 10 '14 at 16:18