MySQL · 备份与恢复

发表于 | 更新于 | 分类于 | 阅读次数：

概述

根据备份方法不同：

  • 热备：数据库运行过程中直接备份，对正在运行的数据库操作没有任何的影响。
  • 冷备：备份操作是在数据库停止的情况下，这种备份最为简单，只需要复制相关的数据库物理文件即可。
  • 温备：数据库运行过程中直接备份，但是会对当前数据库操作有所影响。

根据备份后文件的内容不同：

  • 逻辑备份：是指备份出的文件内容是可读的，一般内容是 SQL 语句，如 mysqldump。
  • 物理备份：是指复制数据库的物理文件，如 xtrabackup。

根据备份数据库内容不同：

  • 全量备份：是指对数据库进行一个完整的备份。
  • 增量备份：是指在上次完全备份的基础上，对于更改的数据进行备份。
  • 日志备份：是指对二进制日志的备份，通过它可以完成 point-in-time 的恢复工作。

自建 MySQL

备份

物理备份

逻辑备份

恢复

阿里云 RDS

备份

全量备份（数据）

增量备份（日志）

恢复

单库单表恢复

整实例恢复

恢复到原实例

恢复到新实例

恢复到本地自建数据库

备份

物理备份

备份内容：共享表空间文件 .frm、独立表空间文件 .ibd、重做日志文件、配置文件 my.cnf。

优缺点：

  • 优点：备份恢复简单、速度快。
  • 缺点：备份的文件通常比逻辑文件大很多，且需要停机，不适于用于生产环境。

逻辑备份

mysqldump

语法

1
2
3
Usage: mysqldump [OPTIONS] database [tables] > [backupfile.sql]
       mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] > [backupfile.sql]
       mysqldump [OPTIONS] --all-databases [OPTIONS] > [backupfile.sql]

参数

  • –single-transaction：在备份之前，先执行 start transaction 命令，以此来获得备份的一致性，当前该参数只对 InnoDB 存储引擎有效。当启用此参数时，确保没有任何 DDL 语句执行，因为一致性读并不能隔离 DDL 操作。

  • –lock-tables (-l)：在备份中，依次锁住每个库下所有表，一般用于 MyISAM 存储引擎，当备份时只对数据库进行读取操作，不过备份依然可以保证一致性。对于 InnoDB 存储引擎，不需要使用该参数。

  • –lock-all-tables (-x)：在备份中，对所有库的所有表上锁。

  • –no-data (-d)：不备份数据。

  • –extended-insert (-e)：使用全新多行 insert 语法，默认开启。

  • –master-data [=value]：通过该参数产生的备份转存文件主要用来建立一个 replication。

    • 1：转存文件中记录 change master 语句。

    • 2：转存文件中 change master 语句被注释。

  • –flush-logs：开始备份之前刷新日志。

  • –events (-E)：备份事件。

  • –routines (-R)：备份存储过程和函数。

  • –triggers：备份触发器，该选项默认启用。

  • –where=’where_condition’ (-w ‘where_condition’)：导出给定条件的数据。

实践

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 备份所有库
mysqldump --user=sre --password='xx' --all-databases > backup.sql

# 备份指定库
mysqldump -u<user> -p<password> --databases school hospital > backup.sql

# 备份指定表
mysqldump --user=sre --password='xx' school table_name > backup.sql

# 导出表结构，无数据
mysqldump --user=sre --password='xx' --all--database --no-data > backup.sql

# 备份存储过程
mysqldump --user=sre --password='xx' --single-transaction --master-data=2 -n -d -t -R user > dump.sql

# 备份InnoDB
mysqldump --socket=mysql.sock --user=user --password=pass --single-transaction --flush-logs --master-data=2 --all-databases --routines --events > dump.sql

# 备份Myisam
mysqldump --socket=mysql.sock --user=user --password=pass --lock-all-tables --flush-logs --master-data=2 --all-databases --routines --events > dump.sql

# 按条件备份
mysqldump -usre -p'xx' -t user uc_event_logs_bak --where="id > 46005503" > uc_event_logs_bak2.sql

# 后台执行任务
nohup mysql -usre -p'xx' user < uc_event_logs_bak2.sql 2> myout.file &

注意：默认是会备份视图。

