-1

I have a Proxysql setup with two servers, one that can handle reads and writes (acm-db-main-1) and one that's set up as read-only (acm-db-main-2). I'd like to configure some users that are heavy read-only users to ONLY connect to acm-db-main-2. Currently, I find that reads are getting distributed across both servers.

Any suggestions on how to achieve this?

mysql_servers =
(
    { address="acm-db-main-1.ldap.mydomain.local" , port=3306 , hostgroup_id=2 , max_connections=500 },
    { address="acm-db-main-2.ldap.mydomain.local" , port=3306 , hostgroup_id=2 , max_connections=500 },
)
 
mysql_replication_hostgroups = 
(
    { writer_hostgroup=1, reader_hostgroup=2, check_type="read_only" },
)

mysql_users =
(
    { username = "billing_service", password = "${main_billing_service}", default_hostgroup = 1, transaction_persistent = 0, active = 1 },
   { username = "oscread", password = "${main_oscread}", default_hostgroup = 2, transaction_persistent = 0, active = 1 },
   ...
)
Geoff Maddock
  • 1,700
  • 3
  • 26
  • 47
  • There may be more than one option to achieve what you need. One option may be to add a new server (`mysql_servers`) with a different `hostgroup_id` (for example: 3) for the `acm-db-main-2` server (read-only) and assign the user's `default_hostgroup` ( `mysql_users`) to `default_hostgroup = 3`. – wchiquito Sep 01 '23 at 09:11

0 Answers0