Load Balancing MySQL servers using ProxySQL

19 min readMar 25, 2020

By Shashi Prakash Gautam and Kalyan Ram Adiraju

What is ProxySQL?

ProxySQL is an open-source MySQL load balancer and proxy solution which can run on almost any Linux distributions. It can be used to improve the performance and availability of the MySQL servers.

In this post, we’ll discuss a general overview of what ProxySQL is and examples of how it can be used to improve the performance and availability of your MySQL servers using Amazon RDS.

How do I Increase the performance of the application?

We can Scale up the performance of the application using Application-level scaling and Database level scaling.

Application-level scaling:

Application Load Balancer is best suited for load balancing of HTTP and HTTPS traffic and provides advanced request routing targeted at the delivery of modern application architectures, including microservices and containers. Operating at the individual request level (Layer 7), Application Load Balancer routes traffic to targets within Amazon Virtual Private Cloud (Amazon VPC) based on the content of the request.

Here are some of the key features of ELB:

Layer-7 Load Balancing
HTTPS Support
Server Name Indication (SNI)
IP addresses as Targets
High Availability
Content-Based Routing
Host-based Routing
Path-based Routing
Containerized Application Support
HTTP/2 Support
WebSockets Support
Native IPv6 Support
Sticky Sessions
Health Checks
Operational Monitoring
Logging
Delete Protection
Request Tracing
Web Application Firewall

Database level scaling :

There are two types of Database scaling:

Vertical scaling
Horizontal scaling

AMAZON RDS:

Using Amazon RDS makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while automating time-consuming administration tasks such as hardware provisioning, database setup, patching and backups. It frees you to focus on your applications so you can give them the fast performance, high availability, security and compatibility they need.

Amazon RDS is available on several database instance types — optimized for memory, performance or I/O — and provides you with six familiar database engines to choose from, including Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle, and Microsoft SQL Server. You can use the AWS Database Migration Service to easily migrate or replicate your existing databases to Amazon RDS.

Scaling Your Amazon RDS Instance Vertically and Horizontally

Vertical Scaling

To handle a higher load in your database, you can vertically scale up your master database with a simple push of a button. There are currently over 18 instance sizes that you can choose from when resizing your RDS MySQL, PostgreSQL, MariaDB, Oracle, or Microsoft SQL Server instance. For Amazon Aurora, you have 5 memory-optimized instance sizes to choose from. The wide selection of instance types allows you to choose the best resource and cost for your database server

Following are some things to consider when scaling up an RDS instance:

Determine when you want to apply the change. You have an option to apply the change immediately or during the maintenance window specified for the instance.
Storage and instance type are decoupled. When you scale your database instance up or down, your storage size remains the same and is not affected by the change. You can separately modify your DB instance to increase the allocated storage space or improve the performance by changing the storage type (such as to General Purpose SSD to Provisioned IOPS SSD).
There is minimal downtime when you are scaling up on a Multi-AZ environment because the standby database gets upgraded first, then a failover will occur to the newly sized database. A Single-AZ instance will be unavailable during the scale operation.

Steps to Modify the RDS instance:

Login into the console
In AWS Services go to Databases and select RDS
Goto Instances and select your master Instance
Click on Instance Actions and select Modify
Then choose the new DB instance class.

6. Finally, determine if you want to apply the change immediately or not. To apply the change immediately, select the Apply Immediately checkbox at the bottom of the Modify page. If you don’t apply the change immediately, then the change will be scheduled to occur during the preferred maintenance window that you defined.

Horizontal Scaling:

In addition to scaling your master database vertically, you can also improve the performance of a read-heavy database by using read replicas to horizontally scale your database. RDS MySQL, PostgreSQL, and MariaDB can have up to 5 read replicas, and Amazon Aurora can have up to 15 read replicas.

Read replicas allow you to create read-only copies that are synchronized with your master database. You can also place your read replica in a different AWS Region closer to your users for better performance. However, read replicas are not a replacement for the high availability and automatic failover capabilities that Multi-AZ provides.

