Redirecting some heavy queries on replica node

[nanazedginidze]

after executing some queries from my user when replication slave is stopped it is still going to replica.
So my question is , if proxy sql has support of replica node , standard pxc + replica .

proxysql version-latest
pxc version -5.7

Here is my config:

mysql> select * from mysql_galera_hostgroups\G
*************************** 1. row ***************************
       writer_hostgroup: 10
backup_writer_hostgroup: 12
       reader_hostgroup: 11
      offline_hostgroup: 13
                 active: 1
            max_writers: 1
  writer_is_also_reader: 2
max_transactions_behind: 0
                comment: NULL

mysql> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight  | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           |node 1 | 3306 | 0         | ONLINE | 1000000 | 0           | 1000            | 0                   | 0       | 0              |         |
| 12           | node 3 | 3306 | 0         | ONLINE | 1       | 0           | 1000            | 0                   | 0       | 0              |         |
| 12           |node 2 | 3306 | 0         | ONLINE | 1       | 0           | 1000            | 0                   | 0       | 0              |         |
| 11           | node3 | 3306 | 0         | ONLINE | 1       | 0           | 1000            | 0                   | 0       | 0              |         |
| 11           | node 2 | 3306 | 0         | ONLINE | 1       | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | node 3 | 3306 | 0         | ONLINE | 1       | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | node 2 | 3306 | 0         | ONLINE | 1       | 0           | 1000            | 0                   | 0       | 0              |         |
| 11           |replica node| 3306 | 0         | ONLINE | 1000000 | 0           | 1000            | 300                 | 0       | 0              |         |
| 13           | node 2 | 3306 | 0         | ONLINE | 1       | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+-----------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+

mysql> SELECT * FROM RUNTIME_MYSQL_SERVERS;
+--------------+---------------+------+-----------+---------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status  | weight  | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+---------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | node1 | 3306 | 0         | ONLINE  | 1000000 | 0           | 1000            | 0                   | 0       | 0              |         |
| 13           | replica | 3306 | 0         | ONLINE  | 1000000 | 0           | 1000            | 300                 | 0       | 0              |         |
| 13           | node2 | 3306 | 0         | ONLINE  | 1       | 0           | 1000            | 0                   | 0       | 0              |         |
| 11           | node2  | 3306 | 0         | ONLINE  | 1       | 0           | 1000            | 0                   | 0       | 0              |         |
| 11           | node3 | 3306 | 0         | ONLINE  | 1       | 0           | 1000            | 0                   | 0       | 0              |         |
| 12           | node3 | 3306 | 0         | ONLINE  | 1       | 0           | 1000            | 0                   | 0       | 0              |         |
| 12           | node2  | 3306 | 0         | ONLINE  | 1       | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | node2   | 3306 | 0         | SHUNNED | 1       | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | node3 | 3306 | 0         | SHUNNED | 1       | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+-----------+---------+---------+-------------+-----------------+---------------------+---------+----------------+---------+

I am testing firstly to redirect queries just from my user on replica then switch off the replica and see where it will go

mysql> select * from mysql_query_rules\G
*************************** 1. row ***************************
              rule_id: 1
               active: 1
             username:myuser
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: NULL
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 13
            cache_ttl: NULL
   cache_empty_result: NULL
        cache_timeout: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
    next_query_flagIN: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
               OK_msg: NULL
          sticky_conn: NULL
            multiplex: NULL
  gtid_from_hostgroup: NULL
                  log: 1
                apply: 1
           attributes:
              comment: NULL

[renecannao]

Hi @NanukaZedginidze .
The content of mysql_servers and runtime_mysql_servers are so different (they are clearly edited manually, and apparently in an incorrect way) that it makes really difficult to understand what are you talking about.

myuser seems to be using hostgroup 13 , that is a configured in mysql_galera_hostgroups , thus should be a Galera node, but you are speaking about "replication slave".
It is all very confusing.
Can you please clarify everything?

Thanks

[nanazedginidze]

