ProxySQL 1.4.2 now supports Native Clustering!!!

ProxySQL enters the club of the software associated with the buzz word “Cluster”.
This blog post is the first in a series describing how to setup ProxySQL Cluster.
For details on the implementation, please refer to the documentation in the wiki

Preface

ProxySQL is a decentralized proxy, recommended to be deployed close to the application. This approach scales pretty well even up to hundreds of nodes, as it was designed to be easily reconfigurable at runtime.
This allows for simple configure a farm of ProxySQL instances using software like Ansible/Chef/Puppet/Salt (in alphabetical order), or service discovery tools like Etcd/Consul/ZooKeeper.
Furthermoore, ProxySQL is highly customizable, and can be adopted in any setup which makes use of these technologies, or even home-made tools.

This solution however have its drawbacks:

External software is required for its configuration (i.e. configuration management software)
Multiple instance can’t sync up their configuration natively
Converge time it is not predictable
There is no protection against split brain (network partition)

In order to address the above, new features have been introduced in ProxySQL 1.4.2 to support clustering natively.
As we’ve already pointed, these features are EXPERIMENTAL and subject to change, especially because not all the features in the roadmap have been implemented yet.

Setting up a 3 node ProxySQL Cluster

We will start by setting up a cluster with just 3 nodes.
We will use the following bootstrap config file for /etc/proxysql.cnf.
Admin variables will mostly be set to their default values, we are specifying them in any case to enumerate the new cluster variables. What is important to note is that the cluster_username and cluster_password variables should specify credentials also listed in admin_credentials. In fact, admin_credentials can contain multiple sets of credentials.

datadir="/var/lib/proxysql"

admin_variables =
{
        admin_credentials="admin:admin;cluster1:secret1pass"
        mysql_ifaces="0.0.0.0:6032"
        cluster_username="cluster1"
        cluster_password="secret1pass"
        cluster_check_interval_ms=200
        cluster_check_status_frequency=100
        cluster_mysql_query_rules_save_to_disk=true
        cluster_mysql_servers_save_to_disk=true
        cluster_mysql_users_save_to_disk=true
        cluster_proxysql_servers_save_to_disk=true
        cluster_mysql_query_rules_diffs_before_sync=3
        cluster_mysql_servers_diffs_before_sync=3
        cluster_mysql_users_diffs_before_sync=3
        cluster_proxysql_servers_diffs_before_sync=3
}

proxysql_servers =
(
    {
        hostname="172.16.3.130"
        port=6032
        comment="proxysql130"
    },
    {
        hostname="172.16.3.131"
        port=6032
        comment="proxysql131"
    },
    {
        hostname="172.16.3.132"
        port=6032
        comment="proxysql132"
    }
)

At this stage, we can start proxysql on all the 3 nodes. As pointed out in the documention, when clustering is active proxysql generates checksums for the configuration of each active module.
Let’s connect to any of the proxysql admin interface, and check the current configuration checksums:

Admin130> SELECT * FROM runtime_checksums_values ORDER BY name;
+-------------------+---------+------------+--------------------+
| name              | version | epoch      | checksum           |
+-------------------+---------+------------+--------------------+
| admin_variables   | 0       | 0          |                    |
| mysql_query_rules | 1       | 1504615779 | 0x0000000000000000 |
| mysql_servers     | 1       | 1504615779 | 0x0000000000000000 |
| mysql_users       | 1       | 1504615779 | 0x0000000000000000 |
| mysql_variables   | 0       | 0          |                    |
| proxysql_servers  | 1       | 1504615779 | 0x474020F334F98128 |
+-------------------+---------+------------+--------------------+
6 rows in set (0.00 sec)

Note that only 4 modules are active right now:

mysql_query_rules
mysql_servers
mysql_users
proxysql_servers

Modules not active have version=0.
Modules with empty configuration have a zero valued checksum.
Epoch is a unix timestamp.