Currently, RDS read replicas support transparent load balancing of queries or connections. Each replica has a unique Domain Name Service (DNS) endpoint so that an application can implement load balancing by connecting to the replica endpoint. Let’s look at the options on how we can make applications aware of RDS read replicas.

Steps to implement Horizontal scaling:

Login into the console
In AWS Services go to Databases and select RDS
Goto Instances and select your master Instance
Click on Instance Actions and select Create Read Replica
Go to settings and create DB_instance_identifier
Scroll down and click on Create Read replica

Note: Each RDS instance we get an endpoint where we can connect to

What we picked:

We picked Horizontal scaling because our Application has mostly read queries and vertical scaling is quite expensive

Installation of Proxy Server in EC2 instance:

Launch your EC2 instance
Go to terminal

ProxySQL is available from the Percona software repositories. If that is what you used to install PXC or any other Percona software, run the corresponding command:

On Debian or Ubuntu:

sudo apt-get install proxysql

On Red Hat Enterprise Linux or CentOS:

sudo yum install proxysql

To start ProxySQL, run the following command:

sudo service proxysql start

Warning!
Do not run ProxySQL with default credentials in production. Before starting the proxysql service, you can change the defaults in the /etc/proxysql.cnf file by changing the admin_credentials variable.

Configuring ProxySQL:
We assume you are already aware of ProxySQL architecture, and this HOWTO assumes that ProxySQL is being reconfigured using the standard SQL admin interface, available by default connecting to port 6032 using trivial (changeable) credentials:

$ mysql -u admin -padmin -h 127.0.0.1 -P6032

First, let’s verify that there is nothing configured. No entries in mysql_servers, nor in mysql_replication_hostgroups or mysql_query_rules tables.

mysql> \R Admin>
PROMPT set to 'Admin> '
Admin> SELECT * FROM mysql_servers;
Empty set (0.00 sec)Admin> SELECT * from mysql_replication_hostgroups;
Empty set (0.00 sec)Admin> SELECT * from mysql_query_rules;
Empty set (0.00 sec)

Add backends:

For this demo, we started 3 MySQL servers locally using MySQL Sandbox. Let’s add them to ProxySQL.

Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'127.0.0.1',21891);
Query OK, 1 row affected (0.01 sec)Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'127.0.0.1',21892);
Query OK, 1 row affected (0.01 sec)Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'127.0.0.1',21893);
Query OK, 1 row affected (0.00 sec)

All looks good so far.

NOTE: By default, MySQL sandbox will set read_only = 0 on slaves. Set set global read_only = 1 on the slaves.

Configure monitoring

ProxySQL constantly monitors the servers it has configured. To do so, it is important to configure some variables. Let’s configure them.
Add the credentials of the users required to monitor the backend (the user needs to be already created in MySQL server):

Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)

Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)

Then we configure the various monitoring intervals:

Admin> UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
Query OK, 3 rows affected (0.00 sec)Admin> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';

There are a lot of variables, and some are not used (yet) or not relevant for this howto. For now, consider only the ones I listed before. Changes related to MySQL Monitor in table global_variables take places only after running the command LOAD MYSQL VARIABLES TO RUNTIME, and they are permanently stored to disk after running SAVE MYSQL VARIABLES TO DISK. Details here.

Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 54 rows affected (0.02 sec)

Backend’s health check

Now, let’s see if ProxySQL is able to communicate with these hosts. ProxySQL has several tables where stores monitoring information.

Admin> SHOW DATABASES;

Admin> SHOW TABLES FROM monitor;

Not all the tables in the monitor are currently used. For now, we can check the relevant tables with the following queries:

Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;

We can conclude that all of the configured servers are healthy. One important thing to note here is that monitoring on connect and ping is performed based on the content of the table mysql_servers, even before this is loaded to RUNTIME. This approach is intentional: in this way, it is possible to perform basic health checks before adding the nodes in production.
Now that we know that the servers are correctly monitored and alive, let’s enable them.

Admin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)Admin> SELECT * FROM mysql_servers;

