同步注意事项:1、库名规范不能有“-”,表名规范不能有“-”
- CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
- ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')
参数说明:
host:port
— MySQL服务地址database
— MySQL数据库名称user
— MySQL用户名password
— MySQL用户密码MySQL引擎将远程的MySQL服务器中的表/库映射到ClickHouse中,MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中(等同于直接使用mysql)
- 数据库同步:
- CREATE DATABASE IF NOT EXISTS mysql_db ENGINE = MySQL ('localhost:3306', 'mysql', 'root', '123456');
-
- 表同步:
- CREATE TABLE IF NOT EXISTS tmp ENGINE = MergeTree ORDER BY id AS SELECT * FROM mysql('localhost:3306','test','user','root','123456')
- CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
- ENGINE = MaterializedMySQL('host:port', ['database' | database], 'user', 'password') [SETTINGS ...]
- [TABLE OVERRIDE table1 (...), TABLE OVERRIDE table2 (...)]
创建ClickHouse数据库,包含MySQL中所有的表,以及这些表中的所有数据。
ClickHouse服务器作为MySQL副本工作。它读取binlog并执行DDL和DML查询
vim /etc/mysql/my.cnf
- [mysqld]
- # 指定binlog日志存储位置
- #log-bin=/data/logs/mysql/mysql-bin.log
- log-bin=/var/lib/mysql/mysql-bin
-
- # 开启GTID模式
- gtid-mode=ON
- # 设置主从强一致性
- enforce-gtid-consistency=1
- # 记录日志
- log-slave-updates=1
- binlog_format=ROW
- #开启物化引擎
- SET allow_experimental_database_materialized_mysql=1;
- CREATE DATABASE yfc
- ENGINE = MaterializeMySQL('localhost:3306', 'yfc', 'root', '123456')
优点:通过监听mysql的binlog文件,实现增量更新,提升了效率
数据限制:
1、同步mysql数据之前,mysql的每张表应该都有primary key(如果没有主键,同步会报错)
2、MaterializedMySQL是库级别的引擎,同步的时候会以全库中表数据同步
3、mysql数据同步到clickhouse后会发生索引转换:在ClickHouse表中,MySQL的 PRIMARY KEY
和 INDEX
子句被转换为 ORDER BY
元组
4、mysql在转换为clickhouse表的时候,每张表都会新增两个字段:_sign(1:写入、-1:删除),_version
5、在clickhouse中同步新增时,并没有实现物理意义上的删除,只通过_sign标志字段来实现数据过滤
6、在mysql转化clickhouse时,默认使用的ReplacingMergeTree引擎,保证没有重复数据出现
- #1、查询同步的数据库
- show databses;
- use yfc;
- show tables;
- #2、查看建表语句
- show create table sku_info;
- #3、查询同步的数据
- select *,_sign,_version from sku_info;
- #4、新增数据(往mysql的sku_info表中插入数据)
- INSERT INTO `yfc`.`sku_info`(`id`, `sku_code`) VALUES (3, '10003');
-
- #5、修改数据
- UPDATE `yfc`.`ts_store_info` set cust_name = '连调测试' where id= 111;
- #6、删除数据
- DELETE FROM `yfc`.`sku_info` where id= 3;