Admin130> SELECT name,FROM_UNIXTIME(epoch), checksum FROM runtime_checksums_values WHERE version ORDER BY name;
+-------------------+----------------------+--------------------+
| name              | FROM_UNIXTIME(epoch) | checksum           |
+-------------------+----------------------+--------------------+
| mysql_query_rules | 2017-09-05 12:49:39  | 0x0000000000000000 |
| mysql_servers     | 2017-09-05 12:49:39  | 0x0000000000000000 |
| mysql_users       | 2017-09-05 12:49:39  | 0x0000000000000000 |
| proxysql_servers  | 2017-09-05 12:49:39  | 0x474020F334F98128 |
+-------------------+----------------------+--------------------+
4 rows in set (0.00 sec)

To view the status of the whole cluster, we need to check table stats_proxysql_servers_checksums:

Admin130> SELECT * FROM stats_proxysql_servers_checksums;
+--------------+------+-------------------+---------+------------+--------------------+------------+------------+------------+
| hostname     | port | name              | version | epoch      | checksum           | changed_at | updated_at | diff_check |
+--------------+------+-------------------+---------+------------+--------------------+------------+------------+------------+
| 172.16.3.132 | 6032 | admin_variables   | 0       | 0          |                    | 0          | 1504618520 | 0          |
| 172.16.3.132 | 6032 | mysql_query_rules | 1       | 1504615782 | 0x0000000000000000 | 1504615782 | 1504618520 | 0          |
| 172.16.3.132 | 6032 | mysql_servers     | 1       | 1504615782 | 0x0000000000000000 | 1504615782 | 1504618520 | 0          |
| 172.16.3.132 | 6032 | mysql_users       | 1       | 1504615782 | 0x0000000000000000 | 1504615782 | 1504618520 | 0          |
| 172.16.3.132 | 6032 | mysql_variables   | 0       | 0          |                    | 0          | 1504618520 | 0          |
| 172.16.3.132 | 6032 | proxysql_servers  | 1       | 1504615782 | 0x474020F334F98128 | 1504615782 | 1504618520 | 0          |
| 172.16.3.131 | 6032 | admin_variables   | 0       | 0          |                    | 0          | 1504618520 | 0          |
| 172.16.3.131 | 6032 | mysql_query_rules | 1       | 1504615780 | 0x0000000000000000 | 1504615781 | 1504618520 | 0          |
| 172.16.3.131 | 6032 | mysql_servers     | 1       | 1504615780 | 0x0000000000000000 | 1504615781 | 1504618520 | 0          |
| 172.16.3.131 | 6032 | mysql_users       | 1       | 1504615780 | 0x0000000000000000 | 1504615781 | 1504618520 | 0          |
| 172.16.3.131 | 6032 | mysql_variables   | 0       | 0          |                    | 0          | 1504618520 | 0          |
| 172.16.3.131 | 6032 | proxysql_servers  | 1       | 1504615780 | 0x474020F334F98128 | 1504615781 | 1504618520 | 0          |
| 172.16.3.130 | 6032 | admin_variables   | 0       | 0          |                    | 0          | 1504618520 | 0          |
| 172.16.3.130 | 6032 | mysql_query_rules | 1       | 1504615779 | 0x0000000000000000 | 1504615780 | 1504618520 | 0          |
| 172.16.3.130 | 6032 | mysql_servers     | 1       | 1504615779 | 0x0000000000000000 | 1504615780 | 1504618520 | 0          |
| 172.16.3.130 | 6032 | mysql_users       | 1       | 1504615779 | 0x0000000000000000 | 1504615780 | 1504618520 | 0          |
| 172.16.3.130 | 6032 | mysql_variables   | 0       | 0          |                    | 0          | 1504618520 | 0          |
| 172.16.3.130 | 6032 | proxysql_servers  | 1       | 1504615779 | 0x474020F334F98128 | 1504615780 | 1504618520 | 0          |
+--------------+------+-------------------+---------+------------+--------------------+------------+------------+------------+
18 rows in set (0.00 sec)

