Native ProxySQL Cluster - part 2

  • Date:
  • Tags: proxy mysql release proxysql ha gpl cluster
Clusters

ProxySQL Cluster management

In the previous blog post we described what ProxySQL Cluster is, and how creating new users and new query rules in one of the node will result in all the other nodes being immediately aware of it and automatically sync.
All looks very straightforward if all the nodes are all up and running when we make a configuration change. But how to handle cases in which nodes are not available, or they will join later? How to handle conclicts?

Disclaimer
Before going forward, please remember that those features are EXPERIMENTAL and subject to changes.

Automatic node re-joining

In this exercise we will see what happen if a node get out of sync.
Specifically, we will shutdown proxysql132, apply some change in mysql_query_rules from one of the nodes, and re-start proxysql132.

Let's start shutting down proxysql132.

From the output below, we can see that proxysql130 isn't getting the checksum from proxysql132, specifically updated_at is not being updated, that means that proxysql132 is not running (we shut it down):

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;
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 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:16:17 | 0          | 2017-09-05 23:16:17 |
| 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:16:17 | 0          | 2017-09-05 23:16:17 |
| 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:12:17 | 0          | 2017-09-05 23:16:17 |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
3 rows in set (0.00 sec)

Admin130> SELECT hostname, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at FROM stats_proxysql_servers_checksums WHERE name='mysql_query_rules' ORDER BY hostname;
+--------------+--------------------+---------------------+---------------------+
| hostname     | checksum           | changed_at          | updated_at          |
+--------------+--------------------+---------------------+---------------------+
| 172.16.3.130 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:51 | 2017-09-05 23:16:21 |
| 172.16.3.131 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:16:21 |
| 172.16.3.132 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:12:17 |
+--------------+--------------------+---------------------+---------------------+
3 rows in set (0.00 sec)

We will now make some useless change in mysql_query_rules, just to show the impact of changing 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)

Admin130> UPDATE mysql_query_rules SET rule_id=rule_id+8, destination_hostgroup=destination_hostgroup+4;
Query OK, 8 rows affected (0.00 sec)

Admin130> SELECT rule_id,schemaname,destination_hostgroup,apply FROM mysql_query_rules WHERE active=1;
+---------+------------+-----------------------+-------+
| rule_id | schemaname | destination_hostgroup | apply |
+---------+------------+-----------------------+-------+
| 9       | shard001   | 5                     | 1     |
| 10      | shard002   | 5                     | 1     |
| 11      | shard003   | 5                     | 1     |
| 12      | shard004   | 5                     | 1     |
| 13      | shard005   | 6                     | 1     |
| 14      | shard006   | 6                     | 1     |
| 15      | shard007   | 6                     | 1     |
| 16      | shard008   | 6                     | 1     |
+---------+------------+-----------------------+-------+
8 rows in set (0.00 sec)

And finally, we load at runtime:

Admin130> SELECT hostname, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at FROM stats_proxysql_servers_checksums WHERE name='mysql_query_rules' ORDER BY hostname;
+--------------+--------------------+---------------------+---------------------+
| hostname     | checksum           | changed_at          | updated_at          |
+--------------+--------------------+---------------------+---------------------+
| 172.16.3.130 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:51 | 2017-09-05 23:21:00 |
| 172.16.3.131 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:21:00 |
| 172.16.3.132 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:12:17 |
+--------------+--------------------+---------------------+---------------------+
3 rows in set (0.00 sec)

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

Admin130> SELECT hostname, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at FROM stats_proxysql_servers_checksums WHERE name='mysql_query_rules' ORDER BY hostname;
+--------------+--------------------+---------------------+---------------------+
| hostname     | checksum           | changed_at          | updated_at          |
+--------------+--------------------+---------------------+---------------------+
| 172.16.3.130 | 0x81A6E7C4159B081D | 2017-09-05 23:21:05 | 2017-09-05 23:21:07 |
| 172.16.3.131 | 0x81A6E7C4159B081D | 2017-09-05 23:21:06 | 2017-09-05 23:21:07 |
| 172.16.3.132 | 0xE2F5A21142C799C0 | 2017-09-05 23:04:52 | 2017-09-05 23:12:17 |
+--------------+--------------------+---------------------+---------------------+
3 rows in set (0.00 sec)

proxysql131 immediately took the change from proxysql130, but proxysql132 is down, so it won't take the change.

Now, let's start proxysql132 and see what happens...

