第02期：ClickHouse 单机部署以及从 MySQL 增量同步数据

mysql 同步数据到 clickhouse

mysql> use yayun;Database changedmysql> show create table tb1\G*************************** 1. row ***************************       Table: tb1Create 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=utf8mb41 row in set (0.00 sec)
1. clickhouse 里面建库，建表。

ck-server-01 :) create database yayun;
CREATE DATABASE yayun
Ok.
0 rows in set. Elapsed: 0.021 sec.

GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT ON *.* TO 'ch_repl'@'127.0.0.1' identified by '123';

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 tb1ENGINE = MergeTreePARTITION BY toYYYYMM(pay_time)ORDER BY pay_time ASSELECT *FROM mysql('127.0.0.1:3306', 'yayun', 'tb1', 'ch_repl', '123') 
Ok.
0 rows in set. Elapsed: 0.031 sec.

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. 

[root@ck-server-01 sync]# cat metainfo.conf # 从这里同步数据[master_server]host='127.0.0.1'port=3306user='ch_repl'passwd='123'server_id=101
# redis配置信息，用于存放pos点[redis_server]host='127.0.0.1'port=6379passwd='12345'log_pos_prefix='log_pos_'
#把log_position记录到文件[log_position]file='./repl_pos.log'
# ch server信息，数据同步以后写入这里[clickhouse_server]host=127.0.0.1port=9000passwd=''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= 25mail_user= 'xxx'mail_pass= 'xxx'mail_send_from = 'xxx'alarm_mail = 'xxx'
#日志存放路径[repl_log]log_dir="/tmp/relication_mysql_clickhouse.log"