GreatSQL 备份报错"PROCESS 权限不足"分析与解决
作者：GreatSQL
- 2025-08-13 福建
本文字数：6193 字
阅读完需：约 20 分钟
GreatSQL 备份报错"PROCESS 权限不足"分析与解决
报错案例
某项目在执行备份时，运行 mysqldump 时报错：
$ /kingdee/greatdb/greatdb/bin/mysqldump -h inner-test.cndrealty.com -P 3307 -u portal_admin -p'xxxx' --set-gtid-purged=OFF cndrealty_mas_console > cndrealty_mas_console-20241224.BAK.sqlmysqldump: [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复制代码
然而检查用户权限时却显示该账号拥有完整的数据库权限：
greatsql> SHOW GRANTS for 'portal_admin'@'%';Grants for portal_admin@%GRANT USAGE ON . TO portal_admin@%GRANT ALL PRIVILEGES ON 3gol.* TO portal_admin@%GRANT ALL PRIVILEGES ON kdrive.* TO portal_admin@%GRANT ALL PRIVILEGES ON nacos.* TO portal_admin@%GRANT ALL PRIVILEGES ON opensys.* TO portal_admin@%GRANT ALL PRIVILEGES ON cndreality_mas_console.* TO portal_admin@%GRANT ALL PRIVILEGES ON portal_db.* TO portal_admin@%复制代码
看似权限足够，为什么备份仍然失败？
一、深度解析 mysqldump 权限机制
mysqldump 备份数据库时，实际上涉及多种权限，不只是简单的 SELECT：
基础权限：
SELECT（读取表数据）SHOW VIEW（导出视图定义）TRIGGER（导出触发器）高级选项依赖权限：
--single-transaction模式下，一般不锁表，但在某些情况下仍可能需要LOCK TABLES权限。默认情况下（未使用--no-tablespaces**）需要**PROCESS权限，这是本次导致故障的关键权限。对于 GreatSQL 开启 GTID 的环境，还需要RELOAD或FLUSH_TABLES权限。
注意：
PROCESS是一个全局权限，必须在全局级别授予，库级权限无法替代。
二、权限测试
测试准备
-- 创建测试用户greatsql> CREATE USER 'testuser'@'%' IDENTIFIED BY 'TestPass123!';
-- 创建测试数据库和表greatsql> CREATE DATABASE testdb;greatsql> USE testdb;greatsql> CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(20));greatsql> INSERT INTO t1 VALUES (1, 'Alice'), (2, 'Bob');复制代码
场景 1：全局授权
greatsql> GRANT ALL on *.* to testuser;Query OK, 0 rows affected (0.01 sec)
greatsql> SHOW GRANTS for testuser\G*************************** 1. row ***************************Grants for testuser@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `testuser`@`%`*************************** 2. row ***************************Grants for testuser@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SKIP_QUERY_REWRITE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `testuser`@`%`2 rows in set (0.00 sec)
-- 查询全局静态权限表greatsql> SELECT * FROM mysql.user where User='testuser'\G*************************** 1. row *************************** Host: % User: testuser Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 password_expired: N password_last_changed: 2024-12-24 17:30:30 password_lifetime: NULL account_locked: N Create_role_priv: Y Drop_role_priv: Y Password_reuse_history: NULL Password_reuse_time: NULLPassword_require_current: NULL User_attributes: NULL1 row in set (0.00 sec)
-- 全局动态权限greatsql> SELECT * FROM global_grants where User='testuser';+----------+------+------------------------------+-------------------+| USER | HOST | PRIV | WITH_GRANT_OPTION |+----------+------+------------------------------+-------------------+| testuser | % | APPLICATION_PASSWORD_ADMIN | N || testuser | % | AUDIT_ABORT_EXEMPT | N || testuser | % | AUDIT_ADMIN | N || testuser | % | AUTHENTICATION_POLICY_ADMIN | N || testuser | % | BACKUP_ADMIN | N || testuser | % | BINLOG_ADMIN | N || testuser | % | BINLOG_ENCRYPTION_ADMIN | N || testuser | % | CLONE_ADMIN | N || testuser | % | CONNECTION_ADMIN | N || testuser | % | ENCRYPTION_KEY_ADMIN | N || testuser | % | FIREWALL_EXEMPT | N || testuser | % | FLUSH_OPTIMIZER_COSTS | N || testuser | % | FLUSH_STATUS | N || testuser | % | FLUSH_TABLES | N || testuser | % | FLUSH_USER_RESOURCES | N || testuser | % | GROUP_REPLICATION_ADMIN | N || testuser | % | GROUP_REPLICATION_STREAM | N || testuser | % | INNODB_REDO_LOG_ARCHIVE | N || testuser | % | INNODB_REDO_LOG_ENABLE | N || testuser | % | PASSWORDLESS_USER_ADMIN | N || testuser | % | PERSIST_RO_VARIABLES_ADMIN | N || testuser | % | REPLICATION_APPLIER | N || testuser | % | REPLICATION_SLAVE_ADMIN | N || testuser | % | RESOURCE_GROUP_ADMIN | N || testuser | % | RESOURCE_GROUP_USER | N || testuser | % | ROLE_ADMIN | N || testuser | % | SENSITIVE_VARIABLES_OBSERVER | N || testuser | % | SERVICE_CONNECTION_ADMIN | N || testuser | % | SESSION_VARIABLES_ADMIN | N || testuser | % | SET_USER_ID | N || testuser | % | SHOW_ROUTINE | N || testuser | % | SKIP_QUERY_REWRITE | N || testuser | % | SYSTEM_USER | N || testuser | % | SYSTEM_VARIABLES_ADMIN | N || testuser | % | TABLE_ENCRYPTION_ADMIN | N || testuser | % | XA_RECOVER_ADMIN | N |+----------+------+------------------------------+-------------------+36 rows in set (0.00 sec)
-- 数据库权限greatsql> SELECT * FROM db where User='testuser'\G Empty set (0.00 sec)复制代码
测试备份：
$ mysqldump -utestuser -pTestPass123! testdb > backup_full_global.sql复制代码
结果：备份成功，无报错。
场景 2：库级授权
greatsql> REVOKE ALL ON *.* FROM testuser;Query OK, 0 rows affected (0.01 sec)
greatsql> GRANT ALL ON testdb.* TO testuser;Query OK, 0 rows affected (0.01 sec)
greatsql> SHOW GRANTS FOR testuser;+------------------------------------------------------+| Grants for testuser@% |+------------------------------------------------------+| GRANT USAGE ON *.* TO `testuser`@`%` || GRANT ALL PRIVILEGES ON `testdb`.* TO `testuser`@`%` |+------------------------------------------------------+2 rows in set (0.00 sec)
-- 查询全局权限表greatsql> SELECT * FROM mysql.user where User='testuser'\G*************************** 1. row *************************** Host: % User: testuser Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 password_expired: N password_last_changed: 2024-12-24 17:30:30 password_lifetime: NULL account_locked: N Create_role_priv: N Drop_role_priv: N Password_reuse_history: NULL Password_reuse_time: NULLPassword_require_current: NULL User_attributes: NULL1 row in set (0.01 sec)
-- 全局动态权限greatsql> SELECT * FROM global_grants where User='testuser';Empty set (0.01 sec)
-- 数据库权限greatsql> SELECT * FROM db where User='testuser'\G*************************** 1. row *************************** Host: % Db: testdb User: testuser Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: YCreate_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Execute_priv: Y Event_priv: Y Trigger_priv: Y1 row in set (0.00 sec)复制代码
测试备份：
$ mysqldump -utestuser -pTestPass123! testdb > backup_db_level.sql$ mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s)' when trying to dump tablespaces复制代码
结果：备份失败，缺少全局 PROCESS 权限。
场景 3：补充 PROCESS 权限
greatsql>GRANT PROCESS ON *.* TO 'testuser'@'%';FLUSH PRIVILEGES;复制代码
再次备份：
$ mysqldump -utestuser -pTestPass123! testdb > backup_with_process.sql复制代码
结果：备份成功。
小结
三、解决方案
为备份账号追加必要的全局权限：
-- 最小权限授权（推荐）greatsql> GRANT PROCESS, RELOAD, LOCK TABLES ON *.* TO 'portal_admin'@'%';复制代码
注意：建议只赋予备份所需最小权限，避免安全风险。
四、避坑指南
权限隔离原则
备份账号绝不等同应用账号，建议专用备份用户，避免因备份账号权限过大导致安全隐患。
最小权限实践示例
greatsql> CREATE USER 'backup_user'@'%' IDENTIFIED BY 'StrongPass!';
greatsql> GRANT SELECT, SHOW VIEW, TRIGGER ON *.* TO 'backup_user'@'%';
greatsql> GRANT PROCESS, RELOAD, LOCK TABLES ON *.* TO 'backup_user'@'%';复制代码
技术备注
MySQL 引入了动态权限管理（如
BACKUP_ADMIN），可以通过global_grants表查看。但对于传统的备份场景，静态权限
PROCESS仍是必不可少的核心权限。缺少全局
PROCESS权限，即便拥有数据库内所有权限，也会导致 mysqldump 备份失败。
划线
评论
复制
发布于: 刚刚阅读数: 3
版权声明: 本文为 InfoQ 作者【GreatSQL】的原创文章。
原文链接:【http://xie.infoq.cn/article/f08b30a196a997698b93c7971】。文章转载请联系作者。
GreatSQL
关注
GreatSQL社区 2023-01-31 加入
GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。 社区：https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL
评论