2017-09-06 00:22:51 main.cpp:703:ProxySQL_daemonize_phase2(): [INFO] Starting ProxySQL
2017-09-06 00:22:51 main.cpp:704:ProxySQL_daemonize_phase2(): [INFO] Sucessfully started
Main daemonize phase1 completed in 0.00089 secs.
2017-09-06 00:22:51 main.cpp:714:ProxySQL_daemonize_phase3(): [INFO] Angel process started ProxySQL process 188934
Standard ProxySQL Cluster rev. 0.1.0702_DEBUG -- ProxySQL_Cluster.cpp -- Wed Sep  6 00:10:30 2017
Admin initialized in 0.019364 secs.
Standard ProxySQL Admin rev. 0.2.0902_DEBUG -- ProxySQL_Admin.cpp -- Tue Sep  5 12:20:48 2017
Standard MySQL Threads Handler rev. 0.2.0902_DEBUG -- MySQL_Thread.cpp -- Tue Sep  5 12:20:48 2017
Main phase3 : GloMyLogger initialized in 4e-06 secs.
Standard MySQL Authentication rev. 0.2.0902_DEBUG -- MySQL_Authentication.cpp -- Wed Sep  6 00:10:30 2017
Main init phase2 completed in 0.022463 secs.
Main phase3 : GloMyLogger initialized in 3e-06 secs.
2017-09-06 00:22:51 MySQL_HostGroups_Manager.cpp:880:generate_mysql_servers_table(): [INFO] Dumping current MySQL Servers structures for hostgroup ALL
2017-09-06 00:22:51 MySQL_HostGroups_Manager.cpp:971:generate_mysql_replication_hostgroups_table(): [INFO] New mysql_replication_hostgroups table
2017-09-06 00:22:51 MySQL_HostGroups_Manager.cpp:1007:generate_mysql_group_replication_hostgroups_table(): [INFO] New mysql_group_replication_hostgroups table
2017-09-06 00:22:51 MySQL_HostGroups_Manager.cpp:880:generate_mysql_servers_table(): [INFO] Dumping current MySQL Servers structures for hostgroup ALL
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:238:ProxySQL_Node_Entry(): [INFO] Created new Cluster Node Entry for host 172.16.3.130:6032
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:238:ProxySQL_Node_Entry(): [INFO] Created new Cluster Node Entry for host 172.16.3.131:6032
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:238:ProxySQL_Node_Entry(): [INFO] Created new Cluster Node Entry for host 172.16.3.132:6032
Main phase3 : GloAdmin initialized in 0.003573 secs.
Standard Query Processor rev. 0.2.0902_DEBUG -- Query_Processor.cpp -- Tue Sep  5 12:20:48 2017
Main phase3 : Query Processor initialized in 0.000246 secs.
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:53:ProxySQL_Cluster_Monitor_thread(): [INFO] Cluster: starting thread for peer 172.16.3.131:6032
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:53:ProxySQL_Cluster_Monitor_thread(): [INFO] Cluster: starting thread for peer 172.16.3.130:6032
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:53:ProxySQL_Cluster_Monitor_thread(): [INFO] Cluster: starting thread for peer 172.16.3.132:6032
Main phase3 : MySQL Threads Handler initialized in 0.002774 secs.
In memory Standard Query Cache (SQC) rev. 1.2.0905_DEBUG -- Query_Cache.cpp -- Tue Sep  5 12:20:48 2017
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:172:ProxySQL_Cluster_Monitor_thread(): [WARNING] Cluster: unable to connect to peer 172.16.3.132:6032 . Error: Can't connect to MySQL server on '172.16.3.132' (107)
Main phase3 : Query Cache initialized in 0.000186 secs.
2017-09-06 00:22: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 3, epoch 1504653665, checksum 0x81A6E7C4159B081D . Not syncing yet ...
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.130:6032, version 1, epoch 1504652633, checksum 0x0000000000000000 . Not syncing yet ...
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:347:set_checksums(): [INFO] Cluster: checksum for mysql_servers from peer 172.16.3.130:6032 matches with local checksum 0x0000000000000000 , we won't sync.
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:365:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.130:6032, version 1, epoch 1504652633, checksum 0x7C60129E5360AC28 . Not syncing yet ...
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:367:set_checksums(): [INFO] Cluster: checksum for mysql_users from peer 172.16.3.130:6032 matches with local checksum 0x7C60129E5360AC28 , we won't sync.
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:401:set_checksums(): [INFO] Cluster: detected a new checksum for proxysql_servers from peer 172.16.3.130:6032, version 1, epoch 1504652633, checksum 0x474020F334F98128 . Not syncing yet ...
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:403:set_checksums(): [INFO] Cluster: checksum for proxysql_servers from peer 172.16.3.130:6032 matches with local checksum 0x474020F334F98128 , we won't sync.
Main phase3 : MySQL Threads Handler listeners started in 6.6e-05 secs.
Standard MySQL Monitor (StdMyMon) rev. 1.2.0723_DEBUG -- MySQL_Monitor.cpp -- Tue Sep  5 12:20:48 2017
Main phase3 : MySQL Monitor initialized in 4.2e-05 secs.
Main init phase3 completed in 0.00966 secs.
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.131:6032, version 3, epoch 1504653665, checksum 0x81A6E7C4159B081D . Not syncing yet ...
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.131:6032, version 1, epoch 1504652633, checksum 0x0000000000000000 . Not syncing yet ...
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:347:set_checksums(): [INFO] Cluster: checksum for mysql_servers from peer 172.16.3.131:6032 matches with local checksum 0x0000000000000000 , we won't sync.
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:365:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.131:6032, version 1, epoch 1504652633, checksum 0x7C60129E5360AC28 . Not syncing yet ...
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:367:set_checksums(): [INFO] Cluster: checksum for mysql_users from peer 172.16.3.131:6032 matches with local checksum 0x7C60129E5360AC28 , we won't sync.
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:401:set_checksums(): [INFO] Cluster: detected a new checksum for proxysql_servers from peer 172.16.3.131:6032, version 1, epoch 1504652633, checksum 0x474020F334F98128 . Not syncing yet ...
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:403:set_checksums(): [INFO] Cluster: checksum for proxysql_servers from peer 172.16.3.131:6032 matches with local checksum 0x474020F334F98128 , we won't sync.
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:172:ProxySQL_Cluster_Monitor_thread(): [WARNING] Cluster: unable to connect to peer 172.16.3.132:6032 . Error: Can't connect to MySQL server on '172.16.3.132' (107)
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:172:ProxySQL_Cluster_Monitor_thread(): [WARNING] Cluster: unable to connect to peer 172.16.3.132:6032 . Error: Can't connect to MySQL server on '172.16.3.132' (107)
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_query_rules version 3, epoch 1504653665, diff_check 3. Own version: 1, epoch: 1504653771. Proceeding with remote sync
2017-09-06 00:22:51 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.131:6032 with mysql_query_rules version 3, epoch 1504653665, diff_check 3. Own version: 1, epoch: 1504653771. Proceeding with remote sync
2017-09-06 00:22:52 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_query_rules version 3, epoch 1504653665, diff_check 4. Own version: 1, epoch: 1504653771. Proceeding with remote sync
2017-09-06 00:22:52 ProxySQL_Cluster.cpp:1105:get_peer_to_sync_mysql_query_rules(): [INFO] Cluster: detected peer 172.16.3.130:6032 with mysql_query_rules version 3, epoch 1504653665
2017-09-06 00:22: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:22: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:22: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:22: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:22: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 1504653772, checksum 0x81A6E7C4159B081D . Not syncing yet ...
2017-09-06 00:22: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 0x81A6E7C4159B081D , we won't sync.
2017-09-06 00:22:52 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.132:6032, version 1, epoch 1504653771, checksum 0x0000000000000000 . Not syncing yet ...
2017-09-06 00:22:52 ProxySQL_Cluster.cpp:347:set_checksums(): [INFO] Cluster: checksum for mysql_servers from peer 172.16.3.132:6032 matches with local checksum 0x0000000000000000 , we won't sync.
2017-09-06 00:22:52 ProxySQL_Cluster.cpp:365:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.132:6032, version 1, epoch 1504653771, checksum 0x7C60129E5360AC28 . Not syncing yet ...
2017-09-06 00:22:52 ProxySQL_Cluster.cpp:367:set_checksums(): [INFO] Cluster: checksum for mysql_users from peer 172.16.3.132:6032 matches with local checksum 0x7C60129E5360AC28 , we won't sync.
2017-09-06 00:22:52 ProxySQL_Cluster.cpp:401:set_checksums(): [INFO] Cluster: detected a new checksum for proxysql_servers from peer 172.16.3.132:6032, version 1, epoch 1504653771, checksum 0x474020F334F98128 . Not syncing yet ...
2017-09-06 00:22:52 ProxySQL_Cluster.cpp:403:set_checksums(): [INFO] Cluster: checksum for proxysql_servers from peer 172.16.3.132:6032 matches with local checksum 0x474020F334F98128 , we won't sync.

