devbox
酷酷是懒虫
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

从 MySQL 到 ClickHouse 实时数据同步 —— MaterializeMySQL + Materialized View

作者：酷酷是懒虫 | 2024-08-12 07:55:46

赞

踩

materializemysql

目录

一、总体架构

二、安装配置 MySQL

1. 创建 mysql 用户

2. 建立 MySQL 使用的目录

3. 解压安装包

4. 配置环境变量

5. 创建 MySQL 配置文件

6. MySQL 系统初始化

7. 启动 mysql 服务器

8. 创建 dba 用户

三、配置 MySQL 主从复制

四、在 ClickHouse 中创建 MySQL 引擎数据库

五、在 ClickHouse 中创建物化视图

六、物化视图数据刷新

1. 初始数据装载

2. 增量数据刷新

参考：

        本篇演示使用 ClickHouse 的 MaterializeMySQL 数据库引擎和物化视图，实时将 MySQL 库表中的数据同步到 ClickHouse 的库表中。相关软件版本如下：

  • MySQL：8.0.16
  • ClickHouse：24.1.8

        这种方案的好处是操作简单，几乎不需要额外配置即可实现。


一、总体架构

        总体结构如下图所示。

        ClickHouse 是由四个实例构成的两分片、没分片两副本集群，票选和协调器使用 ClickHouse 自带的 keeper 组件。分片、副本、keeper 节点部署如下表所示。

IP

主机名

实例角色

ClickHouse Keeper

172.18.4.126

node1

分片1副本1

*

172.18.4.188

node2

分片1副本2

*

172.18.4.71

node3

分片2副本1

*

172.18.4.86

node4

分片2副本2

        ClickHouse 集群部署过程参见“ClickHouse 集群部署（不需要 Zookeeper）”。另外在 172.18.16.156 上安装 MySQL，并启动两个实例做主从复制，主库实例用3306端口，从库实例用3307端口。

二、安装配置 MySQL

        安装配置 MySQL 一主一从双实例。

1. 创建 mysql 用户

  1. # root 用于执行
  2. useradd mysql
  3. passwd mysql

2. 建立 MySQL 使用的目录

  1. # 创建数据目录，确保数据目录 mysqldata 为空
  2. mkdir -p /data/3306/mysqldata
  3.  
  4. # 创建 binlog 目录
  5. mkdir -p /data/3306/dblog
  6.  
  7. # 创建临时目录
  8. mkdir -p /data/3306/tmp
  9.  
  10. # 修改目录属主为 mysql
  11. chown -R mysql:mysql /data
  12.  
  13. # 使用 mysql 用户执行下面的安装过程
  14. su - mysql

3. 解压安装包

  1. # 进入安装目录
  2. cd ~
  3.  
  4. # 从tar包中把提取文件
  5. tar xvf mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz
  6.  
  7. # 建立软连接
  8. ln -s mysql-8.0.16-linux-glibc2.12-x86_64 mysql-8.0.16

4. 配置环境变量

  1. # 将 MySQL 可执行文件所在目录添加到 $PATH 环境变量中
  2. # 编辑文件
  3. vim ~/.bash_profile
  4.  
  5. # 修改或添加如下两行
  6. PATH=$PATH:$HOME/.local/bin:$HOME/bin:/home/mysql/mysql-8.0.16/bin
  7. export PATH
  8.  
  9. # 使配置生效
  10. source ~/.bash_profile

