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

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.
​