= Proxy SQL =

Previously all web servers ran a local MySQL instance for web applications to use.

We are transitioning to using dedicated, network MySQL servers that listen on a private IP range. To ensure that web applications can continue connecting using "localhost", we are using the [https://docs.mayfirst.org/proxysql/ ProxySQL package]. 

== Steps to transition ==

To transition a Mosh to using proxysql:

1. Update the puppet file for the mosh. Add:
{{{
class { "mayfirst::m_proxysql::mosh": 
  transition => true
}
if ( $::fqdn in $::mfpl_mysql_servers ) {
    mayfirst::m_proxysql::permit_host { "REPLACE-WITH-MOSH-NAME": }
}
}}}

2. Push puppet changes to both lumumba and pachamama (and any future network database servers) to grant the new mosh access to the non-privileged service that provides a map of database usernames to network database servers. Note: our network database servers are still under puppet, this will change when we move them to ansible.

3. Push puppet changes to the mosh. You may have to push twice (still don't have the proper ordering). At this point, ''both'' MySQL and ProxySQL are running on the mosh. ProxySQL is proxying all connections and passing them directly to the local MySQL instance (which is now listing on the non-standard port 3307 of localhost). This step incurs a few minutes of downtime. When complete, we should see an increase in CPU since we are supporting both ProxySQL and MySQL on the same host.

4. Login to the network database server you want to move the databases to and run the [https://docs.mayfirst.org/transfer/ mf-transfer] script. You have to run it one database at a time. As soon as the database transition is complete, ProxySQL will seemlessly switch to the network database, so there is no down time. BUT - there may be data loss if data is added to the database after the database is dumped, but before the database is imported. For a large database that could be 10 to 15 minutes.

5. When complete, check the ProxySQL database for any remaining local database users: `mysql -e "SELECT * FROM mysql_users WHERE default_hostgroup = 999" main`

== Trouble Shooting ==

You can login to the local MySQL instance via: `mysql --defaults-file=/root/.my.legacy.cnf`.

You can manually re-load the MySQL users into the proxysql server by running (from the mosh, as root): `proxysql-load`

You can test access to the database servers for a given user with:

1. Local MySQL access: `mysql -u <username> -p --port 3307`
2. Network MySQL access: `mysql -u <username> -p --host pachamama.mayfirst.cx` (the .cx domain signals the use of our internal, private IP range)

You can login to the ProxySQL instance with:

`mysql`

Use the `main` database and peruse the `mysql_users` table to see which users should be configured to use which backend MySQL servers. The `default_hostgroup` of 999 is the localhost. `default_hostgroup` 1 is lumumba and 2 is pachamama.

If you get an error when `mf-transfer` updates the MySQL user about not being allowed to set a MySQL user host to a different host than the database, that means you have a user with access to two different MySQL databases. The solution is to move on to the other database. Then, return to the original database. `mf-transfer` will not re-import an already imported database, it will simply ensure that the control panel items are properly up-to-date so you can safely re-run.

== Post Transition ==

When the transition is complete:

 * Remove the `/root/.my.legacy.cnf` file. This file signals that we are in a transition period.
 * Remove `transition => ture` from the puppet file.
 * Add `mysql => 'n'` to the `m_mosh` definition in the puppet file to prevent puppet from trying to re-install MySQL
 * Purge `mariadb-server` from the host
 * Delete `/var/lib/mysql/`