0

I have multi source mysql replication setup configured, so I have this big server which act as a slave to other smaller server. The setup looks similar to below.

enter image description here

Everything is working fine— until one of the master server is down, my webapp which hosted on that master server would failover to the big server, this changes also make the replicated db on slave being used instead.

When the master goes up again, I have to manually dump the replicated database on slave and restore it to master server to keep up/synchronize with the changes. The process alone took 10 minutes, this defeat the purpose of having this high-availability setup.

What I want to ask, is there any efficient method of restoring ONLY changes commited on slave to the master— without having to dump entire database all over again ?

Also is there any gui management tool to manage all of this ? I have take a look on orchestrator but it seems it does not support multi source replication.

1 Answer 1

0

This is not a "normal" case. So, there is no "canned" solution.

Plan A:

Copy the entire Replica to the rebuilt Primary, then DROP the unneeded data tables/databases.

Plan B:

Use mysqldump with suitable arguments to dump (from the Replica) only those tables/databases needed on the rebuilt Primary. Then use mysql < that-dump-file to reload it.

In either Plan:

  • Remember to reconstruct my.cnf; it probably has binlog_* commands.
  • Make sure server_id is restored
  • Allow for sufficient downtime while doing the transfers.
  • Consider changing to InnoDB Cluster or Galera. With it, you would have 3 equal servers, plus automatic repair when one goes down. (Yes, you would have all the data on each node.)
4
  • Yes, you would have all the data on each node. say if I have 3 system each having it own database, if I go with InnoDB Cluster, do I need 3×3 = 9 server ? (3 server for each system to handle database)
    – Liso
    Commented Dec 8, 2022 at 2:52
  • You have some reason for avoiding all data in each server?
    – Rick James
    Commented Dec 8, 2022 at 4:40
  • So I put all my database to three node, and all my webapp will point to those three node ?
    – Liso
    Commented Dec 8, 2022 at 4:47
  • For Galera, you could have each of 3 nodes writable and point the webapp to any node. And, with a Proxy, you could automatically pick whichever node it likes.
    – Rick James
    Commented Dec 8, 2022 at 4:52

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.