||
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.
合作电话:010-64087828
社区邮箱:greatsql@greatdb.com