从MySQL导入和同步
更新时间：2025-01-20
本页介绍了MySQL与ClickHouse集成的两个方面：
- 使用MySQL表引擎，从MySQL表中读取
- 使用MaterializedMySQL数据库引擎，将MySQL中的数据库与ClickHouse中的数据库同步
使用MySQL表引擎将ClickHouse连接到MySQL
MySQL表引擎允许您将ClickHouse连接到MySQL。SELECT和INSERT语句可以在ClickHouse或MySQL表中执行。本文说明了如何使用MySQL表引擎的基本方法。
配置MySQL
- 在MySQL中创建一个数据库：
Plain Text
1CREATE DATABASE db1;- 创建表：
Plain Text
1CREATE TABLE db1.table1 (
2 id INT,
3 column1 VARCHAR(255)
4);- 插入示例行：
Plain Text
1INSERT INTO db1.table1
2 (id, column1)
3VALUES
4 (1, 'abc'),
5 (2, 'def'),
6 (3, 'ghi');4.创建一个用户以从ClickHouse连接：
Plain Text
1CREATE USER 'mysql_clickhouse'@'%' IDENTIFIED BY 'Password123!';5.根据需要授予特权。（出于演示目的，mysql_clickhouse用户被授予管理员权限。）
Plain Text
1GRANT ALL PRIVILEGES ON *.* TO 'mysql_clickhouse'@'%';在ClickHouse中定义表
- 创建一个使用MySQL表引擎的ClickHouse表：
Plain Text
1CREATE TABLE mysql_table1 (
2 id UInt64,
3 column1 String
4)
5ENGINE = 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中，插入一个示例行：
Plain Text
1INSERT INTO db1.table1
2 (id, column1)
3VALUES
4 (4, 'jkl');- 请注意，MySQL表中的现有行以及您刚才添加的新行都在ClickHouse表中：
Plain Text
1SELECT
2 id,
3 column1
4FROM mysql_table1您可以看有四行：
Plain Text
1Query id: 6d590083-841e-4e95-8715-ef37d3e95197
2
3┌─id─┬─column1─┐
4│ 1 │ abc │
5│ 2 │ def │
6│ 3 │ ghi │
7│ 4 │ jkl │
8└────┴─────────┘
9
104 rows in set. Elapsed: 0.044 sec.- 在ClickHouse表中添加一行：
Plain Text
1INSERT INTO mysql_table1
2 (id, column1)
3VALUES
4 (5,'mno')- 请注意，MySQL中出现了新行：
Plain Text
1mysql> select id,column1 from db1.table1;可以看到新的一行：
Plain Text
1+------+---------+
2| id | column1 |
3+------+---------+
4| 1 | abc |
5| 2 | def |
6| 3 | ghi |
7| 4 | jkl |
8| 5 | mno |
9+------+---------+
105 rows in set (0.01 sec)在ClickHouse中复制MySQL数据库
MaterializedMySQL数据库引擎允许您在ClickHouse中定义一个数据库，该数据库包含MySQL数据库中的所有现有表以及这些表中的所有数据。在MySQL方面，DDL和DML操作可以继续进行，ClickHouse检测到这些更改并充当MySQL数据库的副本。
本文演示了如何配置MySQL和ClickHouse来实现。
配置MySQL
- 配置MySQL数据库以允许复制和本机身份验证。ClickHouse仅适用于本机密码身份验证。将以下条目添加到/etc/my.cnf：
Plain Text
1default_authentication_plugin = mysql_native_password
2gtid_mode = ON
3enforce_gtid_consistency = ON- 创建一个用户从ClickHouse连接：
Plain Text
1CREATE USER clickhouse_user IDENTIFIED BY 'ClickHouse_123';- 向新用户授予所需的权限。出于演示目的，此处授予了完全管理员权限：
Plain Text
1GRANT ALL PRIVILEGES ON *.* TO 'clickhouse_user'@'%';- 在MySQL中创建一个数据库：
Plain Text
1CREATE DATABASE db1;- 创建一个表：
Plain Text
1CREATE TABLE db1.table_1 (
2 id INT,
3 column1 VARCHAR(10),
4 PRIMARY KEY (`id`)
5) ENGINE = InnoDB;- 插入一些示例行：
Plain Text
1INSERT INTO db1.table_1
2 (id, column1)
3VALUES
4 (1, 'abc'),
5 (2, 'def'),
6 (3, 'ghi');配置ClickHouse
- 设置参数：
Plain Text
1set allow_experimental_database_materialized_mysql = 1;- 创建一个使用MaterializedMySQL数据库引擎的数据库：
Plain Text
1CREATE DATABASE db1_mysql
2ENGINE = MaterializedMySQL(
3 'mysql-host.domain.com:3306',
4 'db1',
5 'clickhouse_user',
6 'ClickHouse_123'
7);表二 参数说明
| 参数 | 描述 | 举例 |
|---|---|---|
| host | 主机名或 IP | mysql-host.domain.com |
| database | mysql 数据库名称 | db1 |
| table | mysql 表名 | table1 |
| user | 连接 mysql 的用户名 | mysql_clickhouse |
| password | 连接mysql的密码 | 密码123！ |
测试集成
- 在MySQL中，插入一个示例行：
Plain Text
1INSERT INTO db1.table_1
2 (id, column1)
3VALUES
4 (4, 'jkl');- 请注意，ClickHouse表中出现了新行：
Plain Text
1SELECT
2 id,
3 column1
4FROM db1_mysql.table_1响应如下：
Plain Text
1Query id: d61a5840-63ca-4a3d-8fac-c93235985654
2
3┌─id─┬─column1─┐
4│ 1 │ abc │
5└────┴─────────┘
6┌─id─┬─column1─┐
7│ 4 │ jkl │
8└────┴─────────┘
9┌─id─┬─column1─┐
10│ 2 │ def │
11└────┴─────────┘
12┌─id─┬─column1─┐
13│ 3 │ ghi │
14└────┴─────────┘
15
164 rows in set. Elapsed: 0.030 sec.3.假设MySQL中的表被修改了。在MySQL中为db1.table_1添加一列：
Plain Text
1alter table db1.table_1 add column column2 varchar(10) after column1;- 在修改后的表中插入一行：
Plain Text
1INSERT INTO db1.table_1
2 (id, column1, column2)
3VALUES
4 (5, 'mno', 'pqr');5.ClickHouse中的表格现在有了新列和新行：
Plain Text
1SELECT
2 id,
3 column1,
4 column2
5FROM db1_mysql.table_1前几行第2列将为NULL：
Plain Text
1Query id: 2c32fd15-3c83-480b-9bfc-cba5d932d674
2
3Connecting to localhost:9000 as user default.
4Connected to ClickHouse server version 22.2.2 revision 54455.
5
6┌─id─┬─column1─┬─column2─┐
7│ 3 │ ghi │ ᴺᵁᴸᴸ │
8└────┴─────────┴─────────┘
9┌─id─┬─column1─┬─column2─┐
10│ 2 │ def │ ᴺᵁᴸᴸ │
11└────┴─────────┴─────────┘
12┌─id─┬─column1─┬─column2─┐
13│ 1 │ abc │ ᴺᵁᴸᴸ │
14│ 5 │ mno │ pqr │
15└────┴─────────┴─────────┘
16┌─id─┬─column1─┬─column2─┐
17│ 4 │ jkl │ ᴺᵁᴸᴸ │
18└────┴─────────┴─────────┘
19
205 rows in set. Elapsed: 0.017 sec.