Without going into details (log is quite verbose), within 1 second proxysql132 has detected that its mysql_query_rules was out of sync, and it immediately syncs.

Going back in proxysql130's Admin, we can see that now proxysql132 is in sync with the rest of the cluster:

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;
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name              | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_query_rules | 3       | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:21:05 | 2017-09-05 23:30:49 | 0          | 2017-09-05 23:30:49 |
| 172.16.3.131 | 6032 | mysql_query_rules | 3       | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:21:06 | 2017-09-05 23:30:49 | 0          | 2017-09-05 23:30:49 |
| 172.16.3.132 | 6032 | mysql_query_rules | 2       | 2017-09-05 23:22:52 | 0x81A6E7C4159B081D | 2017-09-05 23:22:52 | 2017-09-05 23:30:49 | 0          | 2017-09-05 23:30:49 |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
3 rows in set (0.00 sec)

Bootstraping a new node

We now want to create a 4th proxysql instance, and make it join the cluster.
All we need in this 4th instance (proxysql133) are at least one peer node and credentials to connect to it. For this, we can use the same config file we used for the other nodes, that has no users, no query rules (and no mysql servers) , but only proxysql servers.
This is the log of proxysql133 upon start:

2017-09-06 00:36:34 ProxySQL_Admin.cpp:6207:flush_error_log(): [INFO] ProxySQL version 1.4.2-31-g924c1fa_DEBUG
2017-09-06 00:36:34 ProxySQL_Admin.cpp:6210:flush_error_log(): [INFO] Detected OS: Linux c-lon33 4.4.0-78-generic #99-Ubuntu SMP Thu Apr 27 15:29:09 UTC 2017 x86_64
2017-09-06 00:36:34 main.cpp:703:ProxySQL_daemonize_phase2(): [INFO] Starting ProxySQL
2017-09-06 00:36:34 main.cpp:704:ProxySQL_daemonize_phase2(): [INFO] Sucessfully started
Main daemonize phase1 completed in 0.00089 secs.
2017-09-06 00:36:34 main.cpp:714:ProxySQL_daemonize_phase3(): [INFO] Angel process started ProxySQL process 41360
Standard ProxySQL Cluster rev. 0.1.0702_DEBUG -- ProxySQL_Cluster.cpp -- Wed Sep  6 00:10:30 2017
2017-09-06 00:36:34 ProxySQL_Admin.cpp:6164:Read_ProxySQL_Servers_from_configfile(): [INFO] Cluster: Adding ProxySQL Servers 172.16.3.130:6032 from config file
2017-09-06 00:36:34 ProxySQL_Admin.cpp:6164:Read_ProxySQL_Servers_from_configfile(): [INFO] Cluster: Adding ProxySQL Servers 172.16.3.131:6032 from config file
2017-09-06 00:36:34 ProxySQL_Admin.cpp:6164:Read_ProxySQL_Servers_from_configfile(): [INFO] Cluster: Adding ProxySQL Servers 172.16.3.132:6032 from config file
Admin initialized in 0.039395 secs.
Standard ProxySQL Admin rev. 0.2.0902_DEBUG -- ProxySQL_Admin.cpp -- Tue Sep  5 12:20:48 2017
Standard MySQL Threads Handler rev. 0.2.0902_DEBUG -- MySQL_Thread.cpp -- Tue Sep  5 12:20:48 2017
Main phase3 : GloMyLogger initialized in 4e-06 secs.
Standard MySQL Authentication rev. 0.2.0902_DEBUG -- MySQL_Authentication.cpp -- Wed Sep  6 00:10:30 2017
Main init phase2 completed in 0.042762 secs.
Main phase3 : GloMyLogger initialized in 3e-06 secs.
2017-09-06 00:36:34 MySQL_HostGroups_Manager.cpp:880:generate_mysql_servers_table(): [INFO] Dumping current MySQL Servers structures for hostgroup ALL
2017-09-06 00:36:34 MySQL_HostGroups_Manager.cpp:971:generate_mysql_replication_hostgroups_table(): [INFO] New mysql_replication_hostgroups table
2017-09-06 00:36:34 MySQL_HostGroups_Manager.cpp:1007:generate_mysql_group_replication_hostgroups_table(): [INFO] New mysql_group_replication_hostgroups table
2017-09-06 00:36:34 MySQL_HostGroups_Manager.cpp:880:generate_mysql_servers_table(): [INFO] Dumping current MySQL Servers structures for hostgroup ALL
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:238:ProxySQL_Node_Entry(): [INFO] Created new Cluster Node Entry for host 172.16.3.130:6032
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:238:ProxySQL_Node_Entry(): [INFO] Created new Cluster Node Entry for host 172.16.3.131:6032
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:238:ProxySQL_Node_Entry(): [INFO] Created new Cluster Node Entry for host 172.16.3.132:6032
Main phase3 : GloAdmin initialized in 0.003828 secs.
Standard Query Processor rev. 0.2.0902_DEBUG -- Query_Processor.cpp -- Tue Sep  5 12:20:48 2017
Main phase3 : Query Processor initialized in 0.000118 secs.
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:53:ProxySQL_Cluster_Monitor_thread(): [INFO] Cluster: starting thread for peer 172.16.3.130:6032
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:53:ProxySQL_Cluster_Monitor_thread(): [INFO] Cluster: starting thread for peer 172.16.3.131:6032
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:53:ProxySQL_Cluster_Monitor_thread(): [INFO] Cluster: starting thread for peer 172.16.3.132:6032
Main phase3 : MySQL Threads Handler initialized in 0.0027 secs.
In memory Standard Query Cache (SQC) rev. 1.2.0905_DEBUG -- Query_Cache.cpp -- Tue Sep  5 12:20:48 2017
Main phase3 : Query Cache initialized in 5.1e-05 secs.
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.131:6032, version 3, epoch 1504653665, checksum 0x81A6E7C4159B081D . Not syncing yet ...
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.131:6032, version 1, epoch 1504652633, checksum 0x0000000000000000 . Not syncing yet ...
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:347:set_checksums(): [INFO] Cluster: checksum for mysql_servers from peer 172.16.3.131:6032 matches with local checksum 0x0000000000000000 , we won't sync.
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:365:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.131:6032, version 1, epoch 1504652633, checksum 0x7C60129E5360AC28 . Not syncing yet ...
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:401:set_checksums(): [INFO] Cluster: detected a new checksum for proxysql_servers from peer 172.16.3.131:6032, version 1, epoch 1504652633, checksum 0x474020F334F98128 . Not syncing yet ...
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:403:set_checksums(): [INFO] Cluster: checksum for proxysql_servers from peer 172.16.3.131:6032 matches with local checksum 0x474020F334F98128 , we won't sync.
2017-09-06 00:36:34 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 1504653772, checksum 0x81A6E7C4159B081D . Not syncing yet ...
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.132:6032, version 1, epoch 1504653771, checksum 0x0000000000000000 . Not syncing yet ...
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:347:set_checksums(): [INFO] Cluster: checksum for mysql_servers from peer 172.16.3.132:6032 matches with local checksum 0x0000000000000000 , we won't sync.
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:365:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.132:6032, version 1, epoch 1504653771, checksum 0x7C60129E5360AC28 . Not syncing yet ...
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:401:set_checksums(): [INFO] Cluster: detected a new checksum for proxysql_servers from peer 172.16.3.132:6032, version 1, epoch 1504653771, checksum 0x474020F334F98128 . Not syncing yet ...
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:403:set_checksums(): [INFO] Cluster: checksum for proxysql_servers from peer 172.16.3.132:6032 matches with local checksum 0x474020F334F98128 , we won't sync.
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:325:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 172.16.3.130:6032, version 3, epoch 1504653665, checksum 0x81A6E7C4159B081D . Not syncing yet ...
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.130:6032, version 1, epoch 1504652633, checksum 0x0000000000000000 . Not syncing yet ...
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:347:set_checksums(): [INFO] Cluster: checksum for mysql_servers from peer 172.16.3.130:6032 matches with local checksum 0x0000000000000000 , we won't sync.
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:365:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_users from peer 172.16.3.130:6032, version 1, epoch 1504652633, checksum 0x7C60129E5360AC28 . Not syncing yet ...
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:401:set_checksums(): [INFO] Cluster: detected a new checksum for proxysql_servers from peer 172.16.3.130:6032, version 1, epoch 1504652633, checksum 0x474020F334F98128 . Not syncing yet ...
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:403:set_checksums(): [INFO] Cluster: checksum for proxysql_servers from peer 172.16.3.130:6032 matches with local checksum 0x474020F334F98128 , we won't sync.
Main phase3 : MySQL Threads Handler listeners started in 6.4e-05 secs.
Standard MySQL Monitor (StdMyMon) rev. 1.2.0723_DEBUG -- MySQL_Monitor.cpp -- Tue Sep  5 12:20:48 2017
Main phase3 : MySQL Monitor initialized in 0.00103 secs.
Main init phase3 completed in 0.011606 secs.
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.131:6032 with mysql_query_rules version 3, epoch 1504653665, diff_check 3. Own version: 1, epoch: 1504654594. Proceeding with remote sync
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.132:6032 with mysql_query_rules version 2, epoch 1504653772, diff_check 3. Own version: 1, epoch: 1504654594. Proceeding with remote sync
2017-09-06 00:36:34 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_query_rules version 3, epoch 1504653665, diff_check 3. Own version: 1, epoch: 1504654594. Proceeding with remote sync
2017-09-06 00:36:35 ProxySQL_Cluster.cpp:473:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.131:6032 with mysql_query_rules version 3, epoch 1504653665, diff_check 4. Own version: 1, epoch: 1504654594. Proceeding with remote sync
2017-09-06 00:36:35 ProxySQL_Cluster.cpp:1105:get_peer_to_sync_mysql_query_rules(): [INFO] Cluster: detected peer 172.16.3.131:6032 with mysql_query_rules version 3, epoch 1504653665
2017-09-06 00:36:35 ProxySQL_Cluster.cpp:572:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Fetching MySQL Query Rules from peer 172.16.3.131:6032 started
2017-09-06 00:36:35 ProxySQL_Cluster.cpp:622:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Fetching MySQL Query Rules from peer 172.16.3.131:6032 completed
2017-09-06 00:36:35 ProxySQL_Cluster.cpp:623:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Loading to runtime MySQL Query Rules from peer 172.16.3.131:6032
2017-09-06 00:36:35 ProxySQL_Cluster.cpp:626:pull_mysql_query_rules_from_peer(): [INFO] Cluster: Saving to disk MySQL Query Rules from peer 172.16.3.131:6032
2017-09-06 00:36:40 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.130:6032 with mysql_users version 1, epoch 1504652633, diff_check 30. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master.
2017-09-06 00:36:40 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.132:6032 with mysql_users version 1, epoch 1504653771, diff_check 30. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master.
2017-09-06 00:36:40 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.131:6032 with mysql_users version 1, epoch 1504652633, diff_check 30. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master.
2017-09-06 00:36:46 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.130:6032 with mysql_users version 1, epoch 1504652633, diff_check 60. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master.
2017-09-06 00:36:46 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.132:6032 with mysql_users version 1, epoch 1504653771, diff_check 60. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master.
2017-09-06 00:36:46 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.131:6032 with mysql_users version 1, epoch 1504652633, diff_check 60. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master.