5. 创建 MySQL 配置文件

  1. # 编辑文件
  2. vim /home/mysql/my_3306.cnf
  3.  
  4. 文件内容如下：
  5.  
  6. [mysqld]
  7. max_allowed_packet=1G
  8. log_timestamps=system
  9. binlog_transaction_dependency_tracking  = WRITESET
  10. transaction_write_set_extraction        = XXHASH64
  11.  
  12. binlog_expire_logs_seconds=259200
  13. lower_case_table_names=1
  14. secure_file_priv=''
  15. log_bin_trust_function_creators=on
  16. character-set-server = utf8mb4
  17. default_authentication_plugin=mysql_native_password
  18. basedir=/home/mysql/mysql-8.0.16-linux-glibc2.12-x86_64
  19. datadir=/data/3306/mysqldata
  20. socket=/data/3306/mysqldata/mysql.sock
  21.  
  22. wait_timeout=30
  23. innodb_buffer_pool_size = 16G
  24. max_connections = 1000
  25.  
  26. default-time-zone = '+8:00'
  27.  
  28. port = 3306
  29. skip-name-resolve 
  30. user=mysql
  31.  
  32. innodb_print_all_deadlocks=1
  33. log_output='table'
  34. slow_query_log = 1
  35. long_query_time = 1
  36.  
  37. tmp_table_size = 32M
  38.  
  39. # 开启 binlog
  40. log-bin=/data/3306/dblog/mysql-bin
  41. log-bin-index = /data/3306/dblog/mysql-bin.index 
  42.  
  43. tmpdir = /data/3306/tmp
  44.  
  45. server-id = 1563306
  46.  
  47. innodb_data_file_path = ibdata1:1G:autoextend
  48. innodb_data_home_dir = /data/3306/mysqldata
  49.  
  50. innodb_log_buffer_size = 16M
  51. innodb_log_file_size = 1G
  52. innodb_log_files_in_group = 3
  53. innodb_log_group_home_dir=/data/3306/dblog
  54. innodb_max_dirty_pages_pct = 90
  55. innodb_lock_wait_timeout = 120
  56.  
  57. gtid-mode = on
  58. enforce_gtid_consistency=true
  59.  
  60. local_infile=0
  61. log_error='/data/3306/mysqldata/master.err'
  62. skip_symbolic_links=yes
  63.  
  64. [mysqldump]
  65. quick
  66. max_allowed_packet = 1G
  67.  
  68. [mysqld_safe]
  69. open-files-limit = 8192

        以下这三个参数必须设置：

  1. default_authentication_plugin=mysql_native_password
  2. gtid-mode = on
  3. enforce_gtid_consistency=true

        如果不设置 default_authentication_plugin，在 ClickHouse 中创建 MySQL 引擎数据库会报以下错误：

  1. Received exception from server (version 24.1.8):
  2. Code: 695. DB::Exception: Received from localhost:9000. DB::Exception: There was an error on [node2:9000]: Code: 695. DB::Exception: Load job 'startup MaterializedMySQL database test_mysql' failed: Code: 537. DB::Exception: Illegal MySQL variables, the MaterializedMySQL engine requires default_authentication_plugin='mysql_native_password'. (ILLEGAL_MYSQL_VARIABLE),. (ASYNC_LOAD_FAILED) (version 24.1.8.22 (official build)). (ASYNC_LOAD_FAILED)

        如果不启用 GTID，在 ClickHouse 中创建 MySQL 引擎数据库会报以下错误：

  1. Received exception from server (version 24.1.8):
  2. Code: 1002. DB::Exception: Received from localhost:9000. DB::Exception: The replication sender thread cannot start in AUTO_POSITION mode: this server has GTID_MODE = OFF instead of ON.. ()

6. MySQL 系统初始化

mysqld --defaults-file=/home/mysql/my_3306.cnf --initialize

7. 启动 mysql 服务器

mysqld_safe --defaults-file=/home/mysql/my_3306.cnf &

8. 创建 dba 用户

  1. # 连接 mysql 服务器
  2. mysql -u root -p -S /data/3306/mysqldata/mysql.sock
  3.  
  4. -- 修改 root 用户密码
  5. alter user user() identified by "123456";
  6.  
  7. -- 创建一个新的 dba 账号
  8. create user 'dba'@'%' identified with mysql_native_password by '123456';
  9. grant all on *.* to 'dba'@'%' with grant option;

        重复执行 2 - 8 步，将 3306 换成 3307，创建从库实例。

