MySQL Migration – Changing Connection String Simplified

mariadbmigrationMySQLreplication

We are migrating our database (MySQL/MariaDB) onto a dedicated server. Previously, our web server hosted the database. I've already set up master-slave replication between the web server and the new database server, and the actual replication is up-to-date (0 Seconds_Behind_Master).

It would seem that the only remaining step is to change the connection string in our app code to connect to the new database server instead of the old DB hosted on the web server. Is this correct?

Of course, this assumes that we have not enabled the flags on the database server to prevent writes to the slave (we did not enable this flag, so we should be good there).

Best Answer

To ensure writes from the Master are complete, do the following on the Master:

SET GLOBAL read_only = 1;
FLUSH TABLES;
FLUSH LOGS;

Go to the Slave and run the following:

STOP SLAVE;
RESET SLAVE ALL;

Then, make sure there is no Slave setup

SHOW SLAVE STATUS\G

If it comes back with nothing, setup the new connect string and you're done !!!

GIVE IT A TRY !!!

Related Question