第三章 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 TABLE NATION (
N_NATIONKEY int(11) NOT NULL,
N_NAME char(25) COLLATE utf8_unicode_ci NOT NULL,
N_REGIONKEY int(11) NOT NULL,
N_COMMENT varchar(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"
                    }
                }
            }
        ]
    }
}

---

---