Wasn’t
setting up database replication for migrating content fun and easy?
What do you mean I owe you for therapy visits?
Now that we have replication in place, sending from the live server over to the target database machine, all we have to do is wait for the up-to-date data to sync up. Then, when we are ready for our final sync, writes to the old server are stopped, the slave is promoted to master and the replication broken, and the application is updated to use the new database server. This is when downtime comes into play.
Interrupt Writes
There are many ways to stop writes to the database. The most accurate one but the least favorable in this situation is to turn off any applications using the database, i.e. stopping your web server, but this of course presents visitors with an unavailable site. I prefer this method since it is totalitarian, and we are planning for downtime to finish the migration anyway.
The next way is to break the connection from the web application to the database, potentially through a firewall or CMS configuration change, but this too will likely cause errors to be displayed on the website (since it continues to attempt writes), and is therefore not ideal.
If your application supports it,
the ideal method is to turn on any maintenance mode or read-only mode that would guarantee writes to cease. This might be making forums read only, or disabling a shopping cart, or stopping IP logging. Check the documentation for your CMS or with your site development team to see if this is something the application can support. It must stop ALL writes to the database. I personally cannot trust this method for every application, since I don’t know their inner workings, but if you know your application well, you can determine if this is feasible.
There is a SQL only method that will make all databases read only:
mysql -e 'flush tables with read lock;'
mysql -e 'set global read_only = 1;'
This can be undone with:
mysql -e 'set global read_only = 0;'
mysql -e 'unlock tables;'
This, too, will cause errors to be displayed on the application, as it will continue to attempt to write to the database, but will receive an error when it tries to do so.
The thing that we
cannot do to interrupt writes is stop MySQL. This has to keep running in order to send any residual data over to the slave, as well as dump any other smaller databases that aren’t set up with replication for your final sync.
Allow Slave Writes To Finish
Keep spamming the status command on the target server until the slave is 0 seconds behind master.
# mysql -e 'show slave status'
Stop Slaving
Now that we are all caught up, and there are no new ongoing writes, we need to break the replication connection on the target server.
# mysql -e 'STOP SLAVE;'
# mysql -e 'RESET SLAVE ALL;'
These two steps will stop slaving and then delete all data related to the connection to the master, including variables.
Now, head into your my.cnf file and remove any settings you added related to the replication or to the old master. You might also add
skip-slave-start to the configuration, which will prevent any slaving from starting up, just in case. The last step is to restart mysql on the new server. Dobby is now a free elf.
Reconnect Applications
Lastly, the applications need to know where to connect to the new database. In my case, this is an internal upgrade, so I visit the web nodes and affect the application’s config file to add the new database server IP. Make sure that the username and password and IP address of the web server are set up with the right access grants in MySQL on the new database server.
If you are migrating your web server to a new machine at the same time as the database, you should update the config file at this time as well, and start testing the application out to ensure it connects appropriately. Then, DNS can be updated to move the web app to its new node.
Disable Maintenance Mode
Since the old database server and the old web server were the ones with maintenance mode added, you probably don’t need to disable whatever you did to stop database writes if you are going to a new web node. However, if you updated the CMS configuration to stop writes, and also synced this information to the target server, you should undo the change on the target web node.
