Menu Close

Understanding ProxySQL

In this post, I will cover a bit of ProxySQL. ProxySQL is a proxy which implements MySQL protocol, allowing it to do things that other proxies cannot do. ProxySQL is gaining a lot of traction nowadays and it’s capable to integrate with a variety of products from MySQL ecosystems, such as replication (master – slave / master – master), Percona XtraDB Cluster and Group Replication.
One of its many functionalities (which IMHO makes it awesome) is the ability to do read/write split seamless to the application. You can start sending reads to your slave without doing a single line of code.

In this article, I will cover a few important points to understand how it works.

Instalation

The instalation is easy, you can do it by:

  1. Downloading the corresponding package for your OS from its official github repo https://github.com/sysown/proxysql/releases
    sudo yum install https://github.com/sysown/proxysql/releases/download/v1.4.9/proxysql-1.4.9-1-centos7.x86_64.rpm
    
  2. Downloading the corresponding package for your OS from Percona website https://www.percona.com/downloads/proxysql/
    sudo yum install https://github.com/sysown/proxysql/releases/download/v1.4.9/proxysql-1.4.9-1-centos7.x86_64.rpm
  3. Installing Percona repository for your OS https://www.percona.com/doc/percona-server/LATEST/installation/apt_repo.html or https://www.percona.com/doc/percona-server/LATEST/installation/yum_repo.html (preferable):
    sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
    sudo yum install proxysql

Now we just need to start the service

sudo service proxysql start 

Interfaces

ProxySQL splits application interface from the admin interface. It will listen on 2 network ports. Admin will be on 6032 and application will listen on 6033 (reverse of 3306 ).

Layers

Other important part to understand how the proxy works is to understand its layers. I am gonna show you a diagram that can be found on its official documentation:

+-------------------------+
|      1. RUNTIME         |   
+-------------------------+
       /|\          |
        |           |
        |           |
        |          \|/
+-------------------------+
|     2.  MEMORY          |   
+-------------------------+ 
       /|\          |      
        |           |      
        |           |       
        |          \|/      
+-------------------------+  
|     3.   DISK           |  
+-------------------------+  

ProxySQL will always read information from 1.Runtime layer, which is stored in memory.
Every time we connect to the admin port (6032) we are manipulating information from layer 2.Memory. As the name infers, its also stored in memory.
We then have the layer 3.Disk. As the other two layers are stored in memory, we need a layer to persist information across service/server restarts.

What is the benefic of this layout?
It allows us to manipulate different areas and apply the changes at once. We can think about how a transaction works, where we run multiple queries and commit them at once. When we alter something, we will be manipulating the Memory layer, then we will run a command LOAD MYSQL [SERVERS | USERS | QUERY RULES] TO RUNTIME to load this information to runtime and we will save the information to disk layer by issuing SAVE MYSQL [SERVERS | USERS | QUERY RULES] TO DISK.

Hostgroups

ProxySQL group servers in something named hostgroup. In a topology which we have a master and two slaves, we will create a hostgroup(HG) 1 and specify that our master is part of that HG and we will create a HG 2 and specify that both slaves belong to that HG. Hostgroup creation is done at the time we specify servers on mysql_servers table. There is not fixed enumeration, you can create your HG with any ID you want.

Later we will configure user and queries to identify if the query coming in is a read it should be answered by one of the servers from HG 2 (where we configured our slaves). If the query is not a read, then our server on HG 1 will receive it.

Authentication

ProxySQL has functionalities like firewall, in which it has the capability of blocking a query even before it reaches our backend server. To do it it’s required to have the user authentication module also present on the proxy side. So we will be required to create all users that we wish to connect via ProxySQL also create on the proxy side.

 

Now that you understand a bit of the basic of how ProxySQL works, you can start playing with it.

Liked? Help and Share!