使用 gt-checksum 迁移表结构到 GreatSQL - ToB企服应用市场:ToB评测及商务社交产业平台

$ 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

复制代码

$ cat qianyi
PCMS,BMSQL_WAREHOUSE
PCMS,BMSQL_CONFIG
PCMS,BMSQL_DISTRICT_TMP

复制代码

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

复制代码

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"

复制代码

$ ./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 !!! --

复制代码

tableJoin = left
#表关联方式 left表示以源端的库表对象为准，目标端不存在的做迁移，已存在的做校验默认为join，表结构迁移时需改为left
fix = table
#生成SQL的方式 file表示将生成的建表SQL写入文件中；table表示将生成的建表SQL直接去目标端执行此处以table为例

复制代码

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> 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)

复制代码