展开查看详情
1 . MaterializeMySQL:
MySQL 到 ClickHouse 的高速公路
演讲人：邰翀
2 .About Me
► Active contributor of ClickHouse & Xenon
► MaterializeMySQL
► MySQL Database Engine
► And more 40+ features https://tceason.github.io/
3 .目录
► MySQL 复制历程
► ClickHouse 同步 MySQL 数据
► MaterializeMySQL Engine
► HTAP 应用场景
5 .MySQL 复制历程
MySQL 3.23 (2001) 开始支持复制
MySQL 5.1.5 (2006-01-10) binlog支持行模式（row-based）
MySQL 5.5.0 (2009-12-07) semi-sync replication
MySQL 5.6.0 (2011) delayed replication
MySQL 5.6.3 (2011-10-03) 基于库的并行复制
MySQL 5.6.5 (2012-04-10) GTID
MySQL 5.7.2 (2013-09-21) lossless replication
MySQL 5.7.5 (2014-09-25) mutli-source replication
MySQL 5.7.x 基于组提交（事务级）的并行复制
MySQL 5.7.17(2016-10-12) group replication（MGR）
MySQL 8.0.1(2017-04-10) group replication（MGR）、基于WriteSet（记录级）的并行复制
MySQL 5.7.22(2018-04-19) 基于WriteSet（记录级）的并行复制
6 .MySQL 复制历程
Master
Xenon
Xenon
(Leader)
MySQL
pingMySQL
heartbeat heartbeat
semi-sync semi-sync
replication replication
pingMySQL pingMySQL
Raft
Slave .........
Slave
(Follower) (Follower)
8 .MySQL 复制历程
集群数据强一致 主从秒级切换 无中心化自动选主 跨区容灾
10 .ClickHouse 同步 MySQL 数据
11 .ClickHouse 同步 MySQL 数据
12 .ClickHouse 同步 MySQL 数据
13 .ClickHouse 同步 MySQL 数据
► MySQL Table Engine
• Mapping to MySQL table
• Fetch table struct from MySQL
• Fetch data from MySQL when execute query
14 .ClickHouse 同步 MySQL 数据
► MySQL Database Engine
• Mapping to MySQL Database
• Fetch table list from MySQL
• Fetch table struct from MySQL
• Fetch data from MySQL when execute query
15 .ClickHouse 同步 MySQL 数据
► Some others
Canal
16 .ClickHouse
能否主动同步并订阅
MySQL ？？？
17 .MaterializeMySQL Engine
18 .MaterializeMySQL Engine
由青云ClickHouse 团队贡献
https://github.com/Click
House/ClickHouse/pull/1
0851
19 .MaterializeMySQL Engine
► • Mapping to MySQL database
► • Consume MySQL BINLOG and store to MergeTree
► • Experimental feature (20.8, recommend latest stable
version)
20 .MaterializeMySQL Engine
21 .MaterializeMySQL Engine
CREATE DATABASE test
ENGINE =
MaterializeMySQL('172.17.0.3:3306',
'demo', 'root', '123')
22 .MaterializeMySQL Engine
History data
New data
23 .MaterializeMySQL Engine
Check MySQL Vars
Select history data
Consume new data
24 .MaterializeMySQL Engine
Transaction{
cleanOutdatedTables;
startBinlogDumpGTID Position.update InitMetadata
dumpDataForTables;
}
prepareSynchronized CheckMySQLVars
Synchronized
readOneBinlogEvent onEvent flushBuffersData
25 .MaterializeMySQL Engine
Check MySQL Vars
Select history data
Consume new data
26 .MaterializeMySQL Engine
Transaction{
cleanOutdatedTables;
startBinlogDumpGTID Position.update InitMetadata
dumpDataForTables;
}
prepareSynchronized CheckMySQLVars
Synchronized
readOneBinlogEvent onEvent flushBuffersData
27 .MaterializeMySQL Engine
Check MySQL Vars
SHOW VARIABLES WHERE (Variable_name = 'log_bin'
AND upper(Value) = 'ON')
OR (Variable_name = 'binlog_format'
AND upper(Value) = 'ROW')
OR (Variable_name = 'binlog_row_image'
AND upper(Value) = 'FULL')
OR (Variable_name = 'default_authentication_plugin'
AND upper(Value) = 'MYSQL_NATIVE_PASSWORD')
OR (Variable_name = 'log_bin_use_v1_row_events'
AND upper(Value) = 'OFF');
28 .MaterializeMySQL Engine
Check MySQL Vars
Select history data
Consume new data
29 .MaterializeMySQL Engine
Transaction{
cleanOutdatedTables;
startBinlogDumpGTID Position.update InitMetadata
dumpDataForTables;
}
prepareSynchronized CheckMySQLVars
Synchronized
readOneBinlogEvent onEvent flushBuffersData
热门城市