Once again, the error log is very verbose, but what is important to note is that within 1 second:

  • it detects that proxysql_servers table is identical with the rest of the cluster, no need to sync
  • it detects that mysql_servers table is identical with the rest of the cluster (well, empty), no need to sync
  • it detects that msyql_query_rules table is different, and immediately syncs
  • it detects that msyql_users table is different, but doesn't sync . This is intentional: if the remote node has version=1, this means that this is its own version and not a cluster version. Executing LOAD MYSQL USERS TO RUNTIME in one of the cluster node will validate which version is correct.
    Why this happened? Before modifying the query rules in the previous section of this blog post I intentionally shutdown the whole cluster and restarted it before adding proxysql132, in order to intentionally create a conflict in mysql_users table and show that this will prevent automatic sync.

Therefore it is important to note that nodes with version=1 cannot be trust as source of truth, and they won't sync from each other.

To solve this issue is easy, as the log suggest: let's run LOAD MYSQL USERS TO RUNTIME on proxysql130, so that version will increase and the new added node will consider proxysql130 a source of truth:

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') FROMxysql_servers_checksums WHERE name='mysql_users' ORDER BY hostname;
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name        | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_users | 1       | 2017-09-05 23:03:53 | 0x7C60129E5360AC28 | 2017-09-05 23:03:53 | 2017-09-05 23:46:34 | 0          | 2017-09-05 23:46:34 |
| 172.16.3.131 | 6032 | mysql_users | 1       | 2017-09-05 23:03:53 | 0x7C60129E5360AC28 | 2017-09-05 23:03:54 | 2017-09-05 23:46:34 | 0          | 2017-09-05 23:46:34 |
| 172.16.3.132 | 6032 | mysql_users | 1       | 2017-09-05 23:22:51 | 0x7C60129E5360AC28 | 2017-09-05 23:03:55 | 2017-09-05 23:46:34 | 0          | 2017-09-05 23:46:34 |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
3 rows in set (0.00 sec)

