Let's suppose I have mysql master-master replication setup with only one server active at a time (i.e. all writes going to one server), and
I have ability to manually switch to another master.
Next, I have auto_increment_increment and auto_increment_offset setup:
Master1
auto_increment_increment = 2
auto_increment_offset = 1
Master2
auto_increment_increment = 2
auto_increment_offset = 2
Master1 is now active master, and I have a table "users", and insert few recrods:
1,3,5,7
then I switch to Master2, all writes going to Master2.
Question is: will next inserted record to users have auto_increment ID "2" or ID "8" ? Where is this documented?
Best Answer
Auto Increment ID is "replicating" to the slaves. Otherwise it would be hell of a job to promote a slave to a new master if the status of auto increment needs to synced. So to answer your question it would start from 8.
If you do not write actively to both master at the same time you don't need to do any
auto_increment_*settings trickery. These settings are meant to avoid situation where the two master picks the same ID for new rows inserted concurrently on both masters which would lead to conflicting IDs while applying changes from the relaylog.