7

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.

Sam R.
  • 16,027
  • 12
  • 69
  • 122
Rpj
  • 5,348
  • 16
  • 62
  • 122
  • Primary keys in distinct setups sound like calling for trouble. As are a number of other consistency requirements. So I'd surprised if this could be made to work reasonably cleanly. There might be hackish solutions, though, involving help from the application and its database schema. E.g. you could include an id for the generating host in the primary keys for new rows. You could add a column to mark which rows have been transferred. And so on. – MvG Apr 04 '13 at 07:22
  • @Rpj Are you going to accept one of the answers below? – nl-x Apr 11 '13 at 07:27

4 Answers4

6

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.

hexist
  • 5,151
  • 26
  • 33
  • I don't think the question involves multiple mysql deamons. Can auto_increment_increment and auto_increment_offset be set per db/schema? – nl-x Apr 04 '13 at 20:37
  • Oh.. well that'd be a weird setup :) If that's the case, then no this solution wouldn't work. – hexist Apr 04 '13 at 20:47
  • This has the constraint that you can have at maximum 9 servers connected in the replication, following this configuration – Martin Muñoz Apr 14 '23 at 18:29
1

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.

Community
  • 1
  • 1
nl-x
  • 11,762
  • 7
  • 33
  • 61
  • Ah, the answer of Hexist gives it a nice touch. Didn't know about @@auto_increment_increment and @@auto_increment_offset. Indeed by using those, you can still use an auto incrementing id. But using this solution probably involves the tables running on 2 different mysql servers. If that is the case, the 'replication' queries I wrote won't work, and you will actually have to do mysql replication. – nl-x Apr 04 '13 at 20:36
1

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.

johnnaras
  • 139
  • 10
0

Assuming you are running both databases on the same mysqld instance, I will add my poor man's replication:

create view dbB.tableA as select * from dbA.tableA;

Which in itself is MySQL's poor man's synonyms, but it works very well in practice, including insert, update, and delete operations.

Basically it means doing away with one of the two copies and replacing it with a synonym of the other one. Whether this fits your needs, only you can tell.

Tobia
  • 17,856
  • 6
  • 74
  • 93
  • This in no way answers the OP's question. – nl-x Apr 11 '13 at 07:25
  • It does not answer his *question* as it was posed, I agree, but it might answer his *need*, depending on the ancillary conditions he omitted from his quesion and the overall architecture of his setup. – Tobia Apr 11 '13 at 20:37