三、配置 MySQL 主从复制

        3306 主库实例执行：

  1. -- 查看复制位点
  2. show master status;
  3. -- 创建复制用户并授权
  4. create user 'repl'@'%' identified with mysql_native_password by '123456';
  5. grant replication client,replication slave on *.* to 'repl'@'%';
  6. -- 创建测试库表及数据
  7. create database test;
  8. create table test.t1 (
  9.   id bigint(20) not null auto_increment,
  10.   remark varchar(32) default null comment '备注',
  11.   createtime timestamp not null default current_timestamp comment '创建时间',
  12.   primary key (id));
  13. insert into test.t1 (remark) values ('第一行：row1'),('第二行：row2'),('第三行：row3');

        输出：

  1. mysql> show master status;
  2. +------------------+----------+--------------+------------------+------------------------------------------+
  3. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
  4. +------------------+----------+--------------+------------------+------------------------------------------+
  5. | mysql-bin.000001 |      977 |              |                  | ba615057-e11c-11ee-b80e-246e961c91f8:1-3 |
  6. +------------------+----------+--------------+------------------+------------------------------------------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> create user 'repl'@'%' identified with mysql_native_password by '123456';
  10. Query OK, 0 rows affected (0.01 sec)
  11.  
  12. mysql> grant replication client,replication slave on *.* to 'repl'@'%';
  13. Query OK, 0 rows affected (0.00 sec)
  14.  
  15. mysql> create database test;
  16. Query OK, 1 row affected (0.00 sec)
  17.  
  18. mysql> create table test.t1 (
  19.     ->   id bigint(20) not null auto_increment,
  20.     ->   remark varchar(32) default null comment '备注',
  21.     ->   createtime timestamp not null default current_timestamp comment '创建时间',
  22.     ->   primary key (id));
  23. Query OK, 0 rows affected (0.01 sec)
  24.  
  25. mysql> insert into test.t1 (remark) values ('第一行：row1'),('第二行：row2'),('第三行：row3');
  26. Query OK, 3 rows affected (0.00 sec)
  27. Records: 3  Duplicates: 0  Warnings: 0

        3307 从库实例执行：

  1. change master to
  2. master_host='172.18.16.156',
  3. master_port=3306,
  4. master_user='repl',
  5. master_password='123456',
  6. master_log_file='mysql-bin.000001',
  7. master_log_pos=977;
  8.  
  9. start slave;
  10. show slave status\G
  11. select user,host from mysql.user;
  12. select * from test.t1;

        输出：

  1. mysql> change master to
  2.     -> master_host='172.18.16.156',
  3.     -> master_port=3306,
  4.     -> master_user='repl',
  5.     -> master_password='123456',
  6.     -> master_log_file='mysql-bin.000001',
  7.     -> master_log_pos=977;
  8. Query OK, 0 rows affected, 2 warnings (0.00 sec)
  9.  
  10. mysql> start slave;
  11. Query OK, 0 rows affected (0.01 sec)
  12.  
  13. mysql> show slave status\G
  14. *************************** 1. row ***************************
  15.                Slave_IO_State: Waiting for master to send event
  16.                   Master_Host: 172.18.16.156
  17.                   Master_User: repl
  18.                   Master_Port: 3306
  19.                 Connect_Retry: 60
  20.               Master_Log_File: mysql-bin.000001
  21.           Read_Master_Log_Pos: 2431
  22.                Relay_Log_File: vvgg-z2-music-mysqld-relay-bin.000002
  23.                 Relay_Log_Pos: 1776
  24.         Relay_Master_Log_File: mysql-bin.000001
  25.              Slave_IO_Running: Yes
  26.             Slave_SQL_Running: Yes
  27.               Replicate_Do_DB: 
  28.           Replicate_Ignore_DB: 
  29.            Replicate_Do_Table: 
  30.        Replicate_Ignore_Table: 
  31.       Replicate_Wild_Do_Table: 
  32.   Replicate_Wild_Ignore_Table: 
  33.                    Last_Errno: 0
  34.                    Last_Error: 
  35.                  Skip_Counter: 0
  36.           Exec_Master_Log_Pos: 2431
  37.               Relay_Log_Space: 1999
  38.               Until_Condition: None
  39.                Until_Log_File: 
  40.                 Until_Log_Pos: 0
  41.            Master_SSL_Allowed: No
  42.            Master_SSL_CA_File: 
  43.            Master_SSL_CA_Path: 
  44.               Master_SSL_Cert: 
  45.             Master_SSL_Cipher: 
  46.                Master_SSL_Key: 
  47.         Seconds_Behind_Master: 0
  48. Master_SSL_Verify_Server_Cert: No
  49.                 Last_IO_Errno: 0
  50.                 Last_IO_Error: 
  51.                Last_SQL_Errno: 0
  52.                Last_SQL_Error: 
  53.   Replicate_Ignore_Server_Ids: 
  54.              Master_Server_Id: 1563306
  55.                   Master_UUID: ba615057-e11c-11ee-b80e-246e961c91f8
  56.              Master_Info_File: mysql.slave_master_info
  57.                     SQL_Delay: 0
  58.           SQL_Remaining_Delay: NULL
  59.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  60.            Master_Retry_Count: 86400
  61.                   Master_Bind: 
  62.       Last_IO_Error_Timestamp: 
  63.      Last_SQL_Error_Timestamp: 
  64.                Master_SSL_Crl: 
  65.            Master_SSL_Crlpath: 
  66.            Retrieved_Gtid_Set: ba615057-e11c-11ee-b80e-246e961c91f8:4-8
  67.             Executed_Gtid_Set: ba615057-e11c-11ee-b80e-246e961c91f8:4-8,
  68. c2df1946-e11c-11ee-8026-246e961c91f8:1-3
  69.                 Auto_Position: 0
  70.          Replicate_Rewrite_DB: 
  71.                  Channel_Name: 
  72.            Master_TLS_Version: 
  73.        Master_public_key_path: 
  74.         Get_master_public_key: 0
  75.             Network_Namespace: 
  76. 1 row in set (0.00 sec)
  77.  
  78. mysql> select user,host from mysql.user;
  79. +------------------+-----------+
  80. | user             | host      |
  81. +------------------+-----------+
  82. | dba              | %         |
  83. | repl             | %         |
  84. | mysql.infoschema | localhost |
  85. | mysql.session    | localhost |
  86. | mysql.sys        | localhost |
  87. | root             | localhost |
  88. +------------------+-----------+
  89. 6 rows in set (0.00 sec)
  90.  
  91. mysql> select * from test.t1;
  92. +----+------------------+---------------------+
  93. | id | remark           | createtime          |
  94. +----+------------------+---------------------+
  95. |  1 | 第一行：row1     | 2024-04-19 08:46:25 |
  96. |  2 | 第二行：row2     | 2024-04-19 08:46:25 |
  97. |  3 | 第三行：row3     | 2024-04-19 08:46:25 |
  98. +----+------------------+---------------------+
  99. 3 rows in set (0.00 sec)

        MySQL主从复制相关配置参见“配置异步复制”。