恢复

二进制日志备份与恢复

作用：二进制日志非常关键，用户可以通过它完成 point-in-time 的恢复工作。对于 InnoDB 存储引擎，推荐的二进制日志的服务器配置应该是：

1
2
3
4
[mysqld]
log-bin = mysql-bin
sync_binlog = 1
innodb_support_xa = 1

备份：在备份二进制日志之前，可以通过 Flush Logs 命令生成一个新的二进制日志文件，然后后备份之前的二进制日志。

恢复：二进制日志的恢复主要通过工具 mysqlbinlog 实现。例如：

1
2
3
4
5
6
7
8
9
10
11
12
// 恢复单个文件
mysqlbinlog mysql-bin.000001 | mysql -uroot -p test 

// 恢复多个文件
mysqlbinlog mysql-bin.[0-10]* | mysql -uroot -p test

// 也可以先导出sql文件，再使用source导入，好处是可以对导出的文件进行修改
mysqlbinlog mysql-bin.000001 > /tmp/backup.sql
mysql -uroot -p -e "source /tmp/backup.sql"

// 也可以通过--start-position和--stop-position指定某个位置进行恢复
// 也可以通过--start-datetime和--stop-datetime指定某个时间点进行恢复

热备

XtraBackup

快照备份

MySQL 数据库本身并不支持快照功能，因此快照备份是指通过文件系统支持的快照功能对数据库进行备份。备份的前提是将所有数据库文件放在同一个文件分区中，然后对该分区进行快照操作。支持快照功能的文件系统和设备包括 GUN/Linux 的逻辑管理器 LVM。

LVM 是 Linux 系统下对磁盘分区进行管理的一种机制，LVM 在硬盘和分区之上建立一个逻辑层，来提高磁盘分区管理的灵活性。LVM 使用了写时复制（Copy-on-write）技术来创建快照，当创建一个快照时，仅复制原始卷中数据的元数据（meta data），并不会有数据的物理操作，因此快照的创建过程非常快。

使用 LVM 快照备份 InnoDB 存储引擎表非常简单，只要把与 InnoDB 存储引擎相关的文件如共享表空间、独立表空间、重做日志文件等放在同一个逻辑卷中，然后对这个逻辑卷做快照备份即可。在对 InnoDB 存储引擎文件做快照时，数据库无须关闭，即可以进行在线备份。

全量备份

增量备份

备份策略

参数 说明
备份周期 建议一周至少需要备份两次（例如：周一、三、五 / 二、四、六）
备份时间 建议设置为业务低峰期时间
保留时间 建议保留 30 天，超过保留天数的备份会被自动删除
日志备份 建议打开，关闭日志备份会导致所有日志被清除，并且无法使用按时间点恢复数据的功能
日志备份保留 日志备份文件保留的天数，默认为 7 天
单库单表 开通后会修改备份格式以支持该功能，默认为开启，无法关闭
实例释放后备份文件是否保留 为防止忘记续费、误操作等情况导致实例数据丢失，建议选择保留最后一个或全部保留，保留不收取费用，可以在已删除实例备份页面下载备份文件，并恢复到本地数据库

恢复

恢复方案概览

RDS 提供了多种数据恢复方案，可以将数据恢复至新实例、原实例或自建数据库。

适用场景 方案
MySQL 单库单表恢复
MySQL 实例恢复
MySQL 备份文件恢复到自建数据库

单库单表恢复

RDS MySQL 支持单库和单表的数据恢复，可以通过备份指定恢复误删的数据库或表，快速恢复 MySQL 的数据。

注意事项：

  • 恢复到原实例过程中会进行主备切换，RDS 服务可能会出现闪断，请确保您的应用有自动重连机制；恢复到新实例不会进行主备切换。
  • 超出免费备份空间额度的部分将会产生额外费用，请合理设计备份周期，以满足业务需求的同时，兼顾备份空间的合理利用。
  • 如果无法确定所有涉及的表，则需要进行实例级别的恢复。
  • 每次最多选择 50 个库或者表。
  • 实例内的表低于 50000 张才可以使用单库单表恢复功能，超过 50000 张表时无法使用。

实例恢复

总结

任何时候都需要做好远程异地备份，也就是容灾的防范。