用户执行mysqldump需要那些权限

1、实验背景

今天看到一道题：

The MySQL user 'adam' currently has USAGE permissions to the database.
The football database is transactional and has non-stop updates from application users. The 'adam' user needs to be able to take 
consistent backups of the football database by using the --single-transaction option. Which extra GRANT permissions are required 
for adam to take mysqldump backups?
A). The 'adam' user must also have SELECT on the football database for backups to work.
B). The 'adam' user needs the PROCESS privilege to be able to take a consistent backup while other users are connected.
C). The 'adam' user must also have SINGLE TRANSACTION global grant to take a consistent backup.
D). The 'adam' user must have the SUPER privilege in order to take data backup.

下面我们就通过实验来探究一下，执行mysqldump需要那些权限；

2、实验过程

2.1 创建用户

mysql> create user adam;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for adam;
+----------------------------------+
| Grants for adam@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'adam'@'%' |
+----------------------------------+
1 row in set (0.01 sec)

2.2 修改密码

mysql> alter user 'adam'@'%' identified by 'adam';
Query OK, 0 rows affected (0.01 sec)

2.3用adam登陆执行备份任务

[root@localhost ~]# mysqldump -uadam -p --databases jl >adam.sql
Enter password: 
mysqldump: Got error: 1044: Access denied for user 'adam'@'%' to database 'jl' when selecting the database

执行失败，提示没有select权限，授权select权限：

mysql> grant select on jl.* to 'adam'@'%';
Query OK, 0 rows affected (0.01 sec)

继续执行，继续报错，提示没有LOCK  TABLES权限;

[root@localhost ~]# mysqldump -uadam -p --databases jl >adam.sql
Enter password: 
mysqldump: Got error: 1044: Access denied for user 'adam'@'%' to database 'jl' when using LOCK TABLES

授权：

mysql> grant lock tables on jl.* to 'adam'@'%';
Query OK, 0 rows affected (0.02 sec)

继续执行，执行成功；

[root@localhost ~]# mysqldump -uadam -p --databases jl >adam.sql
Enter password: 

2.3 增加mysqldump的参数

[root@localhost ~]# mysqldump -uadam -p --single-transaction --master-data=2 --databases jl >adam.sql
Enter password: 
mysqldump: Couldn't execute 'FLUSH /*!40101 LOCAL */ TABLES': Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)

报错，提示没有relad权限，继续授权

mysql> grant reload on jl.* to 'adam'@'%';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

报错，只能授权给全局：

mysql> grant reload on *.* to 'adam'@'%';
Query OK, 0 rows affected (0.02 sec)

继续执行备份，报错，提示没有REPLICATION CLIENT权限

[root@localhost ~]# mysqldump -uadam -p --single-transaction --master-data=2 --databases jl >adam.sql
Enter password: 
mysqldump: Couldn't execute 'SHOW MASTER STATUS': Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation (1227)

增加权限

mysql> grant REPLICATION CLIENT on *.* to 'adam'@'%';
Query OK, 0 rows affected (0.01 sec)

继续执行备份：

[root@localhost ~]# mysqldump -uadam -p --single-transaction --master-data=2 --databases jl >adam.sql
Enter password: 

2.4  继续增加事件备份参数

[root@localhost ~]# mysqldump -uadam -p --single-transaction --master-data=2 --events --databases jl >adam.sql
Enter password: 
mysqldump: Couldn't execute 'show events': Access denied for user 'adam'@'%' to database 'jl' (1044)

报错，重新授权：

mysql> grant  event on *.* to 'adam'@'%';
Query OK, 0 rows affected (0.01 sec)

重新执行备份，成功；

[root@localhost ~]# mysqldump -uadam -p --single-transaction --master-data=2 --events --databases jl >adam.sql
Enter password:

三、总结授权命令

mysql> GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'adam'@'%' IDENTIFIED BYY '123';
Query OK, 0 rows affected, 1 warning (0.02 sec)