数据库相关信息
OceanBase 版本：社区版4.2.1.10
OBProxy 版本：4.3.3.0
OB导数工具版本：4.3.1
租户信息：MySQL租户
租户资源：4C10G
旁路导入方式
LOAD DATA 旁路导入
设置系统变量 secure_file_priv
通过本地 Socket 连接数据库设置 secure_file_priv 路径
[root@server061 ~]# mysql -S /home/admin/oceanbase/run/sql.sock -uroot@obmysql -paaAA11__
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 3221694018
Server version: 5.7.25 OceanBase 4.2.1.10 (r110020012024120420-760d3c19482ba306e5a10e214a8ec64422bbf1f0) (Built Dec 4 2024 20:33:18)Copyright (c) 2000, 2024, 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>
mysql>
mysql> SET GLOBAL secure_file_priv = "/home/load";
Query OK, 0 rows affected (0.15 sec)mysql>
mysql>
配置 secure_file_priv 路径 权限
[root@server061 ~]# cd /home/
[root@server061 home]# ll
total 190000
drwxr-xr-x. 7 admin admin 156 Jan 6 11:18 admin
drwxr-xr-x. 5 root root 42 Dec 26 10:12 data
drwx------ 7 gbase gbase 4096 Jan 22 14:23 gbase
drwxr-xr-x 7 10143 10143 245 Dec 11 2019 jdk1.8.0_241
-rw-r--r-- 1 root root 194545143 Jan 23 00:45 jdk-8u241-linux-x64.tar.gz
drwxr-xr-x 2 root root 6 Jan 25 23:42 load
drwxr-xr-x 3 root root 86 Jan 23 00:44 loader
drwxr-xr-x 2 root root 4096 Jan 8 16:36 mysql
drwxr-xr-x 2 root root 4096 Jan 8 17:15 mysql8
[root@server061 home]#
[root@server061 home]# chown -R admin.admin load
[root@server061 home]#
[root@server061 home]#
导出csv格式文件
重新登录数据库，将 test_order 表数据导出为csv文件
[root@server061 home]# mysql -S /home/admin/oceanbase/run/sql.sock -uroot@obmysql -paaAA11__
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 3221694648
Server version: 5.7.25 OceanBase 4.2.1.10 (r110020012024120420-760d3c19482ba306e5a10e214a8ec64422bbf1f0) (Built Dec 4 2024 20:33:18)Copyright (c) 2000, 2024, 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> use tpccdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql>
mysql>
mysql> SELECT * FROM test_order INTO OUTFILE "/home/load/test.csv" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK, 15001931 rows affected (41.32 sec)mysql>
LOAD DATA 命令导入
旁路导入相关参数如下，LOAD DATA 详细语法请参见官网说明
LOAD DATA-V4.2.1-OceanBase 数据库文档-分布式数据库使用文档
重新登录数据库，创建测试表test_load, 使用LOAD DATA 旁路导入命令 将/home/load/test.csv文件数据导入至该表
[root@server061 home]# mysql -S /home/admin/oceanbase/run/sql.sock -uroot@obmysql -paaAA11__
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 3221695612
Server version: 5.7.25 OceanBase 4.2.1.10 (r110020012024120420-760d3c19482ba306e5a10e214a8ec64422bbf1f0) (Built Dec 4 2024 20:33:18)Copyright (c) 2000, 2024, 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>
mysql>
mysql> use tpccdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql>
mysql>
mysql> create table test_load like test_order;
Query OK, 0 rows affected (0.37 sec)mysql>
mysql>
mysql> LOAD DATA /*+ direct(true,1024) parallel(16) */INFILE '/home/load/test.csv' INTO TABLE test_load FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK, 15001931 rows affected (1 min 3.00 sec)
Records: 15001931 Deleted: 0 Skipped: 0 Warnings: 0mysql>
OB导数工具 旁路导入
前提条件
导出数据
使用obdump将 test_order 数据导出
[root@server65 bin]# ./obdumper -h 10.0.0.61 -P 2883 -uroot@obmysql#obdemo61 -paaAA11__ --sys-user root --sys-password aaAA11__ -D tpccdb --csv --table test_order -f /home/load
导入数据
清空源表数据
mysql> truncate table test_order;
Query OK, 0 rows affected (0.07 sec)mysql>
使用obloader工具将之前导出的CSV文件旁路导入至源表
旁路导入具体参数说明参见官网
旁路导入-V4.3.2.1-OceanBase 导数工具文档-分布式数据库使用文档
[root@server65 bin]# ./obloader -h 10.0.0.61 -P 2883 -uroot@obmysql#obdemo61 -paaAA11__ --sys-user root --sys-password aaAA11__ -D tpccdb --csv --table test_load -f /home/load --thread 16 --rpc-port=2885 --direct --parallel=16
旁路导入成功
