目录
介绍
安装
2.1安装成功完成后，启动ProxySQL。
创建ClickHouse用户
从MySQL连接到客户端ClickHouse
查询ClickHouse之类的MySQL
局限性
6.1结论– ProxySQL版本2.0.8新功能和增强功能
介绍
如今，许多客户使用它ClickHouse，可用于列式数据库分析和MySQL数据存档。您可以使用clickhouse-client从ClickHouse访问数据，但这需要一定的基础知识和技术限制。使用我们的客户MySQL感觉很放松，所以总是喜欢用MySQL客户端进行ClickHouse查询、分析和报告。幸运的是ProxySQL充当了ClickHouse和MySQL对我们和全球客户来说，客户端之间的最佳桥梁确实是个好消息。本文介绍了如何介绍这篇文章ClickHouse中使用MySQL客户端。
安装
https://github.com/sysown/proxysql/releases/(** 下载以clickhouse开头的包)
依赖项安装：
yum -y install perl-DBD-MySQL
安装成功完成后，启动ProxySQL。
# The default configuration file is this:
/etc/proxysql.cnf
# There is no such data directory by default:
mkdir / var / lib / proxysql
# start up
proxysql --clickhouse-server
# ProxySQL will default to daemon mode in the background
创建ClickHouse用户
使用密码在ProxySQL中为ClickHouse创建用户的密码不是ClickHouse但用于访问的配置ProxySQL：
# ProxySQL port is 6032, the default username and password are written in the configuration file
root@10.xxxx: / root # mysql -h 127.0.0.1 -P 6032 -uadmin -padmin
Welcome to the MariaDB monitor. Commands end with; or \ g.
Your MySQL connection id is 3
Server version: 5.6.81 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\ h' for help. Type '\ c' to clear the current input statement.
MySQL [(none)]> INSERT INTO clickhouse_users VALUES ('chuser', 'chpasswd', 1,100);
Query OK, 1 row affected (0.00 sec)
MySQL [(none)] > select * from clickhouse_users;
---------- ---------- -------- -----------------
| username | password | active | max_connections |
---------- ---------- -------- -----------------
| chuser | chpasswd | 1 | 100 |
---------- ---------- -------- -----------------
1 row in set (0.00 sec)
MySQL [(none)]> LOAD CLICKHOUSE USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> SAVE CLICKHOUSE USERS TO DISK;
Query OK, 0 rows affected (0.00 sec)
从MySQL连接到客户端ClickHouse
默认情况下，ProxySQL打开端口6090以接收用户对ClickHouse的访问：
# Use username and password above
# If it is a different machine, remember to change the IP
root@10.xxxx: / root # mysql -h 127.0.0.1 -P 6090 -uclicku -pclickp --prompt "ProxySQL-To-ClickHouse>"
Welcome to the MariaDB monitor. Commands end with; or \ g.
Your MySQL connection id is 64
Server version: 5.6.81 (ProxySQL ClickHouse Module)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\ h' for help. Type '\ c' to clear the current input statement.
ProxySQL-To-ClickHouse >
查询ClickHouse之类的MySQL
MySQL [(none)] > select version ();
-------------------
| version |
-------------------
| 5.6.81-clickhouse |
-------------------
1 row in set (0.00 sec)
MySQL [(none)] > select now ();
---------------------
| now () |
---------------------
| 2019-12-25 20:17:14 |
---------------------
1 row in set (0.00 sec)
MySQL [(none)] > select today ();
------------
| today () |
------------
| 2019-12-25 |
------------
1 row in set (0.00 sec)
# Our table is over 55 billion
ProxySQL-To-ClickHouse > select count (*) from mysql_audit_log_data;
-------------
| count () |
-------------
| 539124837571 |
-------------
1 row in set (8.31 sec)
局限性
此ProxySQL解决方案仅在本地ClickHouse有效(注意)-ClickHouse在这个生态系统/推荐的解决方案中有密码)
ProxySQL查询重写限制–简单的查询可以无缝工作，复杂的查询重写非常昂贵，并且可能存在某些级别的SQL语义限制
结论– ProxySQL版本2.0.8新功能和增强功能
将默认的 max_allowed_packet 从4M 更改为64M
添加了对mysqldump 8.0和Admin ＃2340的支持
添加新变量mysql-aurora_max_lag_ms_only_read_from_replicas：如果使用了max_lag_ms且writer如果至少N个副本是读取器主机组的最佳候选人，则将排除此之外writer。
添加未知字符集和排序规则ID大于255的支持#1273
添加新变量mysql-log_unhealthy_connections禁止关闭与不健康客户端连接的消息
使用khash重新实现rules_fast_routing
添加了对SET CHARACTER SET ＃1692的支持
在多个Galera对同一节点增加支持#2290
为2019年的错误增加了更详细的输出(无法初始化字符集)#2273/p>
为mysql_replication_hostgroups.check_type ＃2186添加了更多可能的值
只读| innodb_read_only
read_only和innodb_read_only
添加了对RHEL / CentOS 8的支持和软件包
参考文献：
http://jackpgao.github.io/2017/12/19/Using-ClickHouse-like-MySQL-by-ProxySQL/
https://www.proxysql.com/blog/clickhouse-and-proxysql-queries-rewrite
https://www.altinity.com/blog/2018/7/13/clickhouse-and-proxysql-queries-rewrite
https://github.com/sysown/proxysql/releases
来源：https://minervadb.com/index.php/2019/12/25/how-to-use-proxysql-to-work-on-clickhouse-like-mysql/