1

I need to replicate our main server's databases (MariaDB) to a local server in our office, which will be used as an offline mirror when we have connectivity issues.

The data on the master should always take priority over the data on the local slave. Our users will be instructed never to write anything to the local "offline" slave, however it will still need to be writeable to enable auth & sessions.

I believe that with a standard Master-Slave setup, this will create problems with data consistency.

Short of writing a shell script to drop & re-migrate all of the databases, & reset the replication logs when the connection is re-established, is there a better way to configure such replication natively with MariaDB or Galera?

6
  • Why do you think normal master-slave replication won't work for this? This is exactly what it's intended for.
    – Barmar
    Commented Jun 3, 2019 at 16:03
  • Because when users access the local mirror, it will create entries in the local database for sessions & logging. Now when the master reconnects, will the data from the master overwrite the rows on the slave, or simply add them on the slave?
    – Doug Ingham
    Commented Jun 3, 2019 at 16:08
  • I missed that you said a writable DB slave. With master-slave, the slave should be read-only. I'm not sure how to do bidirectional replication with MySQL.
    – Barmar
    Commented Jun 3, 2019 at 16:10
  • Consider a Galera cluster.
    – Rick James
    Commented Jun 3, 2019 at 16:22
  • @RickJames I'm looking at my options with Galera, however I'm still unsure/doubtful it can do what I'm after. My particular scenario is to specifically allow split-brain/partitioning of the local mirror, with it then being resolved by automatically dropping all of the local changes in favour of the changes on the master. It's a very untraditional approach, as it allows for planned data loss. Commented Jun 3, 2019 at 20:22

1 Answer 1

1

You could use symmetricds to perform the bidirectional replication. It allows for conflict management in which you can define which side wins in the conflict in your case the master.

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.