本页涵盖了集成MySQL与ClickHouse的两个选项:
使用MySQL表引擎，用于读取MySQL表
使用MaterializedMySQL数据库引擎，用于同步MySQL中的数据库和ClickHouse中的数据库
使用MySQL表引擎连接ClickHouse到MySQL
MySQL表引擎允许你将ClickHouse连接到MySQL。SELECT和INSERT语句可以在ClickHouse或MySQL表中执行。本文说明了如何使用MySQL表引擎的基本方法。
1. 配置MySQL
在MySQL中创建数据库:
CREATE DATABASE db1;创建一个表:
CREATE TABLE db1.table1 (
id INT,
column1 VARCHAR(255)
);
插入示例行:
INSERT INTO db1.table1
(id, column1)
VALUES
(1, 'abc'),
(2, 'def'),
(3, 'ghi');
创建一个用户从ClickHouse连接:
CREATE USER 'mysql_clickhouse'@'%' IDENTIFIED BY 'Password123!';根据需要授予特权。(为了便于演示，mysql_clickhouse用户被授予了管理权限。)
GRANT ALL PRIVILEGES ON *.* TO 'mysql_clickhouse'@'%';2. 在ClickHouse中定义一个表
现在让我们创建一个使用MySQL表引擎的ClickHouse表:
CREATE TABLE mysql_table1 (
id UInt64,
column1 String
)
ENGINE = MySQL('mysql-host.domain.com','db1','table1','mysql_clickhouse','Password123!')
参数至少包含：
字段 描述 举例
host hostname or IP mysql-host.domain.com
database mysql database name db1
table mysql table name table1
user username to connect to mysql mysql_clickhouse
password password to connect to mysql Password123!
3.测试集成
在MySQL中，插入一个示例行:
INSERT INTO db1.table1
(id, column1)
VALUES
(4, 'jkl');
请注意，MySQL表中的现有行都在ClickHouse表中，以及您刚刚添加的新行:
SELECT
id,
column1
FROM mysql_table1
您应该看到4行:
Query id: 6d590083-841e-4e95-8715-ef37d3e95197
┌─id─┬─column1─┐
│ 1 │ abc │
│ 2 │ def │
│ 3 │ ghi │
│ 4 │ jkl │
└────┴─────────┘
4 rows in set. Elapsed: 0.044 sec.
让我们添加一行到ClickHouse表:
INSERT INTO mysql_table1
(id, column1)
VALUES
(5,'mno')
注意MySQL中出现了新行:
mysql> select id,column1 from db1.table1;在ClickHouse中复制MySQL数据库
MaterializedMySQL数据库引擎允许您在ClickHouse中定义一个数据库，该数据库包含MySQL数据库中的所有现有表，以及这些表中的所有数据。在MySQL方面，DDL和DML操作可以继续进行，ClickHouse检测这些更改并充当MySQL数据库的副本。
本文演示如何配置MySQL和ClickHouse来实现这种复制。
1. 配置MySQL
配置MySQL数据库以允许复制和本地身份验证。ClickHouse只适用于本地密码验证。在/etc/my.cnf中添加以下条目:
default-authentication-plugin = mysql_native_password
gtid-mode = ON
enforce-gtid-consistency = ON
创建一个用户从ClickHouse连接:
CREATE USER 'mysql_clickhouse'@'%' IDENTIFIED BY 'Password123!';根据需要授予特权。(为了便于演示，mysql_clickhouse用户被授予了管理权限。)
GRANT ALL PRIVILEGES ON *.* TO 'mysql_clickhouse'@'%';在MySQL中创建数据库:
CREATE DATABASE db1;创建一个表:
CREATE TABLE db1.table1 (
id INT,
column1 VARCHAR(255)
);
插入示例行:
INSERT INTO db1.table1
(id, column1)
VALUES
(1, 'abc'),
(2, 'def'),
(3, 'ghi');
2. 配置ClickHouse
设置参数允许使用实验功能:
set allow_experimental_database_materialized_mysql = 1;创建一个使用MaterializedMySQL数据库引擎的数据库:
CREATE DATABASE db1_mysql
ENGINE = MaterializedMySQL(
'mysql-host.domain.com:3306',
'db1',
'clickhouse_user',
'ClickHouse_123'
);
参数至少包含：
参数 描述 举例
host:port hostname or IP and port mysql-host.domain.com
database mysql database name db1
user username to connect to mysql clickhouse_user
password password to connect to mysql ClickHouse_123
3.测试集成
在MySQL中，插入一个示例行:
INSERT INTO db1.table_1
(id, column1)
VALUES
(4, 'jkl');
注意新行出现在ClickHouse表中:
SELECT
id,
column1
FROM db1_mysql.table_1
回复如下:
Query id: d61a5840-63ca-4a3d-8fac-c93235985654
┌─id─┬─column1─┐
│ 1 │ abc │
└────┴─────────┘
┌─id─┬─column1─┐
│ 4 │ jkl │
└────┴─────────┘
┌─id─┬─column1─┐
│ 2 │ def │
└────┴─────────┘
┌─id─┬─column1─┐
│ 3 │ ghi │
└────┴─────────┘
4 rows in set. Elapsed: 0.030 sec.
假设MySQL中的表被修改了。我们把一列指向db1。MySQL中的table_1:
alter table db1.table_1 add column column2 varchar(10) after column1;现在让我们向修改后的表中插入一行:
INSERT INTO db1.table_1
(id, column1, column2)
VALUES
(5, 'mno', 'pqr');
注意，ClickHouse中的表格现在有了新列和新行:
SELECT
id,
column1,
column2
FROM db1_mysql.table_1
前面的行column2为NULL:
Query id: 2c32fd15-3c83-480b-9bfc-cba5d932d674
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.2.2 revision 54455.
┌─id─┬─column1─┬─column2─┐
│ 3 │ ghi │ ᴺᵁᴸᴸ │
└────┴─────────┴─────────┘
┌─id─┬─column1─┬─column2─┐
│ 2 │ def │ ᴺᵁᴸᴸ │
└────┴─────────┴─────────┘
┌─id─┬─column1─┬─column2─┐
│ 1 │ abc │ ᴺᵁᴸᴸ │
│ 5 │ mno │ pqr │
└────┴─────────┴─────────┘
┌─id─┬─column1─┬─column2─┐
│ 4 │ jkl │ ᴺᵁᴸᴸ │
└────┴─────────┴─────────┘
5 rows in set. Elapsed: 0.017 sec.
