Introduction

ProxySQL is a high-performance MySQL proxy that serves to create high availability on your databases. It does this by multiplexing the hundreds of thousands of concurrent requests onto backend database servers. Advantages offered by ProxySQL include:

Query routing to ensure faster process and response
Firewall feature to prevent and take action on harmful queries (SQL injection)
Failover support by collaborating with other database tools
Query caching for faster response

This requires you to install and configure ProxySQL to communicate with the MySQL server. The connection to the multiple backend servers has to be with a dedicated user.

Before You Get Started

A VPS/Dedicated Server running CentOS 7.
A non-root user with sudo privileges.
ProxySQL already installed and running. You can learn How to Install and Configure ProxySQL on CentOS 7.
Multiple MySQL servers configured as a replication group.

Special Note: The MySQL instances may be running on separate MySQL servers or you can be able to create the MySQL server instances by installing MySQL sandbox and creating them on one host. You can learn How to Install and Create MySQL sandboxes here.

Steps in the Process

We need to confirm whether there is any data in the database prior to anything else.

If so the tables need to be cleared:

$ mysql -u admin -p -h 127.0.0.1 -P6032 --prompt='Admin> '  >--default-auth=mysql_native_password

Admin> SELECT * FROM mysql_servers;

Admin> SELECT * FROM mysql_replication_hostgroups;
Admin> SELECT * FROM mysql_query_rules;

You can use the following command to delete any found data by using the following command.

Admin>TRUNCATETABLE tablename;

Create a sandbox with an all-masters replication topology.

$ dbdeployer deploy --topology=group replication 8.0.12

To find out the ports running on the mysql sandbox nodes, go to the sandbox directory and check the sandbox nodes status:

$ cd ~/sandboxes
$ ./status_all

REPLICATION  /home/linuxuser/sandboxes/all-masters_msb_8_0_12
node1 : node1 on  -        port  21213 (21338)
node2 : node2 on  -        port  21214 (21339)
node3 : node3 off  -        port  21215 (21340)

You can go ahead and start the mysql sandboxes and add them to the mysql_servers tables in the proxySQL prompt:

$ mysql -u admin -p -h127.0.0.1 -P6032 --prompt='Admin>' --default-auth=mysql_native_password

Admin> INSERTINTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'127.0.0.1',21213);
Admin> INSERTINTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'127.0.0.1',21214);
Admin> INSERTINTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'127.0.0.1',21215);
Admin> SELECT hostgroup_id, hostname, port, statusFROM mysql_servers

+--------------+-----------+-------+--------+
| hostgroup_id | hostname  | port  | status |
+--------------+-----------+-------+--------+
| 1            | 127.0.0.1 | 21213 | ONLINE |
| 1            | 127.0.0.1 | 21214 | ONLINE |
| 1            | 127.0.0.1 | 21215 | ONLINE |
+--------------+-----------+-------+--------+

To enable ProxySQL monitor the servers it has been configured to, we first need to add the users required to monitor the backend.

Special Note: You need to have added a user to one of the mysql nodes in your sandbox.

Admin> UPDATE global_variables SET variable_value='administrator'WHERE variable_name='mysql-monitor_username';
Admin> UPDATE global_variables SET variable_value='admin123'WHERE variable_name='mysql-monitor_password';

We now need to configure the intervals for ProxySQL to monitor our hosts.

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');

>The variables created need to be loaded to runtime, which is the in memory data structure used by ProxySQL’s threads that handle requests. The variables also need to be saved to ProxySQL permanent SQLite3 database; disk.

Admin> LOAD MYSQL VARIABLESTO RUNTIME;
Admin> SAVE MYSQL VARIABLES TO DISK;

All the information being monitored can be accessed on the tables under the monitor database.

Admin> SHOWDATABASES;

+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+

Admin> SHOWTABLESFROM monitor;

+------------------------------------+
| tables                             |
+------------------------------------+
| mysql_server_connect_log           |
| mysql_server_group_replication_log |
| mysql_server_ping_log              |
| mysql_server_read_only_log         |
| mysql_server_replication_lag_log   |
+------------------------------------+

Admin> SELECT * FROM mysql_server_ping_log;

Conclusion

The monitor will enable you to check the current health status of the databases running in the background. Information such as connection status of the hosts on each port running or connection state to a server during query execution will all be found here.