This is actually the scenario. 2 web servers in 2 separate locations getting two mysql databases with identical tables. The information inside the tables can also be likely to be identical instantly.
This is actually the problem. if your user either in location concurrently makes its way into a brand new record into identical tables, as highlighted within the two first tables below, in which the third record in each table continues to be joined concurrently through the differing people. The information within the tables is no more identical. The best idea method to maintain the data remains identical instantly as highlighted within the third table below no matter in which the updates occur? That means by the illustrations below rather than winding up with 3 rows in each table, the brand new records are duplicated bi-directionally and they're placed both in tables to produce 2 identical tables again with 4 posts this time around?
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 | |-----------|
There is not much performance to become acquired from replicating your database on two masters. However there's a great little bit of failover should you code the application correct.
Master-Master setup is basically just like the Slave-Master setup, but has both Slaves began as well as an important switch 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
Both of these parameters make sure that when two servers are fighting on the primary key for whatever reason, they don't duplicate and get rid of the replication. Rather than incrementing by 1, any auto-increment area will automatically increment by 2. On a single box it'll start offset from 1 and run the succession 1 3 5 7 9 11 13 etc. Around the second box it'll start offset at 2 and run along 2 4 6 8 10 12 etc. From current testing, the car increment seems to accept next free number, not just one which has left before. E.g. If server 1 card inserts the very first 3 records (1 3 and 5), when Server 2 card inserts the fourth, it will likely be because of the key of 6 (not 2, that is left unused).
Once you have set that up, start each of them as Slaves.
Then to check on both of them are working ok, connect with both machines and perform command
SHOW SLAVE STATUS and you ought to observe that both
Slave_SQL_Running should both say “YES” on each box.
Then obviously, produce a couple of records inside a table and be sure one box is just placing odd designated primary secrets and also the other is just incrementing even designated ones.
Then do all of the tests to actually are capable of doing all of the standard programs on each box by using it replicating towards the other.
It's easy once it is going. But as continues to be pointed out, MySQL do discourage it and propose that you make sure you are conscious of the functionality when writing the application code.
Edit: I guess it's theoretically possible to include more masters should you be sure that the offsets are correct and so forth. You may more reasonably though, then add additional slaves.
MySQL doesn't support synchronous replication, however, even when it did, you'd most likely not desire to use it (can't go ahead and take performance hit of awaiting another server to sync on every transaction commit).
You'll have to consider appropriate architectural methods to it - you will find 3rd party items which is going to do a merge and resolve conflicts inside a predetermined way - this is actually the best way really.
Expecting your architecture to operate in by doing this is naive - there's no "easy fix" for just about any database, not only MySQL.
Could it be essential that the UIDs are identical? Or can you entertain the idea of getting a table or column mapping the remote UID towards the local UID and writing custom synchronisation code for objects you want to duplicate across that does any necessary mapping of UIDs for foreign key posts, etc?