【clickhouse】ClickHouse与MySQL之间实时同步数据（MySQL引擎），将MySQL数据实时同步到clickhouse

07-06 1372阅读

参考1:MySQL(通过该配置实现了实时同步)

参考2:experimental MaterializedMySQL

参考3:[experimental] MaterializedMySQL(包含设置 allow_experimental_database_materialized_mysql)

MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换

MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，因此您可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。

一、在MySQL数据库中创建一个测试数据库

执行如下语句进行创建，数据库名：test、数据表名：mysql_table

mysql> USE test;
Database changed
mysql> CREATE TABLE `mysql_table` (
    ->   `int_id` INT NOT NULL AUTO_INCREMENT,
    ->   `float` FLOAT NOT NULL,
    ->   PRIMARY KEY (`int_id`));
Query OK, 0 rows affected (0,09 sec)
mysql> insert into mysql_table (`int_id`, `float`) VALUES (1,2);
Query OK, 1 row affected (0,00 sec)
mysql> select * from mysql_table;
+------+-----+
| int_id | value |
+------+-----+
|      1 |     2 |
+------+-----+
1 row in set (0,00 sec)

二、在clickhouse数据库中创建MySQL引擎

语法

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

创建过程

说明：由于刚开始使用的是MaterializeMySQL方式，所以在示例代码中能看到MaterializeMySQL，直接替换为MySQL即可

1、执行如下语句

CREATE DATABASE mysqllzh ENGINE = MaterializeMySQL('192.168.0.176:3306', 'integration_shandong', 'root', '123456')
     SETTINGS
        allows_query_when_mysql_lost=true,
        max_wait_time_when_mysql_unavailable=10000;

2、报错如下

报错信息：MaterializedMySQL is an experimental database engine. Enable allow_experimental_database_materialized_mysql to use it.

详细信息如下：

[root@172 clickhouse-server]# clickhouse-client
ClickHouse client version 23.11.1.2711 (official build).
Connecting to localhost:9000 as user default.
Password for user (default): 
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 23.11.1.
Warnings:
 * Linux is not using a fast clock source. Performance can be degraded. Check /sys/devices/system/clocksource/clocksource0/current_clocksource
 * Linux transparent hugepages are set to "always". Check /sys/kernel/mm/transparent_hugepage/enabled
 * Maximum number of threads is lower than 30000. There could be problems with handling a lot of simultaneous queries.
172.20.219.19 :) CREATE DATABASE mysqllzh ENGINE = MaterializeMySQL('192.168.0.176:3306', 'integration_shandong', 'root', '123456')
     SETTINGS
        allows_query_when_mysql_lost=true,
        max_wait_time_when_mysql_unavailable=10000;
CREATE DATABASE mysqllzh
ENGINE = MaterializeMySQL('192.168.0.176:3306', 'integration_shandong', 'root', '123456')
SETTINGS allows_query_when_mysql_lost = 1, max_wait_time_when_mysql_unavailable = 10000
Query id: f7b41d0d-e9b6-4b4f-a2be-86dae43a1e7c
Elapsed: 0.001 sec. 
Received exception from server (version 23.11.1):
Code: 336. DB::Exception: Received from localhost:9000. DB::Exception: MaterializedMySQL is an experimental database engine. Enable allow_experimental_database_materialized_mysql to use it. (UNKNOWN_DATABASE_ENGINE)

3、解决方案

执行如下命令

SET allow_experimental_database_materialized_mysql=1

4、再次执行命令，报错如下

ConnectionFailed: Host '192.168.0.197' is not allowed to connect to this MySQL server ((nullptr):0),. (ASYNC_LOAD_FAILED)

详细信息如下：

172.20.219.19 :) 
CREATE DATABASE mysqllzh ENGINE = MaterializeMySQL('192.168.0.196:3306', 'integration_shandong', 'root', '123456')
     SETTINGS
        allows_query_when_mysql_lost=true,
        max_wait_time_when_mysql_unavailable=10000;