四、在 ClickHouse 中创建 MySQL 引擎数据库

  1. set allow_experimental_database_materialized_mysql=1;
  2. create database test_mysql on cluster cluster_2S_2R
  3. engine = MaterializeMySQL('172.18.16.156:3307', 'test', 'dba', '123456');

        如果不设置 allow_experimental_database_materialized_mysql=1 会报如下错误：

  1. Received exception from server (version 24.1.8):
  2. Code: 336. DB::Exception: Received from localhost:9000. DB::Exception: There was an error on [node3:9000]: Code: 336. DB::Exception: MaterializedMySQL is an experimental database engine. Enable allow_experimental_database_materialized_mysql to use it. (UNKNOWN_DATABASE_ENGINE) (version 24.1.8.22 (official build)). (UNKNOWN_DATABASE_ENGINE)

        输出：

  1. vvml-yz-hbase-test.172.18.4.188 :) set allow_experimental_database_materialized_mysql=1;
  2.  
  3. SET allow_experimental_database_materialized_mysql = 1
  4.  
  5. Query id: 7ce08dff-8d1e-496f-a1af-39c5bec41643
  6.  
  7. Ok.
  8.  
  9. 0 rows in set. Elapsed: 0.001 sec. 
  10.  
  11. vvml-yz-hbase-test.172.18.4.188 :) create database test_mysql on cluster cluster_2S_2R
  12. engine = MaterializeMySQL('172.18.16.156:3307', 'test', 'dba', '123456');
  13.  
  14. CREATE DATABASE test_mysql ON CLUSTER cluster_2S_2R
  15. ENGINE = MaterializeMySQL('172.18.16.156:3307', 'test', 'dba', '123456')
  16.  
  17. Query id: 610e3c86-c5b6-477c-b4a3-33624809d05c
  18.  
  19. ┌─host──┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
  20. │ node4 │ 9000 │      0 │       │                   3 │                0 │
  21. │ node3 │ 9000 │      0 │       │                   2 │                0 │
  22. │ node2 │ 9000 │      0 │       │                   1 │                0 │
  23. │ node1 │ 9000 │      0 │       │                   0 │                0 │
  24. └───────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
  25.  
  26. 4 rows in set. Elapsed: 0.086 sec. 
  27.  
  28. vvml-yz-hbase-test.172.18.4.188 :)

        现在可以查询 MySQL 的库表数据：

  1. vvml-yz-hbase-test.172.18.4.188 :) select * from test_mysql.t1;
  2.  
  3. SELECT *
  4. FROM test_mysql.t1
  5.  
  6. Query id: bae55b87-6e80-4e7f-a2c7-b9312ffef999
  7.  
  8. ┌─id─┬─remark───────┬──────────createtime─┐
  9. │  1 │ 第一行：row1 │ 2024-04-19 08:46:25 │
  10. │  2 │ 第二行：row2 │ 2024-04-19 08:46:25 │
  11. │  3 │ 第三行：row3 │ 2024-04-19 08:46:25 │
  12. └────┴──────────────┴─────────────────────┘
  13.  
  14. 3 rows in set. Elapsed: 0.002 sec. 
  15.  
  16. vvml-yz-hbase-test.172.18.4.188 :) 