MySQL replication hostgroups

Let’s check another table in the monitor schema, monitor.mysql_server_read_only_log:

Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10;
Empty set (0.00 sec)

This table is currently empty. The reason is that ProxySQL checks the value of read_only only for servers configured in hostgroups that are configured in mysql_replication_hostgroups. This table is currently empty:

Admin> SELECT * FROM mysql_replication_hostgroups;
Empty set (0.00 sec)

But what is the functionality of this table? With this table, the listed hostgroups can be configured in pairs of writer and reader hostgroups. ProxySQL will monitor the value of read_only for all the servers in specified hostgroups, and based on the value of read_only will assign the server to the writer or reader hostgroups. To make an example:

Now, all the servers that are either configured in hostgroup 1 or 2 will be moved to the correct hostgroup:

If they have read_only=0 , they will be moved to hostgroup 1
If they have read_only=1 , they will be moved to hostgroup 2

But at this moment, the algorithm is still not running, because the new table isn’t loaded at runtime. In fact:

Admin> SELECT * FROM mysql_servers;

Let’s load mysql_replication_hostgroups at runtime using the same LOAD command for MYSQL SERVERS: in fact LOAD MYSQL SERVERS TO RUNTIME processes both mysql_servers and mysql_replication_hostgroups tables.

Admin> LOAD MYSQL SERVERS TO RUNTIME;                                                                                                              Query OK, 0 rows affected (0.00 sec)

Wait a few seconds, and check again the status:

Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10;

Alright, ProxySQL is monitoring the read_only value for the servers. And also created hostgroup2 where it moved servers with read_only=1 (readers) from hostgroup1.

Admin> SELECT * FROM mysql_servers;

All looks good. It is time to save the configuration to disk:

Admin> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.01 sec)Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 54 rows affected (0.00 sec)

MySQL Users

After we configure the servers in mysql_servers, we also need to configure MySQL users. This is performed using table mysql_users:

Admin> SELECT * FROM mysql_users;
Empty set (0.00 sec)Admin> SHOW CREATE TABLE mysql_users\G
*************************** 1. row ***************************
       table: mysql_usersCreate Table: CREATE TABLE mysql_users (
username VARCHAR NOT NULL,
password VARCHAR,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
default_hostgroup INT NOT NULL DEFAULT 0,
default_schema VARCHAR,
schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 0,
fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
PRIMARY KEY (username, backend), UNIQUE (username, frontend))
1 row in set (0.00 sec)

The table is initially empty. Let’s start configuring users.

Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','',1);
Query OK, 1 row affected (0.00 sec)Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('msandbox','msandbox',1);
Query OK, 1 row affected (0.00 sec)Admin> SELECT * FROM mysql_users;

We left most fields with the default value. The most important fields we configured are :

username
password
default_hostgroup

The meaning of username and password should be very clear. default_hostgroup is the hostgroup that will be used to send traffic generated by that specific user if there are no matching query rules for a specific query (more details later on).
Again, load configuration to runtime to make it live, and save it to disk to make it persistent across the restart.

Admin> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)Admin> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.01 sec)

We can now try to connect from a different terminal:

vagrant@ubuntu-14:~$ mysql -u msandbox -pmsandbox -h 127.0.0.1 -P6033 -e "SELECT 1"
Warning: Using a password on the command line interface can be insecure.
+---+
| 1 |
+---+
| 1 |
+---+vagrant@ubuntu-14:~$ mysql -u msandbox -pmsandbox -h 127.0.0.1 -P6033 -e "SELECT @@port"
Warning: Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|  21891 |
+--------+

It seems it worked, and not surprisingly the query was sent to the server listening on port 21891, the master because it is configured on hostgroup1 and is the default for user msandbox.

Functional tests
Now we can try some “benchmark” to verify that ProxySQL is functional.
Assuming you already created sysbench table, you can run a load test using:

