Mysql read/write splitting with Proxysql

Question

Mysql read/write splitting with Proxysql

ProxySQL is the first open source proxy in the MySQL ecosystem ,which can provide High Availability and high performance with no changes in the application. It has lot of features which are under development . Few of them are

– read/write split
– query caching
– queries filtering and rewrite
– transparent reconnect
– connection pooling
– integration with clustering solution like MHA for planned and/or automatic failover
– support for master-slave setups (simple MySQL replication) or multi-masters setup like Percona Xtradb cluster
– Advanced configuration with zero downtime

One of the most popular use case is mysql read/write splitting, we haven’t seen this feature on any other stable open source applications yet.

Mysql read/write splitting Use case

MySQL Replication is used in a variety of use cases – scale out read workloads, provide high availability, and offload backups . But there is a drawback – the application needs to be able to send writes to the master only . If the application updates one of the slaves by mistake, it would result in data inconsistency. So application has to be designed considering this drawback , it should define two hostgroups, one for writes and other for reads and route the queries accordingly.

With Proxysql, Applications can send queries to database server without worrying about the type of the query. It appears to the clients of ProxySql as if there is a single database server behind Proxysql. Proxysql will split the statements such that write statements will be sent to the current master server in the replication cluster and read statements will be balanced across the rest of the slave servers. Definitely, such a setup needs additional configuration, but it is very simple to do it with ProxySQL . We will explain how to configure ProxySql for mysql read/write splitting

How to Install Proxysql?

For testing we will install Proxysql On Centos7 , make sure you have installed following packages

Automake
bzip2
cmake
make
g++
gcc
git
openssl
openssl-devel
patch

You can install these packages using yum

#Install using yum
#yum install automake bzip2 make cmake openssl openssl-devel

Once installed , you can get the source file from Github and build ProxySQL

#git clone https://github.com/sysown/proxysql.git

Now we can build ProxySQL

#cd proxysql
#make
#make install

Once installed, you should be able to see the binaries at “/usr/local/bin/proxysql” and configuration file at “/etc/proxysql.cnf”

How to start Proxysql?

ProxySQL is a daemon ran by an angel process. The angel process monitors the daemon and restarts it when it has crashed. Proxysql accepts incoming traffic from clients and forwards to correct backends after applying the query routing rules.

We can start Proxysql using following command on Centos7

Systemctl start proxysql

Check the status using

# systemctl status proxysql
● proxysql.service – High Performance Advanced Proxy for MySQL
Loaded: loaded (/usr/lib/systemd/system/proxysql.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2016-12-16 11:24:03 UTC; 3 weeks 6 days ago
Main PID: 21325 (proxysql)
Memory: 57.1M
CGroup: /system.slice/proxysql.service
└─21325 /usr/local/bin/proxysql -f

Checking the content of proxysql’s datadir, we can identify: a database file

# ls -la /var/lib/proxysql
total 80
drwxr-xr-x. 2 root root 24 Jan 5 13:22 .
drwxr-xr-x. 47 root root 4096 Jan 12 03:45 ..
-rw-r–r–. 1 root root 77824 Jan 5 13:22 proxysql.db

Note that the database file is the file that stores all the configuration related to proxysql. If this file is present, the config file is not parsed.

By default, Proxysql Binds to two Ports 6032 and 6033. In this 6032, is admin port and 6033 is the one which accepts incoming connections from clients

These ports are configurable in the proxysql.conf file, below section from config file defines the admin interface and ports

Admin_variables=
{
admin_credentials=”admin:admin”
mysql_ifaces=”127.0.0.1:6032;/tmp/proxysql_admin.sock”
}

Now let us connect the Proxysql admin interface and configure it

The “main” database represents the in-memory configuration and “disk” represents the on-disk configuration which is the persitant configuration . Whenever ProxySQL restarts, it loads the configuration from this file

You can see there are 13 tables present inside proxysql, for our read/write splitting use case we will use the following tables only

Mysql_servers
mysql_users
mysql_query_rules

Let us see how we configure proxysql to handle read/write splitting

1. Define backends

Consider we have a Mysql master-slave replication setup with a master and two slaves

192.168.2.10 – Master
192.168.2.11 – Slave1
192.168.2.12 – Slave2

We have two kind of hostgroups here, one for reads and one for writes .

HostGroup 0 – Reads
HostGroup 1 – Writes

So we will insert these mysql backend server IPs into “mysql_servers” table under correct HostGroups.

Mysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,’192.168.2.11′,3306);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,’192.168.2.12′,3306);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1,’192.168.2.10′,3306);
Query OK, 1 row affected (0.01 sec)
MySQL [admin]> SELECT * FROM mysql_servers;
+————–+————–+——+——–+——–+————-+—————–+———————+———+—————-+———+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+————–+————–+——+——–+——–+————-+—————–+———————+———+—————-+———+
| 0 | 192.168.2.11 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 0 | 192.168.2.12 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.2.10 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+————–+————–+——+——–+——–+————-+—————–+———————+———+—————-+———+

At this time ,backend servers are only configured ,now we need to activate them using following command

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

Now we need to make those config entries persistant by loading those rules from memory to disk (/var/lib/proxysql.db)

Mysql> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.00 sec)

We have added all the backend servers to the Proxysql configuration, under two Hostgroups. Now let us add the query rules which defines which query goes to which backend server

There is a table “mysql_query_rules” inside Proxysql, we will add all the query rules into this table.

We need to write rules for our mysql read/write splt use case , following are our considerations

– If the mysql command starts with any of the common write statements , we will route those traffic to Hostgroup 1.
– If the mysql query starts with any of the common read statements , we will route those traffic to Hostgroup 0
– If the query doesnt match any of the above , it will be routed to HostGroup 0

Now let us insert the rules to “mysql_query_rules”, we will start with rules for reads

INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,’^select’,0,0);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,’^show’,0,1);

Rules to match write query patterns

INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, ‘^SELECT.*FOR UPDATE’, 1, 1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,’^update’,1,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,’^truncate’,1,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,’^savepoint’,1,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,’^revoke’,1,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,’^restore’,1,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,’^reset’,1,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,’^repair’,1,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,’^rename’,1,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,’^purge’,1,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,’^lock’,1,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,’^kill’,1,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,’^grant’,1,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,’^load’,1,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,’^flush’,1,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,’^alter’,1,1);

Now we will load these rules to memory and then save to disk

MySQL [admin]> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
MySQL [admin]> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (1.67 sec)

Now we will add the users , which clients use to connect to the Proxysql. You need to make sure same user with matching password exists on all backend servers with necessary privileges

MySQL [admin]> INSERT INTO mysql_users (username,password) VALUES (‘myuser’,’mypass’);
Query OK, 1 row affected (0.00 sec)
MySQL [admin]> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
MySQL [admin]> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (1.34 sec)

Make sure that user “myuser” exists on all mysql backends with same password. If not client connections will fail.

Now we have setup everything to test the mysql read/write split use case. Let us test the proxy by sending some requests and verify its being routed to correct backends

Mysql -u umyuser -pmypass -h 127.0.0.1 -P6033 -e “SELECT @@hostname”
Warning: Using a password on the command line interface can be insecure.
+————+
| @@hostname |
+————+
| readho |
+————+

To test the write queries, run a create database statement on proxysql

Mysql -u umyuser -pmypass -h 127.0.0.1 -P6033 -e “CREATE DATABASE test1”

When you check on the server you will see database is created on master server. ProxySQL has routed the query correctly to correct backend server. There are much more use cases with the ProxySQL. Please check their github repo at here and read the complete documentation.

Devops

Author: , 0000-00-00

Source