Transferring MySQL from InnoDB to another server without a long shutdown?

Question

MySQL

Actually a subject. There is a base, about 200Gb.
You need to drag it from server to server without long stops. There is a variant with rsync with a working source database, after rsync stops the source database and then catches up with the difference again with the same rsync.
Something like rsync -az [email protected]:/var/lib/mysql/ /var/lib/mysql/
Does this method have the right to life, or are there pitfalls?! With postgres, this goes with a bang!
Or maybe it's easier to raise the last lady on the recipient server, and set up master master replication with the source server and then stupidly switch frontends after full data consistency?!

Answer 1

Since a small stop of the service is acceptable (about a couple of minutes), then no master-master!
0) Raise the master-slave replica (the existing server is the master, the new one is the slave)
1) when the slave caught up with the master, stop the service (if the site - hang up the "log in in a couple of minutes" stub)
2) disassemble the replica
3) edit the service configs so that it addressed to the new server
4) turn on the service.
A couple of years ago, at work with our system administrator, this is how they migrated. No problems on the first try and a simple couple of minutes in total.

Answer 2

Both options work, rsync is just faster.

Answer 3

Take a binary dump using innobackupex from the master.
Run it on your other server as a slave.
Push the slave into read only through a global variable.
When the slave catches up with the master, switch to the slave (it will not break, since nothing will be written).
Turn off replication, turn off read only.