作者:刘安
爱可生测试团队成员,次要负责 DTLE 开源我的项目相干测试工作,善于 Python 自动化测试开发。
本文起源:原创投稿
*爱可生开源社区出品,原创内容未经受权不得随便应用,转载请分割小编并注明起源。
前言:过年前 DTLE 公布了 4.22.01.0 版本,该版本最重要的个性是反对 Oracle-MySQL 增量数据同步。明天我就来给大家介绍一下这个性能。
一、现状
1. 目前只反对增量同步
a. 能够依据 SCN 节点开启增量复制
b. 从工作启动工夫开启增量复制
2. 类型映射
a. 已反对类型
Oracle | MySQL | 限度 |
---|---|---|
BINARY_DOUBLE | float | mysql 不反对Inf/-Inf/Nan数据,用NULL来存储 |
CHAR(n), CHARACTER(n) | CHAR(n), CHARACTER(n) | |
DATE | datetime | |
DECIMAL(p,s), DEC(p,s) | DECIMAL(p,s), DEC(p,s) | |
DOUBLE PRECISION | DOUBLE PRECISION | |
FLOAT(p) | DOUBLE | |
INTEGER, INT | INT | 极值问题 (https://github.com/actiontech...) |
INTERVAL DAY(p) TO SECOND(s) | VARCHAR(30) | |
INTERVAL YEAR(p) TO MONTH | VARCHAR(30) | |
NCHAR VARYING(n) | NCHAR VARYING(n) | |
NCHAR(n) | NCHAR(n)/NVARCHAR(n) | |
NUMBER(p,0), NUMBER(p) | TINYINT/SMALLINT/INT/BIGINT/DECIMAL(p) | |
NUMBER(p,s) | DECIMAL(p,s) | |
NUMBER, NUMBER(*) | DOUBLE | |
NUMERIC(p,s) | NUMERIC(p,s) | |
NVARCHAR2(n) | NVARCHAR(n) | |
RAW(n) | VARBINARY(n) | |
REAL | DOUBLE | |
ROWID | CHAR(100) | |
SMALLINT | DECIMAL(38) | |
TIMESTAMP(p) | datetime | |
VARCHAR2(n) | VARCHAR(n) | |
VARCHAR(n) | VARCHAR(n) |
b. 待反对类型
Oracle | MySQL | 以后不反对起因 |
---|---|---|
BINARY_FLOAT | float | MySQL不反对Inf/-Inf/Nan数据, MySQL float类型无奈准确匹配,导致更新失败 |
BLOB | BLOB | 以后实现逻辑,无奈从redoSQL获取足够的值 |
CLOB | CLOB | 以后实现逻辑,无奈从redoSQL获取足够的值 |
LONG | LONGTEXT | 只反对insert |
LONG RAW | LONGBLOB | 只反对insert |
NCLOB | TEXT | 无奈从redoSQL获取足够的值 |
TIMESTAMP(p) WITH TIME ZONE | datetime | 时区问题未解决 |
c. 不反对类型
Oracle | MySQL | 不反对起因 |
---|---|---|
BFILE | VARCHAR(255) | logminer不反对 |
UROWID(n) | VARCHAR(n) | logminer读取的数据不足以结构新SQL |
XMLTYPE | VARCHAR(30) | logminer不反对 |
3. DML反对度
a. DML类型
DML类型 | Oracle SQL | MySQL SQL |
---|---|---|
INSERT | INSERT INTO ACTION_DB.CHAR_255_COLUMNS VALUES (0, NULL) | replace into ACTION_DB .CHAR_255_COLUMNS ( COL1, COL2`) values ('0', NULL) |
UPDATE | UPDATE ACTION_DB.CHAR_255_COLUMNS SET COL2='abcdefghijklmnopqrstuvwxyz' WHERE COL1=0 | update ACTION_DB .CHAR_255_COLUMNS set COL1 ='0', COL2 ='abcdefghijklmnopqrstuvwxyz' where ((COL1 = '0') and (COL2 is NULL)) limit 1 |
DELETE | DELETE FROM ACTION_DB.CHARACTER_255_COLUMNS WHERE COL1=0 | delete from ACTION_DB .CHAR_255_COLUMNS where ((COL1 = '0') and (COL2 = 'abcdefghijklmnopqrstuvwxyz')) limit 1 |
b. DML函数反对度
函数名 | 是否反对 | 其余 |
---|---|---|
CURRENT_TIMESTAMP | 是 | |
DATE | 是 | |
EMPTY_BLOB | 是 | 函数反对解析为NULL |
EMPTY_CLOB | 是 | 函数反对解析为NULL |
HEXTORAW | 是 | |
LOCALTIMESTAMP | 是 | |
RAWTOHEX | 是 | |
RAWTOHEX(CHR()) | 是 | |
SYSTIMESTAMP | 是 | |
TO_DATE | 是 | |
TO_DSINTERVAL | 是 | |
TO_TIMESTAMP | 是 | |
TO_YMINTERVAL | 是 | |
UNISTR | 是 |
4. DDL反对度
DDL | Target | Option |
---|---|---|
CREATE | 表 TABLE | DEFAULT CREATE |
ALTER | 表 TABLE | 减少字段 |
删除字段 | ||
重命名字段 (以后仅反对MySQL 8.0语法) | ||
变更字段类型 | ||
DROP | 表 TABLE | DEFAULT DROP |
二、环境筹备
1. Oracle数据库开启归档日志
shell> su oracleshell> mkdir /u01/app/oracle/oradata/archive_logshell> sqlplus sys/oracle as sysdbaSQL> alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/archive_log' scope=spfile;SQL> alter system set db_recovery_file_dest_size = 10G;SQL> shutdown immediate;SQL> startup mount;SQL> alter database add logfile group 3 '/u01/app/oracle/fast_recovery_area/XE/onlinelog/redo01.log' size 500m;SQL> alter database add logfile group 4 '/u01/app/oracle/fast_recovery_area/XE/onlinelog/redo02.log' size 500m;SQL> alter database add logfile group 5 '/u01/app/oracle/fast_recovery_area/XE/onlinelog/redo03.log' size 500m;SQL> alter database archivelog;SQL> alter database add supplemental log data (all) columns;SQL> alter database open;SQL> archive log list;# Archive Mode示意已开启归档模式,Archive destination示意归档日志贮存门路
2. 装置 LogMiner 工具(Oracle 装置时默认装置)
可查看零碎中是否存在运行 LogMiner 所须要的 dbms_logmnr 、dbms_logmnr_d 包,如果没有装置 LogMiner 工具须要的包,须要运行上面两个命令:
shell> cat $ORACLE_HOME/rdbms/admin/dbmslm.sql | sqlplus sys/oracle as sysdbashell> cat $ORACLE_HOME/rdbms/admin/dbmslmd.sql | sqlplus sys/oracle as sysdba
3. 创立 logminer 须要角色权限
shell> su oracleshell> sqlplus sys/oracle as sysdbaSQL> create user roma_logminer identified by oracle default tablespace users;SQL> GRANT CREATE SESSION TO roma_logminer;SQL> GRANT SET CONTAINER TO roma_logminer;SQL> GRANT SELECT ON V_$DATABASE TO roma_logminer;SQL> GRANT FLASHBACK ANY TABLE TO roma_logminer;SQL> GRANT SELECT ANY TABLE TO roma_logminer;SQL> GRANT SELECT_CATALOG_ROLE TO roma_logminer;SQL> GRANT EXECUTE_CATALOG_ROLE TO roma_logminer;SQL> GRANT SELECT ANY TRANSACTION TO roma_logminer;SQL> GRANT CREATE TABLE TO roma_logminer;SQL> GRANT LOCK ANY TABLE TO roma_logminer;SQL> GRANT CREATE SEQUENCE TO roma_logminer;SQL> GRANT EXECUTE ON DBMS_LOGMNR TO roma_logminer;SQL> GRANT EXECUTE ON DBMS_LOGMNR_D TO roma_logminer;SQL> GRANT SELECT ON V_$LOG TO roma_logminer;SQL> GRANT SELECT ON V_$LOG_HISTORY TO roma_logminer;SQL> GRANT SELECT ON V_$LOGMNR_LOGS TO roma_logminer;SQL> GRANT SELECT ON V_$LOGMNR_CONTENTS TO roma_logminer;SQL> GRANT SELECT ON V_$LOGMNR_PARAMETERS TO roma_logminer;SQL> GRANT SELECT ON V_$LOGFILE TO roma_logminer;SQL> GRANT SELECT ON V_$ARCHIVED_LOG TO roma_logminer;SQL> GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO roma_logminer;SQL> alter user roma_logminer quota unlimited ON users;
4. 部署一个单节点的 4.22.01.0版本 DTLE
https://github.com/actiontech...
三、创立 Oracle-MySQL 工作
1. 获取 Token
shell> curl -s -X POST "http://172.100.9.11:8190/v2/loginWithoutVerifyCode" -H "accept: application/json" -H "Content-Type: application/json" -d "{ \"password\": \"admin\", \"tenant\": \"platform\", \"username\": \"admin\"}" | jq{ "message": "ok", "data": { "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJleHAiOjE2NDYxOTE2MzksImdyb3VwIjoicGxhdGZvcm0iLCJuYW1lIjoiYWRtaW4ifQ.-c_jVkxd_vP5Ka3gocdfGZIudWPujutdHpQYx8srX00" }}
2. 筹备 job 文件
shell> cat job.json{ "job_id": "test_oracle", "src_task": { "connection_config": { "database_type": "Oracle", "host": "172.100.9.31", "port": 1521, "user": "roma_logminer", "password": "oracle", "service_name": "XE" }, "node_id": "96d28881-f91b-19f4-1614-4d8a0e718e2f", "binlog_relay": false, "repl_chan_buffer_size": 120, "group_max_size": 1, "group_timeout": 100, "oracle_src_task_config": { "scn": 0 }, "task_name": "src", "replicate_do_db": [ { "table_schema": "ACTION_DB" } ] }, "is_password_encrypted": false, "dest_task": { "connection_config": { "database_type": "MySQL", "host": "172.100.9.1", "port": 3306, "user": "test_dest", "password": "test_dest" }, "node_id": "96d28881-f91b-19f4-1614-4d8a0e718e2f", "task_name": "dest", "mysql_dest_task_config": {} }, "task_step_name": "job_stage_full", "failover": true, "retry": 2}
3. 创立 Oracle-MySQL job
shell> curl -s -X POST "http://172.100.9.11:8190/v2/job/migration/create" -H "accept: application/json" -H "Authorization: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJleHAiOjE2NDYxOTE2MzksImdyb3VwIjoicGxhdGZvcm0iLCJuYW1lIjoiYWRtaW4ifQ.-c_jVkxd_vP5Ka3gocdfGZIudWPujutdHpQYx8srX00" -H "Content-Type: application/json" -d @job.json | jq{ "job": { "job_id": "test_oracle-migration", "task_step_name": "job_stage_full", "reverse": false, "failover": true, "is_password_encrypted": false, "src_task": { "task_name": "src", "node_id": "96d28881-f91b-19f4-1614-4d8a0e718e2f", "replicate_do_db": [ { "table_schema": "ACTION_DB", "table_schema_regex": "", "table_schema_rename": "", "tables": null } ], "replicate_ignore_db": null, "skip_create_db_table": false, "drop_table_if_exists": false, "mysql_src_task_config": null, "oracle_src_task_config": { "scn": 0 }, "group_max_size": 1, "group_timeout": 100, "repl_chan_buffer_size": 120, "chunk_size": 2000, "connection_config": { "host": "172.100.9.31", "port": 1521, "user": "roma_logminer", "password": "*", "service_name": "XE", "database_type": "Oracle" } }, "dest_task": { "task_name": "dest", "node_id": "96d28881-f91b-19f4-1614-4d8a0e718e2f", "database_type": "", "mysql_dest_task_config": { "parallel_workers": 1, "use_my_sql_dependency": false, "dependency_history_size": 2500 }, "connection_config": { "host": "172.100.9.1", "port": 3306, "user": "test_dest", "password": "*", "service_name": "", "database_type": "MySQL" } }, "retry": 2 }, "eval_create_index": 12, "job_modify_index": 12, "message": "ok"}
4. 源端 Oracle 写入数据
SQL> create tablespace ACTION_DB datafile 'ACTION_DB.dbf' size 100M;SQL> create user ACTION_DB identified by ACTION_DB default tablespace ACTION_DB;SQL> grant unlimited tablespace to ACTION_DB;SQL> CREATE TABLE ACTION_DB.CHAR_255_COLUMNS(col1 INT, col2 CHAR(255));SQL> INSERT INTO ACTION_DB.CHAR_255_COLUMNS VALUES (0, NULL);SQL> INSERT INTO ACTION_DB.CHAR_255_COLUMNS VALUES (1, 'abcdefghijklmnopqrstuvwxyz');SQL> INSERT INTO ACTION_DB.CHAR_255_COLUMNS VALUES (2, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ');SQL> INSERT INTO ACTION_DB.CHAR_255_COLUMNS VALUES (3, '1234567890');SQL> INSERT INTO ACTION_DB.CHAR_255_COLUMNS VALUES (4, 1234567890);SQL> INSERT INTO ACTION_DB.CHAR_255_COLUMNS VALUES (5, '~`!@#$%^&*()-=_+{}[]|\:;<>,.?/');SQL> INSERT INTO ACTION_DB.CHAR_255_COLUMNS VALUES (6, '中文测试abc');SQL> INSERT INTO ACTION_DB.CHAR_255_COLUMNS VALUES (7, '·~!@#¥%……&*()-=——+{}【】、|;‘:“,。/《》?');
5. 指标端 MySQL 查看同步状况
mysql> SHOW CREATE TABLE ACTION_DB.CHAR_255_COLUMNS\G*************************** 1. row *************************** Table: CHAR_255_COLUMNSCreate Table: CREATE TABLE `CHAR_255_COLUMNS` ( `COL1` int(11) DEFAULT NULL, `COL2` char(255) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 row in set (0.00 sec)mysql> SELECT * FROM ACTION_DB.CHAR_255_COLUMNS;+------+-----------------------------------------------------------------------------+| COL1 | COL2 |+------+-----------------------------------------------------------------------------+| 0 | NULL || 1 | abcdefghijklmnopqrstuvwxyz || 2 | ABCDEFGHIJKLMNOPQRSTUVWXYZ || 3 | 1234567890 || 4 | 1234567890 || 5 | ~`!@#$%^&*()-=_+{}[]|\:;<>,.?/ || 6 | 中文测试abc || 7 | ·~!@#¥%……&*()-=——+{}【】、|;‘:“,。/《》? |+------+-----------------------------------------------------------------------------+8 rows in set (0.01 sec)
四、应用限度
因为 Oracle 和 MySQL 是异构数据库,所以在源端 Oracle 能执行的 Oracle SQL 语句通过 DTLE 转换到指标端的 MySQL SQL 语句后有可能无奈正确执行。比方 Oracle 反对数值范畴 MySQL 不反对,Oracle 的 DATE 类型反对公元前的年份而 MySQL 的 datetime 只能为公元后的年份等等。遇到这些状况,DTLE 的默认行为是报错并且进行同步。
如果须要跳过这种阻塞状况,能够批改 DTLE 的环境变量而后重新启动 DTLE 服务。
shell> vi /etc/systemd/system/multi-user.target.wants/dtle-nomad.service# 增加[Service]Environment="SkipErr=true"shell> systemctl daemon-reloadshell> systemctl restart dtle-nomad
如果在应用 DTLE 时发现了任何问题,请及时分割咱们。
DTLE repo: https://github.com/actiontech...
DTLE docs: https://actiontech.github.io/...
QQ探讨群:852990221