Search code examples
mysqlwindowsserverbigdatamaster-slave

MySQL Master / Slave switch to new Windows Servers / Downtime

I have the following problem, i have to replace the current two Windows servers with two new Windows servers. The master (MySQL 5.7) runs on one server and the slave (MySQL 5.7) on the other. We have a productive system that is visited by many users. What is the best way to create the switch without too much downtime? I have some databases, all together are about 90GB in size.

What would be the regular way?

  1. master / slave stop
  2. mysqldump from master (mysqldump –u -p --flush-logs --hex-blob --all-databases)
  3. Dump import on new master server
  4. Dump import on new slave server

I am on the right way here? or are there possibly better options from mysqldump so that i can reduce the downtime.

Thanks very much!

Solution

  • Given that A is your current Primary and B is your current Replica. And your new servers are C and D...

    Currently: A -> B

    Take B offline and copy it to C, then set up replication: B -> C

    Put B back online and let it catch up: A -> B -> C

    Repeat; now A -> B -> C -> D

    Take a short downtime to move clients from writing to A to writing to C and reading from B to reading from D.

    Retire A and B. Now C -> D

    Other issues

    Why do you have just Primary -> Replica replication? Slightly better is C <-> D, or "dual-Primary". Still better is a cluster (either Galera or InnoDB Cluster); this will provide more HA and auto-failover, but will require 3 nodes.

    With clustering, you get zero downtime, even when any one server crashes.