In this article I talked about a single ProxySQL in front of multiple databases servers.

We often need replicated configuration. This can be usefull (but not mandatory) to be prepared for the next step : high availability.

How to configure a ProxySQL cluster

Since the version 2, ProxySQL has a builtin replication mechanism. This means that the software is able to replicate some of its table to another ProxySQL instance.

To start, we need a new ProxySQL server and that’s all.

In the ProxySQL shell (both), add another admin user (proxysql-cluster-admin) in the admin-admin_credentials variable :

update global_variables set variable_value='admin:admin;proxysql-cluster-admin:proxysql-cluster-admin-pass' where variable_name='admin-admin_credentials';
  • Add the proxysql-cluster-admin user as the cluster administrator and set a password :
update global_variables set variable_value='proxysql-cluster-admin' where variable_name='admin-cluster_username';
update global_variables set variable_value='proxysql-cluster-admin-pass' where variable_name='admin-cluster_password';
  • Change the check frequency
update global_variables set variable_value=200 where variable_name='admin-cluster_check_interval_ms';
update global_variables set variable_value=100 where variable_name='admin-cluster_check_status_frequency';
  • Add the settings to sync
update global_variables set variable_value='true' where variable_name='admin-cluster_mysql_query_rules_save_to_disk';
update global_variables set variable_value='true' where variable_name='admin-cluster_mysql_servers_save_to_disk';
update global_variables set variable_value='true' where variable_name='admin-cluster_mysql_users_save_to_disk';
update global_variables set variable_value='true' where variable_name='admin-cluster_proxysql_servers_save_to_disk';
update global_variables set variable_value=3 where variable_name='admin-cluster_mysql_query_rules_diffs_before_sync';
update global_variables set variable_value=3 where variable_name='admin-cluster_mysql_servers_diffs_before_sync';
update global_variables set variable_value=3 where variable_name='admin-cluster_mysql_users_diffs_before_sync';
update global_variables set variable_value=3 where variable_name='admin-cluster_proxysql_servers_diffs_before_sync';
  • Finally, load the configurations to runtime and save to disk
load admin variables to runtime;
save admin variables to disk;
  • One more thing to do : declare the two (or more) ProxySQL nodes :
INSERT INTO proxysql_servers (hostname,port,weight,comment) VALUES ('192.168.56.1',6032,100,'psql01');
INSERT INTO proxysql_servers (hostname,port,weight,comment) VALUES ('192.168.56.2',6032,100,'psql02');
LOAD PROXYSQL SERVERS TO RUNTIME;
SAVE PROXYSQL SERVERS TO DISK;

Now mysql_servers, mysql_query_rules, mysql_users, mysql_proxy_servers variables will be replicated instantly when you change them.

There are no priorities about the editing server, you can do your changes on the server you want.

If you want to do a test, you can add a MySQL user on one of your proxy :

ProxySQL>INSERT INTO mysql_users (username,password) values ('test','test');

On the other side, you just have to check if this test user is present :

ProxySQL> SELECT * from mysql_users;

This statement should return the user previously created. If not you can check the logs and try to figure out why the replication did not worked with cat /var/lib/proxysql/proxysql.log.

Enjoy, you just created your ProxySQL cluster 😉