Configure ProxySQL on Ubuntu 16.04

We use ProxySQL as a gateway to access MySQL Group Replication nodes. ProxySQL is a high-performance SQL proxy. ProxySQL runs as a daemon watched by a monitoring process. The process monitors the daemon and restarts it in case of a crash to minimize downtime. The daemon accepts incoming traffic from MySQL clients and forwards it to backend MySQL servers.

Node Configuration

Node IP Private Description
Node 1 172.16.0.1 MySQL 1 (Master)
Node 2 172.16.0.2 MySQL 2 (Master)
Node 3 172.16.0.3 ProxySQL

1. Install MySQL Client/Server and Open Up The Port in ProxySQL Node

2. Install and Start ProxySQL

Download debian package from https://github.com/sysown/proxysql/releases

1
2
3
4
5
6
root@proxysqlnode:/download# wget https://github.com/sysown/proxysql/releases/download/v1.4.3/proxysql_1.4.3-clickhouse-ubuntu16_amd64.deb
root@proxysqlnode:/download# dpkg -i proxysql_1.4.3-clickhouse-ubuntu16_amd64.deb
root@proxysqlnode:/download# apt-get update 
root@proxysqlnode:/download# service proxysql start
root@proxysqlnode:/download# proxysql --version
ProxySQL version 1.4.3-1-g79a32d3, codename Truls

3. Login to ProxySQL Admin Module

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
root@proxysqlnode:/download# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Admin>

4. Check ProxySQL Databases and Tables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Admin> SHOW DATABASES;
+-----+---------+-------------------------------+
| seq | name    | file                          |
+-----+---------+-------------------------------+
| 0   | main    |                               |
| 2   | disk    | /var/lib/proxysql/proxysql.db |
| 3   | stats   |                               |
| 4   | monitor |                               |
+-----+---------+-------------------------------+
4 rows in set (0.00 sec)

Admin> SHOW TABLES;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| global_variables                           |
| mysql_collations                           |
| mysql_group_replication_hostgroups         |
| mysql_query_rules                          |
| mysql_replication_hostgroups               |
| mysql_servers                              |
| mysql_users                                |
| proxysql_servers                           |
| runtime_checksums_values                   |
| runtime_global_variables                   |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules                  |
| runtime_mysql_replication_hostgroups       |
| runtime_mysql_servers                      |
| runtime_mysql_users                        |
| runtime_proxysql_servers                   |
| runtime_scheduler                          |
| scheduler                                  |
+--------------------------------------------+
18 rows in set (0.00 sec)

5. Add Group Replication Nodes to ProxySQL

We create 2 hostgroups, the 2nd hostgroup is optional.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'172.16.0.1',3306);
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'172.16.0.2',3306);
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'172.16.0.1',3306);
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'172.16.0.2',3306);
Admin> SELECT * FROM mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 172.16.0.1     | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 172.16.0.2     | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 172.16.0.1     | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 172.16.0.2     | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

Admin> LOAD MYSQL SERVERS TO RUNTIME;
Admin> SAVE MYSQL SERVERS TO DISK;
Admin> SELECT * FROM disk.mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 172.16.0.1     | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 172.16.0.2     | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 172.16.0.1     | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 172.16.0.2     | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

6. Create ProxySQL Monitoring User

To enable monitoring of group replication nodes in ProxySQL, create a user with USAGE privilege on any node in the cluster and configure the user in ProxySQL. We only need to create in 1 node because it will be replicated in another node automatically once the group replication runs.

Add a monitoring user on Node 1:

1
2
mysql> CREATE USER 'proxysql'@'%' IDENTIFIED BY 'ProxySQLPassword';
mysql> GRANT USAGE ON *.* TO 'proxysql'@'%';

Add a monitoring user on ProxySQL:

1
2
mysql> CREATE USER 'proxysql'@'%' IDENTIFIED BY 'ProxySQLPassword';
mysql> GRANT USAGE ON *.* TO 'proxysql'@'%';