五、在 ClickHouse 中创建物化视图

  1. -- 创建数据库
  2. create database db1 on cluster cluster_2S_2R;
  3.  
  4. -- 创建本地表
  5. create table db1.t1 on cluster cluster_2S_2R
  6. ( 
  7.   id Int64,
  8.   remark Nullable(String),
  9.   createtime timestamp,
  10.   primary key id)
  11. engine = ReplicatedMergeTree(
  12.     '/clickhouse/tables/{shard}/t1',
  13.     '{replica}')
  14. order by id;
  15.  
  16. -- 创建分布式表
  17. create table db1.t1_replica_all ON CLUSTER 'cluster_2S_2R'
  18. as db1.t1
  19. engine = Distributed(cluster_2S_2R, db1, t1, rand());
  20.  
  21. -- 创建物化视图
  22. create materialized view db1.t1_mv on cluster cluster_2S_2R
  23. to db1.t1_replica_all
  24. as
  25. select * from test_mysql.t1;

        注意创建本地表时的数据类型及其是否允许为空的属性，都要与 MySQL 表的数据类型匹配，否则会报类似下面的错误：

  1. Received exception from server (version 24.1.8):
  2. Code: 53. DB::Exception: Received from localhost:9000. DB::Exception: Type mismatch for column id. Column has type Int64, got type UInt64. (TYPE_MISMATCH)
  3.  
  4. Received exception from server (version 24.1.8):
  5. Code: 53. DB::Exception: Received from localhost:9000. DB::Exception: Type mismatch for column remark. Column has type Nullable(String), got type String. (TYPE_MISMATCH)

        数据类型的对应如下图所示：

六、物化视图数据刷新