Modules which are not active have version=0, empty configurations also have zero-values, and timestamps are “unix timestamps”:

Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE version > 0 ORDER BY hostname, name;
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name              | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_query_rules | 1       | 2017-09-05 12:49:39 | 0x0000000000000000 | 2017-09-05 12:49:40 | 2017-09-05 13:44:48 | 0          | 2017-09-05 13:44:48 |
| 172.16.3.130 | 6032 | mysql_servers     | 1       | 2017-09-05 12:49:39 | 0x0000000000000000 | 2017-09-05 12:49:40 | 2017-09-05 13:44:48 | 0          | 2017-09-05 13:44:48 |
| 172.16.3.130 | 6032 | mysql_users       | 1       | 2017-09-05 12:49:39 | 0x0000000000000000 | 2017-09-05 12:49:40 | 2017-09-05 13:44:48 | 0          | 2017-09-05 13:44:48 |
| 172.16.3.130 | 6032 | proxysql_servers  | 1       | 2017-09-05 12:49:39 | 0x474020F334F98128 | 2017-09-05 12:49:40 | 2017-09-05 13:44:48 | 0          | 2017-09-05 13:44:48 |
| 172.16.3.131 | 6032 | mysql_query_rules | 1       | 2017-09-05 12:49:40 | 0x0000000000000000 | 2017-09-05 12:49:41 | 2017-09-05 13:44:48 | 0          | 2017-09-05 13:44:48 |
| 172.16.3.131 | 6032 | mysql_servers     | 1       | 2017-09-05 12:49:40 | 0x0000000000000000 | 2017-09-05 12:49:41 | 2017-09-05 13:44:48 | 0          | 2017-09-05 13:44:48 |
| 172.16.3.131 | 6032 | mysql_users       | 1       | 2017-09-05 12:49:40 | 0x0000000000000000 | 2017-09-05 12:49:41 | 2017-09-05 13:44:48 | 0          | 2017-09-05 13:44:48 |
| 172.16.3.131 | 6032 | proxysql_servers  | 1       | 2017-09-05 12:49:40 | 0x474020F334F98128 | 2017-09-05 12:49:41 | 2017-09-05 13:44:48 | 0          | 2017-09-05 13:44:48 |
| 172.16.3.132 | 6032 | mysql_query_rules | 1       | 2017-09-05 12:49:42 | 0x0000000000000000 | 2017-09-05 12:49:42 | 2017-09-05 13:44:48 | 0          | 2017-09-05 13:44:48 |
| 172.16.3.132 | 6032 | mysql_servers     | 1       | 2017-09-05 12:49:42 | 0x0000000000000000 | 2017-09-05 12:49:42 | 2017-09-05 13:44:48 | 0          | 2017-09-05 13:44:48 |
| 172.16.3.132 | 6032 | mysql_users       | 1       | 2017-09-05 12:49:42 | 0x0000000000000000 | 2017-09-05 12:49:42 | 2017-09-05 13:44:48 | 0          | 2017-09-05 13:44:48 |
| 172.16.3.132 | 6032 | proxysql_servers  | 1       | 2017-09-05 12:49:42 | 0x474020F334F98128 | 2017-09-05 12:49:42 | 2017-09-05 13:44:48 | 0          | 2017-09-05 13:44:48 |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
12 rows in set (0.00 sec)

To clarify some fields:

epoch is the timestamp generated by the remote proxy when executing LOAD ... TO RUNTIME
changed_at is the timestamp recorded when the local proxy detected a change in the remote proxy
updated_at is the last timestamp for which the local proxy retrieved the checksum from the remote proxy

Add new users

Now that we have ProxySQL Cluster up and running, let’s see how configuration is propagated from a single node to all other nodes.
Lets start by adding a new user on one of the nodes.

Admin130> SELECT * FROM mysql_users;
Empty set (0.00 sec)

Admin130> INSERT INTO mysql_users(username,password) VALUES ('sbtest','sbtest');
Query OK, 1 row affected (0.00 sec)

Admin130> SELECT * FROM mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| sbtest   | sbtest   | 1      | 0       | 0                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)

Admin130> SELECT * FROM runtime_mysql_users;
Empty set (0.00 sec)

As we can see from the above example, there are no users loaded at runtime, we have just created a new user in memory.
Time to load it into runtime and see what happens next:

