DBCAT部署测试

1、DBCAT安装部署

DBCAT 是一款轻量级的命令行工具,可用于提供源数据库到 OceanBase 数据库的 DDL 转换和 Schema 比对等功能。工具文件名为 dbcat-[版本号]-SNAPSHOT.tar.gz,下载后解压缩即可使用,可执行文件名为 dbcat。
DBCAT 是 OMS 的一个组件,是数据对象采集和转换组件

文件名:dbcat-1.8.1-SNAPSHOT.tar.gz

1.1 环境准备

DBCAT 能运行在 CentOS、macOS 和 Windows 下。需要安装 JDK 1.8 以上(含)版本。可以使用 OpenJDK,安装好后配置环境变量 JAVA_HOME。

CentOS 安装 OpenJDK 示例:
$sudo yum -y install java-1.8.0-openjdk.x86_64

$which java
/usr/local/java/jdk1.8.0_261/bin/java

echo 'export JAVA_HOME=/usr/local/java/jdk1.8.0_261/' >> ~/.bash_profile
. ~/.bash_profile

1.2 解压安装文件

tar xvf dbcat-1.8.1-SNAPSHOT.tar.gz
# cd dbcat-1.8.1-SNAPSHOT
# ./dbcat --help
   ____                                  ____
  / __ \  _____  ___   ____ _   ____    / __ )  ____ _   _____  ___
 / / / / / ___/ / _ \ / __ `/  / __ \  / __  | / __ `/  / ___/ / _ \
/ /_/ / / /__  /  __// /_/ /  / / / / / /_/ / / /_/ /  (__  ) /  __/
\____/  \___/  \___/ \__,_/  /_/ /_/ /_____/  \__,_/  /____/  \___/

Usage: dbcat [-hV] [COMMAND]

The cli tool of dbcat

  -h, --help      Show this help message and exit.
  -V, --version   Print version information and exit.

Commands:
  compare  Compares database objects between different types of databases

  convert  Converts database objects between different types of databases

  help     Displays help information about the specified command

1.3 目录文件说明

bin:可执行文件目录。
conf: 日志文件配置目录。
lib:运行时期依赖的包。
meta:	离线转换场景下,导出字典表数据。
~/output	SQL 文件与报告文件,运行时生成。

2、在线转换

2.1 convert

在线转换是指 DBCAT 能直连源端数据库,将数据库中的对象导出。
当对象非常多时(如超过 1 万),导出过程可能会有点慢。

# 查看转换命令帮助
bin/dbcat help convert

# 命令
bin/dbcat convert -H<host> -P<port> -u<user> -p<password>  -D <database> --from <from> --to <to> --all
# (1) 在线转换mysql数据库db_test中的全部的对象(包括:表、view)
bin/dbcat convert -H192.168.1.150 -P3400 -uroot -p'xxx' -D db_test --from mysql56 --to obmysql2250 --all
# (2)在线转换mysql数据库db_test中的全部的表
bin/dbcat convert -H192.168.1.150 -P3400 -uroot -p'xxx' -D db_test --from mysql56 --to obmysql2250 --table '*'
# (3)在线转换mysql数据库db_test中的表:db_name,rds_instance
bin/dbcat convert -H192.168.1.150 -P3400 -uroot -p'xxx' -D db_test --from mysql56 --to obmysql2250 --table 'db_name,rds_instance'

# 参数特别说明:
 - 目前源端 MySQL 版本只支持 MySQL 5.5/5.6/5.7。--from 只支持 mysql56 和 mysql57。
 - 目标端 OceanBase 版本的参数 --to 只支持 obmysql2230 和 obmysql2250。即使是 OceanBase 2.2.7 和 OceanBase 3.1 版本,也可以写成 obmysql2250。因为在 MySQL 兼容性方面,这些版本的 MySQL 语法是一样的。

2.2 示例

运行后的输出文件在用户 home 目录的 output 下。

