mysqldump备份权限

1.创建一个用于备份数据库的用户

mysql> create user 'backup'@'localhost' identified by '123';

2.添加权限

2.1 添加 SELECT 权限

根据报错，backup用户缺少select权限

[root@private_server ~]# mysqldump -ubackup -p123 -B wordpress > wordpress.sql
mysqldump: Got error: 1044: "Access denied for user 'backup'@'localhost' to database 'wordpress'" when selecting the database

给backup用户添加select权限

mysql> GRANT SELECT ON *.* TO 'backup'@'localhost';

2.2 添加 LOCK TABLES 权限

根据报错，backup用户缺少lock tables权限

[root@private_server ~]# mysqldump -ubackup -p123 -B wordpress > wordpress.sql
mysqldump: Got error: 1044: "Access denied for user 'backup'@'localhost' to database 'wordpress'" when using LOCK TABLES

给backup用户添加lock tables权限

mysql> GRANT lock tables ON *.* TO 'backup'@'localhost';

注意：如果备份时加入–single-transaction选项，则可不需要LOCK TABLES权限

2.3 添加 SHOW VIEW 权限

当数据库中存在view（视图）的时候，使用mysqldump备份数据库，需要有SHOW VIEW权限
给test2库添加一个view

mysql> CREATE VIEW view_1 AS SELECT 1 AS Number;

使用mysqldump备份，会提示缺少SHOW VIEW权限

[root@private_server ~]# mysqldump -ubackup -p123 -B test2 > test2.sql
mysqldump: Couldn't execute 'show create table `view_1`': SHOW VIEW command denied to user 'backup'@'localhost' for table 'view_1' (1142)

给用户backup添加SHOW VIEW权限

mysql> GRANT SHOW VIEW ON *.* TO 'backup'@'localhost'

2.4 添加 RELOAD 权限

加入–master-data选项后，备份需要RELOAD权限

[root@private_server ~]# mysqldump -ubackup -p123 --master-data=2 -B wordpress > wordpress.sql
mysqldump: Couldn't execute 'FLUSH /*!40101 LOCAL */ TABLES': Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)

给backup用户添加RELOAD权限

mysql> GRANT reload ON *.* TO 'backup'@'localhost';

2.5 添加 REPLICATION CLIENT 权限

根据备份的报错提示，需要REPLICATION CLIENT权限

[root@private_server ~]# mysqldump -ubackup -p123 --master-data=2 -B wordpress > wordpress.sql
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)

给backup用户添加REPLICATION CLIENT权限

mysql> GRANT REPLICATION CLIENT ON *.* TO 'backup'@'localhost';

2.6 添加 EVEN 权限

备份是若要备份事件，即使用–events选项，则需要EVENT权限

[root@private_server ~]# mysqldump -ubackup -p123 --single-transaction --master-data=2 --events -B wordpress > wordpress.sql
mysqldump: Couldn't execute 'show events': Access denied for user 'backup'@'localhost' to database 'wordpress' (1044)

给backup用户添加EVENT权限

mysql> GRANT EVENT ON *.* TO 'backup'@'localhost';

2.7 添加 TRIGGER 权限

mysql> GRANT TRIGGER ON *.* TO 'backup'@'localhost';

2.8 添加 PROCESS 权限

在mysql5.7上执行备份操作时，出现以下错误

[root@private_server ~]# mysqldump -ubackup -p123 --single-transaction --master-data=2 --events --triggers -R -B archery > /tmp/archery.sql
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

给backup用户添加 PROCESS 权限

GRANT PROCESS ON *.* TO 'backup'@'localhost';

3.查看backup用户的所有权限

mysql> SHOW GRANTS FOR 'backup'@'localhost'\G
*************************** 1. row ***************************
Grants for backup@localhost: GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER, PROCESS ON *.* TO 'backup'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
1 row in set (0.00 sec)

总结

一个mysql的备份用户需要以下权限：SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER, PROCESS。
最终授权命令可以综合成一条：

GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER, PROCESS ON *.* TO 'backup'@'localhost' IDENTIFIED BY '123';