Is it possible to move MySQL to different hosting using replication?

I am considering switching to a new hosting provider and I would like to know if it is possible to achieve database (MySQL 5.6) migration without a huge downtime (= time to copy tens of GB of DB files from one hosting to other hosting).

So, is it possible to configure the current live MySQL DB as master and the new machine as slave in some mode that the master would not wait with new data inserts or updates for slave to confirm, and the slave would slowly (i.e. would not consume too many resources of master DB machine) try to sync itself, i.e. it will take up to a whole day, or maybe a couple of days (full speed file copy would take cca 4 hours) to fully sync.

Meanwhile I would setup the web server and other services on the new machine and then just switch DNS and switch slave to single master mode and disconnect the old machine. I expect (and I am OK with that) to lose some data during the actual DNS switch (some clients with old DNS record would access the old server and these changes would not replicate to the new machine), but for most visitors, this window would be 15 minutes or so.

So, is something like this possible and somehow easy to do? Alternative is cca 4 hours of downtime, copy all files to new server and just start it, but I am not very happy with such a long downtime.

I do not mind restarting DB service/daemon couple of times during this process in order to switch it to new configuration.

I do not want to do this migration using dumps, when I have to resync tables manually.

Solution 1:

you can get away with the snapshot dump approach:

enable log-bin and make a mysqldump --master-data=1 --all-databases at the master - this will save the bin-log positions into the dump. depending on your DB types (only innodb can use the --single-transaction), this will read-lock the DB at max for the duration of the dump. no further action on the master needed really.

fill the slave with the dump, and after CHANGE MASTER TO ...; SLAVE START; it should start catching up to the running master. once it's synced, you can make the switch.

you can also make an iptables DNAT port redirection to the slave, to avoid DNS-based delays.