I've tried getting information on this elsewhere but can't really find what I'm looking for. I'm making a system to handle sales, etc for a company that has different branches. They want to have the system both on the cloud as well as locally so in case internet goes down they can continue making sales with the local database instead of the database in the cloud. So all databases will have to be masters.
I'm making sure that all race conditions are taken care of in the database (updating stock table, etc) and in the case that one database is disconnected and must continue making sales I am using two columns as index in my sales table and journal_entries table (one column is a character representing the branch and the other column is just an incrementing number), so as far as I know this should allow the databases to sync when the disconnected database comes back online.
Is there anything I'm forgetting though? Any particular scenario where my database design would run into problems by allowing them to run without being able to replicate for a while?
Best Answer
For Master-Master, use Galera or InnoDB Cluster. The pitfalls of M-M are many and subtle.