从MySQL导入和同步
更新时间：2024-11-25
本页介绍了MySQL与ClickHouse集成的两个方面：
- 使用MySQL表引擎，从MySQL表中读取
- 使用MaterializedMySQL数据库引擎，将MySQL中的数据库与ClickHouse中的数据库同步
使用MySQL表引擎将ClickHouse连接到MySQL
MySQL表引擎允许您将ClickHouse连接到MySQL。SELECT和INSERT语句可以在ClickHouse或MySQL表中执行。本文说明了如何使用MySQL表引擎的基本方法。
配置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');4.创建一个用户以从ClickHouse连接：
CREATE USER 'mysql_clickhouse'@'%' IDENTIFIED BY 'Password123!';5.根据需要授予特权。（出于演示目的，mysql_clickhouse用户被授予管理员权限。）
GRANT ALL PRIVILEGES ON *.* TO 'mysql_clickhouse'@'%';在ClickHouse中定义表
- 创建一个使用MySQL表引擎的ClickHouse表：
CREATE TABLE mysql_table1 (
id UInt64,
column1 String
)
ENGINE = MySQL('mysql-host.domain.com','db1','table1','mysql_clickhouse','Password123!')表一 最小参数
| 参数 | 描述 | 举例 |
|---|---|---|
| host | 主机名或 IP | mysql-host.domain.com |
| database | mysql 数据库名称 | db1 |
| table | mysql 表名 | table1 |
| user | 连接 mysql 的用户名 | mysql_clickhouse |
| password | 连接mysql的密码 | 密码123！ |
测试集成
- 在MySQL中，插入一个示例行：
INSERT INTO db1.table1
(id, column1)
VALUES
(4, 'jkl');- 请注意，MySQL表中的现有行以及您刚才添加的新行都在ClickHouse表中：
SELECT
id,
column1
FROM mysql_table1您可以看有四行：
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;可以看到新的一行：
+------+---------+
| id | column1 |
+------+---------+
| 1 | abc |
| 2 | def |
| 3 | ghi |
| 4 | jkl |
| 5 | mno |
+------+---------+
5 rows in set (0.01 sec)在ClickHouse中复制MySQL数据库
MaterializedMySQL数据库引擎允许您在ClickHouse中定义一个数据库，该数据库包含MySQL数据库中的所有现有表以及这些表中的所有数据。在MySQL方面，DDL和DML操作可以继续进行，ClickHouse检测到这些更改并充当MySQL数据库的副本。
本文演示了如何配置MySQL和ClickHouse来实现。
配置MySQL
- 配置MySQL数据库以允许复制和本机身份验证。ClickHouse仅适用于本机密码身份验证。将以下条目添加到/etc/my.cnf：
default_authentication_plugin = mysql_native_password
gtid_mode = ON
enforce_gtid_consistency = ON- 创建一个用户从ClickHouse连接：
CREATE USER clickhouse_user IDENTIFIED BY 'ClickHouse_123';- 向新用户授予所需的权限。出于演示目的，此处授予了完全管理员权限：
GRANT ALL PRIVILEGES ON *.* TO 'clickhouse_user'@'%';- 在MySQL中创建一个数据库：
CREATE DATABASE db1;- 创建一个表：
CREATE TABLE db1.table_1 (
id INT,
column1 VARCHAR(10),
PRIMARY KEY (`id`)
) ENGINE = InnoDB;- 插入一些示例行：
INSERT INTO db1.table_1
(id, column1)
VALUES
(1, 'abc'),
(2, 'def'),
(3, 'ghi');配置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 | 主机名或 IP | mysql-host.domain.com |
| database | mysql 数据库名称 | db1 |
| table | mysql 表名 | table1 |
| user | 连接 mysql 的用户名 | mysql_clickhouse |
| password | 连接mysql的密码 | 密码123！ |
测试集成
- 在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.3.假设MySQL中的表被修改了。在MySQL中为db1.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');5.ClickHouse中的表格现在有了新列和新行：
SELECT
id,
column1,
column2
FROM db1_mysql.table_1前几行第2列将为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.