Admin130> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE version > 0 ORDER BY hostname, name;
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name              | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_query_rules | 1       | 2017-09-05 12:49:39 | 0x0000000000000000 | 2017-09-05 12:49:40 | 2017-09-05 13:47:52 | 0          | 2017-09-05 13:47:53 |
| 172.16.3.130 | 6032 | mysql_servers     | 1       | 2017-09-05 12:49:39 | 0x0000000000000000 | 2017-09-05 12:49:40 | 2017-09-05 13:47:52 | 0          | 2017-09-05 13:47:53 |
| 172.16.3.130 | 6032 | mysql_users       | 2       | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:48 | 2017-09-05 13:47:52 | 0          | 2017-09-05 13:47:53 |
| 172.16.3.130 | 6032 | proxysql_servers  | 1       | 2017-09-05 12:49:39 | 0x474020F334F98128 | 2017-09-05 12:49:40 | 2017-09-05 13:47:52 | 0          | 2017-09-05 13:47:53 |
| 172.16.3.131 | 6032 | mysql_query_rules | 1       | 2017-09-05 12:49:40 | 0x0000000000000000 | 2017-09-05 12:49:41 | 2017-09-05 13:47:53 | 0          | 2017-09-05 13:47:53 |
| 172.16.3.131 | 6032 | mysql_servers     | 1       | 2017-09-05 12:49:40 | 0x0000000000000000 | 2017-09-05 12:49:41 | 2017-09-05 13:47:53 | 0          | 2017-09-05 13:47:53 |
| 172.16.3.131 | 6032 | mysql_users       | 2       | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:49 | 2017-09-05 13:47:53 | 0          | 2017-09-05 13:47:53 |
| 172.16.3.131 | 6032 | proxysql_servers  | 1       | 2017-09-05 12:49:40 | 0x474020F334F98128 | 2017-09-05 12:49:41 | 2017-09-05 13:47:53 | 0          | 2017-09-05 13:47:53 |
| 172.16.3.132 | 6032 | mysql_query_rules | 1       | 2017-09-05 12:49:42 | 0x0000000000000000 | 2017-09-05 12:49:42 | 2017-09-05 13:47:53 | 0          | 2017-09-05 13:47:53 |
| 172.16.3.132 | 6032 | mysql_servers     | 1       | 2017-09-05 12:49:42 | 0x0000000000000000 | 2017-09-05 12:49:42 | 2017-09-05 13:47:53 | 0          | 2017-09-05 13:47:53 |
| 172.16.3.132 | 6032 | mysql_users       | 2       | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:49 | 2017-09-05 13:47:53 | 0          | 2017-09-05 13:47:53 |
| 172.16.3.132 | 6032 | proxysql_servers  | 1       | 2017-09-05 12:49:42 | 0x474020F334F98128 | 2017-09-05 12:49:42 | 2017-09-05 13:47:53 | 0          | 2017-09-05 13:47:53 |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
12 rows in set (0.00 sec)

Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_users' ORDER BY hostname, name;
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name        | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_users | 2       | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:48 | 2017-09-05 13:48:21 | 0          | 2017-09-05 13:48:21 |
| 172.16.3.131 | 6032 | mysql_users | 2       | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:49 | 2017-09-05 13:48:21 | 0          | 2017-09-05 13:48:21 |
| 172.16.3.132 | 6032 | mysql_users | 2       | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:49 | 2017-09-05 13:48:21 | 0          | 2017-09-05 13:48:21 |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
3 rows in set (0.00 sec)

We applied the change on 172.16.3.130 and the change was propagated everywhere else almost instantly.
Checking the log on 172.16.3.131 we can see the log entries generated during the sync:

