☰

如何使用 mysqldump 创建只读 MySQL 用户以进行备份？

tag-icon • tag-icon backup mysql database-backup read-only
如何使用 mysqldump 创建只读 MySQL 用户以进行备份？

我正在使用automysqlbackup脚本转储我的 mysql 数据库，但我希望有一个只读用户来执行此操作，这样我就不会将我的根数据库密码存储在纯文本文件中。

我创建了一个这样的用户：

grant select, lock tables on *.* to 'username'@'localhost' identified by 'password';

当我mysqldump（通过automysqlbackup或直接）运行时，收到以下警告：

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

我做错了吗？我需要为只读用户提供额外的授权吗？还是只能root锁定information_schema表格？发生了什么事？

编辑：

哇哦，现在它起作用了。我之前可能没有运行 FLUSH PRIVILEGES。

顺便问一下，这种情况多久会自动发生一次？

编辑：

不，它不起作用。mysqldump -u username -p --all-databases > dump.sql手动运行不会产生错误，但不会转储 information_schema。automysqlbackup确实会引发错误。

答案1

这些权限应该是 mysqldump 所需的全部权限。

由于您已授予 LOCK TABLES，并且 LOCK TABLES 出现错误，因此权限似乎不一致。您运行过吗FLUSH PRIVILEGES？

答案2

哎呀...来自手册页mysqldump：

mysqldump does not dump the INFORMATION_SCHEMA database. If you name that database explicitly on the command line, mysqldump silently ignores it

似乎要么是手册页​​已过时（并且确实会发出警告），要么是automysqlbackup正在对转储执行一些额外的检查information_schema。

不确定是哪一个，但它与用户授权无关。

编辑

是的，这是 2.5.1 版本（在 Ubuntu 10.04 下使用 MySQL 5.1.41）中的一个错误- 它会在不应该备份的时候automysqlbackup尝试备份。information_schema

修复：在脚本的第 76 行添加information_schema到。DBEXCLUDE

答案3

创建用户

GRANT USAGE ON *.* TO 'dump'@'localhost' IDENTIFIED BY 'plaintext-pass';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON `my-db`.* TO 'dump'@'localhost';

检查权限

mysql> SHOW GRANTS FOR dump@'localhost';
+-----------------------------------------------------------------------------------------------+
| Grants for dump@localhost                                                                     |
+-----------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dump'@'localhost'                                                      |
| GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON `my-db`.* TO 'dump'@'localhost' |
+-----------------------------------------------------------------------------------------------+

使用您最喜欢的编辑器创建~/.my.cnf文件chmod 400

[client]
user=dump
password=plaintext-pass

创建转储文件夹，仅作为示例

mkdir ~/db-dumps

检查是否有效

mysqldump -u dump --hex-blob --routines --triggers my-db | gzip > ~/db-dumps/manual-my-db-dump-`date +%F`.sql.gz

您可以选择进行转储daily，weekly然后删除所有daily早于月份的数据

#m h  dom mon dow   command
0  3  *   *   0,2-6 /usr/bin/mysqldump -u dump --hex-blob --routines --triggers my-db | gzip > ~/db-dumps/daily-my-db-dump-`date +%F`.sql.gz;
0  3  *   *   1     /usr/bin/mysqldump -u dump --hex-blob --routines --triggers my-db | gzip > ~/db-dumps/weekly-my-db-dump-`date +%F`.sql.gz;
0  4  *   *   *     /usr/bin/find ~/db-dumps/ -name "daily-*" -type f -mtime +30 -exec rm -f {} \;

相关内容