MySQL MongoDB ES ClickHouse 写入对比 mysql 导入clickhouse

前言

有很多同学就会问，为什么MySql数据要放到ClikeHouse。有这样一个业务场景在MySQL环境中生成有关年终分析数据的大量报告，查询非常缓慢且费力。主要问题：

1.报告主要集中在两个较大的日志表（emp_Report_model，emp_details）上。

2.报告生成（过程）正在使用Count（*）语句来刺激每次调用时的聚合数据。

3.在MySQL中（使用MySQL 5.7），Count（*）非常慢，因为它需要对表中的所有行进行计数。（MySQL 8.0具有Innodb并行读取线程，可以使count（*）更快）

4.MySQL INDEX无法提供帮助，因为我们正在汇总完整的数据（每个调用中90％的数据），查询将是全表扫描（FTS）

因此Mydbops提供了一种解决方案：可以在Clickhouse的帮助下克服该问题 并将数据迁移到Clickhouse。

 

对此很多同学有疑问什么是ClikeHouse，为什么要使用ClikeHouse？

简单说一下ClickHouse是一个开源的面向列的数据库管理系统，能够使用SQL查询实时生成分析数据报告。相比MySql是关系型数据库在Count(*)上有天然的优势。

 

测试环境准备

服务器：1台

操作系统：Centos7

安装服务：ClikeHouse，MySql，Miniconda3

 

安装Miniconda3 

安装Miniconda3

wget https://repo.anaconda.com/miniconda/Miniconda3-latest-Linux-x86_64.sh

bash Miniconda3-latest-Linux-x86_64.sh

/root/.bashrc

conda -V查看是否安装成功

conda -V

如果找不到conda命令，可以手动配置环境变量

PATH=$PATH:/opt/conda3/miniconda3/bin  #路径名跟自己实际情况而定
export PATH

创建Python3.6环境

conda create -n py36 python=3.6

切换环境

source activate py36

 

ClikeHouse安装

安装非常简单，可在Clickhouse官方网站上找到这些步骤。

yum install rpm-build epel-release

curl -s https://packagecloud.io/install/repositories/altinity/clickhouse/script.rpm.sh | sudo bash

yum install -y mysql-community-devel python34-devel python34-pip gcc python-devel libevent-devel gcc-c++ kernel-devel libxslt-devel libffi-devel openssl-devel python36 python36-devel python36-libs python36-tools

安装Clickhouse服务

yum install -y clickhouse-server clickhouse-client

引入最为关键的ClikeHouse-MySql

pip install clickhouse-mysql

如报以下错误

Collecting clickhouse-mysql
  Using cached clickhouse_mysql-0.0.20200128-py3-none-any.whl (56 kB)
Requirement already satisfied: setuptools in ./envs/py36/lib/python3.6/site-packages (from clickhouse-mysql) (46.0.0.post20200309)
Collecting clickhouse-driver
  Downloading clickhouse_driver-0.1.3-cp36-cp36m-manylinux2010_x86_64.whl (595 kB)
     |████████████████████████████████| 595 kB 12 kB/s 
Collecting mysql-replication
  Downloading mysql-replication-0.21.tar.gz (38 kB)
Collecting mysqlclient
  Downloading mysqlclient-1.4.6.tar.gz (85 kB)
     |████████████████████████████████| 85 kB 9.7 kB/s 
    ERROR: Command errored out with exit status 1:
     command: /opt/conda/miniconda3/envs/py36/bin/python -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/tmp/pip-install-je5bg2oz/mysqlclient/setup.py'"'"'; __file__='"'"'/tmp/pip-install-je5bg2oz/mysqlclient/setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' egg_info --egg-base /tmp/pip-install-je5bg2oz/mysqlclient/pip-egg-info
         cwd: /tmp/pip-install-je5bg2oz/mysqlclient/
    Complete output (12 lines):
    /bin/sh: mysql_config: 未找到命令
    /bin/sh: mariadb_config: 未找到命令
    /bin/sh: mysql_config: 未找到命令
    Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "/tmp/pip-install-je5bg2oz/mysqlclient/setup.py", line 16, in <module>
        metadata, options = get_config()
      File "/tmp/pip-install-je5bg2oz/mysqlclient/setup_posix.py", line 61, in get_config
        libs = mysql_config("libs")
      File "/tmp/pip-install-je5bg2oz/mysqlclient/setup_posix.py", line 29, in mysql_config
        raise EnvironmentError("%s not found" % (_mysql_config_path,))
    OSError: mysql_config not found
    ----------------------------------------
ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output.

引入mysql依赖即可

yum install python-devel mysql-devel

启动ClikeHouse服务

/etc/init.d/clickhouse-server start

测试连接

clickhouse-client

 

第一步：从MySQL转储基于Clickhouse的架构结构

clickhouse-mysql  --src-host=192.168.0.20 --src-user=root --src-password=123456 --create-table-sql-template --with-create-database --src-tables=userlog_db.HttpLogInfo20200310 >HttpLogInfo20200310.sql

测试转储是否成功

less HttpLogInfo20200310.sql | grep CREATE

cat HttpLogInfo20200310.sql | head -n7

 

第二步将架构结构导入Clickhouse

这一步最容易报错，定义主键及分区列错误分区列必须是Date类型

clickhouse-client -mn < HttpLogInfo20200310.sql

 

第三步迁移数据并保持复制同步

在进行最终同步之前，我们需要开启mysql的二进制日志

server-id = 1
binlog_format = ROW
binlog_row_image = FULL

到目前为止我们有两种方式配置复制：

• 迁移现有数据，然后配置复制
• 迁移现有数据并一步配置复制

 

迁移现有数据

clickhouse-mysql  --src-host=192.168.0.2  --src-user=root  --src-password=123456 --migrate-table  --src-tables=userlog_db.HttpLogInfo20200310 --dst-host=127.0.0.1  --dst-schema userlog_db  --dst-table HttpLogInfo20200310  --log-file=HttpLogInfo20200310.log

less HttpLogInfo20200310.log | grep -i migra

配置复制

clickhouse-mysql --src-host=192.168.0.12 --src-user=root --src-password=123456 --src-tables=userlog_db.HttpLogInfo20200310 --dst-host=127.0.0.1 --dst-schema userlog_db --dst-table HttpLogInfo20200310 --src-resume --src-wait --nice-pause=1 --log-level=info --csvpool --mempool-max-flush-interval=60 --mempool-max-events-num=1000 --pump-data --src-server-id=1 --log-file=HttpLogInfo20200310.log

迁移现有数据并一步配置复制，在这里，我们需要使用复制命令定义标志–migrate-table。

clickhouse-mysql --src-host=192.168.0.12 --src-user=root --src-password=123456 --src-tables=userlog_db.HttpLogInfo20200310 --dst-host=127.0.0.1 --dst-schema userlog_db --dst-table HttpLogInfo20200310 --src-resume --src-wait --nice-pause=1 --log-level=info --csvpool --mempool-max-flush-interval=60 --mempool-max-events-num=1000 --pump-data --src-server-id=1 --migrate-table --log-file=HttpLogInfo20200310.log &

 

 

 

社区交流群：964347070