1

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?

1 Answer 1

3

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.

2
  • Yes, these settings are not needed in described case, I but we have it after re-configuration and might need in future.. maybe. About your answer - thanks! Could you point me to docs where this descibred? Or this is undocumented?
    – vsespb
    Commented Mar 24, 2016 at 21:26
  • I think this is the closest to a documentation you can get: dev.mysql.com/doc/refman/5.7/en/… . In statement based replication you can see it in the binlog with mysqlbinlog --base64-output=DECODE-ROWS binlog.***** that SET INSERT_ID=4/*!*/; statements are included in the transactions touching the auto_increment column. Commented Mar 25, 2016 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.