Which is the best way to bi-directionally synchronize dynamic data in real time using mysql

Question

Here is the scenario. 2 web servers in two separate locations having two mysql databases with identical tables. The data within the tables is also expected to be identical in real time.

Here is the problem. if a user in either location simultaneously enters a new record into identical tables, as illustrated in the two first tables below, where the third record in each table has been entered simultaneously by the different people. The data in the tables is no longer identical. Which is the best way to maintain that the data remains identical in real time as illustrated in the third table below regardless of where the updates take place? That way in the illustrations below instead of ending up with 3 rows in each table, the new records are replicated bi-directionally and they are inserted in both tables to create 2 identical tables again with 4 columns this time?

Server A in Location A
==============

Table Names
| ID| NAME  |
|-----------|
| 1 | Tom   |
| 2 | Scott |
|-----------|
| 3 | John  |
|-----------|

Server B in Location B
==============
Table Names
| ID| NAME  |
|-----------|
| 1 | Tom   |
| 2 | Scott |
|-----------|
| 3 | Peter |
|-----------|


Expected Scenario
===========
Table Names
| ID| NAME  |
|-----------|
| 1 | Tom   |
| 2 | Scott |
| 3 | Peter |
| 4 | John  |
|-----------|

Bushore

Answer 1

There isn't much performance to be gained from replicating your database on two masters. However, there is a nifty bit of failover if you code your application correct.

Master-Master setup is essentially the same as the Slave-Master setup but has both Slaves started and an important change to your config files on each box.

Master MySQL 1:

auto_increment_increment = 2
auto_increment_offset = 1 

Master MySQL 2:

auto_increment_increment = 2
auto_increment_offset = 2

These two parameters ensure that when two servers are fighting over a primary key for some reason, they do not duplicate and kill the replication. Instead of incrementing by 1, any auto-increment field will by default increment by 2. On one box it will start offset from 1 and run the sequence 1 3 5 7 9 11 13 etc. On the second box it will start offset at 2 and run along 2 4 6 8 10 12 etc. From current testing, the auto-increment appears to take the next free number, not one that has left before.
E.g. If server 1 inserts the first 3 records (1 3 and 5) when Server 2 inserts the 4th, it will be given the key of 6 (not 2, which is left unused).

Once you've set that up, start both of them up as Slaves.
Then to check both are working ok, connect to both machines and perform the command SHOW SLAVE STATUS and you should note that both Slave_IO_Running and Slave_SQL_Running should both say “YES” on each box.

Then, of course, create a few records in a table and ensure one box is only inserting odd numbered primary keys and the other is only incrementing even numbered ones.

Then do all the tests to ensure that you can perform all the standard applications on each box with it replicating to the other.

It's relatively simple once it's going.
But as has been mentioned, MySQL does discourage it and advise that you ensure you are mindful of this functionality when writing your application code.

Edit: I suppose it's theoretically possible to add more masters if you ensure that the offsets are correct and so on. You might more realistically though, add some additional slaves.

Irrelievable

For redundancy, two masters should be sufficient. For load-balancing, then individual master-slave setups on both locations can be used. But using a master with an offsite slave, to which you can manually switch to in the event of failure, is another option. – Hammad

also, make sure the servers have different values for server-id and that replicate-same-server-id is set to its default of 0. This may be there already, but it will loop and encounter errors if not. – Equilibrant

i like the idea of having the servers auto-increment by 2 then write a custom plugin to track inserts/edits & the plugin will handle the rest. so if server 1 inserts (1,3,5) the plugin can pick it & export to server 2 using the same id's & vice versa therefore no conflicts. – Bushore

I doubt you need to write a plugin to do that, the replication should do it. Your connection code should just attempt to load balance/connect to working box, the replication will do the rest (and because there are no key conflicts, it shouldn't struggle). – Irrelievable

Answer 2

MySQL does not support synchronous replication, however, even if it did, you would probably not want to use it (can't take the performance hit of waiting for the other server to sync on every transaction commit).

You will have to consider more appropriate architectural solutions to it - there are third party products which will do a merge and resolve conflicts in a predetermined way - this is the only way really.

Expecting your architecture to function in this way is naive - there is no "easy fix" for any database, not just MySQL.

Fantast

it doesn't wait as you describe - as above, its really two master-slave instances – Equilibrant

I meant in the hypothetical situation where MySQL supported synchronous replication. – Fantast

Answer 3

Is it important that the UIDs are the same? Or would you entertain the thought of having a table or column mapping the remote UID to the local UID and writing custom synchronisation code for objects you wish to replicate across that does any necessary mapping of UIDs for foreign key columns, etc?

November

Yes, its important that the UID's are the same on both servers. they dont have to follow sequence though, they just need to be similar and unique in both servers. Thanks anyway. i have a pretty good idea of what i will do. – Bushore

Answer 4

The only way to ensure your tables are synchronized is to setup a 2-ways replication between databases.

But, MySQL only permits one-way replication, so you can't simply resolve your problem in this configuration.

To be clear, you can "setup" a 2-ways replication but MySQL AB discourages this.

Constrained