此处以mergetree引擎举例，如果是用于高可用开发，还是需要换成replicatemergetree系列引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- tablename和clustername不用提前建立好,会自动建立

-- 分布式表和本地表都需要删除
drop table if exists <database>.<tablename_local> on cluster <clustername>;
drop table if exists <database>.<tablename_local> on cluster <clustername>;

-- 先创建本地表(因为同步远程表没办法以集群模式同步,所以可以先同步表结构,不插入数据,然后再使用insert同步数据到各个节点)
CREATE TABLE <database>.<tablename_local>
    ENGINE = MergeTree order by cm_id AS
SELECT *
FROM
    mysql(
            '远程mysql_ip:远程mysql_port',
            '远程mysql_database',
            '远程mysql_tablename',
            '远程mysql_user',
            '远程mysql_password'
        ) limit 0;

-- 然后将local表删掉,建立集群下的本地表
show create table <database>.<tablename_local>;
CREATE TABLE default.t_customer on cluster <clustername>
(
xxx String,
xxx UInt64
...
)
ENGINE = MergeTree
ORDER BY <id>
SETTINGS index_granularity = 8192;

-- 再创建一个分布式表(插入数据方式为随机node节点)
create table <database>.<tablename> on cluster <clustername>
AS <database>.<tablename>
engine = Distributed('<clustername>', '<database>', '<tablename_local>', rand());

-- 同步数据(这里同步插入的是分布式表)
insert into <database>.<tablename>
SELECT *
FROM
    mysql(
            '远程mysql_ip:远程mysql_port',
            '远程mysql_database',
            '远程mysql_tablename',
            '远程mysql_user',
            '远程mysql_password'
        );