Network MySQL transition

We are in the process of transitioning all MySQL databases from being run on the MOSH they server and only accessible via localhost, to be running on one of a few dedicated MySQL servers that are backed by solid state drives (see ticket #13886 to track progress).

This page documents how to transition a MOSH from using localhost to a database server.

Overview

The script to perform this tasks resides on your local workstation - it's in our puppet repo in the helpers folder and is called: mf-mysql-move-to-network-server

It is run from your workstation because you have to login to multiple servers to complete this task and we don't want to grant that access to other servers if we can help it.

The script operates on a entire mosh at a time because keeping track of which mysql users go with which mysql databases (and also the fact that many databases are stored in hosting orders that they are not configured to work with) is far too difficult.

The first step: collect information and try to give suggestions for cleaning up. If there are any empty databases or mysql users not assigned to any database, you will be given an opportunity to purge them properly (via deletion via the control panel in an automated way).

Next, the script iterates over the remaining databases and tries to find known configuration files (settings.php, wp-config.php, etc) that contain the database name. It builds out a directory on the host with a file for each database that contains each configuration file found, plus a file with the database name followed by ".modified" that contains the date of the newest table's last modification time.

The script then rsync's this data to your workstation. Once the data is on your workstation, you can re-run it over and over and point to this data and avoid going through the time-consuming reporting phase.

The last step is to iterate over each database found. The script tells you how many configuration files it found and also the date it was last modified. You can choose whether to move it to the network database server or not.

Here's a guide for making the decision:

• If there are no found configuration files and it hasn't been modified in more than a year, don't move it
• If there are no found configuration files and it has been modified recently, then you'll have to dig through the server to find the configuration files and move it over
• If there are configuration files and it hasn't been modified in over a year, it's a judgement call

If you choose to move a database, you get a step by step process of:

1. Update item in the control panel (and associated mysql users)
2. Add new DNS record (<dbname>.db.mayfirst.cx)
3. Run red-node-update on both lumumba and tubman to make all items active
4. Dump database on host, scp to lumumba and import it

At any time you can say no and ctl-c to cancel and start again.

The details

You can invoke the script with:

./helper/mf-mysql-move-to-network-server <moshname>

After you have run it once with a mosh and have downloaded the data, you can re-run with:

./helper/mf-mysql-move-to-network-server <moshname> /path/to/data

For example, the first run might be:

./helper/mf-mysql-move-to-network-server octavia

The output from the script will report the local path used to download the data, so the next time you can run:

./helper/mf-mysql-move-to-network-server octavia /tmp/tmp.Dkf21fJ