Configure Proxysql on Ubuntu Server Part-2

This is the second part of configure proxysql on ubuntu server. This time, we will create group to handle write and read query. So each querry can be loadbalanced and scaled if needed.

You can check my first post about Configure Proxysql on Ubuntu Server here.

1. Make sure that you have already installed and configured ProxySQL as a proxy for your MySQL server, as described in my previous post here.
2. Connect to the ProxySQL admin interface:

mysql -u admin -padmin -h 127.0.0.1 -P 6032

3. Create two new hostgroups for read and write operations:

INSERT INTO mysql_hostgroups (hostgroup_id, writer_hostgroup, comment) VALUES (1, 0, 'Read');
INSERT INTO mysql_hostgroups (hostgroup_id, writer_hostgroup, comment) VALUES (2, 0, 'Write');

4. Assign your backend MySQL servers to the new hostgroups. For example, if you have a single MySQL server running on IP address 192.168.1.100, you can assign it to the read hostgroup:

UPDATE mysql_servers SET hostgroup_id = 1 WHERE hostname = '192.168.1.100';

5. Create a new user for read-only operations, and assign it to the read hostgroup:

INSERT INTO mysql_users (username, password, active, default_hostgroup) VALUES ('readonly', 'readonlypassword', 1, 1);

6. Create a new user for read-write operations, and assign it to the write hostgroup:

INSERT INTO mysql_users (username, password, active, default_hostgroup) VALUES ('readwrite', 'readwritepassword', 1, 2);

7. Load the new hostgroups, servers, and users to runtime and disk

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;

8. Create new rules to route the queries based on the user and the query text

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, '^SELECT', 1, 1);
INSERT INTO mysql_query_rules (rule_id, active, username, destination_hostgroup, apply) VALUES (2, 1, 'readonly', 1, 1);

9. Load the new query rules to runtime and disk

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

10. Restart the proxysql service

sudo service proxysql restart

Now, when a user connects to ProxySQL with the “readonly” username, they will be routed to the read hostgroup and can only perform SELECT queries. When a user connects with the “readwrite” username, or any other username, they will be routed to the write hostgroup and can perform all types of queries.