2017-09-05 14:47:48 [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.130:6032, version 2, epoch 1504619268, checksum 0x7917CD487C11478F . Not syncing yet ...
2017-09-05 14:47:48 [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_users version 2, epoch 1504619268, diff_check 3\. Own version: 1, epoch: 1504615780\. Proceeding with remote sync
2017-09-05 14:47:48 [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_users version 2, epoch 1504619268, diff_check 4\. Own version: 1, epoch: 1504615780\. Proceeding with remote sync
2017-09-05 14:47:48 [INFO] Cluster: detected peer 172.16.3.130:6032 with mysql_users version 2, epoch 1504619268
2017-09-05 14:47:48 [INFO] Cluster: Fetching MySQL Users from peer 172.16.3.130:6032 started
2017-09-05 14:47:48 [INFO] Cluster: Fetching MySQL Users from peer 172.16.3.130:6032 completed
2017-09-05 14:47:48 [INFO] Cluster: Loading to runtime MySQL Users from peer 172.16.3.130:6032
2017-09-05 14:47:48 [INFO] Cluster: Saving to disk MySQL Query Rules from peer 172.16.3.130:6032
2017-09-05 14:47:48 [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.132:6032, version 2, epoch 1504619268, checksum 0x7917CD487C11478F . Not syncing yet ...
2017-09-05 14:47:48 [INFO] Cluster: checksum for mysql_users from peer 172.16.3.132:6032 matches with local checksum 0x7917CD487C11478F , we won't sync.
2017-09-05 14:47:49 [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.131:6032, version 2, epoch 1504619268, checksum 0x7917CD487C11478F . Not syncing yet ...
2017-09-05 14:47:49 [INFO] Cluster: checksum for mysql_users from peer 172.16.3.131:6032 matches with local checksum 0x7917CD487C11478F , we won't sync.

What happened?

ProxySQL detected a change in the checksum for table mysql_users
When diff_check reaches the threshold, the sync process is initialized
MySQL Users are fetched from 172.16.3.130:6032
MySQL Users are loaded to runtime
MySQL Users are also saved to disk because cluster_mysql_users_save_to_disk=true
A new checksum is also detected from 172.16.3.132:6032 , but it matches the local one so there is no need to sync
A new checksum is also detected from 172.16.3.131:6032 (that is the same proxysql instance), but it matches the local one so there is again no need to sync

Let’s now add a new user directly on 172.16.3.131:6032, a different node.
We first verify what user(s) is/are present, then we add a new one:

Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_users' ORDER BY hostname, name;
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name        | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_users | 2       | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:48 | 2017-09-05 14:52:32 | 0          | 2017-09-05 14:52:32 |
| 172.16.3.131 | 6032 | mysql_users | 2       | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:49 | 2017-09-05 14:52:32 | 0          | 2017-09-05 14:52:32 |
| 172.16.3.132 | 6032 | mysql_users | 2       | 2017-09-05 13:47:48 | 0x7917CD487C11478F | 2017-09-05 13:47:48 | 2017-09-05 14:52:32 | 0          | 2017-09-05 14:52:32 |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
3 rows in set (0.00 sec)

Admin131> SELECT * FROM mysql_users;
 +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password                                  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| sbtest   | *2AFD99E79E4AA23DE141540F4179F64FFB3AC521 | 1      | 0       | 0                 |                | 0             | 1                      | 0            | 0       | 1        | 10000           |
| sbtest   | *2AFD99E79E4AA23DE141540F4179F64FFB3AC521 | 1      | 0       | 0                 |                | 0             | 1                      | 0            | 1       | 0        | 10000           |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
2 rows in set (0.00 sec)

Admin131> INSERT INTO mysql_users (username,password) VALUES ('user1','password1');
Query OK, 1 row affected (0.00 sec)

Admin131> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_users' ORDER BY hostname, name;
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name        | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_users | 3       | 2017-09-05 14:53:45 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:53:48 | 0          | 2017-09-05 14:53:48 |
| 172.16.3.131 | 6032 | mysql_users | 3       | 2017-09-05 14:53:44 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:53:48 | 0          | 2017-09-05 14:53:48 |
| 172.16.3.132 | 6032 | mysql_users | 3       | 2017-09-05 14:53:45 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:53:47 | 0          | 2017-09-05 14:53:48 |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
3 rows in set (0.00 sec)

Also in this case, configuration is immediately propagated to all the nodes.

It is interesting to note what happens when running LOAD TO RUNTIME without performing any changes.

Admin131> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_users' ORDER BY hostname, name;
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+

| hostname     | port | name        | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |

+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_users | 3       | 2017-09-05 14:53:45 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:55:45 | 0          | 2017-09-05 14:55:45 |
| 172.16.3.131 | 6032 | mysql_users | 4       | 2017-09-05 14:55:44 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:55:45 | 0          | 2017-09-05 14:55:45 |
| 172.16.3.132 | 6032 | mysql_users | 3       | 2017-09-05 14:53:45 | 0x7C60129E5360AC28 | 2017-09-05 14:53:45 | 2017-09-05 14:55:45 | 0          | 2017-09-05 14:55:45 |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
3 rows in set (0.00 sec)

On proxysql131 , version is increased by 1, but checksum doesn’t change. This means that no sync is triggered as there is no need for it.
Similarly, the version on other nodes does not change, because they don’t re-execute LOAD ... TO RUNTIME.

Create new query rules

We just saw how new users can be created in just one ProxySQL node, and instantly detected by other nodes.
It is now time to show to synchronize MySQL Query Rules.

Let’s start by creating new query rules in a node without any specific behaviour.

Admin130> SELECT * FROM mysql_query_rules;
Empty set (0.00 sec)

Admin130> INSERT INTO mysql_query_rules (schemaname, destination_hostgroup) VALUES ('shard001',1);
Query OK, 1 row affected (0.00 sec)

Admin130> INSERT INTO mysql_query_rules (schemaname, destination_hostgroup) VALUES ('shard002',1);
Query OK, 1 row affected (0.00 sec)

Admin130> INSERT INTO mysql_query_rules (schemaname, destination_hostgroup) VALUES ('shard003',1);
Query OK, 1 row affected (0.00 sec)

Admin130> INSERT INTO mysql_query_rules (schemaname, destination_hostgroup) VALUES ('shard004',1);
Query OK, 1 row affected (0.00 sec)

Admin130> INSERT INTO mysql_query_rules (schemaname, destination_hostgroup) VALUES ('shard005',2), ('shard006',2), ('shard007',2), ('shard008',2);
Query OK, 4 rows affected (0.00 sec)

Admin130> UPDATE mysql_query_rules SET active=1, apply=1 WHERE destination_hostgroup IN (1,2);
Query OK, 8 rows affected (0.00 sec)

Let’s check the current query rules:

Admin130> SELECT rule_id,schemaname,destination_hostgroup,apply FROM mysql_query_rules WHERE active=1;
+---------+------------+-----------------------+-------+
| rule_id | schemaname | destination_hostgroup | apply |
+---------+------------+-----------------------+-------+
| 1       | shard001   | 1                     | 1     |
| 2       | shard002   | 1                     | 1     |
| 3       | shard003   | 1                     | 1     |
| 4       | shard004   | 1                     | 1     |
| 5       | shard005   | 2                     | 1     |
| 6       | shard006   | 2                     | 1     |
| 7       | shard007   | 2                     | 1     |
| 8       | shard008   | 2                     | 1     |
+---------+------------+-----------------------+-------+
8 rows in set (0.00 sec)

On another node, say proxysql131, there are no rules:

Admin131> SELECT rule_id,schemaname,destination_hostgroup,apply FROM mysql_query_rules WHERE active=1;
Empty set (0.00 sec)

Admin131> SELECT rule_id,schemaname,destination_hostgroup,apply FROM runtime_mysql_query_rules WHERE active=1;
Empty set (0.00 sec)

Now it is time to load the rules into runtime on proxysql130:

Admin130> SELECT name,FROM_UNIXTIME(epoch), checksum FROM runtime_checksums_values WHERE name='mysql_query_rules';
+-------------------+----------------------+--------------------+
| name              | FROM_UNIXTIME(epoch) | checksum           |
+-------------------+----------------------+--------------------+
| mysql_query_rules | 2017-09-05 23:03:53  | 0x0000000000000000 |
+-------------------+----------------------+--------------------+
1 row in set (0.00 sec)

Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_query_rules' ORDER BY hostname, name;
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name              | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_query_rules | 1       | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:53 | 2017-09-05 23:04:47 | 0          | 2017-09-05 23:04:47 |
| 172.16.3.131 | 6032 | mysql_query_rules | 1       | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:54 | 2017-09-05 23:04:47 | 0          | 2017-09-05 23:04:47 |
| 172.16.3.132 | 6032 | mysql_query_rules | 1       | 2017-09-05 23:03:54 | 0x0000000000000000 | 2017-09-05 23:03:55 | 2017-09-05 23:04:47 | 0          | 2017-09-05 23:04:47 |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
3 rows in set (0.00 sec)

Admin130> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin130> SELECT name,FROM_UNIXTIME(epoch), checksum FROM runtime_checksums_values WHERE name='mysql_query_rules';
+-------------------+----------------------+--------------------+
| name              | FROM_UNIXTIME(epoch) | checksum           |
+-------------------+----------------------+--------------------+
| mysql_query_rules | 2017-09-05 23:04:51  | 0xE2F5A21142C799C0 |
+-------------------+----------------------+--------------------+
1 row in set (0.00 sec)

Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_query_rules' ORDER BY hostname, name;
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name              | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_query_rules | 2       | 2017-09-05 23:04:51 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:51 | 2017-09-05 23:06:22 | 0          | 2017-09-05 23:06:23 |
| 172.16.3.131 | 6032 | mysql_query_rules | 2       | 2017-09-05 23:04:52 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:06:23 | 0          | 2017-09-05 23:06:23 |
| 172.16.3.132 | 6032 | mysql_query_rules | 2       | 2017-09-05 23:04:52 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:06:23 | 0          | 2017-09-05 23:06:23 |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
3 rows in set (0.00 sec)

As we can see from the above, the information is detected immediately by the other nodes and they immediately sync with the node that has the most recent changes.

Let’s check the logs on proxysql131:

2017-09-06 00:04:51 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.130:6032, version 2, epoch 1504652691, checksum 0xE2F5A21142C799C0 . Not syncing yet ...
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_query_rules version 2, epoch 1504652691, diff_check 3\. Own version: 1, epoch: 1504652633\. Proceeding with remote sync
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_query_rules version 2, epoch 1504652691, diff_check 4\. Own version: 1, epoch: 1504652633\. Proceeding with remote sync
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:1106:get_peer_to_sync_mysql_query_rules(): [INFO] Cluster: detected peer 172.16.3.130:6032 with mysql_query_rules version 2, epoch 1504652691
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:572:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Fetching MySQL Query Rules from peer 172.16.3.130:6032 started
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:622:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Fetching MySQL Query Rules from peer 172.16.3.130:6032 completed
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:623:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Loading to runtime MySQL Servers from peer 172.16.3.130:6032
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:626:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Saving to disk MySQL Query Rules from peer 172.16.3.130:6032
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.132:6032, version 2, epoch 1504652692, checksum 0xE2F5A21142C799C0 . Not syncing yet ...
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:327:set_checksums(): [INFO] Cluster: checksum for mysql_query_rules from peer 172.16.3.132:6032 matches with local checksum 0xE2F5A21142C799C0 , we won't sync.
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.131:6032, version 2, epoch 1504652692, checksum 0xE2F5A21142C799C0 . Not syncing yet ...
2017-09-06 00:04:52 ProxySQL_Cluster.cpp:327:set_checksums(): [INFO] Cluster: checksum for mysql_query_rules from peer 172.16.3.131:6032 matches with local checksum 0xE2F5A21142C799C0 , we won't sync.

Also in this case, we can see that a change is detected, and after a short period of time all nodes are synced.

Conclusion

In this first blog post about ProxySQL Cluster we showed how multiple ProxySQL instances are aware of each other, and how we can create new users and new query rules to any of the proxysql instances as well as how this information will immediately be propagated to all the other nodes.
Stay tuned for future blog post on ProxySQL Cluster.

Native ProxySQL Clustering now available

ProxySQL 1.4.2 now supports Native Clustering!!!

Preface

Setting up a 3 node ProxySQL Cluster

Add new users

Create new query rules

Conclusion

Tracking Cookies

Native ProxySQL Clustering now available

ProxySQL 1.4.2 now supports Native Clustering!!!

Preface

Setting up a 3 node ProxySQL Cluster

Add new users

Create new query rules

Conclusion

Share on social media!

Tracking Cookies