How to Work With ProxySQL Using Single Production User

[JabirBaig9395]

Hi Team,

I am testing proxysql to use in my environment, the thing is I have a single production user using which my application communicates to my databases, like this I have more application servers, the issue I am facing is how to connect from proxysql to my test boxes using single user, please find the details below

I can connect to my test boxes if I have multiple users in users table with default_hostgroup mapped like below
Proxysql> select * from mysql_users; +----------+--------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment | +----------+--------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+ | db1 | Password@321 | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | | | | db2 | Password@321 | 1 | 0 | 2 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | | | +----------+--------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+ 2 rows in set (0.00 sec)
here I have create 2 users with default_hostgroup mapped to mysql_servers
Proxysql> select * from mysql_servers; +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 10.5.126.221 | 3306 | 0 | ONLINE | 1 | 0 | 10000 | 0 | 0 | 0 | | | 2 | 10.5.130.203 | 3306 | 0 | ONLINE | 1 | 0 | 10000 | 0 | 0 | 0 | | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2 rows in set (0.00 sec)

and then I have added query rules as follows

Proxysql> select * from mysql_query_rules; +---------+--------+----------+------------+--------+--------------+------------+------------+--------+--------------+------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+ | rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | attributes | comment | +---------+--------+----------+------------+--------+--------------+------------+------------+--------+--------------+------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+ | 1 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | ([^])rank([^]) | 0 | CASELESS | NULL | \1rank\2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | | NULL | | 2 | 1 | db1 | NULL | 0 | hdbtestol801 | NULL | NULL | NULL | NULL | NULL | 0 | CASELESS | NULL | NULL | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | | NULL | | 3 | 1 | db2 | NULL | 0 | hdbtestol801 | NULL | NULL | NULL | NULL | NULL | 0 | CASELESS | NULL | NULL | 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | | NULL | +---------+--------+----------+------------+--------+--------------+------------+------------+--------+--------------+------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+ 3 rows in set (0.00 sec)

after creating multiple users like db1 and db2 I could able to connect to my test servers
`[root@hdbtestol801 ~]# mysql -udb1 -pPassword@321 -hhdbtestol801 -P6033 -e "select @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| @@hostname |
+---------------+
| hdbtest150p003 |
+---------------+

[root@hdbtestol801 ~]# mysql -udb2 -pPassword@321 -hhdbtestol801 -P6033 -e "select @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| @@hostname |
+---------------+
| hdbtest50p002 |
+---------------+
`
But when I am trying to connect using single user like 'db' with default_hostgroup as '*' I get connected to only one server all the time hdbtest150p003, can I have a piece of advice how I can connect to multiple database servers from proxysql using single user

[JavierJF]

Hi @JabirBaig9395,

this is purely a support question, and as the issue template says, this is not the place for these kind of questions. There is plenty documentation and tutorials on how to use mysql_query_rules for doing traffic redirection to multiple servers. I'm closing this issue as it's not an issue and doesn't follow the issue template.

Thanks, Javier.