使用 gt-checksum 迁移表结构到 GreatSQL
使用 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_WAREHOUSEBMSQL_CONFIGBMSQL_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 ---
元数据校对Excel表格已生成
----begin general gt-checksum config file ---
配置文件已生成
-- 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
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]