掌握从 MySQL 向 Oceanbase 迁移数据的基本方法：mysqldump、datax
工具准备mysqldump 是 MySQL 提供的用于导出 MySQL 数据库对象和数据的工具，非常方便。
DataX 是阿里云 DataWorks数据集成的开源版本，在阿里巴巴集团内被广泛使用的离线数据同步工具/平台。DataX 实现了包括 MySQL、Oracle、SqlServer、Postgre、HDFS、Hive、ADS、Hbase、TableStore(OTS)、MaxCompute(ODPS)、Hologres、DRDS 、Oceanbase 等各种异构数据源之间高效的数据同步功能。
使用DataX工具包，下载地址：
http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
datax 工具需提前安装JDK 8。
使用 mysqldump 迁移数据 mysql源端导出表结构和数据 mysql源端测试数据
mysql> use world Database changed mysql> show tables; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | +-----------------+ 3 rows in set (0.00 sec) mysql> select count(*) from country; +----------+ | count(*) | +----------+ | 239 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from countrylanguage; +----------+ | count(*) | +----------+ | 984 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from city; +----------+ | count(*) | +----------+ | 4079 | +----------+ 1 row in set (0.00 sec)导出表结构
[mysql@l7dbmerge MySQL]$ mysqldump -h 127.1 -uroot -P3306 -ppcZOceiI -d world country city countrylanguage > world_ddl_mysqltoob.sql [mysql@l7dbmerge MySQL]$ ll world_ddl_mysqltoob.sql -rw-rw-r-- 1 mysql mysql 3675 3月 25 10:37 world_ddl_mysqltoob.sql导出数据
[mysql@l7dbmerge MySQL]$ mysqldump -h 127.1 -uroot -P3306 -ppcZOceiI -t world country city countrylanguage > world_data_mysqltoob.sql [mysql@l7dbmerge MySQL]$ ll world_data_mysqltoob.sql -rw-rw-r-- 1 mysql mysql 241608 3月 25 10:37 world_data_mysqltoob.sql将导出文件传输到目标端
[mysql@l7dbmerge MySQL]$ scp world_d*_mysqltoob.sql admin@10.201.0.171:/home/admin/mysqldata/ admin@10.201.0.171's password: world_data_mysqltoob.sql 100% 236KB 23.8MB/s 00:00 world_ddl_mysqltoob.sql 100% 3675 2.8MB/s 00:00oceanbase目标端导入表结构和数据 禁用外键检查约束
MySQL [mytestdb]> set global foreign_key_checks=off; Query OK, 0 rows affected (0.336 sec) MySQL [mytestdb]> show global variables like '%foreign%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | foreign_key_checks | OFF | +--------------------+-------+ 1 row in set (0.007 sec)导入表结构
---有一些特别的语法 Oceanbase MYSQL 会不支持，但是不影响，需要替换掉其中部分。比如说变量 SQL_NOTES，--空行等。 MySQL [mytestdb]> source /home/admin/mysqldata/world_ddl_mysqltoob.sql Query OK, 0 rows affected (0.017 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.006 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.003 sec) Query OK, 0 rows affected (0.005 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.004 sec) Query OK, 0 rows affected (0.007 sec) Query OK, 0 rows affected (0.008 sec) Query OK, 0 rows affected (0.019 sec) Query OK, 0 rows affected, 1 warning (0.047 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected, 1 warning (0.693 sec) Query OK, 0 rows affected (0.118 sec) Query OK, 0 rows affected (0.016 sec) Query OK, 0 rows affected, 1 warning (0.049 sec) Query OK, 0 rows affected (0.003 sec) Query OK, 0 rows affected (0.006 sec) Query OK, 0 rows affected, 1 warning (1.024 sec) Query OK, 0 rows affected (0.111 sec) Query OK, 0 rows affected (0.009 sec) Query OK, 0 rows affected, 1 warning (0.028 sec) Query OK, 0 rows affected (0.008 sec) Query OK, 0 rows affected (0.006 sec) Query OK, 0 rows affected, 1 warning (1.068 sec) Query OK, 0 rows affected (0.106 sec) Query OK, 0 rows affected (0.003 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.007 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) 导入数据
---有一些特别的语法 Oceanbase MYSQL 不支持，但是不影响。比如说变量 SQL_NOTES，ENABLE KEYS，DISABLE KEYS语句等。 MySQL [mytestdb]> source /home/admin/mysqldata/world_ddl_mysqltoob.sql Query OK, 0 rows affected (0.017 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.006 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.003 sec) Query OK, 0 rows affected (0.005 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.004 sec) Query OK, 0 rows affected (0.007 sec) Query OK, 0 rows affected (0.008 sec) Query OK, 0 rows affected (0.019 sec) Query OK, 0 rows affected, 1 warning (0.047 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected, 1 warning (0.693 sec) Query OK, 0 rows affected (0.118 sec) Query OK, 0 rows affected (0.016 sec) Query OK, 0 rows affected, 1 warning (0.049 sec) Query OK, 0 rows affected (0.003 sec) Query OK, 0 rows affected (0.006 sec) Query OK, 0 rows affected, 1 warning (1.024 sec) Query OK, 0 rows affected (0.111 sec) Query OK, 0 rows affected (0.009 sec) Query OK, 0 rows affected, 1 warning (0.028 sec) Query OK, 0 rows affected (0.008 sec) Query OK, 0 rows affected (0.006 sec) Query OK, 0 rows affected, 1 warning (1.068 sec) Query OK, 0 rows affected (0.106 sec) Query OK, 0 rows affected (0.003 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.007 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) MySQL [mytestdb]> source /home/admin/mysqldata/world_data_mysqltoob.sql Query OK, 0 rows affected (0.014 sec) ERROR 1064 (42000) at line 2 in file: '/home/admin/mysqldata/world_data_mysqltoob.sql': You have an error in your SQL syntax; check the manual that corresponds to your Oceanbase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.003 sec) Query OK, 0 rows affected (0.006 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.003 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.004 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.003 sec) Query OK, 0 rows affected (0.003 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) ERROR 1193 (HY000) at line 16 in file: '/home/admin/mysqldata/world_data_mysqltoob.sql': Unknown system variable 'SQL_NOTES' ERROR 1064 (42000) at line 18 in file: '/home/admin/mysqldata/world_data_mysqltoob.sql': You have an error in your SQL syntax; check the manual that corresponds to your Oceanbase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.001 sec) ERROR 1064 (42000) at line 20 in file: '/home/admin/mysqldata/world_data_mysqltoob.sql': You have an error in your SQL syntax; check the manual that corresponds to your Oceanbase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.002 sec) ERROR 1064 (42000) at line 23 in file: '/home/admin/mysqldata/world_data_mysqltoob.sql': You have an error in your SQL syntax; check the manual that corresponds to your Oceanbase version for the right syntax to use near 'DISABLE KEYS */' at line 1 Query OK, 239 rows affected (0.206 sec) Records: 239 Duplicates: 0 Warnings: 0 ERROR 1064 (42000) at line 25 in file: '/home/admin/mysqldata/world_data_mysqltoob.sql': You have an error in your SQL syntax; check the manual that corresponds to your Oceanbase version for the right syntax to use near 'ENABLE KEYS */' at line 1 Query OK, 0 rows affected (0.003 sec) ERROR 1064 (42000) at line 28 in file: '/home/admin/mysqldata/world_data_mysqltoob.sql': You have an error in your SQL syntax; check the manual that corresponds to your Oceanbase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.003 sec) ERROR 1064 (42000) at line 30 in file: '/home/admin/mysqldata/world_data_mysqltoob.sql': You have an error in your SQL syntax; check the manual that corresponds to your Oceanbase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.002 sec) ERROR 1064 (42000) at line 33 in file: '/home/admin/mysqldata/world_data_mysqltoob.sql': You have an error in your SQL syntax; check the manual that corresponds to your Oceanbase version for the right syntax to use near 'DISABLE KEYS */' at line 1 Query OK, 4079 rows affected (0.837 sec) Records: 4079 Duplicates: 0 Warnings: 0 ERROR 1064 (42000) at line 35 in file: '/home/admin/mysqldata/world_data_mysqltoob.sql': You have an error in your SQL syntax; check the manual that corresponds to your Oceanbase version for the right syntax to use near 'ENABLE KEYS */' at line 1 Query OK, 0 rows affected (0.003 sec) ERROR 1064 (42000) at line 38 in file: '/home/admin/mysqldata/world_data_mysqltoob.sql': You have an error in your SQL syntax; check the manual that corresponds to your Oceanbase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.001 sec) ERROR 1064 (42000) at line 40 in file: '/home/admin/mysqldata/world_data_mysqltoob.sql': You have an error in your SQL syntax; check the manual that corresponds to your Oceanbase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.001 sec) ERROR 1064 (42000) at line 43 in file: '/home/admin/mysqldata/world_data_mysqltoob.sql': You have an error in your SQL syntax; check the manual that corresponds to your Oceanbase version for the right syntax to use near 'DISABLE KEYS */' at line 1 Query OK, 984 rows affected (0.401 sec) Records: 984 Duplicates: 0 Warnings: 0 ERROR 1064 (42000) at line 45 in file: '/home/admin/mysqldata/world_data_mysqltoob.sql': You have an error in your SQL syntax; check the manual that corresponds to your Oceanbase version for the right syntax to use near 'ENABLE KEYS */' at line 1 Query OK, 0 rows affected (0.016 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.011 sec) Query OK, 0 rows affected (0.011 sec) Query OK, 0 rows affected (0.002 sec) Query OK, 0 rows affected (0.004 sec) ERROR 1193 (HY000) at line 55 in file: '/home/admin/mysqldata/world_data_mysqltoob.sql': Unknown system variable 'sql_notes' Query OK, 0 rows affected (0.002 sec)
目标端导入的数据
MySQL [mytestdb]> show tables; +--------------------+ | Tables_in_mytestdb | +--------------------+ | city | | country | | countrylanguage | +--------------------+ 3 rows in set (0.005 sec) MySQL [mytestdb]> select count(*) from country; +----------+ | count(*) | +----------+ | 239 | +----------+ 1 row in set (0.010 sec) MySQL [mytestdb]> select count(*) from countrylanguage; +----------+ | count(*) | +----------+ | 984 | +----------+ 1 row in set (0.013 sec) MySQL [mytestdb]> select count(*) from city; +----------+ | count(*) | +----------+ | 4079 | +----------+ 1 row in set (0.061 sec)使用 DataX 迁移数据 安装 DataX
#直接解压即可： tar -xf datax.tar.gz配置文件 查看配置模板
[admin@obdeployer ~]$ cd datax/bin
[admin@obdeployer bin]$ python datax.py -r mysqlreader -w oceanbasev10writer
DataX (DATAX-OPENSOURCE-3.0), From Alibaba !
Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.
Please refer to the mysqlreader document:
https://github.com/alibaba/DataX/blob/master/mysqlreader/doc/mysqlreader.md
Please refer to the oceanbasev10writer document:
https://github.com/alibaba/DataX/blob/master/oceanbasev10writer/doc/oceanbasev10writer.md
Please save the following configuration as a json file and use
python {DATAX_HOME}/bin/datax.py {JSON_FILE_NAME}.json
to run the job.
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"column": [],
"connection": [
{
"jdbcUrl": [],
"table": []
}
],
"password": "",
"username": "",
"where": ""
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"column": [],
"connection": [
{
"jdbcUrl": "",
"table": []
}
],
"obWriteMode": "",
"password": "",
"username": ""
}
}
}
],
"setting": {
"speed": {
"channel": ""
}
}
}
}
生成配置文件
[admin@obdeployer mysqldata]$ python /home/admin/datax/bin/datax.py -r mysqlreader -w oceanbasev10writer > MySQL2Oceanbase.json [admin@obdeployer mysqldata]$ ll 总用量 8 -rw-rw-r-- 1 admin admin 1768 3月 25 15:50 MySQL2Oceanbase.json修改配置文件
[admin@obdeployer mysqldata]$ vi MySQL2Oceanbase.json
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"column": ["*"],
"connection": [
{
"jdbcUrl": [jdbc:mysql://10.201.0.101:3306/world?useUnicode=true&characterEncoding=utf8],
"table": ["country"]
}
],
"password": "pcZOceiI",
"username": "root",
"where": ""
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"column": ["*"],
"connection": [
{
"jdbcUrl": "||_dsc_ob10_dsc_||obce-3zones:my_test_obtenant||_dsc_ob10_dsc_||jdbc:mysql://10.201.0.171:2883/mytestdb?useUnicode=true&characterEncoding=utf8",
"table": ["country"]
}
],
"obWriteMode": "",
"password": "pass4usr",
"username": "mytestuser"
"writerThreadCount": 10,
"batchSize": 100,
"memstoreThreshold": "0.9"
}
}
}
],
"setting": {
"speed": {
"channel": "2"
}
}
}
}
执行迁移
mysql源端必须具有远程连接权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'pcZOceiI' WITH GRANT OPTION; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec)
需要创建目标端的表结构
[admin@obdeployer ~]$ obclient -h 10.201.0.171 -umytestuser@my_test_obtenant#obce-3zones -P2883 -ppass4usr -c -A mytestdb
Welcome to the Oceanbase. Commands end with ; or g.
Your MySQL connection id is 1048586
Server version: 5.6.25 Oceanbase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MySQL [mytestdb]> CREATE TABLE `country` (
`Code` char(3) NOT NULL DEFAULT '',
`Name` char(52) NOT NULL DEFAULT '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
`Region` char(26) NOT NULL DEFAULT '',
`SurfaceArea` decimal(10,2) NOT NULL DEFAULT '0.00',
`IndepYear` smallint(6) DEFAULT NULL,
`Population` int(11) NOT NULL DEFAULT '0',
`LifeExpectancy` decimal(3,1) DEFAULT NULL,
`GNP` decimal(10,2) DEFAULT NULL,
`GNPOld` decimal(10,2) DEFAULT NULL,
`LocalName` char(45) NOT NULL DEFAULT '',
`GovernmentForm` char(45) NOT NULL DEFAULT '',
`HeadOfState` char(60) DEFAULT NULL,
`Capital` int(11) DEFAULT NULL,
`Code2` char(2) NOT NULL DEFAULT '',
PRIMARY KEY (`Code`)
) ;
Query OK, 0 rows affected (0.14 sec)
执行迁移任务
[admin@obdeployer mysqldata]$ python /home/admin/datax/bin/datax.py MySQL2Oceanbase.json
DataX (DATAX-OPENSOURCE-3.0), From Alibaba !
Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.
2022-03-25 16:48:08.843 [main] INFO VMInfo - VMInfo# operatingSystem class => sun.management.OperatingSystemImpl
2022-03-25 16:48:08.866 [main] INFO Engine - the machine info =>
osInfo: Oracle Corporation 1.8 25.262-b10
jvmInfo: Linux amd64 3.10.0-1160.el7.x86_64
cpu num: 4
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-03-25 16:48:08.906 [main] INFO Engine -
{
"content":[
{
"reader":{
"name":"mysqlreader",
"parameter":{
"column":[
"*"
],
"connection":[
{
"jdbcUrl":[
"jdbc:mysql://10.201.0.101:3306/world?useUnicode=true&characterEncoding=utf8"
],
"table":[
"country"
]
}
],
"password":"**********",
"username":"root",
"where":""
}
},
"writer":{
"name":"oceanbasev10writer",
"parameter":{
"batchSize":100,
"column":[
"*"
],
"connection":[
{
"jdbcUrl":"||_dsc_ob10_dsc_||obce-3zones:my_test_obtenant||_dsc_ob10_dsc_||jdbc:mysql://10.201.0.171:2883/mytestdb?useUnicode=true&characterEncoding=utf8",
"table":[
"country"
]
}
],
"memstoreThreshold":"0.9",
"obWriteMode":"",
"password":"********",
"username":"mytestuser",
"writerThreadCount":10
}
}
}
],
"setting":{
"speed":{
"channel":"2"
}
}
}
2022-03-25 16:48:08.955 [main] WARN Engine - prioriy set to 0, because NumberFormatException, the value is: null
2022-03-25 16:48:08.960 [main] INFO PerfTrace - PerfTrace traceId=job_-1, isEnable=false, priority=0
2022-03-25 16:48:08.960 [main] INFO JobContainer - DataX jobContainer starts job.
2022-03-25 16:48:08.966 [main] INFO JobContainer - Set jobId = 0
2022-03-25 16:48:09.926 [job-0] INFO OriginalConfPretreatmentUtil - Available jdbcUrl:jdbc:mysql://10.201.0.101:3306/world?useUnicode=true&characterEncoding=utf8&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true.
2022-03-25 16:48:09.929 [job-0] WARN OriginalConfPretreatmentUtil - 您的配置文件中的列配置存在一定的风险. 因为您未配置读取数据库表的列，当您的表字段个数、类型有变动时，可能影响任务正确性甚至会运行出错。请检查您的配置并作出修改.
2022-03-25 16:48:09.959 [job-0] INFO DBUtil - this is ob1_0 jdbc url.
2022-03-25 16:48:09.959 [job-0] INFO DBUtil - this is ob1_0 jdbc url. user=obce-3zones:my_test_obtenant:mytestuser :url=jdbc:oceanbase://10.201.0.171:2883/mytestdb?useUnicode=true&characterEncoding=utf8
2022-03-25 16:48:10.582 [job-0] INFO DbUtils - value for query [SHOW VARIABLES LIKE 'ob_compatibility_mode'] is [MYSQL]
2022-03-25 16:48:10.595 [job-0] INFO DBUtil - this is ob1_0 jdbc url.
2022-03-25 16:48:10.596 [job-0] INFO DBUtil - this is ob1_0 jdbc url. user=obce-3zones:my_test_obtenant:mytestuser :url=jdbc:oceanbase://10.201.0.171:2883/mytestdb?useUnicode=true&characterEncoding=utf8&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true
2022-03-25 16:48:10.697 [job-0] INFO OriginalConfPretreatmentUtil - table:[country] all columns:[
Code,Name,Continent,Region,SurfaceArea,IndepYear,Population,LifeExpectancy,GNP,GNPOld,LocalName,GovernmentForm,HeadOfState,Capital,Code2
].
2022-03-25 16:48:10.698 [job-0] WARN OriginalConfPretreatmentUtil - 您的配置文件中的列配置信息存在风险. 因为您配置的写入数据库表的列为*，当您的表字段个数、类型有变动时，可能影响任务正确性甚至会运行出错。请检查您的配置并作出修改.
2022-03-25 16:48:10.701 [job-0] INFO OriginalConfPretreatmentUtil - Write data [
INSERT INTO %s (Code,Name,Continent,Region,SurfaceArea,IndepYear,Population,LifeExpectancy,GNP,GNPOld,LocalName,GovernmentForm,HeadOfState,Capital,Code2) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
], which jdbcUrl like:[||_dsc_ob10_dsc_||obce-3zones:my_test_obtenant||_dsc_ob10_dsc_||jdbc:mysql://10.201.0.171:2883/mytestdb?useUnicode=true&characterEncoding=utf8&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true]
2022-03-25 16:48:10.702 [job-0] INFO JobContainer - jobContainer starts to do prepare ...
2022-03-25 16:48:10.702 [job-0] INFO JobContainer - DataX Reader.Job [mysqlreader] do prepare work .
2022-03-25 16:48:10.703 [job-0] INFO JobContainer - DataX Writer.Job [oceanbasev10writer] do prepare work .
2022-03-25 16:48:10.704 [job-0] INFO DBUtil - this is ob1_0 jdbc url.
2022-03-25 16:48:10.705 [job-0] INFO DBUtil - this is ob1_0 jdbc url. user=obce-3zones:my_test_obtenant:mytestuser :url=jdbc:oceanbase://10.201.0.171:2883/mytestdb?useUnicode=true&characterEncoding=utf8&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true
2022-03-25 16:48:10.748 [job-0] INFO DbUtils - value for query [show variables like 'version'] is [3.1.2-Oceanbase CE]
2022-03-25 16:48:10.748 [job-0] INFO JobContainer - jobContainer starts to do split ...
2022-03-25 16:48:10.749 [job-0] INFO JobContainer - Job set Channel-Number to 2 channels.
2022-03-25 16:48:10.756 [job-0] INFO JobContainer - DataX Reader.Job [mysqlreader] splits to [1] tasks.
2022-03-25 16:48:10.758 [job-0] INFO JobContainer - DataX Writer.Job [oceanbasev10writer] splits to [1] tasks.
2022-03-25 16:48:10.792 [job-0] INFO JobContainer - jobContainer starts to do schedule ...
2022-03-25 16:48:10.800 [job-0] INFO JobContainer - Scheduler starts [1] taskGroups.
2022-03-25 16:48:10.804 [job-0] INFO JobContainer - Running by standalone Mode.
2022-03-25 16:48:10.819 [taskGroup-0] INFO TaskGroupContainer - taskGroupId=[0] start [1] channels for [1] tasks.
2022-03-25 16:48:10.826 [taskGroup-0] INFO Channel - Channel set byte_speed_limit to -1, No bps activated.
2022-03-25 16:48:10.826 [taskGroup-0] INFO Channel - Channel set record_speed_limit to -1, No tps activated.
2022-03-25 16:48:10.842 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] taskId[0] attemptCount[1] is started
2022-03-25 16:48:10.846 [0-0-0-writer] INFO OceanbaseV10Writer$Task - tableNumber:1,writerTask Class:com.alibaba.datax.plugin.writer.oceanbasev10writer.task.ConcurrentTableWriterTask
2022-03-25 16:48:10.847 [0-0-0-reader] INFO CommonRdbmsReader$Task - Begin to read record by Sql: [select * from country
] jdbcUrl:[jdbc:mysql://10.201.0.101:3306/world?useUnicode=true&characterEncoding=utf8&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true].
2022-03-25 16:48:10.849 [0-0-0-writer] INFO ConcurrentTableWriterTask - configure url is unavailable, use obclient for connections.
2022-03-25 16:48:10.897 [0-0-0-writer] INFO ConcurrentTableWriterTask - Disable partition calculation feature.
2022-03-25 16:48:10.929 [0-0-0-reader] INFO CommonRdbmsReader$Task - Finished read record by Sql: [select * from country
] jdbcUrl:[jdbc:mysql://10.201.0.101:3306/world?useUnicode=true&characterEncoding=utf8&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true].
2022-03-25 16:48:10.941 [0-0-0-writer] INFO CommonRdbmsWriter$Task - write mode:
2022-03-25 16:48:10.980 [0-0-0-writer] INFO CommonRdbmsWriter$Task - Skip columns: CODE,
2022-03-25 16:48:10.980 [0-0-0-writer] INFO ConcurrentTableWriterTask - writeRecordSql :INSERT INTO country (Code,Name,Continent,Region,SurfaceArea,IndepYear,Population,LifeExpectancy,GNP,GNPOld,LocalName,GovernmentForm,HeadOfState,Capital,Code2) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATe Name=VALUES(Name),Continent=VALUES(Continent),Region=VALUES(Region),SurfaceArea=VALUES(SurfaceArea),IndepYear=VALUES(IndepYear),Population=VALUES(Population),LifeExpectancy=VALUES(LifeExpectancy),GNP=VALUES(GNP),GNPOld=VALUES(GNPOld),LocalName=VALUES(LocalName),GovernmentForm=VALUES(GovernmentForm),HeadOfState=VALUES(HeadOfState),Capital=VALUES(Capital),Code2=VALUES(Code2)
2022-03-25 16:48:10.982 [0-0-0-writer] INFO DBUtil - this is ob1_0 jdbc url.
2022-03-25 16:48:10.983 [0-0-0-writer] INFO DBUtil - this is ob1_0 jdbc url. user=obce-3zones:my_test_obtenant:mytestuser :url=jdbc:oceanbase://10.201.0.171:2883/mytestdb?useUnicode=true&characterEncoding=utf8&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true
2022-03-25 16:48:11.006 [0-0-0-writer] ERROR ConcurrentTableWriterTask - partCalculator is null
2022-03-25 16:48:11.006 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 1 insert task.
2022-03-25 16:48:11.058 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 2 insert task.
2022-03-25 16:48:11.096 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 3 insert task.
2022-03-25 16:48:11.126 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 4 insert task.
2022-03-25 16:48:11.180 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 5 insert task.
2022-03-25 16:48:11.215 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 6 insert task.
2022-03-25 16:48:11.259 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 7 insert task.
2022-03-25 16:48:11.294 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 8 insert task.
2022-03-25 16:48:11.314 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 9 insert task.
2022-03-25 16:48:11.366 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 10 insert task.
2022-03-25 16:48:11.419 [0-0-0-writer] INFO DBUtil - this is ob1_0 jdbc url.
2022-03-25 16:48:11.420 [0-0-0-writer] INFO DBUtil - this is ob1_0 jdbc url. user=obce-3zones:my_test_obtenant:mytestuser :url=jdbc:oceanbase://10.201.0.171:2883/mytestdb?useUnicode=true&characterEncoding=utf8&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true
2022-03-25 16:48:11.503 [0-0-0-writer] INFO ColumnmetaCache - fetch columnmeta of table country success
2022-03-25 16:48:11.689 [0-0-0-writer] INFO CommonRdbmsWriter$Task - isMemstoreFull=false
2022-03-25 16:48:11.835 [0-0-0-writer] INFO ConcurrentTableWriterTask - ConcurrentTableWriter has put all task in queue, queueSize = 0, total = 3, finished = 2
2022-03-25 16:48:11.952 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] taskId[0] is successed, used[1112]ms
2022-03-25 16:48:11.953 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] completed it's tasks.
2022-03-25 16:48:20.839 [job-0] INFO StandAloneJobContainerCommunicator - Total 239 records, 27904 bytes | Speed 2.72KB/s, 23 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 0.001s | All Task WaitReaderTime 0.000s | Percentage 100.00%
2022-03-25 16:48:20.839 [job-0] INFO AbstractScheduler - Scheduler accomplished all tasks.
2022-03-25 16:48:20.840 [job-0] INFO JobContainer - DataX Writer.Job [oceanbasev10writer] do post work.
2022-03-25 16:48:20.841 [job-0] INFO JobContainer - DataX Reader.Job [mysqlreader] do post work.
2022-03-25 16:48:20.842 [job-0] INFO JobContainer - DataX jobId [0] completed successfully.
2022-03-25 16:48:20.843 [job-0] INFO HookInvoker - No hook invoked, because base dir not exists or is a file: /home/admin/datax/hook
2022-03-25 16:48:20.845 [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-03-25 16:48:20.845 [job-0] INFO JobContainer - PerfTrace not enable!
2022-03-25 16:48:20.846 [job-0] INFO StandAloneJobContainerCommunicator - Total 239 records, 27904 bytes | Speed 2.72KB/s, 23 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 0.001s | All Task WaitReaderTime 0.000s | Percentage 100.00%
2022-03-25 16:48:20.848 [job-0] INFO JobContainer -
任务启动时刻 : 2022-03-25 16:48:08
任务结束时刻 : 2022-03-25 16:48:20
任务总计耗时 : 11s
任务平均流量 : 2.72KB/s
记录写入速度 : 23rec/s
读出记录总数 : 239
读写失败总数 : 0
数据校验
[admin@obdeployer ~]$ obclient -h 10.201.0.171 -umytestuser@my_test_obtenant#obce-3zones -P2883 -ppass4usr -c -A mytestdb Welcome to the Oceanbase. Commands end with ; or g. Your MySQL connection id is 1048586 Server version: 5.6.25 Oceanbase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MySQL [mytestdb]> select count(*) from country; +----------+ | count(*) | +----------+ | 239 | +----------+ 1 row in set (0.009 sec)问题记录
DataX连接mysql源端报错
2022-03-25 16:42:30.477 [job-0] ERROR RetryUtil - Exception when calling callable, 即将尝试执行第1次重试.本次重试计划等待[1000]ms,实际等待[1001]ms, 异常Msg:[DataX无法连接对应的数据库，可能原因是：1) 配置的ip/port/database/jdbc错误，无法连接。2) 配置的username/password错误，鉴权失败。请和DBA确认该数据库的连接信息是否正确。] 2022-03-25 16:42:30.485 [job-0] WARN DBUtil - test connection of [jdbc:mysql://10.201.0.101:3306/world?useUnicode=true&characterEncoding=utf8] failed, for Code:[DBUtilErrorCode-10], Description:[连接数据库失败. 请检查您的 账号、密码、数据库名称、IP、Port或者向 DBA 寻求帮助(注意网络环境).]. - 具体错误信息为：java.sql.SQLException: null, message from server: "Host '10.201.0.170' is not allowed to connect to this MySQL server".
mysql源端必须具有远程连接权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'pcZOceiI' WITH GRANT OPTION; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec)