Hi, @renecannao
yes, they are clearly edited manually because I tryed to put it in offline_hostgroup, because standard config did not work.
Firstly I want to redirecting queries from my user on replica node, the config which 'was" used is below :
queries executing from my user was going to node 2 at the moment when replica had high weight in "reader" hostgroup.
how can I "Force" proxy to redirect queries from my user on replica node?

`mysql> select * from mysql_galera_hostgroups\G
*************************** 1. row ***************************
       writer_hostgroup: 10
backup_writer_hostgroup: 12
       reader_hostgroup: 11
      offline_hostgroup: 13
                 active: 1
            max_writers: 1
  writer_is_also_reader: 2
max_transactions_behind: 0
                comment: NULL
1 row in set (0.00 sec)

`

mysql> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+---------+-------------+-----------------+---------------------+---------+---------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight  | compression | max_connections | max_replication_lag | use_ssl | max_lateny_ms | comment |
+--------------+---------------+------+-----------+--------+---------+-------------+-----------------+---------------------+---------+---------------+---------+
| 10           | node2 | 3306 | 0         | ONLINE | 1       | 0           | 1000            | 0                   | 0       | 0             |         |
| 11           | node3 | 3306 | 0         | ONLINE | 1       | 0           | 1000            | 0                   | 0       | 0             |         |
| 11           | replica | 3306 | 0         | ONLINE | 1000000 | 0           | 1000            | 300                 | 0       | 0             |         |
| 11           | node2  | 3306 | 0         | ONLINE | 1       | 0           | 1000            | 0                   | 0       | 0             |         |
| 12           | node2  | 3306 | 0         | ONLINE | 1       | 0           | 1000            | 0                   | 0       | 0             |         |
| 10           | node1 | 3306 | 0         | ONLINE | 1000000 | 0           | 1000            | 0                   | 0       | 0             |         |
+--------------+---------------+------+-----------+--------+---------+-------------+-----------------+---------------------+---------+---------------+---------+


mysql> SELECT * FROM RUNTIME_MYSQL_SERVERS;
+--------------+---------------+------+-----------+--------+---------+-------------+-----------------+---------------------+---------+---------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight  | compression | max_connections | max_replication_lag | use_ssl | max_lateny_ms | comment |
+--------------+---------------+------+-----------+--------+---------+-------------+-----------------+---------------------+---------+---------------+---------+
| 10           | node1 | 3306 | 0         | ONLINE | 1000000 | 0           | 1000            | 0                   | 0       | 0             |         |
| 11           | replica | 3306 | 0         | ONLINE | 1000000 | 0           | 1000            | 300                 | 0       | 0             |         |
| 11           | node3 | 3306 | 0         | ONLINE | 1       | 0           | 1000            | 0                   | 0       | 0             |         |
| 11           | node2 | 3306 | 0         | ONLINE | 1       | 0           | 1000            | 0                   | 0       | 0             |         |
| 12           | node2 | 3306 | 0         | ONLINE | 1       | 0           | 1000            | 0                   | 0       | 0             |         |
| 10           | node2 | 3306 | 0         | ONLINE | 1       | 0           | 1000            | 0                   | 0       | 0             |         |
+--------------+---------------+------+-----------+--------+---------+-------------+-----------------+---------------------+---------+---------------+---------+

mysql> select * from mysql_query_rules\G
*************************** 1. row ***************************
              rule_id: 1
               active: 1
             username: myuser
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: NULL
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 11
            cache_ttl: NULL
   cache_empty_result: NULL
        cache_timeout: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
    next_query_flagIN: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
               OK_msg: NULL
          sticky_conn: NULL
            multiplex: NULL
  gtid_from_hostgroup: NULL
                  log: 1
                apply: 1
           attributes:
              comment: NULL

[renecannao]

Hi @NanukaZedginidze .
As I hinted in my previous comment, you seem to be configuring a replication slave as part of a Galera cluster .
Let me say it in another way: you are telling proxysql (configuring) that the replica is part of Galera, proxysql detects that is not (if you look at the error log you would see that) and put it in the offline_hostgroup.
It seems to me you have a configuration problem: you want to use a replica, but you configure it as a Galera node.