Changes between Initial Version and Version 1 of proxysql

Timestamp:

Dec 12, 2022, 2:24:26 PM (3 years ago)

Author:

Jamie McClelland

Comment:

--

proxysql

@@ +1 @@
+= 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`