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