第三章 oceanbase学习之迁移MySQL数据到oceanbase
锺离浩慨
系列文章目录
第一章 oceanbase学习之docker方式部署
第二章 oceanbase学习之手动部署
第三章 oceanbase学习之迁移MySQL数据到oceanbase
第四章 oceanbase学习之查看oceanbase执行计划
前言
在成功部署oceanbase数据库之后我们需要对测试数据进行迁移，验证oceanbase与MySQL兼容度，本次实验分别通过MySQL自带的mysqldump和第三方开源的datax进行测试。
一、软件介绍
1.迁移工具介绍
mysqldump是mysql自带的导入导出工具，可以实现对MySQL的逻辑导出为sql文件，因为产生的sql语句可以适配oceanbase因此可以直接导入到oceanbase，但要注意部分语法可能不兼容。
datax是阿里开源的第三方数据迁移工具，可以支持异构数据迁移，单无法同步表结构，需要在oceanbase中先建立表结构，通过python脚本调用job进行数据同步。
2.本机安装软件
| 软件 | 版本号 | 登陆方式 |
|---|---|---|
| MySQL | 5.7.35 | mysql -uroot -p |
| oceanbase | 3.11 | mysql -h 127.1 -utest1@obmysql#obce-single -P2883 -p -c -A test |
| datax | DATAX-OPENSOURCE-3.0 |
二.mysqldump迁移数据
1.mysql通过tpcc-mysql 灌入数据
tpcc-mysql下载地址：https://github.com/Percona-Lab/tpcc-mysql
2.上传并解压编译tpcc-mysql
[root@oceanbase software]# ls
tpcc-mysql-master.zip
[root@oceanbase software]# unzip tpcc-mysql-master.zip
Archive: tpcc-mysql-master.zip
1ec1c5eb5b11b55ecf26f81a74db86b659c4e7b9
creating: tpcc-mysql-master/
inflating: tpcc-mysql-master/Dockerfile
inflating: tpcc-mysql-master/README.md
inflating: tpcc-mysql-master/add_fkey_idx.sql
inflating: tpcc-mysql-master/count.sql
inflating: tpcc-mysql-master/create_table.sql
inflating: tpcc-mysql-master/drop_cons.sql
inflating: tpcc-mysql-master/load.sh
inflating: tpcc-mysql-master/load_multi_schema.sh
creating: tpcc-mysql-master/schema2/
inflating: tpcc-mysql-master/schema2/add_fkey_idx.sql
inflating: tpcc-mysql-master/schema2/count.sql
inflating: tpcc-mysql-master/schema2/create_table.sql
inflating: tpcc-mysql-master/schema2/drop_cons.sql
creating: tpcc-mysql-master/scripts/
creating: tpcc-mysql-master/scripts/2instances/
inflating: tpcc-mysql-master/scripts/2instances/run.sh
inflating: tpcc-mysql-master/scripts/analyze.full.sh
inflating: tpcc-mysql-master/scripts/analyze.sh
inflating: tpcc-mysql-master/scripts/analyzeR.sh
inflating: tpcc-mysql-master/scripts/analyze_checkpoint.sh
inflating: tpcc-mysql-master/scripts/analyze_checkpoint_xtradb.sh
inflating: tpcc-mysql-master/scripts/analyze_checkpoint_xtradb1.sh
inflating: tpcc-mysql-master/scripts/analyze_dirty.sh
inflating: tpcc-mysql-master/scripts/analyze_evicted.sh
inflating: tpcc-mysql-master/scripts/analyze_flushed.sh
inflating: tpcc-mysql-master/scripts/analyze_flushed_innodb.sh
inflating: tpcc-mysql-master/scripts/analyze_min.sh
inflating: tpcc-mysql-master/scripts/analyze_modified.sh
inflating: tpcc-mysql-master/scripts/flashcache_stat.sh
inflating: tpcc-mysql-master/scripts/innodb_stat.sh
inflating: tpcc-mysql-master/scripts/memlock
inflating: tpcc-mysql-master/scripts/memlock.c
creating: tpcc-mysql-master/scripts/multi-instances/
inflating: tpcc-mysql-master/scripts/multi-instances/runX.sh
inflating: tpcc-mysql-master/scripts/parse_trx.py
creating: tpcc-mysql-master/scripts/remote/
inflating: tpcc-mysql-master/scripts/remote/runX.sh
inflating: tpcc-mysql-master/scripts/run.sh
inflating: tpcc-mysql-master/scripts/runX.sh
inflating: tpcc-mysql-master/scripts/run_no_backup.sh
inflating: tpcc-mysql-master/scripts/virident_stat.sh
creating: tpcc-mysql-master/src/
inflating: tpcc-mysql-master/src/Makefile
inflating: tpcc-mysql-master/src/delivery.c
inflating: tpcc-mysql-master/src/driver.c
inflating: tpcc-mysql-master/src/load.c
inflating: tpcc-mysql-master/src/main.c
inflating: tpcc-mysql-master/src/neword.c
inflating: tpcc-mysql-master/src/ordstat.c
inflating: tpcc-mysql-master/src/parse_port.h
inflating: tpcc-mysql-master/src/payment.c
inflating: tpcc-mysql-master/src/rthist.c
inflating: tpcc-mysql-master/src/rthist.h
inflating: tpcc-mysql-master/src/sb_percentile.c
inflating: tpcc-mysql-master/src/sb_percentile.h
inflating: tpcc-mysql-master/src/sequence.c
inflating: tpcc-mysql-master/src/sequence.h
inflating: tpcc-mysql-master/src/slev.c
inflating: tpcc-mysql-master/src/spt_proc.c
inflating: tpcc-mysql-master/src/spt_proc.h
inflating: tpcc-mysql-master/src/support.c
inflating: tpcc-mysql-master/src/tpc.h
inflating: tpcc-mysql-master/src/trans_if.h
[root@oceanbase software]# ls
tpcc-mysql-master tpcc-mysql-master.zip
[root@oceanbase software]# cd tpcc-mysql-master
[root@oceanbase tpcc-mysql-master]# ls
add_fkey_idx.sql count.sql create_table.sql Dockerfile drop_cons.sql load_multi_schema.sh load.sh README.md schema2 scripts src
#编译生成tpcc执行文件
[root@oceanbase tpcc-mysql-master]# cd src/
[root@oceanbase src]# make
cc -w -O3 -g -I. `mysql_config --include` -c load.c
/bin/sh: cc: command not found
make: *** [load.o] Error 127
#解决报错cc command not fount
[root@oceanbase src]# yum -y install gcc gcc-c++ libstdc++-devel
#重新编译
[root@oceanbase src]# make
cc -w -O3 -g -I. `mysql_config --include` -c load.c
cc -w -O3 -g -I. `mysql_config --include` -c support.c
cc load.o support.o `mysql_config --libs_r` -lrt -o ../tpcc_load
cc -w -O3 -g -I. `mysql_config --include` -c main.c
cc -w -O3 -g -I. `mysql_config --include` -c spt_proc.c
cc -w -O3 -g -I. `mysql_config --include` -c driver.c
cc -w -O3 -g -I. `mysql_config --include` -c sequence.c
cc -w -O3 -g -I. `mysql_config --include` -c rthist.c
cc -w -O3 -g -I. `mysql_config --include` -c sb_percentile.c
cc -w -O3 -g -I. `mysql_config --include` -c neword.c
cc -w -O3 -g -I. `mysql_config --include` -c payment.c
cc -w -O3 -g -I. `mysql_config --include` -c ordstat.c
cc -w -O3 -g -I. `mysql_config --include` -c delivery.c
cc -w -O3 -g -I. `mysql_config --include` -c slev.c
cc main.o spt_proc.o driver.o support.o sequence.o rthist.o sb_percentile.o neword.o payment.o ordstat.o delivery.o slev.o `mysql_config --libs_r` -lrt -o ../tpcc_start
[root@oceanbase src]# ls
delivery.c driver.o main.c neword.c ordstat.o payment.o rthist.o sb_percentile.o sequence.o spt_proc.c support.c trans_if.h
delivery.o load.c main.o neword.o parse_port.h rthist.c sb_percentile.c sequence.c slev.c spt_proc.h support.o
driver.c load.o Makefile ordstat.c payment.c rthist.h sb_percentile.h sequence.h slev.o spt_proc.o tpc.h
[root@oceanbase src]# cd ..
[root@oceanbase tpcc-mysql-master]# ls
1.out add_fkey_idx.sql count.sql create_table.sql Dockerfile drop_cons.sql load_multi_schema.sh load.sh README.md schema2 scripts src tpcc_load tpcc_start
3 .创建tpcc数据库，并灌入数据
[root@oceanbase tpcc-mysql-master]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.35-38-log Percona Server (GPL), Release 38, Revision 3692a61
Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database tpcc;
Query OK, 1 row affected (0.01 sec)
mysql> use tpcc;
Database changed
mysql> source create_table.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_tpcc |
+----------------+
| customer |
| district |
| history |
| item |
| new_orders |
| order_line |
| orders |
| stock |
| warehouse |
+----------------+
9 rows in set (0.00 sec)
[root@oceanbase tpcc-mysql-master]# ./tpcc_load -hlocalhost -uroot -p123456 -d tpcc -w 2
*************************************
*** TPCC-mysql Data Loader ***
*************************************
option h with value 'localhost'
option u with value 'root'
option p with value '123456'
option d with value 'tpcc'
option w with value '2'
<Parameters>
[server]: localhost
[port]: 3306
[DBname]: tpcc
[user]: root
[pass]: 123456
[warehouse]: 2
2002, HY000, Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
#因为我本地socket位置改变tpcc_load无法直接连接数据库，需要通过软连接方式解决
[root@oceanbase tpcc-mysql-master]# ln -s /mysql/data/3306/run/mysql.sock /var/lib/mysql/mysql.sock
[root@oceanbase tpcc-mysql-master]# ./tpcc_load -hlocalhost -uroot -p123456 -d tpcc -w 2
*************************************
*** TPCC-mysql Data Loader ***
*************************************
option h with value 'localhost'
option u with value 'root'
option p with value '123456'
option d with value 'tpcc'
option w with value '2'
<Parameters>
[server]: localhost
[port]: 3306
[DBname]: tpcc
[user]: root
[pass]: 123456
[warehouse]: 2
TPCC Data Load Started...
Loading Item
.................................................. 5000
.................................................. 10000
.................................................. 15000
.................................................. 20000
.................................................. 25000
.................................................. 30000
.................................................. 35000
.................................................. 40000
.................................................. 45000
.................................................. 50000
. . . . .
. . . . .
. . . . .
Orders Done.
Loading Orders for D=10, W= 2
.......... 1000
.......... 2000
.......... 3000
Orders Done.
...DATA LOADING COMPLETED SUCCESSFULLY.
mysql> select table_schema,table_name,table_rows,data_length from information_schema.tables where table_schema='tpcc';
+--------------+------------+------------+-------------+
| table_schema | table_name | table_rows | data_length |
+--------------+------------+------------+-------------+
| tpcc | customer | 58139 | 38354944 |
| tpcc | district | 20 | 16384 |
| tpcc | history | 58540 | 4734976 |
| tpcc | item | 99533 | 9977856 |
| tpcc | new_orders | 18000 | 524288 |
| tpcc | order_line | 603200 | 43597824 |
| tpcc | orders | 60260 | 2637824 |
| tpcc | stock | 195390 | 71942144 |
| tpcc | warehouse | 2 | 16384 |
+--------------+------------+------------+-------------+
9 rows in set (0.00 sec)
4.mysqldump导出数据
#只导出表结构，不导出数据
mysqldump -uroot -P3306 -p123456 --set-gtid-purged=OFF -d tpcc --compact > tpcc_ddl.sql
#只导出数据不导出表结构
mysqldump -uroot -P3306 -p123456 --set-gtid-purged=OFF -t tpcc > tpcc_data.sql
[root@oceanbase backup]# mysqldump -uroot -P3306 -p123456 --set-gtid-purged=OFF -d tpcc --compact > tpcc_ddl.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@oceanbase backup]# mysqldump -uroot -P3306 -p123456 --set-gtid-purged=OFF -t tpcc > tpcc_data.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@oceanbase backup]# ls
tpcc_data.sql tpcc_ddl.sql
5.数据导入到oceanbase
这个导出来的脚本有几个特征：
视图的定义也会在里面，但是会以注释 /!/。视图我们不关注，这部分内容可以删除。
会有一些特别的语法 OceanBase MYSQL 会不支持，但是不影响，需要替换掉其中部分。比如说变量 SQL_NOTES，DEFINER 语句等。
下面这个示例就是导出的脚本里有一个 MAX_ROWS= 的设置，这个是 MySQL 特有的，OceanBase MySQL 没有这个问题，也不需要这个设置，不支持这个语法，会报错。
/*!40101 SET character_set_client = @saved_cs_client /;
/!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 */;
CREATE TABLENATION(
N_NATIONKEYint(11) NOT NULL,
N_NAMEchar(25) COLLATE utf8_unicode_ci NOT NULL,
N_REGIONKEYint(11) NOT NULL,
N_COMMENTvarchar(152) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (N_NATIONKEY)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci MAX_ROWS=4294967295;
需要把所有 MAX_ROWS= 以及后面部分注释掉。使用批量替换技术。如在 vim 中使用 :%s/MAX_ROWS=/; – MAX_ROWS=/g 。
注意：上面导出的 SQL 中表名是大写，说明源端 MySQL 里设置表名默认很可能是大小写敏感。因此目标 OceanBase MySQL 租户也要设置。
在导出的表结构语句里，可能包含外键。在导入 OceanBase MySQL 里时，如果外键依赖的表没有创建时，导入脚本会报错。因此导入之前需要将外键检查约束先禁用掉。
MySQL [oceanbase]> set global foreign_key_checks=off;
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> show global variables like ‘%foreign%’;
±-------------------±------+
| Variable_name | Value |
±-------------------±------+
| foreign_key_checks | OFF |
±-------------------±------+
1 row in set (0.00 sec)
[root@oceanbase backup]# mysql -h 127.1 -utest1@obmysql#obce-single -P2883 -p -c -A test
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05)
Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database tpcc;
Query OK, 1 row affected (0.00 sec)
mysql> use tpcc;
Database changed
mysql> source tpcc_ddl.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
. . . .
. . . .
mysql> source tpcc_data.sql
Query OK, 1762 rows affected (0.23 sec)
Records: 1762 Duplicates: 0 Warnings: 0
Query OK, 1759 rows affected (0.23 sec)
Records: 1759 Duplicates: 0 Warnings: 0
. . . . .
. . . . .
mysql> show tables;
+----------------+
| Tables_in_tpcc |
+----------------+
| customer |
| district |
| history |
| item |
| new_orders |
| order_line |
| orders |
| stock |
| warehouse |
+----------------+
9 rows in set (0.00 sec)
三.datax迁移数据
datax下载地址：http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
1.datax环境初始化
#上传并解压datax
[root@oceanbase datax]# tar -xvf datax.tar.gz
[root@oceanbase software]# cd datax
[root@oceanbase datax]# ls
bin conf job lib log log_perf plugin script tmp
[root@oceanbase datax]# find plugin -name ".*" | xargs rm -f
#datax依赖java环境需要先安装java
[root@oceanbase software]# rpm -Uvh jdk-8u251-linux-x64.rpm
warning: jdk-8u251-linux-x64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:jdk1.8-2000:1.8.0_251-fcs ################################# [100%]
Unpacking JAR files...
tools.jar...
plugin.jar...
javaws.jar...
deploy.jar...
rt.jar...
jsse.jar...
charsets.jar...
localedata.jar...
2.datax运行结果
[root@oceanbase datax]# python /mysql/software/datax/bin/datax.py /mysql/software/datax/job/oceanbase.json
DataX (DATAX-OPENSOURCE-3.0), From Alibaba !
Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.
2022-04-27 23:30:46.146 [main] INFO VMInfo - VMInfo# operatingSystem class => sun.management.OperatingSystemImpl
2022-04-27 23:30:46.150 [main] INFO Engine - the machine info =>
osInfo: Oracle Corporation 1.8 25.251-b08
jvmInfo: Linux amd64 3.10.0-957.el7.x86_64
cpu num: 16
totalPhysicalMemory: -0.00G
freePhysicalMemory: -0.00G
maxFileDescriptorCount: -1
currentOpenFileDescriptorCount: -1
GC Names [PS MarkSweep, PS Scavenge]
MEMORY_NAME | allocation_size | init_size
PS Eden Space | 256.00MB | 256.00MB
Code Cache | 240.00MB | 2.44MB
Compressed Class Space | 1,024.00MB | 0.00MB
PS Survivor Space | 42.50MB | 42.50MB
PS Old Gen | 683.00MB | 683.00MB
Metaspace | -0.00MB | 0.00MB
2022-04-27 23:30:46.161 [main] INFO Engine -
{
"content":[
{
"reader":{
"name":"mysqlreader",
"parameter":{
"column":[
"*"
],
"connection":[
{
"jdbcUrl":[
"jdbc:mysql://10.40.204.170:3306/tpcc?useUnicode=true&characterEncoding=utf8&useSSL=false"
],
"table":[
"district"
]
}
],
"password":"******",
"username":"root"
}
},
"writer":{
"name":"oceanbasev10writer",
"parameter":{
"batchSize":1000,
"column":[
"*"
],
"connection":[
{
"jdbcUrl":"||_dsc_ob10_dsc_||obce-single:obmysql||_dsc_ob10_dsc_||jdbc:oceanbase://10.40.204.170:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true",
"table":[
"district"
]
}
],
"memstoreThreshold":"0.9",
"obWriteMode":"insert",
"password":"*****",
"preSql":[
"truncate table district"
],
"username":"test1",
"writerThreadCount":10
}
}
}
],
"setting":{
"errorLimit":{
"percentage":0.1,
"record":0
},
"speed":{
"channel":4
}
}
}
2022-04-27 23:30:46.171 [main] WARN Engine - prioriy set to 0, because NumberFormatException, the value is: null
2022-04-27 23:30:46.172 [main] INFO PerfTrace - PerfTrace traceId=job_-1, isEnable=false, priority=0
2022-04-27 23:30:46.172 [main] INFO JobContainer - DataX jobContainer starts job.
2022-04-27 23:30:46.174 [main] INFO JobContainer - Set jobId = 0
2022-04-27 23:30:46.365 [job-0] INFO OriginalConfPretreatmentUtil - Available jdbcUrl:jdbc:mysql://10.40.204.170:3306/tpcc?useUnicode=true&characterEncoding=utf8&useSSL=false&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true.
2022-04-27 23:30:46.366 [job-0] WARN OriginalConfPretreatmentUtil - 您的配置文件中的列配置存在一定的风险. 因为您未配置读取数据库表的列，当您的表字段个数、类型有变动时，可能影响任务正确性甚至会运行出错。请检查您的配置并作出修改.
2022-04-27 23:30:46.373 [job-0] INFO DBUtil - this is ob1_0 jdbc url.
2022-04-27 23:30:46.373 [job-0] INFO DBUtil - this is ob1_0 jdbc url. user=obce-single:obmysql:test1 :url=jdbc:oceanbase://10.40.204.170:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true
2022-04-27 23:30:46.510 [job-0] INFO DbUtils - value for query [SHOW VARIABLES LIKE 'ob_compatibility_mode'] is [MYSQL]
2022-04-27 23:30:46.515 [job-0] INFO DBUtil - this is ob1_0 jdbc url.
2022-04-27 23:30:46.515 [job-0] INFO DBUtil - this is ob1_0 jdbc url. user=obce-single:obmysql:test1 :url=jdbc:oceanbase://10.40.204.170:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true
2022-04-27 23:30:46.522 [job-0] INFO OriginalConfPretreatmentUtil - table:[district] all columns:[
d_id,d_w_id,d_name,d_street_1,d_street_2,d_city,d_state,d_zip,d_tax,d_ytd,d_next_o_id
].
2022-04-27 23:30:46.523 [job-0] WARN OriginalConfPretreatmentUtil - 您的配置文件中的列配置信息存在风险. 因为您配置的写入数据库表的列为*，当您的表字段个数、类型有变动时，可能影响任务正确性甚至会运行出错。请检查您的配置并作出修改.
2022-04-27 23:30:46.524 [job-0] INFO OriginalConfPretreatmentUtil - Write data [
INSERT INTO %s (d_id,d_w_id,d_name,d_street_1,d_street_2,d_city,d_state,d_zip,d_tax,d_ytd,d_next_o_id) VALUES(?,?,?,?,?,?,?,?,?,?,?)
], which jdbcUrl like:[||_dsc_ob10_dsc_||obce-single:obmysql||_dsc_ob10_dsc_||jdbc:oceanbase://10.40.204.170:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true]
2022-04-27 23:30:46.524 [job-0] INFO JobContainer - jobContainer starts to do prepare ...
2022-04-27 23:30:46.524 [job-0] INFO JobContainer - DataX Reader.Job [mysqlreader] do prepare work .
2022-04-27 23:30:46.524 [job-0] INFO JobContainer - DataX Writer.Job [oceanbasev10writer] do prepare work .
2022-04-27 23:30:46.525 [job-0] INFO DBUtil - this is ob1_0 jdbc url.
2022-04-27 23:30:46.525 [job-0] INFO DBUtil - this is ob1_0 jdbc url. user=obce-single:obmysql:test1 :url=jdbc:oceanbase://10.40.204.170:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true
2022-04-27 23:30:46.530 [job-0] INFO CommonRdbmsWriter$Job - Begin to execute preSqls:[truncate table district]. context info:||_dsc_ob10_dsc_||obce-single:obmysql||_dsc_ob10_dsc_||jdbc:oceanbase://10.40.204.170:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true.
2022-04-27 23:30:46.564 [job-0] INFO DBUtil - this is ob1_0 jdbc url.
2022-04-27 23:30:46.565 [job-0] INFO DBUtil - this is ob1_0 jdbc url. user=obce-single:obmysql:test1 :url=jdbc:oceanbase://10.40.204.170:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true
2022-04-27 23:30:46.571 [job-0] INFO DbUtils - value for query [show variables like 'version'] is [3.1.1]
2022-04-27 23:30:46.571 [job-0] INFO JobContainer - jobContainer starts to do split ...
2022-04-27 23:30:46.571 [job-0] INFO JobContainer - Job set Channel-Number to 4 channels.
2022-04-27 23:30:46.573 [job-0] INFO JobContainer - DataX Reader.Job [mysqlreader] splits to [1] tasks.
2022-04-27 23:30:46.573 [job-0] INFO JobContainer - DataX Writer.Job [oceanbasev10writer] splits to [1] tasks.
2022-04-27 23:30:46.585 [job-0] INFO JobContainer - jobContainer starts to do schedule ...
2022-04-27 23:30:46.587 [job-0] INFO JobContainer - Scheduler starts [1] taskGroups.
2022-04-27 23:30:46.588 [job-0] INFO JobContainer - Running by standalone Mode.
2022-04-27 23:30:46.592 [taskGroup-0] INFO TaskGroupContainer - taskGroupId=[0] start [1] channels for [1] tasks.
2022-04-27 23:30:46.595 [taskGroup-0] INFO Channel - Channel set byte_speed_limit to -1, No bps activated.
2022-04-27 23:30:46.595 [taskGroup-0] INFO Channel - Channel set record_speed_limit to -1, No tps activated.
2022-04-27 23:30:46.600 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] taskId[0] attemptCount[1] is started
2022-04-27 23:30:46.602 [0-0-0-writer] INFO OceanBaseV10Writer$Task - tableNumber:1,writerTask Class:com.alibaba.datax.plugin.writer.oceanbasev10writer.task.ConcurrentTableWriterTask
2022-04-27 23:30:46.602 [0-0-0-writer] INFO ConcurrentTableWriterTask - configure url is unavailable, use obclient for connections.
2022-04-27 23:30:46.603 [0-0-0-reader] INFO CommonRdbmsReader$Task - Begin to read record by Sql: [select * from district
] jdbcUrl:[jdbc:mysql://10.40.204.170:3306/tpcc?useUnicode=true&characterEncoding=utf8&useSSL=false&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true].
2022-04-27 23:30:46.610 [0-0-0-writer] INFO ConcurrentTableWriterTask - Disable partition calculation feature.
2022-04-27 23:30:46.615 [0-0-0-writer] INFO CommonRdbmsWriter$Task - write mode: insert
2022-04-27 23:30:46.615 [0-0-0-writer] INFO ConcurrentTableWriterTask - writeRecordSql :INSERT INTO district (d_id,d_w_id,d_name,d_street_1,d_street_2,d_city,d_state,d_zip,d_tax,d_ytd,d_next_o_id) VALUES(?,?,?,?,?,?,?,?,?,?,?)
2022-04-27 23:30:46.616 [0-0-0-writer] INFO DBUtil - this is ob1_0 jdbc url.
2022-04-27 23:30:46.616 [0-0-0-writer] INFO DBUtil - this is ob1_0 jdbc url. user=obce-single:obmysql:test1 :url=jdbc:oceanbase://10.40.204.170:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true
2022-04-27 23:30:46.618 [0-0-0-reader] INFO CommonRdbmsReader$Task - Finished read record by Sql: [select * from district
] jdbcUrl:[jdbc:mysql://10.40.204.170:3306/tpcc?useUnicode=true&characterEncoding=utf8&useSSL=false&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true].
2022-04-27 23:30:46.620 [0-0-0-writer] ERROR ConcurrentTableWriterTask - partCalculator is null
2022-04-27 23:30:46.620 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 1 insert task.
2022-04-27 23:30:46.624 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 2 insert task.
2022-04-27 23:30:46.628 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 3 insert task.
2022-04-27 23:30:46.632 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 4 insert task.
2022-04-27 23:30:46.635 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 5 insert task.
2022-04-27 23:30:46.638 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 6 insert task.
2022-04-27 23:30:46.642 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 7 insert task.
2022-04-27 23:30:46.646 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 8 insert task.
2022-04-27 23:30:46.649 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 9 insert task.
2022-04-27 23:30:46.653 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 10 insert task.
2022-04-27 23:30:46.659 [0-0-0-writer] INFO DBUtil - this is ob1_0 jdbc url.
2022-04-27 23:30:46.659 [0-0-0-writer] INFO DBUtil - this is ob1_0 jdbc url. user=obce-single:obmysql:test1 :url=jdbc:oceanbase://10.40.204.170:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true
2022-04-27 23:30:46.665 [0-0-0-writer] INFO ColumnMetaCache - fetch columnMeta of table district success
2022-04-27 23:30:46.670 [0-0-0-writer] INFO CommonRdbmsWriter$Task - isMemstoreFull=false
2022-04-27 23:30:46.670 [0-0-0-writer] INFO ConcurrentTableWriterTask - ConcurrentTableWriter has put all task in queue, queueSize = 0, total = 1, finished = 0
2022-04-27 23:30:46.700 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] taskId[0] is successed, used[101]ms
2022-04-27 23:30:46.701 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] completed it's tasks.
2022-04-27 23:30:56.599 [job-0] INFO StandAloneJobContainerCommunicator - Total 20 records, 1648 bytes | Speed 164B/s, 2 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 0.000s | All Task WaitReaderTime 0.000s | Percentage 100.00%
2022-04-27 23:30:56.599 [job-0] INFO AbstractScheduler - Scheduler accomplished all tasks.
2022-04-27 23:30:56.599 [job-0] INFO JobContainer - DataX Writer.Job [oceanbasev10writer] do post work.
2022-04-27 23:30:56.600 [job-0] INFO JobContainer - DataX Reader.Job [mysqlreader] do post work.
2022-04-27 23:30:56.600 [job-0] INFO JobContainer - DataX jobId [0] completed successfully.
2022-04-27 23:30:56.600 [job-0] INFO HookInvoker - No hook invoked, because base dir not exists or is a file: /mysql/software/datax/hook
2022-04-27 23:30:56.601 [job-0] INFO JobContainer -
[total cpu info] =>
averageCpu | maxDeltaCpu | minDeltaCpu
-1.00% | -1.00% | -1.00%
[total gc info] =>
NAME | totalGCCount | maxDeltaGCCount | minDeltaGCCount | totalGCTime | maxDeltaGCTime | minDeltaGCTime
PS MarkSweep | 0 | 0 | 0 | 0.000s | 0.000s | 0.000s
PS Scavenge | 0 | 0 | 0 | 0.000s | 0.000s | 0.000s
2022-04-27 23:30:56.601 [job-0] INFO JobContainer - PerfTrace not enable!
2022-04-27 23:30:56.601 [job-0] INFO StandAloneJobContainerCommunicator - Total 20 records, 1648 bytes | Speed 164B/s, 2 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 0.000s | All Task WaitReaderTime 0.000s | Percentage 100.00%
2022-04-27 23:30:56.602 [job-0] INFO JobContainer -
任务启动时刻 : 2022-04-27 23:30:46
任务结束时刻 : 2022-04-27 23:30:56
任务总计耗时 : 10s
任务平均流量 : 164B/s
记录写入速度 : 2rec/s
读出记录总数 : 20
读写失败总数 : 0
3.json文件内容
[root@oceanbase datax]# cat job/oceanbase.json
{
"job": {
"setting": {
"speed": {
"channel": 4
},
"errorLimit": {
"record": 0,
"percentage": 0.1
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "root",
"password": "123456",
"column": [
"*"
],
"connection": [
{
"table": [
"district"
],
"jdbcUrl": ["jdbc:mysql://10.40.204.170:3306/tpcc?useUnicode=true&characterEncoding=utf8&useSSL=false"]
}
]
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"obWriteMode": "insert",
"column": [
"*"
],
"preSql": [
"truncate table district"
],
"connection": [
{
"jdbcUrl": "||_dsc_ob10_dsc_||obce-single:obmysql||_dsc_ob10_dsc_||jdbc:oceanbase://10.40.204.170:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true",
"table": [
"district"
]
}
],
"username": "test1",
"password":"test1",
"writerThreadCount":10,
"batchSize": 1000,
"memstoreThreshold": "0.9"
}
}
}
]
}
}
类似资料：