mysqldump 权限

1. 创建备份用户 backup

-- 创建备份用户
create user 'backup'@'localhost' identified by '123456';

执行备份命令：

mysqldump -ubackup -p123456 admin > admin.sql
# 报错 - 缺少 select 权限
# Access denied for user 'backup'@'localhost' to database 'admin' when selecting the database

2. 添加 select 权限

grant select on *.* to 'backup'@'localhost';

执行备份命令：

mysqldump -ubackup -p123456 admin > admin.sql
# 报错 - 缺少 lock tables 权限
# Access denied for user 'backup'@'localhost' to database 'admin' when using LOCK TABLES

3. 添加 lock tables权限

grant lock tables on *.* to 'backup'@'localhost';

4. 添加 show databases 权限

用于获取数据库列表，判断数据库是否存在之类的

grant show databases on *.* to 'backup'@'localhost';

总结

-- 创建备份用户
create user 'backup'@'localhost' identified by '123456';
grant select,lock tables, show databases on *.* to 'backup'@'localhost';