# cd /data/dbcat-1.8.1-SNAPSHOT
# bin/dbcat convert -H192.168.1.150 -P3400 -uroot -p'xxx' -D db_test --from mysql56 --to obmysql2250 --all
Parsed args:
[--all] true
[--host] 192.168.1.150
[--port] 3400
[--user] root
[--password] ******
[--database] db_test
[--from] mysql56
[--to] obmysql2250
.....
2022-12-30 15:53:31 INFO  Query 41 tables elapsed 208.4 ms
2022-12-30 15:53:31 INFO  Query 0 views elapsed 1.353 ms
2022-12-30 15:53:31 INFO  Query 0 triggers elapsed 7.552 ms
2022-12-30 15:53:32 INFO  Query 0 funtions elapsed 1.660 ms
2022-12-30 15:53:32 INFO  Query 0 procedures elapsed 3.053 ms
2022-12-30 15:53:32 WARN  Include types is empty. Ignore schema: db_test
2022-12-30 15:53:33 WARN  Skip to compare/convert tablespaces as TABLESPACE is unsupported
2022-12-30 15:53:33 WARN  Skip to compare/convert aliases as ALIAS is unsupported
2022-12-30 15:53:33 WARN  Skip to compare/convert synonyms as SYNONYM is unsupported
2022-12-30 15:53:33 WARN  Skip to compare/convert sequences as SEQUENCE is unsupported
2022-12-30 15:53:33 WARN  Skip to compare/convert dblinks as DBLINK is unsupported
2022-12-30 15:33:33 INFO  Init convert config finished.
2022-12-30 15:33:33 INFO  {dataSource-1} inited
2022-12-30 15:33:33 INFO  Init source druid connection pool finished.
2022-12-30 15:33:33 INFO  Load meta/mysql/mysql56.xml successed
2022-12-30 15:33:33 INFO  Query 0 dependencies elapsed 18.88 ms
2022-12-30 15:33:33 INFO  Starting to convert schema to path: "/data/output/dbcat-2022-12-30-153333/db_test"
2022-12-30 15:33:33 INFO  Successed to generate report in the path: "/data/output/dbcat-2022-12-30-153333/db_test-conversion.html"
2022-12-30 15:33:33 INFO  {dataSource-1} closing ...
2022-12-30 15:33:33 INFO  {dataSource-1} closed

# cd /data/output/dbcat-2022-12-30-153320
# ls
db_test  db_test-conversion.html
# cd db_test/
# [root@1-150 db_test]# ls
TABLE-schema.sql
# cat Table-schema.sql
# cat TABLE-schema.sql
create table if not exists `db_test`.`mysqldiff_user_source` (
	`id` int(11) unsigned not null auto_increment comment '全局唯一id',
	`source` varchar(32) not null default '' comment '来源',
	`source_env` varchar(32) not null default '' comment '比对的db环境',
	`source_unit_name` varchar(32) default 'default' comment '比对库的单元名称',
	`source_dbname` varchar(200) not null comment '比对的库的dbname',
	`target_env` varchar(32) not null default '' comment '参照db环境',
	`target_unit_name` varchar(32) default 'default' comment '参照库的单元名称',
	`target_dbname` varchar(200) not null comment '参照的dbname',
	`create_user` varchar(64) not null comment '用户标识,用于后续发送钉钉消息',
	`create_time` datetime default CURRENT_TIMESTAMP comment '创建时间',
	`modify_user` varchar(64) comment '修改的用户',
	`modify_time` datetime default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '修改时间',
	primary key (`id`)
)
auto_increment=17
default charset=utf8mb4
default collate=utf8mb4_general_ci
comment='用户的全库表结构比对信息表';

create index `idx_createuser` on `db_test`.`mysqldiff_user_source` (`create_user` ASC);

3、compare

功能:表结构比对
支持:  
  - mysql   -》OB
  - obmysql -》obmysql

3.1 help参数说明

#help参数详情
[root@1-150]# bin/dbcat help compare
Usage: dbcat compare [--no-quote] [--no-schema] [--valid-only] --config-file=<configFile>
Compares database objects between different types of databases

      --config-file=<configFile>   Interpret the config file for comparing

Global settings:
      --no-quote                   Generate DDL without quotation
      --no-schema                  Generate DDL without schema name
      --valid-only                 Interpret query the VALID status objects

Compare Schema Examples:

	e.g: ./dbcat --compare --config-file ../conf/dbcat.properties

[NOTE] Edit the config file "../conf/dbcat.properties" before running compare command.

#说明:
运⾏ ./dbcat compare --config-file ../conf/dbcat.properties 脚本之前,请正确地编辑 ../conf/dbcat.properties 配置⽂件!

3.2 配置参数设置:

##############################################################################
#                               Global Settings
##############################################################################

#
# If true, the word should be wrapped with escape character. Or(false), show the word without escape character.
# e.g:
#      true   ->   (oracle) "id"   (mysql) `id`
#      false  ->   (oracle)  id    (mysql)  id
#
global.quota=true

#
# If true, provide the best conversion. Or(false), only convert smooth.
#
global.optimum=true

#
# If true, show object name without schema name. Or (false), show object name with schema name.
# e.g:
#      true   ->  employee
#      false  ->  scott.employee
#
global.schemaless=false

#
# If true, show sql without terminator character. Or(false) the terminator character of the sql is ";"
# e.g:
#      true   ->  create table t_test(id bigint)
#      false  ->  create table t_test(id bigint);
#
global.termless=false

#
# Which types of object should be compared and converted
# Only [TABLE, VIEW] is supported now
#
global.includes=TABLE

#
# Specified the object name, and multiple names should be separated by commas
#
global.blacklist=

#
# Specified the object name, and multiple names should be separated by commas
#
# Format: OBJECT_TYPE:OBJECT_NAME,OBJECT_NAME;OBJECT_TYPE:OBJECT_NAME,OBJECT_NAME
#
global.whitelist=


##############################################################################
#                     Source Database JDBC Configuration
##############################################################################

#
# The optional value like below:
#
#    source.db.type=DB2LUW
#    source.db.type=DB2ZOS
#    source.db.type=DB2IBMI
#    source.db.type=MYSQL
#    source.db.type=ORACLE
#    source.db.type=OBMYSQL
#    source.db.type=OBORACLE
#    source.db.type=SYBASE
#
source.db.type=OBMYSQL

#
# The optional value can reference to official docs. The supported versions like below:
#
# [DB2LUW]    source.db.version=10.5.0
# [DB2IBMi]   source.db.version=6.1 | 7.1
# [MYSQL]     source.db.version=5.6
# [ORACLE]    source.db.version=11g
# [OBMYSQL]   source.db.version=1.4.x | 2.1.x
# [OBORACLE]  source.db.version=2.1.x | 2.2.x
# [SYBASE]    source.db.version=12.x | 15.x
#
source.db.version=3.2.3.1

#
source.db.schema=db_slowlog_ob

#
# The url template like below:
#
# [DB2LUW]
#    source.jdbc.url=jdbc:db2://<host>[:<port>]/<database_name>
#
# [DB2IBMI]
#    source.jdbc.url=jdbc:as400://<host>[:<port>];libraries=<database_name>;
#
# [MYSQL]
#    source.jdbc.url=jdbc:mysql://<host>:<port>/<database_name>
#
# [ORACLE]
#    source.jdbc.url=jdbc:oracle:thin:@<host>:<port>:<SID>
#    source.jdbc.url=jdbc:oracle:thin:@//<host>:<port>/ServiceName
#
# [OBMYSQL]
#    source.jdbc.url=jdbc:oceanbase://<host>:<port>/?useUnicode=true&characterEncoding=utf-8
#
# [OBORACLE]
#    source.jdbc.url=jdbc:oceanbase://<host>:<port>/?useUnicode=true&characterEncoding=utf-8
#
# [SYBASE]
#    source.jdbc.url=jdbc:sybase:Tds:<host>:<port>/<database_name>
#
source.jdbc.url=jdbc:oceanbase://xxx:3306/?useUnicode=true&characterEncoding=utf-8
source.jdbc.user=root@tenant_name#ob_cluster_name
source.jdbc.password=xxx

#
# Connect to the sys tenant
#
# source.jdbc.sysUser=root@sys
# source.jdbc.sysPassword=

##############################################################################
#                    Target Database JDBC Configuration
##############################################################################

#
# The optional value like below:
#
#    target.db.type=OBMYSQL
#    target.db.type=OBORACLE
#
target.db.type=OBMYSQL

#
# The optional value can reference to official docs. The supported versions like below:
#
# [ OBMYSQL]   target.db.version=1.4.x | 2.1.x | 2.2.x
# [OBORACLE]  target.db.version=2.2.x
#
target.db.version=3.2.3.1

#
target.db.schema=dba_test003

#
# This is optional when the global.action=CONVERT or global.action=DUMP
#
# The url template like below:
#
# [ OBMYSQL]  target.jdbc.url=jdbc:oceanbase://<host>:<port>/?useUnicode=true&characterEncoding=utf-8
#
# [OBORACLE]  target.jdbc.url=jdbc:oceanbase://<host>:<port>/?useUnicode=true&characterEncoding=utf-8
#
target.jdbc.url=jdbc:oceanbase://xxx:3306/?useUnicode=true&characterEncoding=utf-8
target.jdbc.user=root@tenant_name#ob_cluster_name
target.jdbc.password=xxx

#
# Connect to the sys tenant
#
# target.jdbc.sysUser=root@sys
# target.jdbc.sysPassword=

3.3 示例

cd /data/dbcat-1.8.1-SNAPSHOT
bin/dbcat compare --config-file conf/dbcat.properties