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

Improve this question
8
  • What version of MySQL ?
    – RolandoMySQLDBA
    Sep 10, 2015 at 21:44
  • MariaDB 10.0.21
    – rinogo
    Sep 10, 2015 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, 2015 at 21:46
  • Do you have binary logging enabled on the Slave ???
    – RolandoMySQLDBA
    Sep 10, 2015 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 - AWS
    Sep 10, 2015 at 22:04

1 Answer 1

Reset to default
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 !!!

Improve this answer
8
  • 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, 2015 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, 2015 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, 2015 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, 2015 at 21:55
  • 1
    If the original Master is still in use, then Yes.
    – RolandoMySQLDBA
    Sep 10, 2015 at 22:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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