vagrant@ubuntu-14:~/sysbench/sysbench-0.5/sysbench$ ./sysbench --report-interval=5 --num-threads=4 --num-requests=0 --max-time=20 --test=tests/db/oltp.lua --mysql-user='msandbox' --mysql-password='msandbox' --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 run[ output omitted ]

All run correctly through ProxySQL. Does ProxySQL exports metrics about what was running? Yes…
For older versions of sysbench, report-interval should be removed and — db-ps-mode=disable added.

sysbench — num-threads=4 — max-requests=0 — max-time=20 — test=tests/db/oltp.lua — mysql-user=’msandbox’ — mysql-password=’msandbox’ — oltp-table-size=10000 — mysql-host=127.0.0.1 — mysql-port=6033 — db-ps-mode=disable run

[ output omitted ]

ProxySQL Statistics

ProxySQL collects a lot of real-time statistics in the stats schema:

Admin> SHOW SCHEMAS;

Admin> SHOW TABLES FROM stats;

A lot of tables are present in the stats schema. We will analyze all.

stats.stats_mysql_connection_pool

Admin> SELECT * FROM stats.stats_mysql_connection_pool;

currently, when a server is removed (completely removed, or moved away from a hostgroup), it is internally marked as OFFLINE_HARD and not really removed. This is why it shows server on port 21892 as OFFLINE_HARD for hostgroup1.
This table returns a lot of information about the traffic sent to each server. As expected, all traffic was sent to the server on port 21891, the master.

stats_mysql_commands_counters

What type of queries was exactly? Table stats_mysql_commands_counters answer this question:

Admin> SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;

Table stats_mysql_commands_counters returns detailed information about the type of statements executed and the distribution of execution time!

stats_mysql_query_digest

Table stats_mysql_commands_counters provides very useful information. Can we get more details about the query that were executed? Table stats_mysql_query_digest helps in this

Admin> SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC;

Too much information makes it hard to format it here. Let get only important metrics:

Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

All traffic is sent to hostgroup1. Let’s assume that now we want to send specific queries to slaves…

MySQL Query Rules

Table mysql_query_rules has a lot of fields and it is a very powerful vehicle to control the traffic passing through ProxySQL. Its table definition is as follows:

Admin> SHOW CREATE TABLE mysql_query_rules\G

