Skip to main content

MySQL备份

账号​

用于备份的用户账号​

  • 最起码，需要有 SELECT 的权限才能读取数据库。应该限制备份账号只能访问 需要备份的库。尤其是，备份账号不应该拥有对 mysql 数据库的 SELECT 权限，因为 该数据库含有登录密码；
  • 因为备份时需要锁住表，所以需要 LOCK TABLES 权限；
  • 如果数据库含有视图或触发器，那用户账号还分别需要 SHOW VIEW 和 TRIGGER 权限。

我们来创建 admin_backup@localhost，并授予它在 rookery 和 birdwatchers 上进行 SELECT 和 LOCK TABLES 的权限。

CREATE USER 'admin_backup'@'localhost' IDENTIFIED BY 'its_password_123';

GRANT SELECT, LOCK TABLES ON rookery.* TO 'admin_backup'@'localhost';

GRANT SELECT, LOCK TABLES ON birdwatchers.* TO 'admin_backup'@'localhost';

用于恢复备份的用户账号​

  • 将数据从 dump 文件恢复到表中时，用户账号至少需要 INSERT 权限；
  • 在插入数据时，还需要 LOCK TABLES 权限来锁住表；
  • 需要 CREATE 和 INDEX 权限来分别创建表和索引；
  • 因为 dump 文件可能包含设置校对集的语句，所以需要 ALTER 权限；
  • 基于 Lena Stankoska 用来恢复表的方法，她可能还想将数据恢复到临时表；这样的话， 则需要 CREATE TEMPORARY TABLES 权限；（临时表会在连接关闭时被删掉。）
  • 如果数据库有视图或触发器，则需要 CREATE VIEW 和 TRIGGER 权限。
CREATE USER 'admin_restore'@'localhost' IDENTIFIED BY 'different_pwd_456';

GRANT INSERT, LOCK TABLES, CREATE, CREATE TEMPORARY TABLES, INDEX, ALTER
ON rookery.* TO 'admin_restore'@'localhost';

GRANT INSERT, LOCK TABLES, CREATE, CREATE TEMPORARY TABLES, INDEX, ALTER
ON birdwatchers.* TO 'admin_restore'@'localhost';

备份和恢复​

备份所有数据库​

mysqldump --user=admin_backup \
--password --lock-all-tables
--all-databases > /data/backups/all-dbs.sql
  • --user=admin_backup : 让 mysqldump 以 admin_backup 账号与 MySQL 服务器进行交互；
  • --password : 让 mysqldump 在下一行弹出输入密码的提示符。它的效果如同 mysql；如果这个备份命 令要写在让 cron 执行的 shell 脚本中，那这个选项就要写成 --password=my_pwd；
  • --lock-all-tables : 在做备份前，先让 MySQL 锁住所有表，然后直到备份完成才解锁；对于繁忙的数据库 来说，长时间锁住所有表会有很大影响；
  • --all-databases : 导出所有数据库。

备份指定的数据库​

mysqldump --user=admin_backup --password --lock-tables --no-tablespaces \
--verbose --databases rookery > rookery.sql

如果只想备份某个数据库的结构，而不需要备份数据，那么可以再带上 --no-data。这样出来的 dump 文件便只会有数据库和表的结构，而没有任何数据。

备份多个数据库​

mysqldump --user=admin_backup --password --lock-tables --no-tablespaces \
--databases rookery birdwatchers > rookery-birdwatchers.sql

创建备份脚本*​

#!/bin/sh
my_user='admin_back'
my_pwd='my_silly_password'
db1='rookery'
db2='birdwatchers'
date_today=$(date +%Y-%m-%d)
backup_dir='/data/backup/'
dump_file=$db1-$db2-$date_today'.sql'
/usr/bin/mysqldump --user=$my_user --password=$my_pwd --lock-tables --no-tablespaces \
--databases $db1 $db2 > $backup_dir$dump_file
exit

恢复备份​

恢复数据库​

mysql --user=admin_restore --password < backup.sql
tip

以上是在命令行中用 mysql 执行 rookery.sql 里的语句。

恢复多个数据库​

mysql -u username -p < /path/to/backup.sql
tip

恢复多个数据库和恢复单个数据库是一样的。

docker mysqldump​

# Backup
docker exec CONTAINER /usr/bin/mysqldump -u root --password=root DATABASE > backup.sql

# Restore
cat backup.sql | docker exec -i CONTAINER /usr/bin/mysql -u root --password=root DATABASE