1. 初始数据装载

        ClickHouse 物化视图创建时缺省不会进行初始数据装载。初始装载的方法有两个，一是在创建物化视图时使用 POPULATE。POPULATE 关键字决定了物化视图的更新策略：

  • 若有 POPULATE 则在创建视图的过程会将源表已经存在的数据一并导入，类似于create table ... as
  • 若无 POPULATE 则物化视图在创建之后没有数据，只会同步物化视图创建之后写入源表的数据

        ClickHouse 官方并不推荐使用 POPULATE，因为在创建物化视图的过程中同时写入的数据不能被插入物化视图。本例使用 TO [db].[table] 语法，物化视图创建后手工执行初始数据装载。

  1. -- MySQL 从库停止复制
  2. stop slave;
  3.  
  4. -- ClickHouse 初始装载物化视图
  5. insert into db1.t1_mv(id,remark,createtime) select * from test_mysql.t1;
  6.  
  7. -- MySQL 从库开启复制
  8. start slave;

        这么简单的一句却是实现初始数据装载的关键所在。从库停止复制，不影响主库的正常使用，也就不会影响业务。此时从库的数据处于静止状态，不会产生变化，这使得获取存量数据变得轻而易举。然后执行普通的 insert ... select 语句向物化视图插入数据，数据实际是被写入 db1.t1_replica_all 表。之后在 ClickHouse 集群中的任一实例上，都能从物化视图中查询到一致的 MySQL 存量数据。

  1. vvml-yz-hbase-test.172.18.4.86 :) select * from db1.t1_replica_all;
  2.  
  3. SELECT *
  4. FROM db1.t1_replica_all
  5.  
  6. Query id: 5063cd12-6eba-4a81-9c75-d892cb152b17
  7.  
  8. ┌─id─┬─remark───────┬──────────createtime─┐
  9. │  1 │ 第一行：row1 │ 2024-04-19 08:46:25 │
  10. │  2 │ 第二行：row2 │ 2024-04-19 08:46:25 │
  11. │  3 │ 第三行：row3 │ 2024-04-19 08:46:25 │
  12. └────┴──────────────┴─────────────────────┘
  13.  
  14. 3 rows in set. Elapsed: 0.004 sec. 
  15.  
  16. vvml-yz-hbase-test.172.18.4.86 :) select * from db1.t1_mv;
  17.  
  18. SELECT *
  19. FROM db1.t1_mv
  20.  
  21. Query id: e319f96b-f299-41a1-b730-737a42ceedb0
  22.  
  23. ┌─id─┬─remark───────┬──────────createtime─┐
  24. │  1 │ 第一行：row1 │ 2024-04-19 08:46:25 │
  25. │  2 │ 第二行：row2 │ 2024-04-19 08:46:25 │
  26. │  3 │ 第三行：row3 │ 2024-04-19 08:46:25 │
  27. └────┴──────────────┴─────────────────────┘
  28.  
  29. 3 rows in set. Elapsed: 0.006 sec. 
  30.  
  31. vvml-yz-hbase-test.172.18.4.86 :) 

