oceanabse 数据导出导入 ob-loader-dumper 使用
访问量:59
创建时间:2025-12-14
官方文档：ob-loader-dumper
导出整个库
./ob-loader-dumper-4.3.4.3-RELEASE/bin/obdumper -h 127.0.0.1 -P 2883 -u root -t sys -c demo -p kWJjaHKyINT8UVA7Bbfz --sys-user=root --sys-password kWJjaHKyINT8UVA7Bbfz -D students --ddl --sql --all -f data/ob_export
导入
下面是只导入DDL语句，报错是创建监控用户已经存在。
[root@localhost ~]# ./ob-loader-dumper-4.3.4.3-RELEASE/bin/obloader -h 127.0.0.1 -P 2883 -u root -p kWJjaHKyINT8UVA7Bbfz --sys-user root --sys-password kWJjaHKyINT8UVA7Bbfz -c demo -t sys -D students --ddl --all -f data/ob_export
2025-12-15 09:27:40 [INFO] Parsed args:
[--ddl] true
[--file-path] data/ob_export
[--host] 127.0.0.1
[--port] 2883
[--user] root
[--tenant] sys
[--cluster] demo
[--password] ******
[--database] students
[--sys-user] root
[--sys-password] ******
[--all] true
2025-12-15 09:27:41 [INFO] Log files will be written to data/ob_export/logs
2025-12-15 09:27:41 [INFO] Trying to establish JDBC connection to `root@sys#demo`...
2025-12-15 09:27:41 [INFO] Server Mode: OBMYSQL-4.3.5.5
2025-12-15 09:27:41 [INFO] Version: 4.3.4.3
2025-12-15 09:27:41 [INFO] List all matched data files in dest path...
2025-12-15 09:27:42 [INFO] Find 3 data files in "data/ob_export" success. Elapsed: 19.90 ms
2025-12-15 09:27:44 [INFO] Executing sql(1/1) of SQL-format file: "/root/data/ob_export/data/students/TABLE/yangpep-schema.sql"...
2025-12-15 09:27:44 [INFO] Executing sql(1/1) of SQL-format file: "/root/data/ob_export/data/students/TABLE/users-schema.sql"...
2025-12-15 09:27:45 [INFO] Load SQL-format file: "/root/data/ob_export/data/students/TABLE/yangpep-schema.sql" success. Elapsed: 918.0 ms
2025-12-15 09:27:45 [INFO] Load SQL-format file: "/root/data/ob_export/data/students/TABLE/users-schema.sql" success. Elapsed: 938.5 ms
2025-12-15 09:27:45 [INFO] Executing sql(1/2) of SQL-format file: "/root/data/ob_export/data/students/USER/ocp_monitor-schema.sql"...
2025-12-15 09:27:45 [ERROR] ....Loading sql of SQL-format file: "/root/data/ob_export/data/students/USER/ocp_monitor-schema.sql" exec failure. (1/2). Reason: SQLException: Operation CREATE USER failed for 'ocp_monitor'@'%'. SQL: create user `ocp_monitor` identified by '*774F2988A8DECB7B06B43362B756EA259A62D622'
2025-12-15 09:27:46 [ERROR] Error: Loading SQL-format file: "/root/data/ob_export/data/students/USER/ocp_monitor-schema.sql" failure. Reason: SQLException: Operation CREATE USER failed for 'ocp_monitor'@'%'. Please see ../logs/ob-loader-dumper.error
2025-12-15 09:27:46 [INFO] ---------- Finished Tasks: 3 Running Tasks: 0 Progress: 100.00% ----------
2025-12-15 09:27:46 [INFO]
All Load Tasks Finished:
----------------------------------------------------------------------------------------------------------------------------
No.# | Type | Name | Count | Status
----------------------------------------------------------------------------------------------------------------------------
1 | TABLE | yangpep | 1 -> 1 | SUCCESS
2 | TABLE | users | 1 -> 1 | SUCCESS
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
No.# | Type | Name | Count | Status
----------------------------------------------------------------------------------------------------------------------------
1 | USER | ocp_monitor | 2 -> 0 | FAILURE
----------------------------------------------------------------------------------------------------------------------------
Total Count: 2 End Time: 2025-12-15 09:27:46
2025-12-15 09:27:46 [INFO] Load schema finished. Total Elapsed: 4.205 s
2025-12-15 09:27:46 [ERROR] System exit 1
只导入数据文件
[root@localhost ~]# ./ob-loader-dumper-4.3.4.3-RELEASE/bin/obloader -h 127.0.0.1 -P 2883 -u root -p kWJjaHKyINT8UVA7Bbfz --sys-user root --sys-password kWJjaHKyINT8UVA7Bbfz -c demo -t sys -D students --sql --table '*' -f data/ob_export
2025-12-15 09:36:00 [INFO] Parsed args:
[--sql] true
[--file-path] data/ob_export
[--host] 127.0.0.1
[--port] 2883
[--user] root
[--tenant] sys
[--cluster] demo
[--password] ******
[--database] students
[--sys-user] root
[--sys-password] ******
[--table] [*]
2025-12-15 09:36:01 [INFO] Log files will be written to data/ob_export/logs
2025-12-15 09:36:01 [INFO] Trying to establish JDBC connection to `root@sys#demo`...
2025-12-15 09:36:02 [INFO] Server Mode: OBMYSQL-4.3.5.5
2025-12-15 09:36:02 [INFO] Version: 4.3.4.3
2025-12-15 09:36:02 [INFO] Querying table column metadata, this might take a while...
2025-12-15 09:36:02 [INFO] Listing all matched data files in dest path...
2025-12-15 09:36:02 [INFO] Cannot find a binding for "file:/root/data/ob_export/data/students/TABLE/users-schema.sql", ignore it
2025-12-15 09:36:02 [INFO] Cannot find a binding for "file:/root/data/ob_export/data/students/TABLE/yangpep-schema.sql", ignore it
2025-12-15 09:36:02 [INFO] Binding table: "yangpep" to file: "file:/root/data/ob_export/data/students/TABLE/yangpep.sql" finished
2025-12-15 09:36:02 [INFO] Binding table: "users" to file: "file:/root/data/ob_export/data/students/TABLE/users.sql" finished
2025-12-15 09:36:02 [INFO] Cannot find a binding for "file:/root/data/ob_export/data/students/USER/ocp_monitor-schema.sql", ignore it
2025-12-15 09:36:02 [INFO] Find 2 data files in: "data/ob_export" success. Elapsed: 17.38 ms
2025-12-15 09:36:02 [INFO] Splitting data files into 64 MB logical chunks...
2025-12-15 09:36:02 [INFO] Split 2 data files to 2 logical chunks success. Elapsed: 20.18 ms
2025-12-15 09:36:02 [INFO] Querying primary/unique constraints metadata for data replacing...
2025-12-15 09:36:02 [INFO] Querying primary key for table "yangpep"...
2025-12-15 09:36:02 [INFO] Querying primary key for table "users"...
2025-12-15 09:36:03 [INFO] Partition calculation flag is true
2025-12-15 09:36:03 [INFO] Query entry for table: "users" finished. Remain: [1/2]
2025-12-15 09:36:03 [INFO] Query entry for table: "yangpep" finished. Remain: [0/2]
2025-12-15 09:36:03 [INFO] Query leader location of table: "yangpep" finished. Remain: [1/2]
2025-12-15 09:36:03 [INFO] Query leader location of table: "users" finished. Remain: [0/2]
2025-12-15 09:36:03 [INFO] Waiting to calculate leader for 2 sub-files....
2025-12-15 09:36:04 [INFO] Create 4096 slots for ring buffer and leader server [127.0.0.1:2881] succeed.
2025-12-15 09:36:04 [INFO] Start 4 writers for 4 threads and leader server [127.0.0.1:2881] succeed.
2025-12-15 09:36:04 [INFO] Start 2 readers for 4 threads succeed.
2025-12-15 09:36:04 [INFO]
---------------------------------------------------------------------------------------------------------
OBServerLoadThrottle:
Server: 127.0.0.1:2881 Merge Status: false Mem Used Ratio: 20% Load Mode: FAST
---------------------------------------------------------------------------------------------------------
DummyGCThrottle:
Name: G1 Young Generation GC Count: 1 GC Cost: 00:00:00.017
Name: G1 Old Generation GC Count: 0 GC Cost: 00:00:00.000
---------------------------------------------------------------------------------------------------------
RingBufferThrottle: Blocking: false
---------------------------------------------------------------------------------------------------------
MemoryThrottle: [HEAP_MEM: 193.4 MB/4 GB], [NON_HEAP_MEM: 51.0 MB/488 MB], Block Possible: 0 %
---------------------------------------------------------------------------------------------------------
TpsThrottle: [Tps: 2147483647], Blocking: false
---------------------------------------------------------------------------------------------------------
2025-12-15 09:36:04 [INFO] Block Threads: 1
---------------------------------------------------------------------------------------------------------
ThreadSubmitTpsThrottle: [Tps: 1.0000], Blocking: false
---------------------------------------------------------------------------------------------------------
ThreadSubmitMemThrottle: [HEAP_MEM: 193.4 MB/4 GB], [NON_HEAP_MEM: 51.1 MB/488 MB], Block Possible: 0 %
---------------------------------------------------------------------------------------------------------
2025-12-15 09:36:04 [INFO] Reset batch size [0->2000] for table "yangpep".
2025-12-15 09:36:04 [INFO] File: "/root/data/ob_export/data/students/TABLE/yangpep.sql" has been read finished.
2025-12-15 09:36:06 [INFO] Reset batch size [0->1100] for table "users".
2025-12-15 09:36:06 [INFO] File: "/root/data/ob_export/data/students/TABLE/users.sql" has been read finished.
2025-12-15 09:36:08 [INFO] ---------- Finished Tasks: 2 Running Tasks: 0 Progress: 100.00% ----------
2025-12-15 09:36:08 [INFO]
All Load Tasks Finished:
----------------------------------------------------------------------------------------------------------------------------
No.# | Type | Name | Count | Status
----------------------------------------------------------------------------------------------------------------------------
1 | TABLE | yangpep | 3 -> 3 | SUCCESS
2 | TABLE | users | 2 -> 2 | SUCCESS
----------------------------------------------------------------------------------------------------------------------------
Total Count: 5 End Time: 2025-12-15 09:36:08
2025-12-15 09:36:08 [INFO] Load record finished. Total Elapsed: 5.849 s
2025-12-15 09:36:08 [INFO] System exit 0
一次性导入ddl与数据
[root@localhost ~]# ./ob-loader-dumper-4.3.4.3-RELEASE/bin/obloader -h 127.0.0.1 -P 2883 -u root -t sys -c demo -p kWJjaHKyINT8UVA7Bbfz --sys-user=root --sys-password kWJjaHKyINT8UVA7Bbfz -D students --ddl --sql --all -f data/ob_export
2025-12-16 09:42:21 [INFO] Parsed args:
[--ddl] true
[--sql] true
[--file-path] data/ob_export
[--host] 127.0.0.1
[--port] 2883
[--user] root
[--tenant] sys
[--cluster] demo
[--password] ******
[--database] students
[--sys-user] root
[--sys-password] ******
[--all] true
2025-12-16 09:42:22 [INFO] Log files will be written to data/ob_export/logs
2025-12-16 09:42:22 [INFO] Trying to establish JDBC connection to `root@sys#demo`...
2025-12-16 09:42:23 [INFO] Server Mode: OBMYSQL-4.3.5.5
2025-12-16 09:42:23 [INFO] Version: 4.3.4.3
2025-12-16 09:42:23 [INFO] List all matched data files in dest path...
2025-12-16 09:42:23 [INFO] Find 3 data files in "data/ob_export" success. Elapsed: 20.02 ms
2025-12-16 09:42:24 [INFO] Executing sql(1/1) of SQL-format file: "/root/data/ob_export/data/students/TABLE/users-schema.sql"...
2025-12-16 09:42:24 [INFO] Executing sql(1/1) of SQL-format file: "/root/data/ob_export/data/students/TABLE/yangpep-schema.sql"...
2025-12-16 09:42:25 [INFO] Load SQL-format file: "/root/data/ob_export/data/students/TABLE/users-schema.sql" success. Elapsed: 406.1 ms
2025-12-16 09:42:25 [INFO] Load SQL-format file: "/root/data/ob_export/data/students/TABLE/yangpep-schema.sql" success. Elapsed: 412.8 ms
2025-12-16 09:42:25 [INFO] Executing sql(1/2) of SQL-format file: "/root/data/ob_export/data/students/USER/ocp_monitor-schema.sql"...
2025-12-16 09:42:25 [ERROR] ....Loading sql of SQL-format file: "/root/data/ob_export/data/students/USER/ocp_monitor-schema.sql" exec failure. (1/2). Reason: SQLException: Operation CREATE USER failed for 'ocp_monitor'@'%'. SQL: create user `ocp_monitor` identified by '*774F2988A8DECB7B06B43362B756EA259A62D622'
2025-12-16 09:42:25 [ERROR] Error: Loading SQL-format file: "/root/data/ob_export/data/students/USER/ocp_monitor-schema.sql" failure. Reason: SQLException: Operation CREATE USER failed for 'ocp_monitor'@'%'. Please see ../logs/ob-loader-dumper.error
2025-12-16 09:42:25 [INFO] ---------- Finished Tasks: 3 Running Tasks: 0 Progress: 100.00% ----------
2025-12-16 09:42:25 [INFO]
All Load Tasks Finished:
----------------------------------------------------------------------------------------------------------------------------
No.# | Type | Name | Count | Status
----------------------------------------------------------------------------------------------------------------------------
1 | TABLE | yangpep | 1 -> 1 | SUCCESS
2 | TABLE | users | 1 -> 1 | SUCCESS
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
No.# | Type | Name | Count | Status
----------------------------------------------------------------------------------------------------------------------------
1 | USER | ocp_monitor | 2 -> 0 | FAILURE
----------------------------------------------------------------------------------------------------------------------------
Total Count: 2 End Time: 2025-12-16 09:42:25
2025-12-16 09:42:25 [INFO] Load schema finished. Total Elapsed: 2.785 s
2025-12-16 09:42:25 [INFO] Querying table column metadata, this might take a while...
2025-12-16 09:42:26 [INFO] Listing all matched data files in dest path...
2025-12-16 09:42:26 [INFO] Cannot find a binding for "file:/root/data/ob_export/data/students/TABLE/users-schema.sql", ignore it
2025-12-16 09:42:26 [INFO] Cannot find a binding for "file:/root/data/ob_export/data/students/TABLE/yangpep-schema.sql", ignore it
2025-12-16 09:42:26 [INFO] Binding table: "yangpep" to file: "file:/root/data/ob_export/data/students/TABLE/yangpep.sql" finished
2025-12-16 09:42:26 [INFO] Binding table: "users" to file: "file:/root/data/ob_export/data/students/TABLE/users.sql" finished
2025-12-16 09:42:26 [INFO] Cannot find a binding for "file:/root/data/ob_export/data/students/USER/ocp_monitor-schema.sql", ignore it
2025-12-16 09:42:26 [INFO] Find 2 data files in: "data/ob_export" success. Elapsed: 4.693 ms
2025-12-16 09:42:26 [INFO] Splitting data files into 64 MB logical chunks...
2025-12-16 09:42:26 [INFO] Split 2 data files to 2 logical chunks success. Elapsed: 19.01 ms
2025-12-16 09:42:26 [INFO] Querying primary/unique constraints metadata for data replacing...
2025-12-16 09:42:26 [INFO] Querying primary key for table "yangpep"...
2025-12-16 09:42:26 [INFO] Querying primary key for table "users"...
2025-12-16 09:42:26 [INFO] Partition calculation flag is true
2025-12-16 09:42:26 [INFO] Query entry for table: "yangpep" finished. Remain: [1/2]
2025-12-16 09:42:26 [INFO] Query entry for table: "users" finished. Remain: [0/2]
2025-12-16 09:42:26 [INFO] Query leader location of table: "yangpep" finished. Remain: [1/2]
2025-12-16 09:42:26 [INFO] Query leader location of table: "users" finished. Remain: [0/2]
2025-12-16 09:42:26 [INFO] Waiting to calculate leader for 2 sub-files....
2025-12-16 09:42:27 [INFO] Create 4096 slots for ring buffer and leader server [127.0.0.1:2881] succeed.
2025-12-16 09:42:27 [INFO] Start 4 writers for 4 threads and leader server [127.0.0.1:2881] succeed.
2025-12-16 09:42:27 [INFO] Start 2 readers for 4 threads succeed.
2025-12-16 09:42:27 [INFO] Reset batch size [0->2000] for table "yangpep".
2025-12-16 09:42:27 [INFO] File: "/root/data/ob_export/data/students/TABLE/yangpep.sql" has been read finished.
2025-12-16 09:42:27 [INFO]
---------------------------------------------------------------------------------------------------------
OBServerLoadThrottle:
Server: 127.0.0.1:2881 Merge Status: false Mem Used Ratio: 16% Load Mode: FAST
---------------------------------------------------------------------------------------------------------
DummyGCThrottle:
Name: G1 Young Generation GC Count: 3 GC Cost: 00:00:00.151
Name: G1 Old Generation GC Count: 0 GC Cost: 00:00:00.000
---------------------------------------------------------------------------------------------------------
RingBufferThrottle: Blocking: false
---------------------------------------------------------------------------------------------------------
MemoryThrottle: [HEAP_MEM: 31.0 MB/4 GB], [NON_HEAP_MEM: 63.6 MB/488 MB], Block Possible: 0 %
---------------------------------------------------------------------------------------------------------
TpsThrottle: [Tps: 2147483647], Blocking: false
---------------------------------------------------------------------------------------------------------
2025-12-16 09:42:27 [INFO] Block Threads: 1
---------------------------------------------------------------------------------------------------------
ThreadSubmitTpsThrottle: [Tps: 1.0000], Blocking: true
---------------------------------------------------------------------------------------------------------
ThreadSubmitMemThrottle: [HEAP_MEM: 31.0 MB/4 GB], [NON_HEAP_MEM: 63.6 MB/488 MB], Block Possible: 0 %
---------------------------------------------------------------------------------------------------------
2025-12-16 09:42:30 [INFO] Reset batch size [0->1100] for table "users".
2025-12-16 09:42:30 [INFO] File: "/root/data/ob_export/data/students/TABLE/users.sql" has been read finished.
2025-12-16 09:42:30 [INFO] ---------- Finished Tasks: 2 Running Tasks: 0 Progress: 100.00% ----------
2025-12-16 09:42:30 [INFO]
All Load Tasks Finished:
----------------------------------------------------------------------------------------------------------------------------
No.# | Type | Name | Count | Status
----------------------------------------------------------------------------------------------------------------------------
1 | TABLE | yangpep | 3 -> 3 | SUCCESS
2 | TABLE | users | 2 -> 2 | SUCCESS
----------------------------------------------------------------------------------------------------------------------------
Total Count: 5 End Time: 2025-12-16 09:42:30
2025-12-16 09:42:30 [INFO] Load record finished. Total Elapsed: 4.536 s
2025-12-16 09:42:30 [ERROR] System exit 1
| 选项列表 | 是否必选 | 说明 |
|---|---|---|
| -h host_name , --host= host_name | 是 | 用于连接 ODP 或者 OceanBase 物理节点的主机地址 |
| -P port_num , --port= port_num | 是 | 用于连接 ODP 或者 OceanBase 物理节点的主机端口。 |
| -c cluster_name , --cluster= cluster_name | 否 | 用于连接给定的OceanBase集群。命令行未指定该选项时，表示当前连接的是数据库的物理节点，相关的选项（例如：-h/-P）指定的是数据库物理节点的主机地址和端口；命令行指定该选项时，表示当前连接的是ODP服务，相关的选项（例如：-h/-P）指定的是 ODP 服务的主机地址和端口。 |
| -t tenant_name , --tenant= tenant_name | 否 | 用于连接 OceanBase 数据库中给定的租户 |
| -u user_name , --user= user_name | 是 | 用于连接目标数据库的用户名。如果指定的用户名错误，则 OBLOADER 无法连接到数据库。 |
| -p 'password' , --password='password' | 否 | 用于连接OceanBase数据库的帐户密码。命令行未指定该选项时，表示当前的数据库账户未设置密码；命令行指定该选项时，需要在参数值前后加上单引号。例如：-p'**' |
| --sys-user sys_username | 否 | 用于连接 sys 租户下拥有特定权限的用户。例如：root 用户或者 proxyro 用户。OBLOADER 需要 sys 租户下的特殊用户查询系统表中的元数据。默认值：root。OceanBase 4.0.0 及之后的版本无需指定该选项。 |
| --sys-password 'sys_password' | 否 | 用于连接 sys 租户下拥有特定权限的账户密码。与 --sys-user 选项搭配使用，默认情况下 sys 租户下 root 用户密码为空。命令行指定该选项时，需要在参数值前后加上单引号。例如：--sys-password '**'。OceanBase 4.0.0 及之后的版本无需指定该选项。 |
| -f 'file_path ' , --file-path='file_path ' | 是 | 用于指定数据文件存放在本地磁盘的绝对路径。从 Aliyun OSS 导入数据文件时亦需要指定 -f 选项，以保存生成的日志和二进制文件。 |
| --ddl | 否 | 用于标识导入 DDL 文件。DDL 文件是指数据库对象定义文件，文件的命名规范为 对象名-schema.sql 。命令行指定该选项时，表示仅导入数据库对象定义，不会导入表数据。 |
| --sql | 否 | 用于标识导入 SQL 格式的数据文件。SQL 文件是指数据按照 INSERT 语句格式存储，文件的命名规范为 表名.sql 。文件的内容是每一行表数据对应一条可执行的 INSERT 语句。SQL 文件在内容格式上明显区别于 DDL 文件。建议该选项搭配 --table 选项使用。如果搭配 --all 选项使用时，OBLOADER 只会导入表对应的数据文件，不会导入数据库对象定义。 |
| --all | 否 | 用于标识导入所有已支持的数据库对象定义和表数据。该选项与 --ddl 搭配使用时，表示导入所有的数据库对象定义文件；该选项与--csv, --sql, --cut, --pos 任意一种数据格式选项搭配使用时，表示导入所有对应格式的数据文件。如果需要导入所有的数据库对象定义和表数据，可指定 --all, --ddl 选项再搭配任意一种数据格式选项。注意--all 选项与任意的数据库对象选项之间都是互斥的，不可同时指定；如果同时指定 --all 选项与任意的数据库对象选项，则会优先执行 --all 选项命令。 |
登陆评论:
使用GITHUB登陆