1

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).

  • What version of MySQL ? – RolandoMySQLDBA Sep 10 '15 at 21:44
  • MariaDB 10.0.21 – rinogo Sep 10 '15 at 21:45
  • mysql --version: mysql Ver 15.1 Distrib 10.0.21-MariaDB, for Linux (x86_64) using readline 5.1 – rinogo Sep 10 '15 at 21:46
  • Do you have binary logging enabled on the Slave ??? – RolandoMySQLDBA Sep 10 '15 at 21:48
  • 1
    Pardon for not making a technical point, but have you had the chance to QA this new server/process as well before pointing prod over? If not, it's a great practice. – Ali Razeghi Sep 10 '15 at 22:04
1

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 !!!

  • Awesome! I'll give it a shot. Is this not the typical way to move a live, production database to another server? This seems like the "bread and butter" of a DBA. I'm surprised I couldn't find more documentation about this online! – rinogo Sep 10 '15 at 21:52
  • Also, I'm assuming that SHOW SLAVE STATUS\G is to also be executed on the slave (new database server), correct? – rinogo Sep 10 '15 at 21:53
  • 1
    Yes, SHOW SLAVE STATUS\G is to be run on the Slave. It should return Empty set (0.00 sec). – RolandoMySQLDBA Sep 10 '15 at 21:55
  • Ok, final question - We're actually only migrating some of the databases to the database server. Smaller databases used by cPanel/WHM/etc on the web server will not be migrated. In order to not leave the web server's database in a read-only state, can we simply execute SET GLOBAL read_only = 0; on master (the web server) at the end of this entire process? – rinogo Sep 10 '15 at 21:55
  • 1
    If the original Master is still in use, then Yes. – RolandoMySQLDBA Sep 10 '15 at 22:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.