在Clickhouse 20.8.2.3 版本中新增一个数据库引擎，将clickhouse模拟为MySQL的从库，可以通过mysql的binlog实时的接收来自mysql的数据并在clickhouse物化,极大提升了数仓的查询性能和数据同步的时效性。同时增加了获取mysql数据的方式，除了mysql协议和mysql函数和mysql表引擎，clickhouse可以作为mysql的从库。
ClickHouse can work as MySQL replica - it is implemented by MaterializeMySQL database engine. 运行环境：
CentOS 7.6
Clickhouse 20.10.1.4608
MySQL 8.0.20
MaterializeMySQL database engine 支持的情况：
1.支持mysql 库级别的数据同步，暂不支持表级别的。
2.MySQL 库映射到clickhouse中自动创建为ReplacingMergeTree 引擎的表
3.支持全量和增量同步，首次创建数据库引擎时进行一次全量复制，之后通过监控binlog变化进行增量数据同步
4.支持的MySQL版本：5.6 5.7 8.0
5.支持的操作:insert，update，delete，alter，create，drop，truncate等大部分DDL操作
7.支持的MySQL复制为GTID复制
MySQL需要配置的部分：
1.开启binlog并设置为row格式：
log-bin=mysqlbin.log
binlog_format=ROW
server-id=1 查看验证：
mysql> show variables like '%log%bin%';
+----------------------------------+-------------------------------+
| Variable_name | Value |
+----------------------------------+-------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysqlbin |
| log_bin_index | /var/lib/mysql/mysqlbin.index |
.....
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.00 sec)
MySQL的版本号：
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.20 |
+-----------+
1 row in set (0.00 sec)
在MySQL中创建一个数据库表：
mysql> create table dataset.users(id bigint not null auto_increment primary key,usercode varchar(8) not null comment '用户编码',username varchar(16) comment '用户名称',state int comment '状态 1在线0离线',createtime datetime not null, lastmodifytime datetime(6) not null default current_timestamp(6) on update current_timestamp(6),key ix_usercode(usercode))ENGINE=InnoDB default charset=utf8mb4 comment '用户信息';
Query OK, 0 rows affected (0.04 sec)
mysql> insert into dataset.users(usercode,username,state,createtime)values('A008001','xiaowu',1,now()),('A008002','xiaoming',1,now());
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from dataset.users;
+----+----------+----------+-------+---------------------+----------------------------+
| id | usercode | username | state | createtime | lastmodifytime |
+----+----------+----------+-------+---------------------+----------------------------+
| 1 | A008001 | xiaowu | 1 | 2020-09-02 09:05:08 | 2020-09-02 09:05:08.073302 |
| 2 | A008002 | xiaoming | 1 | 2020-09-02 09:05:08 | 2020-09-02 09:05:08.073302 |
+----+----------+----------+-------+---------------------+----------------------------+
2 rows in set (0.00 sec)
mysql> show create table dataset.users\G
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`id` bigint NOT NULL AUTO_INCREMENT,
`usercode` varchar(8) NOT NULL COMMENT '用户编码',
`username` varchar(16) DEFAULT NULL COMMENT '用户名称',
`state` int DEFAULT NULL COMMENT '状态 1在线0离线',
`createtime` datetime NOT NULL,
`lastmodifytime` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
PRIMARY KEY (`id`),
KEY `ix_usercode` (`usercode`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户信息'
1 row in set (0.00 sec)
注意：
1.主键ID不能为null 即需要设置为not null
2.索引列也需要设置为not null
在clickhouse中查看信息并设置：
Clickhouse> select * from system.settings where name ='allow_experimental_database_materialize_mysql';
SELECT *
FROM system.settings
WHERE name = 'allow_experimental_database_materialize_mysql'
┌─name──────────────────────────────────────────┬─value─┬─changed─┬─description─────────────────────────────────────────────────┬─min──┬─max──┬─readonly─┬─type─┐
│ allow_experimental_database_materialize_mysql │ 0 │ 0 │ Allow to create database with Engine=MaterializeMySQL(...). │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 0 │ Bool │
└───────────────────────────────────────────────┴───────┴─────────┴─────────────────────────────────────────────────────────────┴──────┴──────┴──────────┴──────┘
1 rows in set. Elapsed: 0.006 sec.
Clickhouse> select version();
SELECT version()
┌─version()────┐
│ 20.10.1.4608 │
└──────────────┘
1 rows in set. Elapsed: 0.004 sec.
Clickhouse> set allow_experimental_database_materialize_mysql=1;
Clickhouse> create database dataset_users engine=MaterializeMySQL('192.168.8.110:3306','dataset','root','oracle');
CREATE DATABASE dataset_users
ENGINE = MaterializeMySQL('192.168.8.110:3306', 'dataset', 'root', 'oracle')
Ok.
0 rows in set. Elapsed: 2.449 sec.
root为用户名
oracle为root对应的密码
dataset即为mysql中的数据库名称
Clickhouse> use dataset_users;
USE dataset_users
Ok.
0 rows in set. Elapsed: 0.002 sec.
Clickhouse> show tables;
SHOW TABLES
┌─name──┐
│ users │
└───────┘
1 rows in set. Elapsed: 0.005 sec.
Clickhouse> select * from users;
SELECT *
FROM users
Received exception from server (version 20.10.1):
Code: 1002. DB::Exception: Received from localhost:9000. DB::Exception: The replication sender thread cannot start in AUTO_POSITION mode: this server has GTID_MODE = OFF instead of ON..
0 rows in set. Elapsed: 0.003 sec.
需要在MySQL端开启GTID模式：
1.确保MySQL版本在5.6. 以上
2.在MySQL 5.7版本支持热部署，即不停止服务的情况下开启GTID模式
操作步骤：
SET GLOBAL ENFORCE_GTID_CONSISTENCY = 'WARN';
SET GLOBAL ENFORCE_GTID_CONSISTENCY = 'ON';
SET GLOBAL GTID_MODE = 'OFF_PERMISSIVE';
SET GLOBAL GTID_MODE = 'ON_PERMISSIVE';
SET GLOBAL GTID_MODE = 'ON';
查看验证：
mysql> show variables like 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'ENFORCE_GTID_CONSISTENCY';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON |
+--------------------------+-------+
1 row in set (0.00 sec)
开启之后继续查询：
在MySQL端需要开启GTID模式：
Clickhouse> select * from users;
SELECT *
FROM users
Received exception from server (version 20.10.1):
Code: 1002. DB::Exception: Received from localhost:9000. DB::Exception: The replication sender thread cannot start in AUTO_POSITION mode: this server has GTID_MODE = OFF instead of ON..
0 rows in set. Elapsed: 0.002 sec.
开启之后仍然报错。
需要删除数据库，重新创建MaterializeMySQL数据库表：
CREATE DATABASE dataset_users
ENGINE = MaterializeMySQL('192.168.8.110:3306', 'dataset', 'root', 'oracle')
重新查询数据：
Clickhouse> select * from users FORMAT PrettyCompactMonoBlock;
SELECT *
FROM users
FORMAT PrettyCompactMonoBlock
┌─id─┬─usercode─┬─username─┬─state─┬──────────createtime─┬─────────────lastmodifytime─┐
│ 1 │ A008001 │ xiaowu │ 1 │ 2020-09-02 09:05:08 │ 2020-09-02 09:05:08.073302 │
│ 2 │ A008002 │ xiaoming │ 1 │ 2020-09-02 09:05:08 │ 2020-09-02 09:05:08.073302 │
└────┴──────────┴──────────┴───────┴─────────────────────┴────────────────────────────┘
2 rows in set. Elapsed: 0.006 sec.
在clickhouse端传表的定义：
Clickhouse> desc table users;
DESCRIBE TABLE users
┌─name───────────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int64 │ │ │ │ │ │
│ usercode │ String │ │ │ │ │ │
│ username │ Nullable(String) │ │ │ │ │ │
│ state │ Nullable(Int32) │ │ │ │ │ │
│ createtime │ DateTime │ │ │ │ │ │
│ lastmodifytime │ DateTime64(6) │ │ │ │ │ │
│ _sign │ Int8 │ MATERIALIZED │ 1 │ │ │ │
│ _version │ UInt64 │ MATERIALIZED │ 1 │ │ │ │
└────────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
8 rows in set. Elapsed: 0.003 sec.
Clickhouse> show create table users;
SHOW CREATE TABLE users
Received exception from server (version 20.10.1):
Code: 390. DB::Exception: Received from localhost:9000. DB::Exception: There is no SHOW CREATE TABLE query for DatabaseMaterializeMySQL.
0 rows in set. Elapsed: 0.002 sec.
在默认的数据库下，可以看到users表的定义：
/var/lib/clickhouse/metadata/dataset_users
# cat users.sql
ATTACH TABLE users
(
`id` Int64,
`usercode` String,
`username` Nullable(String),
`state` Nullable(Int32),
`createtime` DateTime,
`lastmodifytime` DateTime64(6),
`_sign` Int8 MATERIALIZED 1,
`_version` UInt64 MATERIALIZED 1
)
ENGINE = ReplacingMergeTree(_version)
PARTITION BY intDiv(id, 18446744073709551)
ORDER BY (usercode, id)
SETTINGS index_granularity = 8192结论：通过MaterializeMySQL 可以全量的抽取数据。
增量数据验证：
MySQL端：
1.插入数据：
mysql> insert into dataset.users(usercode,username,state,createtime)values('A008003','xiaozhao',1,now()),('A008004','xiaowang',1,now());
clickhouse端：
Clickhouse> select * from users FORMAT PrettyCompactMonoBlock;
SELECT *
FROM users
FORMAT PrettyCompactMonoBlock
┌─id─┬─usercode─┬─username─┬─state─┬──────────createtime─┬─────────────lastmodifytime─┐
│ 2 │ A008002 │ xiaoming │ 1 │ 2020-09-02 09:05:08 │ 2020-09-02 09:05:08.073302 │
│ 4 │ A008004 │ xiaowang │ 1 │ 2020-09-02 09:29:41 │ 2020-09-02 09:29:41.683995 │
│ 1 │ A008001 │ xiaowu │ 1 │ 2020-09-02 09:05:08 │ 2020-09-02 09:05:08.073302 │
│ 3 │ A008003 │ xiaozhao │ 1 │ 2020-09-02 09:29:41 │ 2020-09-02 09:29:41.683995 │
└────┴──────────┴──────────┴───────┴─────────────────────┴────────────────────────────┘
4 rows in set. Elapsed: 0.007 sec.
可能报错：
Received exception from server (version 20.10.1):
Code: 48. DB::Exception: Received from localhost:9000. DB::Exception: Unsupported data type from MySQL..
0 rows in set. Elapsed: 0.003 sec.
2.更新数据：
mysql> update dataset.users set state=0 where usercode='A008001';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Clickhouse> select * from dataset_users.users where usercode='A008001';
┌─id─┬─usercode─┬─username─┬─state─┬──────────createtime─┬─────────────lastmodifytime─
│ 1 │ A008001 │ xiaowu │ 0 │ 2020-09-02 09:05:08 │ 2020-09-02 09:45:20.177437 │
└────┴──────────┴──────────┴───────┴─────────────────────┴────────────────────────────┴
3.删除数据：
mysql> delete from dataset.users where usercode='A008002';
Query OK, 1 row affected (0.00 sec)
Clickhouse> select * from dataset_users.users where usercode='A008002';
SELECT *
FROM dataset_users.users
WHERE usercode = 'A008002'
Ok.
0 rows in set. Elapsed: 0.006 sec.
列字段操作：
1.增加一列：
mysql> alter table dataset.users add column sex varchar(6) comment '性别';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
Clickhouse> desc table dataset_users.users;
DESCRIBE TABLE dataset_users.users
┌─name───────────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int64 │ │ │ │ │ │
│ usercode │ String │ │ │ │ │ │
│ username │ Nullable(String) │ │ │ │ │ │
│ state │ Nullable(Int32) │ │ │ │ │ │
│ createtime │ DateTime │ │ │ │ │ │
│ lastmodifytime │ DateTime64(6) │ │ │ │ │ │
│ sex │ Nullable(String) │ │ │ │ │ │
│ _sign │ Int8 │ MATERIALIZED │ 1 │ │ │ │
│ _version │ UInt64 │ MATERIALIZED │ 1 │ │ │ │
└────────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
9 rows in set. Elapsed: 0.006 sec.
2.增加两列：
mysql> alter table dataset.users add column birthday date comment '生日',add column salary decimal(22,6) comment '基本薪资';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
Clickhouse> desc table dataset_users.users;
DESCRIBE TABLE dataset_users.users
┌─name───────────┬─type─────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int64 │ │ │ │ │ │
│ usercode │ String │ │ │ │ │ │
│ username │ Nullable(String) │ │ │ │ │ │
│ state │ Nullable(Int32) │ │ │ │ │ │
│ createtime │ DateTime │ │ │ │ │ │
│ lastmodifytime │ DateTime64(6) │ │ │ │ │ │
│ sex │ Nullable(String) │ │ │ │ │ │
│ birthday │ Nullable(Date) │ │ │ │ │ │
│ salary │ Nullable(Decimal(22, 6)) │ │ │ │ │ │
│ _sign │ Int8 │ MATERIALIZED │ 1 │ │ │ │
│ _version │ UInt64 │ MATERIALIZED │ 1 │ │ │ │
└────────────────┴──────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
11 rows in set. Elapsed: 0.005 sec.
3.删除列：
mysql> alter table dataset.users drop column salary;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
Clickhouse> desc table dataset_users.users;
DESCRIBE TABLE dataset_users.users
┌─name───────────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int64 │ │ │ │ │ │
│ usercode │ String │ │ │ │ │ │
│ username │ Nullable(String) │ │ │ │ │ │
│ state │ Nullable(Int32) │ │ │ │ │ │
│ createtime │ DateTime │ │ │ │ │ │
│ lastmodifytime │ DateTime64(6) │ │ │ │ │ │
│ sex │ Nullable(String) │ │ │ │ │ │
│ birthday │ Nullable(Date) │ │ │ │ │ │
│ _sign │ Int8 │ MATERIALIZED │ 1 │ │ │ │
│ _version │ UInt64 │ MATERIALIZED │ 1 │ │ │ │
└────────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
10 rows in set. Elapsed: 0.005 sec.
4.修改列字段类型：
mysql> alter table dataset.users change birthday birthday int;
语法等同于：
alter table dataset.users modify birthday int;
Clickhouse> desc table dataset_users.users;
DESCRIBE TABLE dataset_users.users
┌─name───────────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int64 │ │ │ │ │ │
│ usercode │ String │ │ │ │ │ │
│ username │ Nullable(String) │ │ │ │ │ │
│ state │ Nullable(Int32) │ │ │ │ │ │
│ createtime │ DateTime │ │ │ │ │ │
│ lastmodifytime │ DateTime64(6) │ │ │ │ │ │
│ sex │ Nullable(String) │ │ │ │ │ │
│ birthday │ Nullable(Int32) │ │ │ │ │ │
│ _sign │ Int8 │ MATERIALIZED │ 1 │ │ │ │
│ _version │ UInt64 │ MATERIALIZED │ 1 │ │ │ │
└────────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
10 rows in set. Elapsed: 0.004 sec.
5.修改列的名称：
mysql> alter table dataset.users rename column birthday to birth;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
Clickhouse> desc table dataset_users.users;
DESCRIBE TABLE dataset_users.users
┌─name───────────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int64 │ │ │ │ │ │
│ usercode │ String │ │ │ │ │ │
│ username │ Nullable(String) │ │ │ │ │ │
│ state │ Nullable(Int32) │ │ │ │ │ │
│ createtime │ DateTime │ │ │ │ │ │
│ lastmodifytime │ DateTime64(6) │ │ │ │ │ │
│ sex │ Nullable(String) │ │ │ │ │ │
│ birth │ Nullable(Int32) │ │ │ │ │ │
│ _sign │ Int8 │ MATERIALIZED │ 1 │ │ │ │
│ _version │ UInt64 │ MATERIALIZED │ 1 │ │ │ │
└────────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
10 rows in set. Elapsed: 0.003 sec.
6.同时修改列字段的名称和数据类型：
mysql> desc dataset.users;
+----------------+-------------+------+-----+----------------------+--------------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+----------------------+--------------------------------------------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| usercode | varchar(8) | NO | MUL | NULL | |
| username | varchar(16) | YES | | NULL | |
| state | int | YES | | NULL | |
| createtime | datetime | NO | | NULL | |
| lastmodifytime | datetime(6) | NO | | CURRENT_TIMESTAMP(6) | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(6) |
| sex | varchar(6) | YES | | NULL | |
| birth | int | YES | | NULL | |
+----------------+-------------+------+-----+----------------------+--------------------------------------------------+
8 rows in set (0.00 sec)
mysql> alter table dataset.users change sex gender varchar(16);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
在clickhouse则无法查看了修改的字段名了，此时需要重新定义MaterializeMySQL
Clickhouse> desc table dataset_users.users;
DESCRIBE TABLE dataset_users.users
┌─name───────────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int64 │ │ │ │ │ │
│ usercode │ String │ │ │ │ │ │
│ username │ Nullable(String) │ │ │ │ │ │
│ state │ Nullable(Int32) │ │ │ │ │ │
│ createtime │ DateTime │ │ │ │ │ │
│ lastmodifytime │ DateTime64(6) │ │ │ │ │ │
│ sex │ Nullable(String) │ │ │ │ │ │
│ birth │ Nullable(Int32) │ │ │ │ │ │
│ _sign │ Int8 │ MATERIALIZED │ 1 │ │ │ │
│ _version │ UInt64 │ MATERIALIZED │ 1 │ │ │ │
└────────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
10 rows in set. Elapsed: 0.004 sec
DROP DATABASE dataset_users;
CREATE DATABASE dataset_users
ENGINE = MaterializeMySQL('192.168.8.110:3306', 'dataset', 'root', 'oracle');
Clickhouse> desc table dataset_users.users;
DESCRIBE TABLE dataset_users.users
┌─name───────────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int64 │ │ │ │ │ │
│ usercode │ String │ │ │ │ │ │
│ username │ Nullable(String) │ │ │ │ │ │
│ state │ Nullable(Int32) │ │ │ │ │ │
│ createtime │ DateTime │ │ │ │ │ │
│ lastmodifytime │ DateTime64(6) │ │ │ │ │ │
│ gender │ Nullable(String) │ │ │ │ │ │
│ birth │ Nullable(Int32) │ │ │ │ │ │
│ _sign │ Int8 │ MATERIALIZED │ 1 │ │ │ │
│ _version │ UInt64 │ MATERIALIZED │ 1 │ │ │ │
└────────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
10 rows in set. Elapsed: 0.004 sec.
在MySQL的库dataset中新增表：
mysql> use dataset;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table city(id int not null auto_increment primary key,province varchar(32),city varchar(32));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into city(province,city)values('hubei','wuhan');
Query OK, 1 row affected (0.01 sec)
在Clickhouse中查看新增的表和数据：
Clickhouse> show tables;
SHOW TABLES
┌─name──┐
│ city │
│ users │
└───────┘
2 rows in set. Elapsed: 0.006 sec.
Clickhouse> select * from city;
SELECT *
FROM city
┌─id─┬─province─┬─city──┐
│ 1 │ hubei │ wuhan │
└────┴──────────┴───────┘
1 rows in set. Elapsed: 0.004 sec.
表名重新命名:
mysql> alter table city rename to citys;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-------------------+
| Tables_in_dataset |
+-------------------+
| citys |
| users |
+-------------------+
2 rows in set (0.00 sec)
Clickhouse> show tables;
SHOW TABLES
┌─name──┐
│ citys │
│ users │
└───────┘
2 rows in set. Elapsed: 0.004 sec.
删除表：
mysql> drop table city;
Query OK, 0 rows affected (0.01 sec)
Clickhouse> show tables;
SHOW TABLES
┌─name──┐
│ users │
└───────┘
一次删除多个表的操作：
Clickhouse> show tables;
SHOW TABLES
┌─name──┐
│ a │
│ b │
│ citys │
│ users │
└───────┘
4 rows in set. Elapsed: 0.007 sec.
mysql> drop table a,b;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_dataset |
+-------------------+
| citys |
| users |
+-------------------+
2 rows in set (0.00 sec)
Clickhouse> show tables;
SHOW TABLES
┌─name──┐
│ a │
│ b │
│ citys │
│ users │
└───────┘
4 rows in set. Elapsed: 0.004 sec.
Clickhouse> drop table a;
DROP TABLE a
Received exception from server (version 20.10.1):
Code: 48. DB::Exception: Received from localhost:9000. DB::Exception: MaterializeMySQL database not support drop table..
0 rows in set. Elapsed: 0.002 sec. 结论：
1.clickhouse作为MySQL的从库可以支持表级别的增加和删除操作，支持rename操作；不支持MySQL中一次删除多个表的操作。
2.clickhouse 支持新增字段，仅修改字段类型，仅列名重命名，删除字段，目前的版本不支持MYSQL的同时修改字段名和类型的
3.clickhouse的增加和删除动作是实时同步的，原因在于我们创建的MaterializeMySQL engine会默认为每一张表生成ReplacingMergeTree engine，当clickhouse遇到删除的binlog操作时，会将这条数据的_sign字段设为-1；
目前ReplacingMergeTree还只是标记性删除，并非物理上的实际删除，索引随着删除日志的增多，查询过滤会有一定的负担。
4.截止这个版本目前还不支持datetime(n)类型的数据实时同步，不支持MySQL 8.0版本中的快速新加字段语法。
参考：
https://clickhouse.tech/docs/en/whats-new/changelog/
https://github.com/ClickHouse/ClickHouse/issues/4006
https://github.com/ClickHouse/ClickHouse/pull/10851
https://github.com/ClickHouse/ClickHouse/issues/15354
486