Admin130> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (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;
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 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 23:46:56 | 0x7C60129E5360AC28 | 2017-09-05 23:03:53 | 2017-09-05 23:47:09 | 0          | 2017-09-05 23:47:09 |
| 172.16.3.131 | 6032 | mysql_users | 1       | 2017-09-05 23:03:53 | 0x7C60129E5360AC28 | 2017-09-05 23:03:54 | 2017-09-05 23:47:09 | 0          | 2017-09-05 23:47:09 |
| 172.16.3.132 | 6032 | mysql_users | 1       | 2017-09-05 23:22:51 | 0x7C60129E5360AC28 | 2017-09-05 23:03:55 | 2017-09-05 23:47:09 | 0          | 2017-09-05 23:47:09 |
+--------------+------+-------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
3 rows in set (0.00 sec)

There are few interesting points here:

  • the version changes only on local node, this because the other nodes have the same checksum, so they won't sync and load any configuration at runtime
  • proxysql133 is not in the list : this is intentional, and it is to prevent that a new node could corrupt the cluster

What happened on proxysql133? It finally synced mysql_users:

low sync. This message will be repeated every 30 checks until LOAD MYSQL SERVERS TO RUNTIME is executed on candidate master.
2017-09-06 00:46:47 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.130:6032 with mysql_users version 1, epoch 1504652633, diff_check 3060. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master.
2017-09-06 00:46:47 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.132:6032 with mysql_users version 1, epoch 1504653771, diff_check 3060. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master.
2017-09-06 00:46:47 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.131:6032 with mysql_users version 1, epoch 1504652633, diff_check 3060. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master.
2017-09-06 00:46:53 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.130:6032 with mysql_users version 1, epoch 1504652633, diff_check 3090. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master.
2017-09-06 00:46:53 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.132:6032 with mysql_users version 1, epoch 1504653771, diff_check 3090. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master.
2017-09-06 00:46:53 ProxySQL_Cluster.cpp:521:set_checksums(): [WARNING] Cluster: detected a peer 172.16.3.131:6032 with mysql_users version 1, epoch 1504652633, diff_check 3090. Own version: 1, epoch: 1504654594. diff_check is increasing, but version 1 doesn't allow sync. This message will be repeated every 30 checks until LOAD MYSQL USERS TO RUNTIME is executed on candidate master.
2017-09-06 00:46:56 ProxySQL_Cluster.cpp:515:set_checksums(): [INFO] Cluster: detected a peer 172.16.3.130:6032 with mysql_users version 2, epoch 1504655216, diff_check 3106. Own version: 1, epoch: 1504654594. Proceeding with remote sync
2017-09-06 00:46:56 ProxySQL_Cluster.cpp:1167:get_peer_to_sync_mysql_users(): [INFO] Cluster: detected peer 172.16.3.130:6032 with mysql_users version 2, epoch 1504655216
2017-09-06 00:46:56 ProxySQL_Cluster.cpp:671:pull_mysql_users_from_peer(): [INFO] Cluster: Fetching MySQL Users from peer 172.16.3.130:6032 started
2017-09-06 00:46:56 ProxySQL_Cluster.cpp:703:pull_mysql_users_from_peer(): [INFO] Cluster: Fetching MySQL Users from peer 172.16.3.130:6032 completed
2017-09-06 00:46:56 ProxySQL_Cluster.cpp:704:pull_mysql_users_from_peer(): [INFO] Cluster: Loading to runtime MySQL Users from peer 172.16.3.130:6032
2017-09-06 00:46:56 ProxySQL_Cluster.cpp:707:pull_mysql_users_from_peer(): [INFO] Cluster: Saving to disk MySQL Query Rules from peer 172.16.3.130:6032

Should the nodes in the original cluster knows about the new node?

Currently, the new node knows it is not part of the cluster:

Admin133> SELECT * FROM proxysql_servers;
+--------------+------+--------+-------------+
| hostname     | port | weight | comment     |
+--------------+------+--------+-------------+
| 172.16.3.130 | 6032 | 0      | proxysql130 |
| 172.16.3.131 | 6032 | 0      | proxysql131 |
| 172.16.3.132 | 6032 | 0      | proxysql132 |
+--------------+------+--------+-------------+
3 rows in set (0.00 sec)

Admin133> 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 | 3       | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0          | 2017-09-06 00:08:25 |
| 172.16.3.130 | 6032 | mysql_servers     | 1       | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0          | 2017-09-06 00:08:25 |
| 172.16.3.130 | 6032 | mysql_users       | 2       | 2017-09-05 23:46:56 | 0x7C60129E5360AC28 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0          | 2017-09-06 00:08:25 |
| 172.16.3.130 | 6032 | proxysql_servers  | 1       | 2017-09-05 23:03:53 | 0x474020F334F98128 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0          | 2017-09-06 00:08:25 |
| 172.16.3.131 | 6032 | mysql_query_rules | 3       | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0          | 2017-09-06 00:08:25 |
| 172.16.3.131 | 6032 | mysql_servers     | 1       | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0          | 2017-09-06 00:08:25 |
| 172.16.3.131 | 6032 | mysql_users       | 1       | 2017-09-05 23:03:53 | 0x7C60129E5360AC28 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0          | 2017-09-06 00:08:25 |
| 172.16.3.131 | 6032 | proxysql_servers  | 1       | 2017-09-05 23:03:53 | 0x474020F334F98128 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0          | 2017-09-06 00:08:25 |
| 172.16.3.132 | 6032 | mysql_query_rules | 2       | 2017-09-05 23:22:52 | 0x81A6E7C4159B081D | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0          | 2017-09-06 00:08:25 |
| 172.16.3.132 | 6032 | mysql_servers     | 1       | 2017-09-05 23:22:51 | 0x0000000000000000 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0          | 2017-09-06 00:08:25 |
| 172.16.3.132 | 6032 | mysql_users       | 1       | 2017-09-05 23:22:51 | 0x7C60129E5360AC28 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0          | 2017-09-06 00:08:25 |
| 172.16.3.132 | 6032 | proxysql_servers  | 1       | 2017-09-05 23:22:51 | 0x474020F334F98128 | 2017-09-05 23:36:34 | 2017-09-06 00:08:25 | 0          | 2017-09-06 00:08:25 |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
12 rows in set (0.00 sec)

Similarly, the other nodes don't know this new node exists (well, they know "something" from 172.16.3.133 is pulling configuration, but that is all they know):

Admin130> SELECT * FROM proxysql_servers;
+--------------+------+--------+-------------+
| hostname     | port | weight | comment     |
+--------------+------+--------+-------------+
| 172.16.3.130 | 6032 | 0      | proxysql130 |
| 172.16.3.131 | 6032 | 0      | proxysql131 |
| 172.16.3.132 | 6032 | 0      | proxysql132 |
+--------------+------+--------+-------------+
3 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 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 | 3       | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:21:05 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.130 | 6032 | mysql_servers     | 1       | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:53 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.130 | 6032 | mysql_users       | 2       | 2017-09-05 23:46:56 | 0x7C60129E5360AC28 | 2017-09-05 23:03:53 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.130 | 6032 | proxysql_servers  | 1       | 2017-09-05 23:03:53 | 0x474020F334F98128 | 2017-09-05 23:03:53 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.131 | 6032 | mysql_query_rules | 3       | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:21:06 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.131 | 6032 | mysql_servers     | 1       | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:54 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.131 | 6032 | mysql_users       | 1       | 2017-09-05 23:03:53 | 0x7C60129E5360AC28 | 2017-09-05 23:03:54 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.131 | 6032 | proxysql_servers  | 1       | 2017-09-05 23:03:53 | 0x474020F334F98128 | 2017-09-05 23:03:54 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.132 | 6032 | mysql_query_rules | 2       | 2017-09-05 23:22:52 | 0x81A6E7C4159B081D | 2017-09-05 23:22:52 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.132 | 6032 | mysql_servers     | 1       | 2017-09-05 23:22:51 | 0x0000000000000000 | 2017-09-05 23:03:55 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.132 | 6032 | mysql_users       | 1       | 2017-09-05 23:22:51 | 0x7C60129E5360AC28 | 2017-09-05 23:03:55 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.132 | 6032 | proxysql_servers  | 1       | 2017-09-05 23:22:51 | 0x474020F334F98128 | 2017-09-05 23:03:55 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
12 rows in set (0.00 sec)

Should the nodes in the original cluster knows about the new node? It depends!
The fact that the rest of the cluster doesn't know anything about the new node means that changes applied directly to the new node won't be replicated to the rest of the cluster.
This creates a core cluster where nodes know about each other and synchronize from each other, and nodes that can only sync from the core cluster without affecting it. This is a big plus: only the core cluster can be the source of truth.
If we want to expand the core cluster, it is enough to insert the new node in proxysql_servers table on any of the core cluster nodes.

Admin130> SELECT * FROM proxysql_servers;
+--------------+------+--------+-------------+
| hostname     | port | weight | comment     |
+--------------+------+--------+-------------+
| 172.16.3.130 | 6032 | 0      | proxysql130 |
| 172.16.3.131 | 6032 | 0      | proxysql131 |
| 172.16.3.132 | 6032 | 0      | proxysql132 |
+--------------+------+--------+-------------+
3 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 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 | 3       | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:21:05 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.130 | 6032 | mysql_servers     | 1       | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:53 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.130 | 6032 | mysql_users       | 2       | 2017-09-05 23:46:56 | 0x7C60129E5360AC28 | 2017-09-05 23:03:53 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.130 | 6032 | proxysql_servers  | 1       | 2017-09-05 23:03:53 | 0x474020F334F98128 | 2017-09-05 23:03:53 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.131 | 6032 | mysql_query_rules | 3       | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:21:06 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.131 | 6032 | mysql_servers     | 1       | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:54 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.131 | 6032 | mysql_users       | 1       | 2017-09-05 23:03:53 | 0x7C60129E5360AC28 | 2017-09-05 23:03:54 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.131 | 6032 | proxysql_servers  | 1       | 2017-09-05 23:03:53 | 0x474020F334F98128 | 2017-09-05 23:03:54 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.132 | 6032 | mysql_query_rules | 2       | 2017-09-05 23:22:52 | 0x81A6E7C4159B081D | 2017-09-05 23:22:52 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.132 | 6032 | mysql_servers     | 1       | 2017-09-05 23:22:51 | 0x0000000000000000 | 2017-09-05 23:03:55 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.132 | 6032 | mysql_users       | 1       | 2017-09-05 23:22:51 | 0x7C60129E5360AC28 | 2017-09-05 23:03:55 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
| 172.16.3.132 | 6032 | proxysql_servers  | 1       | 2017-09-05 23:22:51 | 0x474020F334F98128 | 2017-09-05 23:03:55 | 2017-09-06 00:09:54 | 0          | 2017-09-06 00:09:54 |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
12 rows in set (0.00 sec)

Admin130> INSERT INTO proxysql_servers VALUES ('172.16.3.133',6032,0,'proxysql133');
Query OK, 1 row affected (0.00 sec)

Admin130> LOAD PROXYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (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 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 | 3       | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:21:05 | 2017-09-06 00:11:53 | 0          | 2017-09-06 00:11:53 |
| 172.16.3.130 | 6032 | mysql_servers     | 1       | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:53 | 2017-09-06 00:11:53 | 0          | 2017-09-06 00:11:53 |
| 172.16.3.130 | 6032 | mysql_users       | 2       | 2017-09-05 23:46:56 | 0x7C60129E5360AC28 | 2017-09-05 23:03:53 | 2017-09-06 00:11:53 | 0          | 2017-09-06 00:11:53 |
| 172.16.3.130 | 6032 | proxysql_servers  | 2       | 2017-09-06 00:11:47 | 0x5E1BA8E79166D590 | 2017-09-06 00:11:48 | 2017-09-06 00:11:53 | 0          | 2017-09-06 00:11:53 |
| 172.16.3.131 | 6032 | mysql_query_rules | 3       | 2017-09-05 23:21:05 | 0x81A6E7C4159B081D | 2017-09-05 23:21:06 | 2017-09-06 00:11:53 | 0          | 2017-09-06 00:11:53 |
| 172.16.3.131 | 6032 | mysql_servers     | 1       | 2017-09-05 23:03:53 | 0x0000000000000000 | 2017-09-05 23:03:54 | 2017-09-06 00:11:53 | 0          | 2017-09-06 00:11:53 |
| 172.16.3.131 | 6032 | mysql_users       | 1       | 2017-09-05 23:03:53 | 0x7C60129E5360AC28 | 2017-09-05 23:03:54 | 2017-09-06 00:11:53 | 0          | 2017-09-06 00:11:53 |
| 172.16.3.131 | 6032 | proxysql_servers  | 2       | 2017-09-06 00:11:48 | 0x5E1BA8E79166D590 | 2017-09-06 00:11:48 | 2017-09-06 00:11:53 | 0          | 2017-09-06 00:11:53 |
| 172.16.3.132 | 6032 | mysql_query_rules | 2       | 2017-09-05 23:22:52 | 0x81A6E7C4159B081D | 2017-09-05 23:22:52 | 2017-09-06 00:11:53 | 0          | 2017-09-06 00:11:53 |
| 172.16.3.132 | 6032 | mysql_servers     | 1       | 2017-09-05 23:22:51 | 0x0000000000000000 | 2017-09-05 23:03:55 | 2017-09-06 00:11:53 | 0          | 2017-09-06 00:11:53 |
| 172.16.3.132 | 6032 | mysql_users       | 1       | 2017-09-05 23:22:51 | 0x7C60129E5360AC28 | 2017-09-05 23:03:55 | 2017-09-06 00:11:53 | 0          | 2017-09-06 00:11:53 |
| 172.16.3.132 | 6032 | proxysql_servers  | 2       | 2017-09-06 00:11:48 | 0x5E1BA8E79166D590 | 2017-09-06 00:11:48 | 2017-09-06 00:11:53 | 0          | 2017-09-06 00:11:53 |
| 172.16.3.133 | 6032 | mysql_query_rules | 2       | 2017-09-05 23:36:35 | 0x81A6E7C4159B081D | 2017-09-06 00:11:47 | 2017-09-06 00:11:53 | 0          | 2017-09-06 00:11:53 |
| 172.16.3.133 | 6032 | mysql_servers     | 1       | 2017-09-05 23:36:34 | 0x0000000000000000 | 2017-09-06 00:11:47 | 2017-09-06 00:11:53 | 0          | 2017-09-06 00:11:53 |
| 172.16.3.133 | 6032 | mysql_users       | 2       | 2017-09-05 23:46:56 | 0x7C60129E5360AC28 | 2017-09-06 00:11:47 | 2017-09-06 00:11:53 | 0          | 2017-09-06 00:11:53 |
| 172.16.3.133 | 6032 | proxysql_servers  | 2       | 2017-09-06 00:11:48 | 0x5E1BA8E79166D590 | 2017-09-06 00:11:48 | 2017-09-06 00:11:53 | 0          | 2017-09-06 00:11:53 |
+--------------+------+-------------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
16 rows in set (0.00 sec)

Now all the nodes know that proxysql133 is part of the cluster.
Note that the creation of a Cluster with core nodes and satellite nodes makes this solution highly scalable, because it is possible to setup a relarively small core cluster (dozen of nodes), and a very large number of satellite nodes (thousands).
This also allows interesting future enhancements, for example:

  • nodes outside the core cluster won't allow any reconfiguration other than reconfiguration originated in the core (Admin in read only mode)
  • nodes in the core cluster won't allow any reconfiguration if they are not part of the quorum (switch to read only)
  • stop handling traffic is the quorum in the core cluster is lost

Conclusion

In this second blog post about ProxySQL Cluster we showed how this solution allows to add new nodes, either as a core node or as a satellite node. We also showed how nodes re-synchronize if they have been outside the cluster for some time, and also how to handle conflicting configurations.
Finally, we described future roadmap on how to scale this solution and create a quorum.