Migrating from Maxscale to the ProxySQL Load Balancer

A database load balancer, or proxy, is a middleware service between application layer and database layer. Application connects to the database proxy, and the proxy forwards the connection to the database. There are some benefits using a database proxy, for example: split read and write queries, cache queries, distribute queries based on some routing algorithm, queries rewrite, and scale your read-only workload. A database proxy also abstracts the database topology (and any changes) for the application layer, so applications only need to connect to one single endpoint.

There are various database proxies out there, from commercial to open source options e.g., HAProxy, Nginx, ProxySQL, Maxscale, etc.  In this blog, we will discuss how to migrate database proxies from Maxscale to ProxySQL with the help of ClusterControl.

Current Architecture with Maxscale

Consider a highly available database architecture which consists of 3 nodes in a Galera Cluster, and on top of it, 2 Maxscale and Keepalived services for high availability of the database proxy. Galera Cluster is “virtually” synchronous replications, it uses a certification based for replication ensuring your data will be available on all the nodes. The current architecture is shown below:

Maxscale is a database proxy from MariaDB Corporation, which acts as middleware between applications and databases. 

Here’s the topology architecture for Galera Cluster and Maxscale load balancers in ClusterControl. You are able to deploy all this directly from ClusterControl, or import existing databases and proxy nodes into ClusterControl. You can see your database topology in the Topology Tab.

Deploy ProxySQL & Keepalived

ProxySQL is another database proxy from ProxySQL, which provides some features such as query caching, queries rewrites, queries split for write and read based on queries pattern. To deploy ProxySQL in ClusterControl, you need to go to Manage -> Load Balancers in your cluster. ClusterControl supports a few different database proxies; HAProxy, ProxySQL, MaxScale.

Choose ProxySQL, and it will show the below page:

We need to choose the server address where ProxySQL will be installed. We can either install on the existing nodes or if you want to have a dedicated node for ProxySQL, just type the IP address in the list. Fill the password for Administration and Monitoring users, Add the application user into ProxySQL or you can configure later. Enable the database servers to be included in the load balancing set in ProxySQL. Click the Deploy ProxySQL button. We need to have at least 2 ProxySQL for high availability.

If we forget to add a database user into ProxySQL during the setup, we can configure it in the ProxySQL user tab as shown below:

ProxySQL requires database users to be configured in ProxySQL as well.

After ProxySQL is deployed, we continue to configure the Keepalived on each ProxySQL host. Keepalived services will act as master/backup roles across the ProxySQL instances. Keepalived service uses VIP (Virtual IP Address), so application will connect to a virtual IP Address on master role, and will forward the connection to the local ProxySQL. If the services fail, the VIP automatically be floated to another node.

Deploying keepalived in ClusterControl is done on the same page as the database proxy, you just need to choose the Keepalived tab. Choose the load balancers type, which is ProxySQL, and then add the current ProxySQL for Keepalived1 and Keepalived2. Fill the Virtual IP Address and Network interface. And finally, click the Deploy Keepalived button.

Running two ProxySQL with Keepalived services gives us a high availability proxy layer. In ClusterControl, it is shown in the below topology view:

Switchover

Switchover of the traffic is really straight forward, just need to change the ip address connection in the application layer to use Virtual IP Address for ProxySQL, and then monitor the traffic through ProxySQL.