New to MySQL scaling-how to have 2 master-write servers?

Question

From what I learned, a MySQL MASTER server is one that can both read and write. Correct?

My question is: Suppose you have 2 MySQL master-write servers (and many slave-read servers).

How can you solve the problem of auto-increment? How do you sync it? Suppose you are writing to both the Master1 and Master2...how will you sync it so that the auto-increment is consistent? Can someone please explain the solution to this in a simple sense.

Answer 1

You set server #1 to use odd numbered auto increment values and server number #2 to use even numbered.

# server 1 
auto_increment_increment 2                                
auto_increment_offset    1   

# server 2
auto_increment_increment 2                                
auto_increment_offset    2

There's a linux how-to which outlines the process. See also MySQL docs on this.

Answer 2

Gaps should be okay if your system doesn't need it to be. As soon as you hit the limit of INT or BIGINT you should be ready with a data warehousing setup so you can offload your old data and get ready rollover back to the base offset values. On a write-intensive application, you'll need to be prepared and be able to offload old data as soon as possible.