Hello All,
I need some advice on the best setup for MySQL Replication.
Please correct me if I am wrong in anything that I say 
.
We have 5 servers, 2 in the US, 2 In Canada (Toronto), and 1 DEV Server (VPS)
Note, the DEV server does not need to replicate from the main, but it would be nice if I could "switch it on" if need be.
We now have a need for SQL Replication (the site kind of exploded). For two reasons - High Availability and to keep everything moving fast.
Scenario A:
1 Master Server, 3 Slaves.
This much I understand and could probably setup without question, but the downside to that is that if the primary server goes down for any reason whatsoever, writes are not possible.
Scenario B:
2 Master, 4 Salves (I know each has to be a slave of Each master in this case).
This is obviously most advantageous, as there will be 2 separate data centers, 2 separate power grids, 2 separate connections. Highly unlikely that they will both go down at the same time.
NOW:
I want to do Scenario B, of course, as if in the unlikely scenario that 2 servers go down, we could put something in the site code to tell users to check back later as it would mainly affect their ability to purchase, "reading" would still allow them to browse the site, etc.
The caveat is that the order system uses the ID (Primary Key, Auto Increment INT) as part of the order number, and must be unique.
For instance, with scenario B assuming two people complete a transaction at the exact same time, is there a possibility of the row ID being duplicated?
Looking for any advice on the best setup, and any information I may need to know.
Thanks in advance..