2. 增量数据刷新

        ClickHouse 的物化视图能够在底层数据更新后，自动更新自己的数据。数据更新包括两个方面的变化：基础表的数据修改和基础表的数据新增。

  • 基础表的数据修改

        如果基础表的数据修改，物化视图会自动更新。这是通过 ClickHouse 的引擎和存储方式来实现的。当基础表的一行记录被修改，ClickHouse 会将这个修改转化为一个新的 INSERT 语句，并且将其发送到物化视图中。这样，物化视图就能够自动更新自己的数据。

  • 基础表的数据新增

        如果基础表的数据新增，物化视图同样会自动更新。这是通过设置物化视图的刷新机制来实现的。刷新机制有两种类型：定时刷新和自动刷新。

        需要注意的是，ClickHouse 的物化视图虽然能够自动更新数据，但是会带来一些性能上的损失，尤其是在基础表数据量较大的情况下。因此，在设计物化视图时，需要考虑这个因素，同时选择合适的刷新机制来平衡性能和数据实时性的需求。

        当然既然选择了 ClickHouse，使用场景就应该是数据新增比较多，而极少去修改或删除。对于一般对实时要求不高的业务场景，定时刷新完全够用了。

  1. -- MySQL 主库修改数据
  2. insert into test.t1 (remark) values ('第四行：row4');
  3. update test.t1 set remark = '第五行：row5' where id = 4;
  4. delete from test.t1 where id =1;
  5. insert into test.t1 (remark) values ('第六行：row6');
  6.  
  7. 此时 MySQL 的数据如下：
  8. mysql> select * from test.t1;
  9. +----+------------------+---------------------+
  10. | id | remark           | createtime          |
  11. +----+------------------+---------------------+
  12. |  2 | 第二行：row2     | 2024-04-19 08:46:25 |
  13. |  3 | 第三行：row3     | 2024-04-19 08:46:25 |
  14. |  4 | 第五行：row5     | 2024-04-19 11:24:33 |
  15. |  5 | 第六行：row6     | 2024-04-19 11:56:20 |
  16. +----+------------------+---------------------+
  17. 4 rows in set (0.00 sec)

        ClickHouse 查询数据，所有实例上查询物化视图返回相同的数据：

  1. vvml-yz-hbase-test.172.18.4.86 :) select * from db1.t1_mv order by id;
  2.  
  3. SELECT *
  4. FROM db1.t1_mv
  5. ORDER BY id ASC
  6.  
  7. Query id: 1e3f8bb3-6fdd-4f3e-a494-af8c72e3dab2
  8.  
  9. ┌─id─┬─remark───────┬──────────createtime─┐
  10. │  1 │ 第一行：row1 │ 2024-04-19 08:46:25 │
  11. └────┴──────────────┴─────────────────────┘
  12. ┌─id─┬─remark───────┬──────────createtime─┐
  13. │  1 │ 第一行：row1 │ 2024-04-19 08:46:25 │
  14. │  1 │ 第一行：row1 │ 2024-04-19 08:46:25 │
  15. │  1 │ 第一行：row1 │ 2024-04-19 08:46:25 │
  16. │  1 │ 第一行：row1 │ 2024-04-19 08:46:25 │
  17. │  2 │ 第二行：row2 │ 2024-04-19 08:46:25 │
  18. │  3 │ 第三行：row3 │ 2024-04-19 08:46:25 │
  19. └────┴──────────────┴─────────────────────┘
  20. ┌─id─┬─remark───────┬──────────createtime─┐
  21. │  4 │ 第四行：row4 │ 2024-04-19 11:24:33 │
  22. └────┴──────────────┴─────────────────────┘
  23. ┌─id─┬─remark───────┬──────────createtime─┐
  24. │  4 │ 第五行：row5 │ 2024-04-19 11:24:33 │
  25. └────┴──────────────┴─────────────────────┘
  26. ┌─id─┬─remark───────┬──────────createtime─┐
  27. │  4 │ 第四行：row4 │ 2024-04-19 11:24:33 │
  28. │  4 │ 第五行：row5 │ 2024-04-19 11:24:33 │
  29. │  4 │ 第四行：row4 │ 2024-04-19 11:24:33 │
  30. │  4 │ 第四行：row4 │ 2024-04-19 11:24:33 │
  31. │  4 │ 第五行：row5 │ 2024-04-19 11:24:33 │
  32. │  4 │ 第五行：row5 │ 2024-04-19 11:24:33 │
  33. └────┴──────────────┴─────────────────────┘
  34. ┌─id─┬─remark───────┬──────────createtime─┐
  35. │  5 │ 第六行：row6 │ 2024-04-19 11:56:20 │
  36. └────┴──────────────┴─────────────────────┘
  37. ┌─id─┬─remark───────┬──────────createtime─┐
  38. │  5 │ 第六行：row6 │ 2024-04-19 11:56:20 │
  39. └────┴──────────────┴─────────────────────┘
  40.  
  41. 17 rows in set. Elapsed: 0.005 sec. 

        查询本地表，同一分片的副本返回相同的结果，不同分片的数据不同：

  1. -- node1
  2. vvml-yz-hbase-test.172.18.4.126 :) select * from db1.t1 order by id;
  3.  
  4. SELECT *
  5. FROM db1.t1
  6. ORDER BY id ASC
  7.  
  8. Query id: c4d50038-f73f-4989-948e-031d6ff5d5ee
  9.  
  10. ┌─id─┬─remark───────┬──────────createtime─┐
  11. │  1 │ 第一行：row1 │ 2024-04-19 08:46:25 │
  12. │  1 │ 第一行：row1 │ 2024-04-19 08:46:25 │
  13. │  1 │ 第一行：row1 │ 2024-04-19 08:46:25 │
  14. │  1 │ 第一行：row1 │ 2024-04-19 08:46:25 │
  15. │  2 │ 第二行：row2 │ 2024-04-19 08:46:25 │
  16. │  3 │ 第三行：row3 │ 2024-04-19 08:46:25 │
  17. │  4 │ 第四行：row4 │ 2024-04-19 11:24:33 │
  18. │  4 │ 第五行：row5 │ 2024-04-19 11:24:33 │
  19. │  4 │ 第四行：row4 │ 2024-04-19 11:24:33 │
  20. │  4 │ 第四行：row4 │ 2024-04-19 11:24:33 │
  21. │  4 │ 第五行：row5 │ 2024-04-19 11:24:33 │
  22. │  4 │ 第五行：row5 │ 2024-04-19 11:24:33 │
  23. └────┴──────────────┴─────────────────────┘
  24. ┌─id─┬─remark───────┬──────────createtime─┐
  25. │  5 │ 第六行：row6 │ 2024-04-19 11:56:20 │
  26. └────┴──────────────┴─────────────────────┘
  27.  
  28. 13 rows in set. Elapsed: 0.002 sec. 
  29.  
  30. vvml-yz-hbase-test.172.18.4.126 :) 
  31.  
  32. -- node3
  33. vvml-yz-hbase-test.172.18.4.71 :) select * from db1.t1 order by id;
  34.  
  35. SELECT *
  36. FROM db1.t1
  37. ORDER BY id ASC
  38.  
  39. Query id: e91e72b7-508a-48a2-b08f-779e49b7cd01
  40.  
  41. ┌─id─┬─remark───────┬──────────createtime─┐
  42. │  1 │ 第一行：row1 │ 2024-04-19 08:46:25 │
  43. └────┴──────────────┴─────────────────────┘
  44. ┌─id─┬─remark───────┬──────────createtime─┐
  45. │  4 │ 第四行：row4 │ 2024-04-19 11:24:33 │
  46. └────┴──────────────┴─────────────────────┘
  47. ┌─id─┬─remark───────┬──────────createtime─┐
  48. │  4 │ 第五行：row5 │ 2024-04-19 11:24:33 │
  49. └────┴──────────────┴─────────────────────┘
  50. ┌─id─┬─remark───────┬──────────createtime─┐
  51. │  5 │ 第六行：row6 │ 2024-04-19 11:56:20 │
  52. └────┴──────────────┴─────────────────────┘
  53.  
  54. 4 rows in set. Elapsed: 0.002 sec. 
  55.  
  56. vvml-yz-hbase-test.172.18.4.71 :) 

        MySQL 中只有三行数据，ClickHouse 却有17行。ID=1 的行在 MySQL 中被删除，而在 ClickHouse 中并没有删除，而是变为了5行。虽然定义本地表时指定了 id 字段为主键，但 ClickHouse 中自定义的主键并不保证唯一性，即便本地表也是如此。ID=2 和 ID=3 的行在 MySQL 中没有变化，在 ClickHouse 中也分别是唯一的一行。ID=4 的行在 MySQL 中先新增后修改，在 ClickHouse 中都是新增数据。ID=6 的行在 MySQL 中新增一行，在 ClickHouse 中却增加了两行。分布式表的分片规则用的是随机，为什么 MySQL 端新增一条数据，到 ClickHouse 中两个分片都写了呢？

        实验到此实现了数据实时同步，但 ClickHouse 中的数据明显多了很多行，这与选择的表引擎、使用的分片规则都有关系，比较复杂，对数据的解释也变得很重要。所以这里得出的结论是，要用 ClickHouse，最好还是定期从源端导入数据比较靠谱，而且源端最好是只新增数据。

参考：

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/酷酷是懒虫/article/detail/968464
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号