Configure the monitoring user in ProxySQL. Use LOAD command to load this configuration at runtime. Use SAVE to save these changes to disk so they can persist after ProxySQL is shutdown.

1
2
3
4
Admin> UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_username';
Admin> UPDATE global_variables SET variable_value='ProxySQLPassword' WHERE variable_name='mysql-monitor_password';
Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Admin> SAVE MYSQL VARIABLES TO DISK;

Check the monitoring logs to ensure that monitoring is enabled:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 6;
+----------------+------+------------------+-------------------------+---------------+
| hostname       | port | time_start_us    | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+---------------+
| 172.16.0.2     | 3306 | 1512972003466378 | 497                     | NULL          |
| 172.16.0.1     | 3306 | 1512972003463802 | 365                     | NULL          |
| 172.16.0.2     | 3306 | 1512971993466218 | 396                     | NULL          |
| 172.16.0.1     | 3306 | 1512971993463651 | 794                     | NULL          |
| 172.16.0.2     | 3306 | 1512971983466198 | 208                     | NULL          |
| 172.16.0.1     | 3306 | 1512971983463640 | 209                     | NULL          |
+----------------+------+------------------+-------------------------+---------------+
6 rows in set (0.00 sec)

Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 6;
+----------------+------+------------------+----------------------+------------+
| hostname       | port | time_start_us    | ping_success_time_us | ping_error |
+----------------+------+------------------+----------------------+------------+
| 172.16.0.2     | 3306 | 1512972003466378 | 497                  | NULL       |
| 172.16.0.1     | 3306 | 1512972003463802 | 365                  | NULL       |
| 172.16.0.2     | 3306 | 1512971993466218 | 396                  | NULL       |
| 172.16.0.1     | 3306 | 1512971993463651 | 794                  | NULL       |
| 172.16.0.2     | 3306 | 1512971983466198 | 208                  | NULL       |
| 172.16.0.1     | 3306 | 1512971983463640 | 209                  | NULL       |
+----------------+------+------------------+----------------------+------------+
6 rows in set (0.00 sec)

Enable monitoring of these nodes by loading them at runtime

1
Admin> LOAD MYSQL SERVERS TO RUNTIME;

7. Create ProxySQL Client User

In ProxySQL Node:

1
2
3
4
5
6
7
8
9
10
11
Admin> INSERT INTO mysql_users (username,password,default_hostgroup) VALUES ('sbuser','sbpassword','1');
Admin> SELECT * FROM mysql_users;
+----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password   | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| sbuser   | sbpassword | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |
+----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)

Admin> LOAD MYSQL USERS TO RUNTIME;
Admin> SAVE MYSQL USERS TO DISK;

In MySQL group replication Node 1, grant permission for sbuser for ProxySQL host (172.16.0.3). You only do this in one MySQL group replication node because it will be replicated.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> CREATE USER 'sbuser'@'172.16.0.3' IDENTIFIED BY 'sbpassword';
mysql> GRANT ALL ON playground.* TO 'sbuser'@'172.16.0.3';
mysql> SELECT User,Host FROM mysql.user;
+---------------+----------------+
| User          | Host           |
+---------------+----------------+
| proxysql      | %              |
| repl          | %              |
| sbuser        | 172.16.0.3	 |
| mysql.session | localhost      |
| mysql.sys     | localhost      |
| root          | localhost      |
+---------------+----------------+
6 rows in set (0.00 sec)

In ProxySQL Node, try to login with the new user to check if the user has been set up correctly:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
root@proxysqlnode:~# mysql -u sbuser -psbpassword -h 127.0.0.1 -P 6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 366392
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

8. Add a Scheduler to Verify MySQL InnoDB Cluster (Group Replication) - Optional

Still in ProxySQL node, now we will add a scheduler that will use a script that verifies our MySQL InnoDB Cluster (Group Replication). The script is available on https://github.com/lefred/proxysql_groupreplication_checker. Download the sh file and you can put it in “/var/lib/proxysql/“. Insert, save and load the scheduler.