We can now configure ProxySQL to send the top 2 queries to slaves, and everything else to the masters

Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'msandbox','^SELECT c FROM sbtest1 WHERE id=\?$',2,1);
Query OK, 1 row affected (0.00 secAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (20,1,'msandbox','DISTINCT c FROM sbtest1',2,1);
Query OK, 1 row affected (0.00 sec)

Few notes:

query rules are processed ordered by rule_id
only rules that have active=1 are processed. Because query rules are a very powerful tool and if misconfigured can lead to difficult debugging (we all love regex, right?), by default active is 0 (active=0). You should double-check rules regexes before enabling them!
the first rule example uses caret (^) and dollar ($): these are special regex characters that mark the beginning and the end of a pattern. In that case, it means that match_digest or match_pattern should completely match the query
by the contrast of the first rule example, the second rule example doesn’t use caret or dollar: the match could be anywhere in the query
pay a lot of attention to regex to avoid that some rule matches what it shouldn’t!
you probably notice that the question mark is escaped. It has a special meaning in the regex, so as said, pay really a lot of attention to regex syntax!
apply=1 means that no further rules are checked if there is a match

Table mysql_query_rules looks like:

Admin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username='msandbox' ORDER BY rule_id;

For these 2 specific rules, queries will be sent to slaves. If no rules match a query, default_hostgroup applies (that is 1 for user msandbox).
Next, let’s reset the content of the table stats_mysql_query_digest. To achieve this we can simply run any query against stats_mysql_query_digest_reset, for example:

SELECT * FROM stats_mysql_query_digest_reset LIMIT 1;

Querying stats_mysql_query_digest_reset allows to atomically get the content of the stats_mysql_query_digest table, and truncate it!
Now we can load the query rules at runtime :

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

And finally, we re-execute the sysbench load:

vagrant@ubuntu-14:~/sysbench/sysbench-0.5/sysbench$ ./sysbench --report-interval=5 --num-threads=4 --max-requests=0 --max-time=20 --test=tests/db/oltp.lua --mysql-user='msandbox' --mysql-password='msandbox' --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 run

And let’s verify the content of table stats_mysql_query_digest :

Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

As expected, the top 2 queries are not sent to hostgroup2 (the slaves). Table stats_mysql_query_digest allows to aggregate results, for example:

Admin> SELECT hostgroup hg, SUM(sum_time), SUM(count_star) FROM stats_mysql_query_digest GROUP BY hostgroup;

Let us explore the basic Query Routing (Read/Write split) for effective load sharing. We have set up 4 nodes to make the architecture.

node1, Master
node2 , read-replica-one
node3, read-replica-two
node4 , ProxySQL

The main advantages of using ProxySQL is, it is designed to run continuously without needing to be restarted. Most configuration can be done at runtime using queries similar to SQL statements and it is more lightweight.

MySQL Replication setup :

Configuring MySQL master-slave replication is outside the scope of this tutorial, we already have nodes with replication running.

Before entering to the admin interface of ProxySQL, create one application user with all privileges required to your application and one monitoring user at every MySQL DB server.

mysql> CREATE USER 'sysbench'@'    .0.%' IDENTIFIED BY 'sysbench';
mysql> GRANT ALL PRIVILEGES on *.* TO 'sysbench'@'172.17.0.%';
mysql> CREATE USER  'monitor'@'172.17.0.%' IDENTIFIED BY 'monitor';
mysql> GRANT USAGE,REPLICATION CLIENT on *.* TO 'monitor'@'172.17.0.%';mysql> FLUSH PRIVILEGES;

Start proxysql service

$ service proxysql start

Now connect to ProxySQL admin interface to start with configuration :

$ mysql -u admin -padmin -h 127.0.0.1 -P6032

Configure Backends :

ProxySQL uses the concept of hostgroup. A hostgroup is a group of the host with logical functionalities.

In this setup, we have used just need 2 hostgroups:

hostgroup 0 for the master [Used for Write queries ]

hostgroup 1 for the slaves [Used for Read Queries ]

Apart from this, we can also have one analytical server as a slave of the same master and we can assign new hostgroup id for the server and redirect all analytical related queries (long-running) at this host.

Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (0,'172.17.0.1',3306);Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'172.17.0.2',3306);Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'172.17.0.3',3306);Admin> INSERT INTO  mysql_replication_hostgroups VALUES (0,1,'production');Admin > SELECT * FROM mysql_replication_hostgroups;
+------------------+------------------+------------+
| writer_hostgroup | reader_hostgroup | comment    |
+------------------+------------------+------------+
| 0                | 1                | production |
+------------------+------------------+------------+Admin> LOAD MYSQL SERVERS TO RUNTIME; 
Admin> SAVE MYSQL SERVERS TO DISK;

Note: When we load MYSQL SERVERS, Our writer host also get configured in reader hostgroup automatically by ProxySQL to handle all those queries which are redirected to reader hostgroup in case no slaves are online.

So bonus point here is we can decrease the weightage assigned to master servers inside mysql_server table for reader hostgroup, so that our most of the read queries will go on the server which has a higher weight.

UPDATE mysql_servers SET weight=200 WHERE hostgroup_id=1 AND hostname='172.17.0.1';Admin> SELECT hostgroup_id,hostname,port,status,weight FROM mysql_servers;

Configure User :

monitor user will continuously check the status of the backend in the specified interval. sysbench is user created for the application.

Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Admin> SAVE MYSQL VARIABLES TO DISK;Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES (sysbench,sysbench,1);
Admin> SELECT username,password,active,default_hostgroup,default_schema,max_connections,max_connections FROM mysql_users;

Admin> LOAD MYSQL USERS TO RUNTIME;
Admin> SAVE MYSQL USERS TO DISK;

Configure monitoring :

