问题描述
使用 obdumper 的服务端模式导出 MySQL 租户数据时，遇到报错，信息如下：
[admin@localhost:~/ob-loader-dumper/ob-loader-dumper-4.2.8.1-RELEASE/bin]$ ./obdumper -h xxx.xxx.xxx.111 -P 2881 -u test_user -t mysql -D test -p xxx --server --csv --no-sys --table='t1' --file-path '/home/admin' --log-path '/tmp/logs'
返回结果如下：
2024-04-08 13:21:18 [INFO] Parsed args:
[--csv] true
[--file-path] /home/admin
[--no-sys] true
[--server] true
[--log-path] /tmp/logs
[--host] xxx.xxx.xxx.111
[--port] 2881
[--user] test_user
[--tenant] mysql
[--password] ******
[--database] test
[--sys-user] root
[--table] [t1]
2024-04-08 13:21:18 [INFO] Trying to connect `test_user@mysql`...
2024-04-08 13:21:19 [INFO] Server Mode: OBMYSQL-3.2.4.7
2024-04-08 13:21:19 [INFO] Execute SQL: select /*+parallel(1)*/ * into outfile '/home/admin/t1.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' from `t1`
2024-04-08 13:21:19 [ERROR] Dump [t1] failed! java.sql.SQLSyntaxErrorException: (conn=3221524400) Access denied; you need (at least one of) the FILE privilege(s) for this operation
at com.oceanbase.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:110) ~[oceanbase-client-2.4.7.1.jar:?]
at com.oceanbase.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:202) ~[oceanbase-client-2.4.7.1.jar:?]
at com.oceanbase.jdbc.OceanBaseStatement.executeExceptionEpilogue(OceanBaseStatement.java:318) ~[oceanbase-client-2.4.7.1.jar:?]
at com.oceanbase.jdbc.OceanBaseStatement.executeInternal(OceanBaseStatement.java:492) ~[oceanbase-client-2.4.7.1.jar:?]
at com.oceanbase.jdbc.OceanBaseStatement.executeLargeUpdate(OceanBaseStatement.java:910) ~[oceanbase-client-2.4.7.1.jar:?]
at com.oceanbase.jdbc.OceanBaseStatement.executeLargeUpdate(OceanBaseStatement.java:892) ~[oceanbase-client-2.4.7.1.jar:?]
at com.oceanbase.tools.loaddump.client.SelectIntoOutfileClient.lambda$dumpRecords$0(SelectIntoOutfileClient.java:136) ~[ob-loader-dumper-4.2.8.1-RELEASE.jar:?]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_322]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[?:1.8.0_322]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_322]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_322]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_322]
at java.lang.Thread.run(Thread.java:750) ~[?:1.8.0_322]
Caused by: com.oceanbase.jdbc.internal.util.exceptions.OceanBaseSqlException: Access denied; you need (at least one of) the FILE privilege(s) for this operation
at com.oceanbase.jdbc.internal.util.exceptions.OceanBaseSqlException.of(OceanBaseSqlException.java:79) ~[oceanbase-client-2.4.7.1.jar:?]
at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:206) ~[oceanbase-client-2.4.7.1.jar:?]
at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:329) ~[oceanbase-client-2.4.7.1.jar:?]
at com.oceanbase.jdbc.OceanBaseStatement.executeInternal(OceanBaseStatement.java:487) ~[oceanbase-client-2.4.7.1.jar:?]
... 9 more
Caused by: java.sql.SQLException: Access denied; you need (at least one of) the FILE privilege(s) for this operation
at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:2364) ~[oceanbase-client-2.4.7.1.jar:?]
at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:2227) ~[oceanbase-client-2.4.7.1.jar:?]
at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:2115) ~[oceanbase-client-2.4.7.1.jar:?]
at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:324) ~[oceanbase-client-2.4.7.1.jar:?]
at com.oceanbase.jdbc.OceanBaseStatement.executeInternal(OceanBaseStatement.java:487) ~[oceanbase-client-2.4.7.1.jar:?]
... 9 more
2024-04-08 13:21:19 [INFO]
All Remote Dump Tasks Finished:
----------------------------------------------------------------------------------------------------------------------------
No.# | Type | Name | Count | Status
----------------------------------------------------------------------------------------------------------------------------
1 | TABLE | t1 | 0 | FAILURE
----------------------------------------------------------------------------------------------------------------------------
Total Count: 0 End Time: 2024-04-08 13:21:19
2024-04-08 13:21:19 [INFO] Remote dump record finished. Total Elapsed: 89.24 ms
2024-04-08 13:21:19 [ERROR] System exit 1
问题原因
根据错误信息可知，执行 select into outfile 命令的用户缺少一些基本的权限。参考官网文档 LOAD DATA。
导出数据到外部文件或者从外部文件导入数据时，用户需要具有 FILE 权限。可以使用 GRANT FILE ON *.* TO <user_name>; 语句进行授权，其中，<user_name> 是需要执行 SELECT INTO OUTFILE/LOAD DATA 命令的用户。
检查当前使用的导出用户的权限，可以发现只有基本的 select 权限：
MySQL [test]> show grants for test_user;
+------------------------------------+
| Grants for test_user@% |
+------------------------------------+
| GRANT SELECT ON *.* TO 'test_user' |
+------------------------------------+
1 row in set (0.00 sec)
适用版本
OceanBase 数据库 V2.x、V3.x、V4.x 版本。
影响的租户
影响 OceanBase 数据库的 MySQL 租户，对 SYS 租户、Oracle 租户无影响。
解决方法
通过数据库管理员 root 用户连接到该 MySQL 租户，为该用户赋予 FILE 权限：
MySQL [test]> grant file on . to test_user;
Query OK, 0 rows affected (0.11 sec)
