How to Use ProxySQL 2 on Percona XtraDB Cluster for Failover

ProxySQLIf you are thinking of using ProxySQL in our Percona XtraDB Cluster environment, I’ll explain how to use ProxySQL 2 for failover tasks.

How to Test

ProxySQL uses the “weight” column to define who is the WRITER node. For this example, I’ll use the following list of hostnames and IPs for references:

My current WRITER node is the “pxc1” node, but how can I see who is the current WRITER? It’s easy, just run the following query:

This is the output:

Now for some maintenance reasons I need to failover to “pxc2” node because on “pxc1” I need to do some hardware changes (like to increase the physical memory or increase the disk partition), so there are 3 steps.

1. Move the WRITER node to pxc2 node. We need to decrease the “weight” value on the current WRITER and increase the “weight” value on “pxc2” to the new WRITER.

Note: I used these names <IP_PXC1> and <IP_PXC2> to be easy to read, but you need to change for the current IPs.

2. Get out the “pxc1” node from the cluster to avoid continuing to receive SELECTs, because after the failover this will be a READER node.

3. This step is optional in case you need to stop/start MySQL, and you keep this node out from the cluster.

Finally, this should be the output after the failover:

The “pxc2” node is the new WRITER and the “pxc1” node was moved to go the hostgroup 14 (offline_hostgroup), but is still online and in sync without receiving any query.

In case you restarted MySQL (previous step 3), it will continue out from ProxySQL, but now you need to allow accepting SELECTs:

And don’t forget to remove from your my.cnf file (previous step 3), if you did that.

Observations

Why do you need to move the WRITER node? Because sometimes it is needed to get out a node for maintenance, or the server is deprecated, etc.

On ProxySQL 1.X you need to configure a scheduler to run an external script to perform the backend health checks and update the database servers state. This “scheduler” table continues on ProxySQL 2 to keep consistency with previous versions, but now you don’t need to define a scheduler because there is a new feature and now this is supported natively for Galera Cluster or Percona XtraDB Cluster.

Another thing to keep in mind, for this new feature, is that it will check the MySQL status by monitoring the following statuses/variables:

This variable “pxc_maint_mode” is no longer used any more from ProxySQL 2.

There was a bug reported before about this param “pxc_maint_mode” for ProxySQL 2 because it is used a lot for us on ProxySQL 1, but now I recommend to use this new param “wsrep_reject_queries” to remove it from the rotation, in case you need to work on a particular server.

Also in case the current WRITER goes down, ProxySQL will failover to another mysql server automatically, checking the following high “weight” column, and when the server comes back, it will move the WRITER to the previous server.

Finally, but not least, here there are more details from this new feature/table.

There are a few settings for this table, I’ll explain it below.

writer_hostgroup: it will contain the writers (read_only=0 in case master/slave topology) or the writers nodes in Percona XtraDB, this last option depends on the “max_writers” config
backup_writer_hostgroup: this refers to the hostgroup that will contain the candidate servers
reader_hostgroup: it will contain the readers (read_only=1 in case master/slave topology) or the readers nodes in Percona XtraDB, this last depends on the “max_writers” config
offline_hostgroup: it will contain all those nodes which were deemed not usable
active: values 1/0 if this configuration needs to be used or not
max_writers: how many nodes you need to write at the same time, you can set up it up to the number of nodes, by default is 1
writer_is_also_reader: values 0/1/2, I’ll explain later, but the default value is 1
max_transactions_behind: based on wsrep_local_recv_queue status, if the node exceeds the “max_transactions_behind” the node will be marked as SHUNNED and it will not receive more traffic
comment: A little description about this config

Summary

This new feature makes it easy to manage/configure Percona XtraDB Cluster/Galera nodes. You only need to update the “weight” column and load to the runtime table and check the”runtime_mysql_servers” table to be sure if the WRITER node was changed. I hope you find this post helpful!

Check out some of my previous blogs:

ProxySQL Experimental Feature: Native ProxySQL Clustering

How to Add More Nodes to an Existing ProxySQL Cluster

How to Manage ProxySQL Cluster with Core and Satellite Nodes

Share this post

Comment (1)

  • Jon K. Reply

    Hola Walter, I don’t like that ProxySQL uses the “weight” to define who is the node WRITER.
    Imagine, according to your example, that node pxc1 goes down abnormally…which node will be the new WRITER, pxc2 or pxc3 (both have same “weight”) ?

    If you have only one ProxySQL it is indifferent whether it is the pxc2 or the pxc3 the new WRITER but if you have several ProxySQL (not in Cluster), it could happen that pxc2 and pxc3 will remain as WRITER in different ProxySQLat the same time and this can be a big problem if max_writers=1.

    Thanks.

    January 10, 2020 at 3:11 pm

Leave a Reply