ProxySQL constantly monitors the servers it has configured. To do so, it is important to configure some interval and timeout variables ( in milliseconds).

Admin> UPDATE global_variables SET variable_value=2000 WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');Admin> UPDATE global_variables SET variable_value = 1000 where variable_name = 'mysql-monitor_connect_timeout';
Admin> UPDATE global_variables SET variable_value = 500 where variable_name = 'mysql-monitor_ping_timeout';Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Admin> SAVE MYSQL VARIABLES TO DISK;

Monitor module regularly checks replication lag (using seconds_behind_master) if a server has max_replication_lag set to a non-zero value.

With below configuration, servers will only be shunned in case replication delay exceeds 60 seconds ( 1 min) behind the master.

Admin> UPDATE mysql_servers SET max_replication_lag=60;
Query OK, 1 row affected (0.00 sec)

There are also other important things in the monitoring module which we can configure as per our need.

Configure Query Rules :

To send all SELECT queries on a slave ( based on Regex ).

Admin> INSERT INTO mysql_query_rules (active, match_digest, destination_hostgroup, apply) VALUES (1, '^SELECT.*', 1, 0);
Admin> INSERT INTO mysql_query_rules (active, match_digest, destination_hostgroup, apply) VALUES (1, '^SELECT.*FOR UPDATE', 0, 1);
Admin> SELECT active, match_pattern, destination_hostgroup, apply FROM mysql_query_rules;Admin> SELECT rule_id, match_digest,destination_hostgroup hg_id, apply FROM mysql_query_rules WHERE active=1;

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Admin> SAVE MYSQL QUERY RULES TO DISK;

When the Query Processor scans the query rules trying to find a match with no success and it reaches the end, it will apply the default_hostgroup for the specific user according to mysql_users entry.
In our case, user sysbench has a default_hostgroup=0, therefore any query not matching the above rules [Eg ALL WRITES ] will be sent to hostgroup 0 [Master]. Below stats, tables are used to validate if your query rules getting used by incoming traffic.

SELECT rule_id, hits, destination_hostgroup hg FROM mysql_query_rules NATURAL JOIN stats_mysql_query_rules;

We can also redirect some specific pattern queries by using digest in stats_mysql_query_digest

Validate the DB Connection :

The application will connect to 6033 port on host 172.17.0.4 of ProxySQL to send DB traffic.

ProxySQL-Host$ mysql -u sysbench -psysbench -h 127.0.0.1 -P6033 -e "SELECT @@server_id"
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+

Check Backend Status :

It shows ProxySQL is able to successfully connect to all backends.

mysql> select * from monitor.mysql_server_ping_log order by time_start_us desc limit 3;

I have executed some sysbench test on the cluster to check query distributions . Below the table of ProxySQL shows the number of queries executed per host.

Admin > select hostgroup,srv_host,status,Queries,Bytes_data_sent,Latency_us from stats_mysql_connection_pool where hostgroup in (0,1);

If any of your servers goes unreachable from any hostgroup, the status gets changed from ONLINE to SHUNNED.
It means ProxySQL won't send any queries to that host until it comes back to ONLINE.
We can also take any server offline for maintenance. To disable a backend server it is required to change its status to OFFLINE_SOFT (Gracefully disabling a backend server) or OFFLINE_HARD(Immediately disabling a backend server.) In this case, no new traffic will be sent to the node.

Admin> UPDATE mysql_servers SET status='OFFLINE_SOFT' WHERE hostname='172.17.0.2';
Query OK, 1 row affected (0.00 sec)

Okay, you still worried about reading stale data from the slave ?
Then do not worry, ProxySQL is coming up with a new feature to make sure your application gets the latest updated data. Currently, this feature is available only with row-based replication with GTID enabled.

Performance Report:

Performance reports for all the nodes

Conclusion:

With ProxySQL you can have as many different dedicated databases based on the type of data relations. Which now brings more visibility and effectiveness in your MySQL management. Also, think of a situation where one of the resources is affected, but other resources will still be up and working fine.

References:

Happy Coding!!!