ClickHouse 之 MaterializeMySQL 实战

前言

MaterializeMySQL^[1]数据库引擎目前还是实验室产品，其原理简单来说就是把 ClickHouse 服务器用作 MySQL 副本。ClickHouse 自动读取 binlog 并执行 DDL 和 DML 查询，因此本实战需要一定的创建MySQL主从库的知识。

环境

• Ubuntu Server

  $ cat /etc/os-release
  NAME="Ubuntu"
  VERSION="20.04.2 LTS (Focal Fossa)"
  
  复制

• ClickHouse

  clickhouse :) select version();
  ┌─version()─┐
  │ 21.2.5.5  │
  └───────────┘
  
  复制

• MySQL Server

  mysql> select version();
  +-------------------------+
  | version()               |
  +-------------------------+
  | 8.0.23-0ubuntu0.20.04.1 |
  +-------------------------+
  
  复制

安装 ClickHouse

sudo apt-get install apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4

echo "deb https://repo.clickhouse.tech/deb/stable/ main/" | sudo tee \
    /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update

sudo apt-get install -y clickhouse-server clickhouse-client

sudo /etc/init.d/clickhouse-server start
clickhouse-client

复制

安装配置 MySQL Server

安装 MySQL Server

$ sudo apt install mysql-server

复制

配置 mysql.cnf

MySQL Server 在此扮演主库角色，因此需要按主库的要求来做配置，

默认路径/etc/mysql/mysql.conf.d/mysqld.cnf

default_authentication_plugin=mysql_native_password
log_bin_use_v1_row_events=OFF

gtid_mode=ON
enforce_gtid_consistency=1

server-id=1

log_bin  =/var/log/mysql/mysql-bin.log
innodb_flush_log_at_trx_commit  = 1
sync_binlog                 = 1
binlog-format               = ROW


复制

创建用户,并赋予权限

CREATE USER 'clickhouse'@'%' IDENTIFIED BY 'alitrack';
GRANT select ON db.* TO 'clickhouse'@'%';
GRANT  replication client,replication slave, reload on *.* to 'clickhouse'@'%';
FLUSH PRIVILEGES;

复制

• 创建用户

• 全局赋予 replication client,replication slave, reload 权限

• 对同步库 db 赋予 select 权限

如果赋予权限不正确，会报错，

Code: 556. DB::Exception: Received from localhost:9000.
DB::Exception: MySQL SYNC USER ACCESS ERR:
mysql sync user needs at least GLOBAL PRIVILEGES:
'RELOAD, REPLICATION SLAVE, REPLICATION CLIENT' and
SELECT PRIVILEGE on Database db.

复制

建表

CREATE TABLE test (
  key_column bigint primary key,
  third_column varchar(100)
)


复制

必须有主键， 如果创建一个没有主键的表 test1，

CREATE TABLE test1 (
  key_column bigint,
  third_column varchar(100)
)

复制

clickhouse 报错，

Code: 48. DB::Exception: Received from localhost:9000.
DB::Exception: The db.test1 cannot be materialized,
because there is no primary keys.:
While executing MYSQL_QUERY_EVENT.
The query: CREATE TABLE test1 (   key_column bigint,   third_column varchar(100) ).


复制

出现这样的异常后，即使 MySQL 下面执行这样的代码也不解决问题了，

ALTER TABLE test1 ADD PRIMARY KEY (key_column);

复制

只能在 clickhouse 端 drop database 再重新创建了。

插入数据

insert into test values(1,'hello'),(2,'World'),'

复制

ClickHouse

1. 先执行

# 开启MaterializeMySQL同步功能
set allow_experimental_database_materialize_mysql=1;


复制

否则报错，

Received exception from server (version 21.2.5):
Code: 336. DB::Exception: Received from localhost:9000.
DB::Exception: MaterializeMySQL is an experimental database engine.
Enable allow_experimental_database_materialize_mysql to use it..

复制

因为到目前为止，MaterializeMySQL 还处于实验阶段，并未开发使用。

1. 创建引擎为 MaterializeMySQL 库

# 创建slave库，参数分别是("MySQL服务地址和端口", "待同步库名", "授权账户", "密码")

CREATE DATABASE db ENGINE = MaterializeMySQL('127.0.0.1:3306', 'db', 'clickhouse', 'alitrack');

SHOW TABLES FROM db;



复制

) show create table db.test;

SHOW CREATE TABLE db.test

Query id: 3b69e1a6-a14e-4bb1-9217-b99b9bdf6ab3

┌─statement──────────────────────────────────────────────┐
│ CREATE TABLE db.test
(
    `key_column` Int64,
    `third_column` Nullable(String),
    `_sign` Int8 MATERIALIZED 1,
    `_version` UInt64 MATERIALIZED 1
)
ENGINE = ReplacingMergeTree(_version)
PARTITION BY intDiv(key_column, 18446744073709551)
ORDER BY tuple(key_column)
SETTINGS index_granularity = 8192 │
└─────────────────────────────────────────────────────────┘

复制

使用 MaterializeMySQL 数据库引擎时，表引擎为 ReplacecingMergeTree, 并增加了虚拟列_sign 和_version。

• _version: Transaction counter。
• _sign: 删除标记,可能的值：
• 1 —未删除行，
• -1 —删除行。

创建自动更新的字典

创建字典

CREATE DICTIONARY default.ndict(
  key_column UInt64 DEFAULT 0,
  third_column String DEFAULT 'haha'
)
PRIMARY KEY key_column
SOURCE(CLICKHOUSE(HOST 'localhost' PORT 9000 USER 'default' TABLE 'test' PASSWORD '' DB 'db'))
LIFETIME(MIN 1 MAX 10)
LAYOUT(HASHED());

复制

在 default 数据库创建字典 ndict， 源是 db（MaterializeMySQL 库）里的 test 表,这样当我们修改MySQL 端 的test 表（新增、修改、删除）时，ndict 自动更新(有延迟）。

⚠️ 注意，

字典不要创建在 db 数据库里，否者删除不掉，而且会报错，万一这样做了，就只有

detach database db;

复制

sudo rm -rf /var/lib/clickhouse/data/db

复制

重启 clickhouse-server 再drop database db

MariaDB

不支持，报错如下，

Code: 537. DB::Exception: Received from localhost:9000.
DB::Exception: Illegal MySQL variables,
the MaterializeMySQL engine requires
log_bin_use_v1_row_events='OFF',
default_authentication_plugin='mysql_native_password'.

复制

参考资料