About
ProxySQL is high-performance MySQL proxy that is used for better control over database traffic and queries that are issued against the databases.
It comes with a lot of benefits that are mainly focused on better load balancing, MySQL traffic and security.
Also look at official ProxySQL site or this article.
Installation
The installation of ProxySQL is quite simple. But it needs a client (mariadb or community-mysql) for executing the commands and for any operations on your ProxySQL server. Thus you need to install some client package along with ProxySQL. In this case we'll use mariadb.
$ sudo yum install proxysql mariadb
Or with dnf in Fedora 22 and later versions:
$ sudo dnf install proxysql mariadb
The ProxySQL server is turned off and disabled by default. You can enable its start during the boot using following command:
$ sudo systemctl enable proxysql
You can start the ProxySQL server only when necessary as follows.
$ sudo systemctl start proxysql
Firewall
If needed, allow tcp port for ProxySQL communication
$ sudo firewall-cmd --permanent --add-port=6033/tcp
Reload it
$ sudo firewall-cmd --reload
Start
As mentioned above, to do any kind of operation for ProxySQL you need to have a database client installed.
In this case we'll be using mariadb (mysql is alias for mariadb, so it's the same)
$ mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQL> '
-u -> defines the user you want to log as
-p<password> -> password
-h -> hostname
-P -> listening port (ProxySQL needs two ports: 6033, on which it listens for traffic and 6032, which works as a gateway for managing ProxySQL)
--prompt=<prompt> -> defines client's prompt
For more information you can see official mini-HOWTO
Update admin's credentials
It's a good habit to change admin's passwords after first login. To do that just simply run this command after logging into ProxySQL.
ProxySQL> UPDATE global_variables SET variable_value='admin:123' WHERE variable_name='admin-admin_credentials'; Query OK, 1 row affected (0.001 sec)
For any operation that updates or inserts something into ProxySQL you need to load it into memory and store it on a disk. This is how to do it in this case:
LOAD: ProxySQL> LOAD ADMIN VARIABLES TO RUNTIME; Query OK, 0 rows affected (0.001 sec)
SAVE: ProxySQL> SAVE ADMIN VARIABLES TO DISK; Query OK, 33 rows affected (0.011 sec)
Monitoring other database servers
One of the things that are really common in ProxySQL is monitoring one or more database servers. For this purpose you need to configure some things, so the ProxySQL knows about the databases and vice-versa.
On ProxySQL side, these information are stored in mysql_servers table.
In case of master-slave cluster the slaves (read only) have their own tabel named mysql_replication_hostgroups which I won't use here.
Also there is a table that is used for query rules between ProxySQL and the databases called mysql_query_rules.
If you didn't inserted anything inside these tables, they should be empty by default.
ProxySQL> SELECT * FROM mysql_servers; Empty set (0.00 sec)
ProxySQL> SELECT * from mysql_replication_hostgroups; Empty set (0.00 sec)
ProxySQL> SELECT * from mysql_query_rules; Empty set (0.00 sec)
Create backends servers
In this case we need to have 2 running machines used as mariadb-servers. Let's call them SERVER1 and SERVER2 for the rest of this article.
If not specified, the commands are applied for both of the servers.
Firstly you need to install mariadb-server and mariadb client.
$ sudo dnf install mariadb-server mariadb
Then start the client's service
$ sudo systemctl start mariadb
Log into the server as root
$ mysql -u root -p123
Then run these MySQL queries to create monitor user
MariaDB [(none)]> CREATE USER 'monitor'@'%' IDENTIFIED BY '1234'; MariaDB [(none)]> GRANT SELECT ON sys.* TO 'monitor'@'%'; MariaDB [(none)]> FLUSH PRIVILEGES;
Add backends to ProxySQL
We need the ProxySQL to know about these servers too, so we add them to the mysql_servers table.
In my case SERVER1's IP = 10.0.137.109 and SERVER2's IP = 10.0.138.18.
ProxySQL> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.0.137.109',3306); Query OK, 1 row affected (0.001 sec)
ProxySQL> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.0.138.18',3306); Query OK, 1 row affected (0.001 sec)
Then the table should look like this (except the hostnames)
ProxySQL> SELECT hostgroup_id,hostname,port,status FROM mysql_servers; +--------------+--------------+------+--------+ | hostgroup_id | hostname | port | status | +--------------+--------------+------+--------+ | 1 | 10.0.137.109 | 3306 | ONLINE | | 1 | 10.0.138.18 | 3306 | ONLINE | +--------------+--------------+------+--------+ 2 rows in set (0.001 sec)
Don't forget to LOAD and SAVE it.
ProxySQL> LOAD MYSQL SERVERS TO RUNTIME; Query OK, 0 rows affected (0.006 sec)
ProxySQL> SAVE MYSQL SERVERS TO DISK; Query OK, 0 rows affected (0.050 sec)
Update monitor user's credentials
The monitor user is created by default. You can change his atributes (e.g. password, username) just make sure it matches the one you've created on mariadb-servers.
Change them with these simple queries:
USERNAME: ProxySQL> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'; Query OK, 1 row affected (0.003 sec)
PASSWORD: ProxySQL> UPDATE global_variables SET variable_value='1234' WHERE variable_name='mysql-monitor_password'; Query OK, 1 row affected (0.002 sec)
Always LOAD and SAVE:
ProxySQL> LOAD MYSQL VARIABLES TO RUNTIME; Query OK, 0 rows affected (0.003 sec)
ProxySQL> SAVE MYSQL VARIABLES TO DISK; Query OK, 140 rows affected (0.015 sec)
Update query rules
Also you can change the query intervals for monitor user
They are stored as global variables.
In this case we'll change only connect and ping interval
ProxySQL> UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval');
Query OK, 2 rows affected (0.001 sec)
You can also check if it worked executing this query
ProxySQL> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%'; ... | mysql-monitor_history | 600000 | | mysql-monitor_connect_interval | 2000 | | mysql-monitor_ping_interval | 2000 | | mysql-monitor_read_only_interval | 2000 | | mysql-monitor_read_only_timeout | 500 | +--------------------------------------------------------------+----------------+
LOAD and SAVE:
ProxySQL> LOAD MYSQL VARIABLES TO RUNTIME; ProxySQL> SAVE MYSQL VARIABLES TO DISK;
Test the monitor connection
If you've followed the instructions above, you should be able to pass this simple connection test.
The monitor user stores the connections in the table named monitor.mysql_server_connect_log.
So we can simply select the data:
ProxySQL> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10; If you see both servers hostnames, and the NULL value in the connect_error column, it means the connection works.
Test the user login in database server
This test is performed only on SERVER1 and ProxySQL.
Configure SERVER1
Log into the database server locally:
SERVER1 $ mysql -u root -p123
Create new testing database:
MariaDB [(none)]> create database test_db; Query OK, 1 row affected (0.001 sec)
Create test user:
MariaDB [(none)]> CREATE USER 'testuser'@'%' IDENTIFIED BY '123'; Query OK, 0 rows affected (0.031 sec)
Give him access to newly created database:
MariaDB [(none)]> GRANT ALL ON test_db.* TO 'testuser'@'%'; Query OK, 0 rows affected (0.003 sec)
Apply it:
MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.001 sec)
Configure ProxySQL
Add the user into the mysql_user table:
ProxySQL> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('testuser','123',1);
Query OK, 1 row affected (0.001 sec)
LOAD and SAVE it:
ProxySQL> LOAD MYSQL USERS TO RUNTIME; Query OK, 0 rows affected (0.001 sec) ProxySQL> SAVE MYSQL USERS TO DISK; Query OK, 0 rows affected (0.020 sec)
Now exit the admin ProxySQL account:
ProxySQL> EXIT
From the ProxySQL machine run client with testuser credentials (port is now 6033 for communication):
mysql -u testuser -p123 -h 127.0.0.1 -P6033
Test the hostname:
MySQL [(none)]> select @@hostname; +---------------------------------------------------+ | @@hostname | +---------------------------------------------------+ | 10.0.137.109 | +---------------------------------------------------+ 1 row in set (0.003 sec)
See what databases do test_user can access:
MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | test_db | | information_schema | +--------------------+