CREATE DATABASE mysqllzh
ENGINE = MaterializeMySQL('192.168.0.196:3306', 'integration_shandong', 'root', '123456')
SETTINGS allows_query_when_mysql_lost = 1, max_wait_time_when_mysql_unavailable = 10000
Query id: 4713dd46-d3c1-44b1-9aa5-9c96e7b33f31
Elapsed: 4.588 sec. 
Received exception from server (version 23.11.1):
Code: 695. DB::Exception: Received from localhost:9000. DB::Exception: Load job 'startup MaterializedMySQL database mysqllzh' failed: Poco::Exception. Code: 1000, e.code() = 1130, mysqlxx::ConnectionFailed: Host '192.168.0.197' is not allowed to connect to this MySQL server ((nullptr):0),. (ASYNC_LOAD_FAILED)

5、解决方案

将mysql的root权限设置为允许所有ip地址可以访问，执行如下sql语句

use mysql;
SELECT Host, User FROM mysql.user;
update mysql.user set host='%' where user='root';  
-- GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.0.197' IDENTIFIED BY '123456' WITH GRANT OPTION;
-- FLUSH PRIVILEGES;

6、再次执行命令，报错如下

Received exception from server (version 23.11.1):
Code: 695. DB::Exception: Received from localhost:9000. DB::Exception: Load job 'startup MaterializedMySQL database mysqllzh' failed: Code: 537. DB::Exception: Illegal MySQL variables, the MaterializedMySQL engine requires default_authentication_plugin='mysql_native_password'. (ILLEGAL_MYSQL_VARIABLE),. (ASYNC_LOAD_FAILED)

7、解决方案

先查看一下目前default_authentication_plugin的值如下
```
show variables like '%default_authentication_plugin%';
```
- 通过如下语句设置为mysql_native_password，但是执行后会看到如下错误，因为是只读的，所以通过修改配置文件来修改
```
set default_authentication_plugin='mysql_native_password'
```
  - 在配置文件my.ini中增加如下配置内容
```
[mysqld]
default_authentication_plugin = mysql_native_password
```
    注意：mysql配置文件目录
    1.安装路径,例如:C:\Program Files\MySQL\MySQL Server 8.2\bin\mysqld.exe
    2.配置目录,例如:“C:\ProgramData\MySQL\MySQL Server 8.2\my.ini”
    - 修改后，重启MySQL服务
      再次执行命令
      
      show variables like ‘%default_authentication_plugin%’;
      
      8、在clickhouse服务器中，再次执行命令，成功了
      
      CREATE DATABASE mysqllzh ENGINE = MaterializeMySQL(‘192.168.0.137:3306’, ‘db’, ‘root’, ‘123456’)
      SETTINGS
      allows_query_when_mysql_lost=true,
      max_wait_time_when_mysql_unavailable=10000;
      其实是要用如下的语句
      CREATE DATABASE mysql_db ENGINE = MySQL('192.168.0.137:3306', 'test', 'root', '123456')
      
      9、继续执行如下sql语句，检查是否存在表test
      
      show tables from mysqllzh
      
      10、以下语句，检查是否存在数据
      select * from mysqllzh.test
      
      在MySQL中新增几条数据后，检查是否能自动同步过来?
      最后发现并没有
      
      三、新方法，只是创建MySQL引擎的语句有改变，其余的都没有变（补充）
      
      1、先在mysql中创建数据库和表以及数据
```
create table mysql_table(
int_id int not null AUTO_INCREMENT,
`float` FLOAT not null,
primary key(int_id)
);
insert into mysql_table(int_id,`float`)VALUES(5,6);
select * from mysql_table;
```
      参考：重要
      
      2、在clickhouse中执行如下语句，创建MySQL引擎（与上一部重复，请用此步骤）
```
CREATE DATABASE mysql_db ENGINE = MySQL('192.168.0.137:3306', 'test', 'root', '123456')
```
      3、更改mysql数据后，自动同步