1
2
3
Admin> INSERT INTO scheduler(id,interval_ms,filename,arg1,arg2,arg3,arg4, arg5) VALUES (1,'10000','/var/lib/proxysql/proxysql_groupreplication_checker.sh','1','2','1','0','/var/lib/proxysql/proxysql_groupreplication_checker.log');
Admin> SAVE SCHEDULER TO DISK;
Admin> LOAD SCHEDULER TO RUNTIME;

What are those values in arg1 to arg5 ?

  • arg1 is the hostgroup_id for write
  • arg2 is the hostgroup_id for read
  • arg3 is the number of writers we want active at the same time
  • arg4 represents if we want that the member acting for writes is also candidate for reads
  • arg5 is the log file

Because we only have 2 nodes in each group, we will not see the difference in mysql_servers table. If we use 3 nodes in each group, we will see the difference. 

1
Admin> select * from mysql_servers;

It’s time to add some routing rules to be able to use those hostgroups. If you don only the first hostgroup will be used.

1
Admin> insert into mysql_query_rules (active, match_pattern, destination_hostgroup, apply) values (1,"^SELECT",2,1);

We will route all queries starting by select (this is not a recommendation of course a we will also send to hostgroup 2 all SELECT… FOR UPDATE, for example).

1
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;

You can now save to disk the setup as it works as expected and it was easy and quick to setup:

1
2
Admin> save MYSQL SERVERS TO DISK;
Admin> SAVE MYSQL QUERY RULES TO DISK;

9. Check and Test Everything

In ProxySQL node, make sure you can access group replication user and manage the database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
root@proxysqlnode:~# mysql -u sbuser -psbpassword -h 127.0.0.1 -P 6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 366392
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

SELECT * FROM playground.equipment;
+----+-------+-------+--------+
| id | type  | quant | color  |
+----+-------+-------+--------+
|  1 | slide |     2 | blue   |
|  2 | swing |    10 | yellow |
|  9 | slide |     3 | green  |
+----+-------+-------+--------+
3 rows in set (0.01 sec)


mysql> SELECT * FROM playground.equipment;
+----+-------+-------+--------+
| id | type  | quant | color  |
+----+-------+-------+--------+
|  1 | slide |     2 | blue   |
|  2 | swing |    10 | yellow |
|  9 | slide |     3 | green  |
+----+-------+-------+--------+
3 rows in set (0.01 sec)

mysql> INSERT INTO playground.equipment (type, quant, color) VALUES ("pen", 5, "purple");
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM playground.equipment;
+----+-------+-------+--------+
| id | type  | quant | color  |
+----+-------+-------+--------+
|  1 | slide |     2 | blue   |
|  2 | swing |    10 | yellow |
|  9 | slide |     3 | green  |
| 15 | pen   |     5 | purple |
+----+-------+-------+--------+
4 rows in set (0.00 sec)

mysql>

Make sure also you can access the user and manage the database trough internet. Use ProxySQL IP public to access it. For example the ProxySQL IP Public is 50.50.50.50. You can use this command on your mysql client in your computer: mysql -u sbuser -psbpassword -h 50.50.50.50 -P 6033

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Setting environment for using XAMPP for Windows.
user@DESKTOP-P20ELHV d:\xampp
# mysql -u sbuser -psbpassword -h 50.50.50.50 -P 6033
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 367836
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> select * from playground.equipment;
+----+-------+-------+--------+
| id | type  | quant | color  |
+----+-------+-------+--------+
|  1 | slide |     2 | blue   |
|  2 | swing |    10 | yellow |
|  9 | slide |     3 | green  |
| 15 | pen   |     5 | purple |
| 16 | watch |     6 | black  |
+----+-------+-------+--------+
5 rows in set (0.06 sec)

MySQL [(none)]>

Reference

Posted on 
© 2017 - Muh Rizal
Powered by Hexo