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';
点赞

发表回复