Mysql使用mysqldump备份权限整理

小辉博客
小辉博客
小辉博客
394
文章
3
评论
2021-06-1311:06:34
评论
13.5K 3455字阅读11分31秒

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

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

1)创建test测试库

mysql> create database test;

2、添加权限

1)备份test库

[root@localhost ~]# mysqldump -u'backup' -p123456 -B test > test.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: Got error: 1044: Access denied for user 'backup'@'localhost' to database 'test' when selecting the database

2)添加SELECT权限

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

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

3)添加LOCK TABLES权限

[root@localhost ~]# mysqldump -u'backup' -p123456 -B test > test.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: Got error: 1044: Access denied for user 'backup'@'localhost' to database 'test' when using LOCK TABLES

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

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

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

4)添加SHOW VIEW权限

当数据库中存在view(视图)的时候,使用mysqldump备份数据库,需要有SHOW VIEW权限

给test库添加一个view

mysql> use test;

mysql> CREATE VIEW view AS SELECT 1 AS Number;

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

[root@localhost ~]# mysqldump -u'backup' -p123456 -B test > test.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: Couldn't execute 'show create table `view`': SHOW VIEW command denied to user 'backup'@'localhost' for table 'view' (1142)

给用户'backup'添加SHOW VIEW权限

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

5)添加RELOAD权限

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

[root@localhost ~]# mysqldump -u'backup' -p123456 --master-data=2 -B test > test.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

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

6)添加REPLICATION CLIENT权限

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

[root@localhost ~]# mysqldump -u'backup' -p123456 --master-data=2 -B test > test.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

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

7)添加EVEN权限

备份时若需要备份事件,即使用--events选项,则需要EVENT权限

[root@localhost ~]# mysqldump -u'backup' -p123456 --single-transaction --master-data=2 --events -B test > test.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: Couldn't execute 'show events': Access denied for user ''backup''@'localhost' to database 'test' (1044)

给'backup'用户添加EVENT权限

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

8)添加TRIGGER权限

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

9)添加PROCESS权限

在Mysql-5.7上执行备份操作时,出现以下错误

[root@localhost ~]# mysqldump -u'backup' -p123456 --single-transaction --master-data=2 --events --triggers -R -B test > test.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

给'backup'用户添加PROCESS权限

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

3、查看'backup'用户的所有权限

mysql> show grants for 'backup'@'localhost'\G

*************************** 1. row ***************************

Grants for 'backup'@localhost: GRANT SELECT, RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT ON *.* TO ''backup''@'localhost'

总结:一个Mysql的备份用户需要以下权限:SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER, PROCESS。

最终授权命令可以综合成一条:

mysql> GRANT SELECT,RELOAD,LOCK TABLES,REPLICATION CLIENT,SHOW VIEW,EVENT,TRIGGER,PROCESS ON *.* TO 'backup'@'localhost' identified BY '123456';

继续阅读
若文章图片、下载链接等信息出错,请在评论区留言反馈,博主将第一时间更新!如果喜欢本站,请打赏支持本站,谢谢!
  • 我的微信
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
小辉博客
  • 本文由 发表于 2021-06-1311:06:34
  • 除非特殊声明,本站文章均为原创,转载请务必保留本文链接
MySQL Binlog日志三种模式 Mysql

MySQL Binlog日志三种模式

binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制...
MySQL 连接 Mysql

MySQL 连接

MySQL 连接 使用mysql二进制方式连接 您可以使用MySQL二进制方式进入到mysql命令提示符下来连接MySQL数据库。 实例 以下是从命令行中连接mysql服务...
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: