1.創(chuàng)建一個用于備份數(shù)據(jù)庫的用戶
mysql> create user 'backup'@'localhost' identified by '123';
2.添加權(quán)限
2.1 添加 SELECT 權(quán)限
根據(jù)報錯耻台，backup用戶缺少select權(quán)限
[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權(quán)限
mysql> GRANT SELECT ON *.* TO 'backup'@'localhost';
2.2 添加 LOCK TABLES 權(quán)限
根據(jù)報錯捂掰，backup用戶缺少lock tables權(quán)限
[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權(quán)限
mysql> GRANT lock tables ON *.* TO 'backup'@'localhost';
注意：如果備份時加入--single-transaction選項擂橘，則可不需要LOCK TABLES權(quán)限
2.3 添加 SHOW VIEW 權(quán)限
當(dāng)數(shù)據(jù)庫中存在view（視圖）的時候园匹，使用mysqldump備份數(shù)據(jù)庫琅绅，需要有SHOW VIEW權(quán)限
- 給test2庫添加一個view
mysql> CREATE VIEW view_1 AS SELECT 1 AS Number;
- 使用mysqldump備份季惯，會提示缺少SHOW VIEW權(quán)限
[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權(quán)限
mysql> GRANT SHOW VIEW ON *.* TO 'backup'@'localhost'
2.4 添加 RELOAD 權(quán)限
加入--master-data選項后鼓鲁，備份需要RELOAD權(quán)限
[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權(quán)限
mysql> GRANT reload ON *.* TO 'backup'@'localhost';
2.5 添加 REPLICATION CLIENT 權(quán)限
根據(jù)備份的報錯提示邮辽，需要REPLICATION CLIENT權(quán)限
[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權(quán)限
mysql> GRANT REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
2.6 添加 EVEN 權(quán)限
備份是若要備份事件唠雕，即使用--events選項，則需要EVENT權(quán)限
[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權(quán)限
mysql> GRANT EVENT ON *.* TO 'backup'@'localhost';
2.7 添加 TRIGGER 權(quán)限
mysql> GRANT TRIGGER ON *.* TO 'backup'@'localhost';
3.查看backup用戶的所有權(quán)限
MariaDB [test2]> SHOW GRANTS FOR 'backup'@'localhost'\G
*************************** 1. row ***************************
Grants for backup@localhost: GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
1 row in set (0.00 sec)
總結(jié)：一個mysql的備份用戶需要以下權(quán)限：SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER吨述。
最終授權(quán)命令可以綜合成一條：
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost' IDENTIFIED BY '123';
