使用 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_0429

Oracle端表结构

  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。