DBCAT is a lightweight command-line tool that provides features including DDL conversion and schema comparison between a source database and an OceanBase database. The file name of the tool package is dbcat-[version number]-SNAPSHOT.tar.gz. You can use the tool after you download and decompress the package. The name of the executable file is dbcat. OceanBase Community Edition is compatible only with MySQL. Therefore, this topic describes schema conversion only for MySQL tables.
Note DBCAT is a component of OMS. OceanBase Community Edition does not provide a separate package for DBCAT.
Prepare the environment
DBCAT can run in CentOS, Mac OS X, and Windows. Java Development Kit (JDK) 1.8 or a later version is required. You can install OpenJDK and configure the environment variable JAVA_HOME after installation.
The following example takes the installation of OpenJDK in CentOS as an example.
$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
Decompress the installation package:
tar zxvf dbcat-1.3.0-SNAPSHOT.tar.gz
cd dbcat-1.3.0-SNAPSHOT/
chmod +x bin/dbcat
$tree -L 3 --filelimit 30
.
├── bin
│ ├── dbcat
│ ├── dbcat.bat
│ └── dbcat-debug
├── conf
│ ├── dbcat.properties
│ └── logback.xml
├── docs
│ ├── README.docx
│ ├── README.md
│ └── README.txt
├── LEGAL.md
├── lib [45 entries exceeds filelimit, not opening dir]
├── LICENSE
├── meta
│ └── README
└── NOTICE
5 directories, 12 files
Pay attention to the following folders in the installation file:
| Folder | Description |
|---|---|
| bin | The folder containing executable files. |
| conf | The folder containing configuration files and log files. |
| lib | The runtime dependent package. |
| meta | Dictionary table data exported in offline conversion scenarios. |
| ~/output | SQL files and report files generated in runtime. |
Online conversion
The online conversion feature allows you to directly connect DBCAT to the source database and export objects from the database. When a lot of objects, for example, more than 10,000 objects, exist, the export process may take a long time.
You can run the bin/dbcat help convert command to view the help information of the conversion command. For more information about the parameters, see the DBCAT documentation.
bin/dbcat convert -H<host> -P<port> -u<user> -p<password> -D <database> --from <from> --to <to> --all
bin/dbcat convert -H 127.1 -P 3306 -uroot -p****** -D tpccdb --from mysql56 --to obmysql2230 --all
Note:
Currently, DBCAT supports only MySQL 5.5, 5.6 and 5.7 as the source database. Therefore, the
--fromparameter supports onlymysql56andmysql57.The
--toparameter, which specifies the version of the destination OceanBase database, supports onlyobmysql2230andobmysql2250. For OceanBase Database V2.2.7 and OceanBase Database V3.1, you can useobmysql2250. These versions are supported because they support the same syntax as MySQL Database.
The files generated by the command are output to the output folder in the home directory.
$tree ~/output/dbcat-2021-09-19-164533/
/home/qing.meiq/output/dbcat-2021-09-19-164533/
├── tpccdb
│ └── TABLE-schema.sql
└── tpccdb-conversion.html
1 directory, 2 files
Example: View the table schema creation statement in the native MySQL database and that in the OceanBase database.
MariaDB [tpccdb]> show create table bmsql_customer \G
*************************** 1. row ***************************
Table: bmsql_customer
Create Table: CREATE TABLE `bmsql_customer` (
`c_w_id` bigint(20) NOT NULL,
`c_d_id` bigint(20) NOT NULL,
`c_id` bigint(20) NOT NULL,
`c_discount` decimal(4,4) DEFAULT NULL,
`c_credit` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`c_last` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
`c_first` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
`c_credit_lim` decimal(12,2) DEFAULT NULL,
`c_balance` decimal(12,2) DEFAULT NULL,
`c_ytd_payment` decimal(12,2) DEFAULT NULL,
`c_payment_cnt` bigint(20) DEFAULT NULL,
`c_delivery_cnt` bigint(20) DEFAULT NULL,
`c_street_1` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`c_street_2` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`c_city` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`c_state` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`c_zip` char(9) COLLATE utf8_unicode_ci DEFAULT NULL,
`c_phone` char(16) COLLATE utf8_unicode_ci DEFAULT NULL,
`c_since` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`c_middle` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`c_data` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`c_w_id`,`c_d_id`,`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.01 sec)
vim ~/output/dbcat-2021-09-19-164533/tpccdb/TABLE-schema.sql
create table if not exists tpccdb.bmsql_customer (
c_w_id bigint(20) not null,
c_d_id bigint(20) not null,
c_id bigint(20) not null,
c_discount decimal(4,4),
c_credit char(2),
c_last varchar(16),
c_first varchar(16),
c_credit_lim decimal(12,2),
c_balance decimal(12,2),
c_ytd_payment decimal(12,2),
c_payment_cnt bigint(20),
c_delivery_cnt bigint(20),
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
c_middle char(2),
c_data varchar(500),
primary key (c_w_id, c_d_id, c_id)
)
default charset=utf8mb4
default collate=utf8mb4_unicode_ci;