mysqldump 權(quán)限

1. 創(chuàng)建備份用戶 backup

-- 創(chuàng)建備份用戶
create user 'backup'@'localhost' identified by '123456';

執(zhí)行備份命令：

mysqldump -ubackup -p123456 admin > admin.sql
# 報錯 - 缺少 select 權(quán)限
# Access denied for user 'backup'@'localhost' to database 'admin' when selecting the database

2. 添加 select 權(quán)限

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

執(zhí)行備份命令：

mysqldump -ubackup -p123456 admin > admin.sql
# 報錯 - 缺少 lock tables 權(quán)限
# Access denied for user 'backup'@'localhost' to database 'admin' when using LOCK TABLES

3. 添加 lock tables權(quán)限

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

4. 添加 show databases 權(quán)限

用于獲取數(shù)據(jù)庫列表，判斷數(shù)據(jù)庫是否存在之類的

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

總結(jié)

-- 創(chuàng)建備份用戶
create user 'backup'@'localhost' identified by '123456';
grant select,lock tables, show databases on *.* to 'backup'@'localhost';