Convert MOSH to use ProxySQL

To transition the databases from the MOSH’es to the network database servers, we first re-configure MySQL to listen on localhost port 3307 (instead of 3306).

Then we install proxysql and instruct it to proxy for the local MySQL server.

Then, we can transfer each database one at a time with minimal downtime.

The steps are:

  1. In the puppet .pp file of the mosh add (replace $HOST with the actual name of the host):
class { "mayfirst::m_proxysql::mosh": 
  transition => true
}
  1. In the Ansible Seed Inventory repository, add to the MOSH’es yaml file:
proxysql_profile: proxy
  1. Ansible push to mysql00[45678] (and all proxysql databases) sower --live playbook --tags proxysql mysql00[45678].mayfirst.org
  2. Puppet push to the MOSH in question
  3. After puppet runs on the MOSH, you may need to manually run: proxysql-load as root
  4. You need to configure the proxysql monitor user’s password.
  • Lookup the password on any existing proxysql MOSH (e.g. ossie): mysql -e "SELECT * FROM global_variables WHERE variable_name = 'mysql-monitor_password'"
  • Configure the password on the MOSH you are working on:
     mysql -e "UPDATE global_variables SET variable_value = 'LOOKED UP PASSWORD' WHERE variable_name = 'mysql-monitor_password'"
     mysql -e "LOAD MYSQL VARIABLES TO RUNTIME"
     mysql -e "SAVE MYSQL VARIABLES TO DISK"
  1. Indicate in red that this server is now a proxysql server: update red_server set server_proxysql = 1 where server = 'HOST.mayfirst.org';

Now, all databases should be accessible via localhost - but going through proxysql. You can login to mysql004 (or any of our network database servers) and run mf-transfer to move the database.

After all database have been moved off the server, you can edit the puppet .pp file and remove the “transition => true” part and also add: mysql => "n" to the m_mosh class. Then you can purge MySQL from the MOSH.