Dba+開源工具：補齊MySQL 8.0遷移短板，Binlog解析ETL抽取數據工具

作者：賀春旸 2023-06-27 07:29:30

將解析binlog和執行SQL語句的兩個過程分別由兩個線程來執行。其中，解析binlog的線程每次解析完一個事件后通過隊列將SQL語句傳給SQL執行線程，SQL執行線程從隊列中取出SQL語句并按順序依次執行，這樣就保證了SQL語句的串行執行。

由于MariaDB已經遠離MySQL，從MySQL 8.0遷移到MariaDB 10.5時，binlog同步復制已經不兼容（參考手冊：https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/）。

圖片

1、若MariaDB是主庫，MySQL是從庫，在GTID模式下，從MariaDB同步復制數據時，GTID與MySQL不兼容，同步將報錯。

2、若MySQL是主庫，MariaDB是從庫，MariaDB無法從MySQL 8.0主庫上復制，因為MySQL 8.0具有不兼容的二進制日志格式。

因此，借助binlog_parse_sql工具，可將binlog解析并生成SQL語句，反向插入MariaDB數據庫里。

1、從MySQL 8.0實時解析binlog并復制到MariaDB，適用于將MySQL 8.0遷移至MariaDB（ETL抽數據工具）--- binlog_parse_queue.py

2、數據恢復（研發手抖誤刪除一張表，通過歷史全量恢復+binlog增量恢復）

3、從MySQL 8.0實時解析binlog并復制到ClickHouse，適用于將MySQL 8.0遷移至ClickHouse（ETL抽數據工具）--- binlog_parse_clickhouse.py

1、安裝：

shell> pip3 install pymysql mysql-replication -i "http://mirrors.aliyun.com/pypi/simple" --trusted-host "mirrors.aliyun.com"

2、前臺運行：

shell> python3 binlog_parse_queue.py

圖片

3、后臺運行：

shell> nohup python3 binlog_parse_queue.py > from_mysql_to_mariadb.log 2>&1 &

4、工具運行后，會生成binlog_info.txt文件，即實時保存已經解析過的binlog文件名和position位置點，以方便程序掛掉后的斷點續傳。

注：運行后如報錯LookupError: unknown encoding: utf8mb3

解決方案：

編輯/usr/local/python3/lib/python3.10/site-packages/pymysql/charset.py文件，尾部增加如下兩行：

_charsets.add(Charset(256, "utf8mb3", "utf8mb3_general_ci", "Yes"))
_charsets.add(Charset(257, "utf8mb3", "utf8mb3_bin", ""))

參考如下鏈接：https://github.com/julien-duponchelle/python-mysql-replication/issues/386

1、安裝：

shell> pip3 install clickhouse-driver -i "http://mirrors.aliyun.com/pypi/simple" --trusted-host "mirrors.aliyun.com"

注：clickhouse_driver庫需要調用ssl，由于python 3.10之后版本不在支持libressl使用ssl，需要用openssl 1.1.1版本或者更高版本。

參見：python 3.10編譯安裝報SSL失敗解決方法（https://blog.csdn.net/mdh17322249/article/details/123966953）

2、MySQL表結構轉換為ClickHouse表結構

shell> vim mysql_to_clickhose_schema.py（修改腳本里的配置信息）

注：mysql_to_clickhose_schema_test.py（該工具僅為單表測試使用）

運行：

shell> python3 mysql_to_clickhose_schema.py

原理：連接MySQL獲取表結構schema，然后在ClickHouse里執行建表語句。

3、MySQL全量數據遷移至ClickHouse步驟：

第一步：

/usr/bin/mydumper -h 192.168.192.180 -u hechunyang -p wdhcy159753 -P 3306 --no-schemas -t 12 --csv -v 3 --regex '^hcy.user$' -o ./

注：需要mydumper 0.12.3-3版本支持導出CSV格式。

第二步：

clickhouse-client --query="INSERT INTO hcy.user FORMAT CSV" < hcy.user.00000.dat

第三步：或者使用mysql_to_clickhouse_sync.py工具（MySQL全量數據導入到ClickHouse里，默認并行10張表同時導出數據，每次輪詢取1000條數據）。

使用條件：表必須有自增主鍵，測試環境MySQL 8.0

如果你說服不了開發對每張表增加自增主鍵ID，那么你要設置參數

sql_generate_invisible_primary_key

開啟這個參數，會在建表時，檢查表中是否有主鍵，如果沒有主鍵，則會自動創建。該參數非常實用，減少了DBA對SQL語句表結構的審計（參考：https://blog.51cto.com/hcymysql/5952924）。

4、binlog_parse_clickhouse.py（ETL抽數據工具）將MySQL8.0增量數據遷移至ClickHouse

shell> vim binlog_parse_clickhouse.py（修改腳本里的配置信息）

前臺運行：

shell> python3 binlog_parse_clickhouse.py

后臺運行：

shell> nohup python3 binlog_parse_clickhouse.py > from_mysql_to_clickhouse.log 2>&1 &

此工具現通過dbaplus社群免費為大家提供下載使用。若使用過程中有任何問題或建議，可隨時與我們聯系，歡迎大家試用。

登錄以下鏈接即可下載：

https://github.com/hcymysql/binlog_parse_sql

責任編輯：武曉燕來源： dbaplus社群