How to Run ProxySQL Against MySQL for Monitoring on CentOS 7

How to Run ProxySQL Against MySQL for Monitoring on CentOS 7

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

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.

Check out these top 3 VPS services:

Kamatera
$4.00 /mo
Starting price
Visit Kamatera
Rating based on expert review
  • User Friendly
    3.5
  • Support
    3.0
  • Features
    3.9
  • Reliability
    4.0
  • Pricing
    4.3
Hostinger
$2.99 /mo
Starting price
Visit Hostinger
Rating based on expert review
  • User Friendly
    4.7
  • Support
    4.7
  • Features
    4.8
  • Reliability
    4.8
  • Pricing
    4.7
Liquid Web
$5.00 /mo
Starting price
Visit Liquid Web
Rating based on expert review
  • User Friendly
    3.8
  • Support
    4.8
  • Features
    4.5
  • Reliability
    4.6
  • Pricing
    3.8
  • Do you need the best VPS? Read about our different offers.
 

How to Install phpMyAdmin on a CentOS 7 VPS or Dedicated Server

Learn how to setup phpMyAdmin on your CentOs 7 VPS server and manage your MySQL/
2 min read
Max Ostryzhko
Max Ostryzhko
Senior Web Developer, HostAdvice CTO

How to Create New MySQL Users Accounts and Manage MySQL Privileges

This tutorial will help you create new MySQL (database) user accounts and grant
4 min read
David Malcom
David Malcom
Author

How to Install MYSQL 8.0 and Create a Database on a CentOS 7 Linux VPS or Dedicated Server

MySQL is one of the mostly utilized open source database platform globally. This
3 min read
Eliran Ouzan
Eliran Ouzan
Web Designer & Hosting Expert

Part Two: How to Monitor Nginx using Elastic Stack on a CentOS 7 VPS or Dedicated Server

This tutorial will explain how to use the different components of the Elastic St
3 min read
Idan Cohen
Idan Cohen
Marketing Expert
HostAdvice.com provides professional web hosting reviews fully independent of any other entity. Our reviews are unbiased, honest, and apply the same evaluation standards to all those reviewed. While monetary compensation is received from a few of the companies listed on this site, compensation of services and products have no influence on the direction or conclusions of our reviews. Nor does the compensation influence our rankings for certain host companies. This compensation covers account purchasing costs, testing costs and royalties paid to reviewers.
Click to go to the top of the page
Go To Top