浅谈OceanBase旁路导入

数据库相关信息

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

旁路导入成功