0

I am trying to figure out a way to have 2 Aurora MySQL clusters in a Multi master setup where both clusters can serve traffic, and the data should be in sync between both clusters even if it is eventually consistent and needs to be done manually.

The issue I am facing is that we have a column with AUTO_INCREMENT property and it is the primary key. If we are performing write operations on both the clusters, with ongoing replication from one cluster to the other, there could be a chance that there would be conflict in the auto increment column.

Is there any workaround to this problem?

Improve this question
4
  • 1
    Read about auto_increment_increment.
    – Bill Karwin
    Commented Aug 15 at 0:21
  • 1
    I hope you've validated other conflict possibilities. Two writeable nodes in an async replication is not for the light of heart. A clear eventual consistency acceptance and a limited set of write operations and the data they depend on needs to be carefully considered.
    – danblack
    Commented Aug 15 at 2:07
  • I second the comment from danblack. Specifically: unique constraints and foreign key constraints are going to be troublesome.
    – Bill Karwin
    Commented Aug 15 at 3:12
  • This is what GUIDs and UUIDs are for: generate unique values across multiple database instances. These are used in most replcation-based setups regardless of database product. Btw, amazon aurora is only mysql compatible, but is not mysql. Amazon rewrote significant parts of mysql under the hood. Even its official product descruption says that it is mysql and postresql compatible database.
    – Shadow
    Commented Aug 15 at 7:52

0

Reset to default

Your Answer

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