掌握从 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:00
oceanbase目标端导入表结构和数据
禁用外键检查约束
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)