1.首先,我们先阅读一下官网的介绍 MaterializedMySQL | ClickHouse Docs

官网MaterializedMySQL配置

2.准备工作

2.1.mysql配置准备

• 使用root修改mysql配置my.cnf

#寻找my.cnf文件在哪里
$ whereis my.cnf
#my.cnf文件所在位置
> my: /etc/my.cnf
#修改my.cnf
vi /etc/my.cnf

• 加入以下配置

#clickhouse外部表引擎需要的参数
#这个是mysql5.7的密码认证插件,必须选这个
default_authentication_plugin=mysql_native_password
#binlog日志格式
binlog_format=ROW
#开启gtid
gtid-mode=on
#同时开启enforce_gtid_consistency
enforce_gtid_consistency=on

• 重启mysql服务

service mysqld restart

• 重点(mysql5.8的修改,5.7以下可以不用管)

如果我们的mysql使用的是5.8,他默认的default_authentication_plugin为caching_sha2_password,和我们在上面配置的default_authentication_plugin=mysql_native_password不同,需要重新去修改账号的密码认证插件

1. 登录mysql

--连接默认端口3306,root用户
mysql -u root -p
--连接数据库mysql
use mysql
--查询user表信息
select user,host,plugin from user;
--结果如下
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| datasharing      | %         | caching_sha2_password |
| hive             | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
6 rows in set (0.01 sec)

2. 可以看到我们的datasharing用户他的plugin还是采用的caching_sha2_password,这并不符合我们目前使用的mysql_native_password插件
3. 修改datasharing用户的密码认证策略

ALTER USER 'datasharing'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

4.修改完毕之后,我们查看并连接测试一下,那么mysql的准备工作做完了

select user,host,plugin from user;
--结果如下
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| datasharing      | %         | mysql_native_password |
| hive             | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
6 rows in set (0.01 sec)

2.2.clickhouse的操作

• 创建clickhouse表

--由于功能是实验性的,需要手动开启
set allow_experimental_database_materialized_mysql=1;
--创建ck表,MaterializeMySQL(mysqlhost:port,数据库名称,用户,密码)
CREATE DATABASE mysql_DStest
    ENGINE = MaterializeMySQL('node95:3306', 'DStest', 'datasharing', '123456');

MaterializeMySQL操作

3.DML验证

3.1.select

-- mysql查询
select * from test1;

+--+----+---+----+
|id|name|age|ss  |
+--+----+---+----+
|1 |t1  |1  |NULL|
|2 |t2  |2  |NULL|
+--+----+---+----+

-- clickhouse查询
select * from mysql_DStest.test1;

+--+----+---+----+
|id|name|age|ss  |
+--+----+---+----+
|1 |t1  |1  |NULL|
|2 |t2  |2  |NULL|
+--+----+---+----+

3.2.update

--mysql修改
update test1 set name='修改' where id =1;
--mysql查询
select * from test1;

+--+----+---+----+
|id|name|age|ss  |
+--+----+---+----+
|1 |修改  |1  |NULL|
|2 |t2  |2  |NULL|
+--+----+---+----+

--clickhouse查询
select * from mysql_DStest.test1;

+--+----+---+----+
|id|name|age|ss  |
+--+----+---+----+
|1 |修改  |1  |NULL|
|2 |t2  |2  |NULL|
+--+----+---+----+

3.3.delete

--mysql删除
delete from test1 where id=1;
--mysql查询
select * from test1;

+--+----+---+----+
|id|name|age|ss  |
+--+----+---+----+
|2 |t2  |2  |NULL|
+--+----+---+----+

--clickhouse查询
select * from mysql_DStest.test1;

+--+----+---+----+
|id|name|age|ss  |
+--+----+---+----+
|2 |t2  |2  |NULL|
+--+----+---+----+

3.4.insert

--mysql新增
insert into test1 values (3,'新增',11,'测试');
--mysql查询
select * from test1;

+--+----+---+----+
|id|name|age|ss  |
+--+----+---+----+
|2 |t2  |2  |NULL|
|3 |新增  |11 |测试|
+--+----+---+----+

--clickhouse查询
select * from mysql_DStest.test1;

+--+----+---+----+
|id|name|age|ss  |
+--+----+---+----+
|2 |t2  |2  |NULL|
|3 |新增  |11 |测试|
+--+----+---+----+

4.DDL验证

4.1.create

--mysql新增表
CREATE TABLE `test3` (
  `id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

--clickhouse 查询表
show tables;

+-----+
|name |
+-----+
|test1|
|test2|
|test3|
+-----+

--clickhouse查看ddl
show create table test3;

CREATE TABLE mysql_DStest.test3
(
    `id` Int32,
    `_sign` Int8 MATERIALIZED 1,
    `_version` UInt64 MATERIALIZED 1,
    INDEX _version _version TYPE minmax GRANULARITY 1
)
ENGINE = ReplacingMergeTree(_version)
PARTITION BY intDiv(id, 4294967)
ORDER BY tuple(id)
SETTINGS index_granularity = 8192

4.2 ADD COLUMN

--mysql 新增字段
ALTER TABLE `DStest`.`test3` 
ADD COLUMN `new_col` varchar(255) NULL AFTER `idsss`;

--clickhouse查看ddl
show create table test3;

create table test3
(
    id       String,
    new_col  Nullable(String),
    _sign    Int8 materialized 1,
    _version UInt64 materialized 1
)
    engine = MaterializedMySQL(_version)
        ORDER BY tuple(id)
        SETTINGS index_granularity = 8192;

4.3.DROP COLUMN

--mysql删除字段
ALTER TABLE `DStest`.`test3` 
DROP COLUMN `new_col`;

--clickhouse查看ddl
show create table test3;

create table test3
(
    id       String,
    _sign    Int8 materialized 1,
    _version UInt64 materialized 1
)
    engine = MaterializedMySQL(_version)
        ORDER BY tuple(id)
        SETTINGS index_granularity = 8192;

4.4.alter

--mysql 修改字段
ALTER TABLE `DStest`.`test3` 
CHANGE COLUMN `id` `idsss` int(0) NOT NULL FIRST,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`idsss`) USING BTREE;

报错信息

Code: 48. DB::Exception: Cannot rename and modify the same column id in a single ALTER query: While executing MYSQL_QUERY_EVENT. The query: ALTER TABLE DStest.test3 CHANGE COLUMN id idsss int(0) NOT NULL FIRST, DROP PRIMARY KEY, ADD PRIMARY KEY (idsss) USING BTREE. (NOT_IMPLEMENTED) (version 21.12.3.32 (official build))

这里解决方案有很多,可以重建MaterializedMySQL等,其他的DDL不验证了,由于是实验性功能,可能在使用上存在一些bug

感谢

clickhouse高级功能之MaterializeMySQL详解
the MaterializeMySQL engine requires default_authentication_plugin=‘mysql_native_password‘.
解决mysql8.0因密码认证插件导致的链接不上
default_authentication_plugin 参数的设定