使用 gt-checksum 迁移表结构到 GreatSQL
背景
本文以从 ORACLE 迁移到 GreatSQL 为例讲述如何使用 gt-checksum 迁移表结构。
关于gt-checksum
gt-checksum是GreatSQL社区开源的一款静态数据库校验修复工具，支持MySQL、Oracle等主流数据库。其商业版本近期新增了表结构迁移功能，如下是一个简单的表结构迁移使用案例。
本次使用的是 gt-checksum 商业版本
配置gc-task.cnf
gc-task.cnf是gt-checksum的初始配置文件，内容包括源端目标端DB连接串以及迁移对象列表等信息，位于gt-checksum程序的config-simple目录下，gt-cheksum会根据gc-task.cnf来生成表结构迁移相关配置文件。
$ cd ${gtdir}
$ cp -r config-simple config
$ cd config
$ vi gc-task.cnf
#源端URL
srcDSN = "oracle|user/password@ip:port/sid"
#目标端URL
dstDSN = "mysql|user:password@tcp(ip:port)/information_schema?charset=utf8mb4"
#待迁移对象列表
object = "qianyi"
#object中 库名表名的分隔符
limiter = ","
#任务类型 struct表示迁移表结构
active = struct 配置迁移对象列表
以迁移PCMS库下BMSQL_WAREHOUSE BMSQL_CONFIG BMSQL_DISTRICT_TMP三张普通表为例 配置方式如下：
配置格式 库名 分隔符 表
文件位置 与gt-checksum在同级目录
配置示例
$ cat qianyi
PCMS,BMSQL_WAREHOUSE
PCMS,BMSQL_CONFIG
PCMS,BMSQL_DISTRICT_TMP迁移对象列表配置方式说明
object 支持如下四种配置方式：
- 一般情况 无需映射（迁移到目标端后，库名表名不变）
PCMS,BMSQL_WAREHOUSE
PCMS,BMSQL_CONFIG
PCMS,BMSQL_DISTRICT_TMP映射关系示意图
SOURCE --> DEST
#示例1：PCMS,BMSQL_WAREHOUSE
PCMS.BMSQL_WAREHOUSE --> PCMS.BMSQL_WAREHOUSE- 库映射 （迁移到目标端后，表名不变，库名改变）
PCMS:WLKY,BMSQL_WAREHOUSE
PCMS:WLKY,BMSQL_CONFIG
PCMS:WLKY,BMSQL_DISTRICT_TMP映射关系示意图
SOURCE --> DEST
#示例1：PCMS:WLKY,BMSQL_WAREHOUSE
PCMS.BMSQL_WAREHOUSE --> WLKY.BMSQL_WAREHOUSE- 表映射 （迁移到目标端后，库名不变，表名改变）
PCMS,BMSQL_WAREHOUSE:BMSQL_WAREHOUSE_0429
PCMS,BMSQL_CONFIG:BMSQL_CONFIG_0429
PCMS,BMSQL_DISTRICT_TMP:BMSQL_DISTRICT_TMP_0429映射关系示意图
SOURCE --> DEST
#示例1：PCMS,BMSQL_WAREHOUSE:BMSQL_WAREHOUSE_0429
PCMS.BMSQL_WAREHOUSE --> PCMS.BMSQL_WAREHOUSE_0429- 库表映射 （迁移到目标端后，库名表名都改变）
PCMS:WLKY,BMSQL_WAREHOUSE:BMSQL_WAREHOUSE_0429
PCMS:WLKY,BMSQL_CONFIG:BMSQL_CONFIG_0429
PCMS:WLKY,BMSQL_DISTRICT_TMP:BMSQL_DISTRICT_TMP_0429映射关系示意图
SOURCE --> DEST
#示例1：PCMS:WLKY,BMSQL_WAREHOUSE:BMSQL_WAREHOUSE_0429
PCMS.BMSQL_WAREHOUSE --> WLKY.BMSQL_WAREHOUSE_0429Oracle端表结构
CREATE TABLE "PCMS"."BMSQL_WAREHOUSE"
( "W_ID" NUMBER(*,0) NOT NULL ENABLE,
"W_YTD" NUMBER(12,2),
"W_TAX" NUMBER(4,4),
"W_NAME" VARCHAR2(10),
"W_STREET_1" VARCHAR2(20),
"W_STREET_2" VARCHAR2(20),
"W_CITY" VARCHAR2(20),
"W_STATE" CHAR(2),
"W_ZIP" CHAR(9),
CONSTRAINT "BMSQL_WAREHOUSE_PKEY" PRIMARY KEY ("W_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
CREATE TABLE "PCMS"."BMSQL_CONFIG"
( "CFG_NAME" VARCHAR2(30),
"CFG_VALUE" VARCHAR2(50),
PRIMARY KEY ("CFG_NAME")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
CREATE TABLE "PCMS"."BMSQL_DISTRICT_TMP"
( "D_W_ID" NUMBER(*,0) NOT NULL ENABLE,
"D_ID" NUMBER(*,0) NOT NULL ENABLE,
"D_YTD" NUMBER(12,2),
"D_TAX" NUMBER(4,4),
"D_NEXT_O_ID" NUMBER(*,0),
"D_NAME" VARCHAR2(10),
"D_STREET_1" VARCHAR2(20),
"D_STREET_2" VARCHAR2(20),
"D_CITY" VARCHAR2(20),
"D_STATE" CHAR(2),
"D_ZIP" CHAR(9)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"生成gc-struct.cnf
gc-struct.cnf是表结构迁移的任务配置文件，gt-checksum根据gc-task.cnf来生成，放在gt-checksum同级目录下，生成方式如下：
$ ./gt-checksum -f config/gc-task.cnf如下输出表示配置文件生成成功
$ ./gt-checksum -f config/gc-task.cnf
-- gt-checksum init configuration files --
-- gt-checksum init log files --
-- gt-checksum check parameter legality--
godror WARNING: discrepancy between DBTIMEZONE ("+00:00"=0) and SYSTIMESTAMP ("+08:00"=800) - set connection timezone, see https://github.com/godror/godror/blob/master/doc/timezone.md
----begin read table object file and init table meta data---
----begin write data to xls ---
[gt_tableObjectOptimizer_2024-10-14T14-59-34.xlsx] 元数据校对Excel表格已生成
----begin general gt-checksum config file ---
[gc-struct.cnf] 配置文件已生成
-- gt-task Table object sorting completed !!! --编辑gc-struct.cnf
gc-struct.cnf中部分参数根据gc-task.cnf生成，无需修改，还有部分参数是默认配置，需要根据项目实际情况来修改，此处仅展示表结构迁移过程中部分必改参数，其余参数及其含义见文件内容。
tableJoin = left
#表关联方式 left表示以源端的库表对象为准，目标端不存在的做迁移，已存在的做校验 默认为join，表结构迁移时需改为left
fix = table
#生成SQL的方式 file表示将生成的建表SQL写入文件中；table表示将生成的建表SQL直接去目标端执行 此处以table为例迁移表结构
gc-struct.cnf配置完成后即可开始迁移表结构，需要注意的是要提前在目标端创建数据库。
greatsql> CREATE DATABASE pcms;执行表结构迁移
$ ./gt-checksum -f gc-struct.cnf如下输出表示表结构迁移任务完成
$ ./gt-checksum -f gc-struct.cnf
-- gt-checksum init configuration files --
-- gt-checksum init log files --
-- gt-checksum check parameter legality--
godror WARNING: discrepancy between DBTIMEZONE ("+00:00"=0) and SYSTIMESTAMP ("+08:00"=800) - set connection timezone, see https://github.com/godror/godror/blob/master/doc/timezone.md
-- gt-checksum begin check table --
~~~~~~~~~~~~~~~~~~~~~~~~~~gt-checksum start~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Overall progress: [> ] 0.00%
----------------------------Dividing line------------------------------------
##########################gt-checksum end######################################
Monitor Turned closed
~~~~~~~~~~~~~~~~~~~~~~~~~~gt-checksum start~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Overall progress: [==================================================>] 100.00%
##########################gt-checksum end######################################
Verification is over and data collection is in progress. Please wait...
Check end
** gt-checksum Overview of results **
Check time: 21.10s (Seconds)
seq schema table checkMode issue Type fix Type chkStat
3 PCMS BMSQL_DISTRICT_TMP struct no table bad
1 PCMS BMSQL_CONFIG struct no table bad
2 PCMS BMSQL_WAREHOUSE struct no table bad
----------------**********----------------
gt-checksum report: totalTime: 21.101560578 (Seconds) checkTables: 3 normalTabls: 0 abnormalTables: 3 missTables: 0 GreatSQL验证
greatsql> USE pcms
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
greatsql> SHOW tables;
+--------------------+
| Tables_in_pcms |
+--------------------+
| bmsql_config |
| bmsql_district_tmp |
| bmsql_warehouse |
+--------------------+
3 rows in set (0.00 sec)
greatsql> DESC bmsql_config;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| CFG_NAME | varchar(30) | NO | PRI | NULL | |
| CFG_VALUE | varchar(50) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
greatsql> DESC bmsql_district_tmp;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| D_W_ID | decimal(40,0) | NO | MUL | NULL | |
| D_ID | decimal(40,0) | NO | | NULL | |
| D_YTD | decimal(12,2) | YES | | NULL | |
| D_TAX | decimal(4,4) | YES | | NULL | |
| D_NEXT_O_ID | decimal(40,0) | YES | | NULL | |
| D_NAME | varchar(10) | YES | | NULL | |
| D_STREET_1 | varchar(20) | YES | | NULL | |
| D_STREET_2 | varchar(20) | YES | | NULL | |
| D_CITY | varchar(20) | YES | | NULL | |
| D_STATE | char(2) | YES | | NULL | |
| D_ZIP | char(9) | YES | | NULL | |
+-------------+---------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
greatsql> DESC bmsql_warehouse;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| W_ID | decimal(40,0) | NO | PRI | NULL | |
| W_YTD | decimal(12,2) | YES | | NULL | |
| W_TAX | decimal(4,4) | YES | | NULL | |
| W_NAME | varchar(10) | YES | | NULL | |
| W_STREET_1 | varchar(20) | YES | | NULL | |
| W_STREET_2 | varchar(20) | YES | | NULL | |
| W_CITY | varchar(20) | YES | | NULL | |
| W_STATE | char(2) | YES | | NULL | |
| W_ZIP | char(9) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
9 rows in set (0.00 sec)到此表结构成功从 Oracle 迁移到 GreatSQL。
Enjoy GreatSQL :)
关于 GreatSQL
GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。
相关链接： GreatSQL社区 Gitee GitHub Bilibili
GreatSQL社区：
社区有奖建议反馈： https://greatsql.cn/thread-54-1-1.html
社区博客有奖征稿详情： https://greatsql.cn/thread-100-1-1.html
（对文章有疑问或者有独到见解都可以去社区官网提出或分享哦~）
技术交流群：
微信&QQ群：
QQ群：533341697
微信群：添加GreatSQL社区助手（微信号：wanlidbc ）好友，待社区助手拉您进群。
812
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
