如何使用proxysql ，配置规则，分数据库查询数据?

Question

我希望使用proxysql在6033端口打通mysql和clickhouse,配置了规则，查询的表明开头是ck_ 则在clickhouse查询，其余在mysql查询
使用软件版本如下

[root@localhost ~]# proxysql --version
ProxySQL version 2.4.3-0-gf1b25b8, codename Truls

[root@localhost ~]# mysql --version
mysql  Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)

[root@localhost ~]# clickhouse-client --version
ClickHouse client version 22.8.4.7 (official build).

proxysql配置如下
mysql_users

[root@localhost ~]# mysql -uadmin -padmin  -P6032 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 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 |
+----------+--------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| issa     | 123456 | 0      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 500             |            |         |
+----------+--------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
1 row in set (0.00 sec)

mysql_servers

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    |
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------+
| 0            | 127.0.0.1   | 3306 | 0         | ONLINE | 1      | 0           | 2048            | 0                   | 0       | 0              | MySQL      |
| 1            | 127.0.0.1   | 9004 | 0         | ONLINE | 1      | 0           | 2048            | 0                   | 0       | 0              | ClickHouse |
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------+
3 rows in set (0.00 sec)

mysql_query_rules

mysql> 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 |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------------+--------------------------------------------+----------------------+-----------------+---------+--------------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
| 0       | 1      |          | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | SSA\.|INFORMATION_SCHEMA\. | NULL                                       | 0                    | CASELESS        | NULL    | NULL               | 0                     | NULL      | NULL               | NULL          | NULL      | NULL    | 3       | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     |            | NULL    |
| 1       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SET PROFILING             | NULL                                       | 0                    | CASELESS        | NULL    | NULL               | NULL                  | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      |        | NULL        | NULL      | NULL                | NULL | 1     |            | NULL    |
| 2       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SET SQL_MODE              | NULL                                       | 0                    | CASELESS        | NULL    | NULL               | NULL                  | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      |        | NULL        | NULL      | NULL                | NULL | 1     |            | NULL    |
| 3       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SET SQL_SELECT_LIMIT      | NULL                                       | 0                    | CASELESS        | NULL    | NULL               | NULL                  | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      |        | NULL        | NULL      | NULL                | NULL | 1     |            | NULL    |
| 4       | 1      |          | default    | 0      | NULL        | 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    |
| 5       | 1      |          | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | default\.                  | NULL                                       | 0                    | CASELESS        | NULL    | NULL               | 1                     | NULL      | NULL               | NULL          | NULL      | NULL    | 3       | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     |            | NULL    |
| 6       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL                       | CK_(INFO|THREAT|SCENE|SYS|ALARM|XCTI|XEBA) | 1                    | CASELESS        | NULL    | NULL               | 0                     | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     |            | NULL    |
| 7       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL                       | CK_INFO                                    | 0                    | CASELESS,GLOBAL | NULL    |  default.CK_INFO   | 1                     | 180000    | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 0     |            | NULL    |
| 8       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL                       | CK_THREAT                                  | 0                    | CASELESS,GLOBAL | NULL    |  default.CK_THREAT | 1                     | 180000    | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 0     |            | NULL    |
| 9       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL                       | CK_SCENE                                   | 0                    | CASELESS,GLOBAL | NULL    |  default.CK_SCENE  | 1                     | 180000    | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 0     |            | NULL    |
| 10      | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL                       | CK_SYS                                     | 0                    | CASELESS,GLOBAL | NULL    |  default.CK_SYS    | 1                     | 180000    | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 0     |            | NULL    |
| 11      | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL                       | CK_ALARM                                   | 0                    | CASELESS,GLOBAL | NULL    |  default.CK_ALARM  | 1                     | 180000    | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 0     |            | NULL    |
| 12      | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL                       | CK_XCTI                                    | 0                    | CASELESS,GLOBAL | NULL    |  default.CK_XCTI   | 1                     | 180000    | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 0     |            | NULL    |
| 13      | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL                       | CK_XEBA                                    | 0                    | CASELESS,GLOBAL | NULL    |  default.CK_XEBA   | 1                     | 180000    | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 0     |            | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------------+--------------------------------------------+----------------------+-----------------+---------+--------------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
14 rows in set (0.00 sec)

但是，在6033端口，use default 时无法切换ck

mysql> use default;
ERROR 1049 (42000): Unknown database 'default'

请问应该如何配置啊？

Answer 1

clickhouse 新增用户的时候不要用create 在user.xml里添加即可