[bug] mysqldump: Error: ‘Access denied without PROCESS privilege

2023年 11月 15日 66.0k 0

1、问题现象

MySQL 8.0.25 mysqldump 报错 Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces] (mysqldump 5.7.21无异常)

2、问题原因

mysqlpump of 8.0.21 之后增加读  INFORMATION_SCHEMA.FILES操作,这需要 PROCESS 权限 ,mysqldump使用该表来获取表空间信息,此更改被视为不兼容mysqlpump of 5.7的更改。

这是一个Bug #100229, mysqldump/mysqlpump, 8.0.21: The need for PROCESS privilege is not documented :https://bugs.mysql.com/bug.php?id=100229

3、问题影响

如果业务表未使用表空间, mysqldump导出过程中报Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) ,但不影响业务库的正常导出

4、解决方案

方案1、 导出用户额外赋权 GRANT PROCESS on . TO 导出用户@'%'; (推荐)

方案2、 在业务表未使用表空间的前提下,mysqldump 导出参数添加 --no-tablespaces (推荐)

方案3、用mysqldump 5.7 操作 MySQL 8 的库

5、问题复现

问题复现

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.25 |
+-----------+
1 row in set (0.00 sec)

mysql> create user andy@'%' identified by 'andydojdo##r2S';
Query OK, 0 rows affected (0.10 sec)

mysql> create database andy;
Query OK, 1 row affected (0.01 sec)

mysql> use andy;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table a (id int, primary key (id));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into a value(1);
Query OK, 1 row affected (0.01 sec)

mysql> grant all on andy.* to andy;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for andy@'%';
+------------------------------------------------+
| Grants for andy@% |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `andy`@`%` |
| GRANT ALL PRIVILEGES ON `andy`.* TO `andy`@`%` |
+------------------------------------------------+
2 rows in set (0.01 sec)

#错误复现
MySQL [andy]> select count(*) from INFORMATION_SCHEMA.FILES ;
ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation


[root@test_greatsql_01 ~]# /opt/mysql3303/bin/mysqldump -uandy -p"andydojdo##r2S" --host="172.17.134.50" --port=3303 --set-gtid-purged=OFF andy > mysql_8.0.25.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

#规避办法
[root@test_greatsql_01 ~]# /opt/mysql3303/bin/mysqldump -uandy -p"andydojdo##r2S" --host="172.17.134.50" --port=3303 --set-gtid-purged=OFF --no-tablespaces andy > mysql_8.0.25.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.


[root@test_greatsql_01 ~]# /opt/mysql3305/bin/mysqldump --version
mysqldump Ver 10.13 Distrib 5.7.21, for linux-glibc2.12 (x86_64)
[root@test_greatsql_01 ~]# /opt/mysql3305/bin/mysqldump -uandy -p"andydojdo##r2S" --host="172.17.134.50" --port=3303 --set-gtid-purged=OFF andy > mysql_8.0.25.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论