# clickhouse ...
# mysql ...
# mysql2clickhouse
镜像: mariadb:latest
apt-get update -y apt-get install -y wget vim net-tools curl git cron axel zip unzip apt-get install -y cmake golang python3 python3-distutils apt-get install -y build-essential autoconf automake libtool apt-get install -y libmysqlclient-dev python3-dev apt-get dist-upgrade -y apt-get upgrade -y apt autoremove apt clean ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime python3 -V curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py python3 get-pip.py pip3 install mysqlclient pip3 install mysql-replication pip3 install clickhouse-driver pip3 install redis #pip3 install MySQL-python pip3 install mysql-connector==2.2.9 pip3 install pymysql
# proxysql安装 https://github.com/sysown/proxysql/releases
镜像: debian:latest
cd /opt
apt-get update -y
apt-get install -y wget vim net-tools curl git cron axel zip unzip
apt-get install -y cmake golang python3 python3-distutils
apt-get install -y build-essential autoconf automake libtool
apt-get install -y python3-dev libmariadb-dev-compat libmariadb-dev
apt-get install -y python3-dev
apt-get dist-upgrade -y
apt-get upgrade -y
apt autoremove
apt clean
ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
apt-get install -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
wget https://github.com/sysown/proxysql/releases/download/v2.0.10/proxysql_2.0.10-clickhouse-debian10_amd64.deb
dpkg -i proxysql_2.0.10-clickhouse-debian10_amd64.deb
# 启动（必须这样启动，否则是不支持clickhouse的）
proxysql --clickhouse-server
# 登录proxysql，设置账户
mysql -uadmin -padmin -h127.0.0.1 -P6032
INSERT INTO clickhouse_users VALUES ('clicku','clickp',1,100);
LOAD CLICKHOUSE USERS TO RUNTIME;
SAVE CLICKHOUSE USERS TO DISK;
# 使用proxysql连接到clickhouse
mysql -u clicku -pclickp -h 127.0.0.1 -P6090
show databases;
# mysql同步数据到clickhouse
--- mysql里面有个库yayun，库里面有张表tb1，同步这张表到clickhoue
mysql> use yayun; Database changed mysql> show create table tb1\G *************************** 1. row *************************** Table: tb1 Create Table: CREATE TABLE `tb1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `pay_money` decimal(20,2) NOT NULL DEFAULT '0.00', `pay_day` date NOT NULL, `pay_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)
--- clickhoue里面建库，建表
ck-server-01 :) create database yayun; CREATE DATABASE yayun Ok. 0 rows in set. Elapsed: 0.021 sec. ck-server-01 :)
--- 建表（clickhouse建表的格式以及字段类型和mysql完全不一样，如果字段少还可以自己建，如果字段多比较痛苦，可以使用clickhouse自带的从mysql导数据的命令来建表）,在建表之前需要进行授权，因为程序同步也是模拟一个从库拉取数据.
GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT ON *.* TO 'ch_repl'@'127.0.0.1' identified by '123';
--- 登陆clickhouse进行建表
ck-server-01 :) use yayun;
USE yayun
Ok.
0 rows in set. Elapsed: 0.001 sec.
ck-server-01 :) CREATE TABLE tb1
:-] ENGINE = MergeTree
:-] PARTITION BY toYYYYMM(pay_time)
:-] ORDER BY (pay_time) AS
:-] SELECT *
:-] FROM mysql('127.0.0.1:3306', 'yayun', 'tb1', 'ch_repl', '123') ;
CREATE TABLE tb1
ENGINE = MergeTree
PARTITION BY toYYYYMM(pay_time)
ORDER BY pay_time AS
SELECT *
FROM mysql('127.0.0.1:3306', 'yayun', 'tb1', 'ch_repl', '123')
Ok.
0 rows in set. Elapsed: 0.031 sec.这里使用MergeTree引擎，MergeTree是clickhouse里面最牛逼的引擎，支持海量数据，支持索引，支持分区，支持更新删除。toYYYYMM(pay_time)的意思是根据pay_time分区，粒度是按月。ORDER BY (pay_time)的意思是根据pay_time排序存储，同时也是索引。上面的create table命令如果mysql表里面以后数据那么数据也会一并进入clickhouse里面。通常会limit 1，然后更改一下表结构。上面没有报错的话我们看看clickhouse里面的表结构：
ck-server-01 :) show create table tb1; SHOW CREATE TABLE tb1 ┌─statement────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ CREATE TABLE yayun.tb1 (`id` UInt32, `pay_money` String, `pay_day` Date, `pay_time` DateTime) ENGINE = MergeTree PARTITION BY toYYYYMM(pay_time) ORDER BY pay_time SETTINGS index_granularity = 8192 │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.002 sec.
其中这里的index_granularity = 8192是指索引的粒度。如果数据量没有达到百亿，那么通常无需更改。
--- 表结构也创建完成以后, 现在配置同步程序配置文件：metainfo.conf
[root@ck-server-01 sync]# cat metainfo.conf # 从这里同步数据 [master_server] host='127.0.0.1' port=3306 user='ch_repl' passwd='123' server_id=101 # redis配置信息，用于存放pos点 [redis_server] host='127.0.0.1' port=6379 passwd='12345' log_pos_prefix='log_pos_' #把log_position记录到文件 [log_position] file='./repl_pos.log' # ch server信息，数据同步以后写入这里 [clickhouse_server] host=127.0.0.1 port=9000 passwd='' user='default' #字段大小写. 1是大写，0是小写 column_lower_upper=0 # 需要同步的数据库 [only_schemas] schemas='yayun' # 需要同步的表 [only_tables] tables='tb1' # 指定库表跳过DML语句(update,delete可选) [skip_dmls_sing] skip_delete_tb_name = '' skip_update_tb_name = '' #跳过所有表的DML语句（update，delete可选） [skip_dmls_all] #skip_type = 'delete' #skip_type = 'delete,update' skip_type = '' [bulk_insert_nums] #多少记录提交一次 insert_nums=10 #选择每隔多少秒同步一次,负数表示不启用,单位秒 interval=60 # 同步失败告警收件人 [failure_alarm] mail_host= 'xxx' mail_port= 25 mail_user= 'xxx' mail_pass= 'xxx' mail_send_from = 'xxx' alarm_mail = 'xxx' #日志存放路径 [repl_log] log_dir="/tmp/relication_mysql_clickhouse.log"
--- 设置pos点：和mysql搭建从库一样，配置从哪里开始同步，看mysql的pos点
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000069 | 4024404 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
--- 把pos点写入文件或者redis，我选择记录到文件就是
[root@ck-server-01 sync]# cat repl_pos.log [log_position] filename = mysql-bin.000069 position = 4024404 [root@ck-server-01 sync]#
--- 启动同步程序
pypy mysql-clickhouse-replication.py --help
--- 默认pos点就是记录文件，无需再指定记录binlog pos方式
[root@ck-server-01 sync]# pypy mysql-clickhouse-replication.py --conf metainfo.conf --debug 11:59:54 INFO 开始同步数据时间 2019-07-17 11:59:54 11:59:54 INFO 从服务器 127.0.0.1:3306 同步数据 11:59:54 INFO 读取binlog: mysql-bin.000069:4024404 11:59:54 INFO 同步到clickhouse server 127.0.0.1:9000 11:59:54 INFO 同步到clickhouse的数据库: ['yayun'] 11:59:54 INFO 同步到clickhouse的表: ['tb1']
--- mysql插入10条数据
mysql> insert into tb1 (pay_money,pay_day,pay_time)values('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00') ;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from tb1;
+----+-----------+------------+---------------------+
| id | pay_money | pay_day | pay_time |
+----+-----------+------------+---------------------+
| 1 | 66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
| 3 | 66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
| 5 | 66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
| 7 | 66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
| 9 | 66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
| 11 | 66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
| 13 | 66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
| 15 | 66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
| 17 | 66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
| 19 | 66.22 | 2019-06-29 | 2019-06-29 14:00:00 |--- 同步程序日志输出
[root@ck-server-01 sync]# pypy mysql-clickhouse-replication.py --conf metainfo.conf --debug
12:12:09 INFO 开始同步数据时间 2019-07-17 12:12:09
12:12:09 INFO 从服务器 127.0.0.1:3306 同步数据
12:12:09 INFO 读取binlog: mysql-bin.000069:4024404
12:12:09 INFO 同步到clickhouse server 127.0.0.1:9000
12:12:09 INFO 同步到clickhouse的数据库: ['yayun']
12:12:09 INFO 同步到clickhouse的表: ['tb1']
12:12:09 INFO INSERT 数据插入SQL: INSERT INTO yayun.tb1 VALUES, [{u'id': 1, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 3, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 5, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 7, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 9, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 11, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 13, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 15, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 17, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 19, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}]--- clickhoue数据查询
ck-server-01 :) select * from tb1; SELECT * FROM tb1 ┌─id─┬─pay_money─┬────pay_day─┬────────────pay_time─┐ │ 1 │ 66.22 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 3 │ 66.22 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 5 │ 66.22 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 7 │ 66.22 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 9 │ 66.22 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 11 │ 66.22 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 13 │ 66.22 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 15 │ 66.22 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 17 │ 66.22 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 19 │ 66.22 │ 2019-06-29 │ 2019-06-29 14:00:00 │ └────┴───────────┴────────────┴─────────────────────┘ 10 rows in set. Elapsed: 0.005 sec.
--- mysql数据更新
mysql> update tb1 set pay_money='88.88'; Query OK, 10 rows affected (0.00 sec) Rows matched: 10 Changed: 10 Warnings: 0 mysql> select * from tb1; +----+-----------+------------+---------------------+ | id | pay_money | pay_day | pay_time | +----+-----------+------------+---------------------+ | 1 | 88.88 | 2019-06-29 | 2019-06-29 14:00:00 | | 3 | 88.88 | 2019-06-29 | 2019-06-29 14:00:00 | | 5 | 88.88 | 2019-06-29 | 2019-06-29 14:00:00 | | 7 | 88.88 | 2019-06-29 | 2019-06-29 14:00:00 | | 9 | 88.88 | 2019-06-29 | 2019-06-29 14:00:00 | | 11 | 88.88 | 2019-06-29 | 2019-06-29 14:00:00 | | 13 | 88.88 | 2019-06-29 | 2019-06-29 14:00:00 | | 15 | 88.88 | 2019-06-29 | 2019-06-29 14:00:00 | | 17 | 88.88 | 2019-06-29 | 2019-06-29 14:00:00 | | 19 | 88.88 | 2019-06-29 | 2019-06-29 14:00:00 | +----+-----------+------------+---------------------+ 10 rows in set (0.00 sec)
--- clickhoue数据查询
ck-server-01 :) select * from tb1; SELECT * FROM tb1 ┌─id─┬─pay_money─┬────pay_day─┬────────────pay_time─┐ │ 1 │ 88.88 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 3 │ 88.88 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 5 │ 88.88 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 7 │ 88.88 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 9 │ 88.88 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 11 │ 88.88 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 13 │ 88.88 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 15 │ 88.88 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 17 │ 88.88 │ 2019-06-29 │ 2019-06-29 14:00:00 │ │ 19 │ 88.88 │ 2019-06-29 │ 2019-06-29 14:00:00 │ └────┴───────────┴────────────┴─────────────────────┘ 10 rows in set. Elapsed: 0.009 sec.
https://github.com/yymysql/mysql-clickhouse-replication