MySQL 备份基础知识

2020-02-27 2022-04-25 Operations > Manage Service > Database 阅读次数：字数： 10k 时长 ≈ 9 分钟

了解常用的备份知识，拓展 MySQL 新的备份知识和方法。

数据库的备份类型

数据的备份类型根据自身的特性主要分为以下两种：

完全备份
备份整个数据集
部分备份
部分备份指的是备份部分数据集（例如：只备份一个表）

部分备份又分为以下两种：

增量备份
增量备份指的是备份自上一次备份以来（增量或完全）以来变化的数据；特点：节约空间、还原麻烦；
差异备份
差异备份指的是备份自上一次完全备份以来变化的数据；特点：浪费空间、还原比增量备份简单。

MySQL 备份数据的方式

数据备份方式：

热备份
热备份指的是当数据库进行备份时，数据库的读写操作均不受影响
温备份
温备份指的是当数据库进行备份时，数据库的读操作可以执行，但是不能执行写操作
冷备份
冷备份指的是当数据库进行备份时，数据库不能进行读写操作，即数据库要下线，从安全角度来说，冷备份（离线备份）是最安全的，也是最快速的。

数据备份方法

物理备份
一般就是通过 tar，cp 等命令直接打包复制数据库的数据文件达到备份的效果，特点：速度快；
逻辑备份
一般就是通过特定工具从数据库中导出数据并另存备份（逻辑备份会丢失数据精度），特点：速度慢、丢失浮点数精度，方便使用文本处理工具直接处理、可移植能力强。

MySQL 引擎

MySQL中进行不同方式的备份还要考虑引擎是否支持：

引擎	热备	温备	冷备
MyISAM	×	√	√
InnoDB	√	√	√

备份需要考虑的问题

需要备份什么？

一般情况下，需要备份的数据分为以下几种：

数据
二进制日志、InnoDB 事务日志
代码（存储过程、储存函数、触发器、事件调度器）
服务器配置文件

备份工具

常用的几种备份工具：

mysqldump：逻辑备份工具，适用于所有的存储引擎，支持温备、完全备份、部分备份、对于 InnoDB 存储引擎支持热备 cp，tar 等归档复制工具；物理备份工具、适用于所有的存储引擎、冷备、完全备份、部分备份
lvm2 snapshot：几乎热备，借助文件系统管理工具进行备份
mysqlhostcopy：名不副实的工具，几乎冷备，仅支持 MyISAM 存储引擎
xtrabackup：一款非常强大的 InnoDB/XtraDB 热备工具、支持完全备份、增量备份，由 perconna 提供

设计合适的备份策略

针对不同的场景，指定不同的备份策略对数据库进行备份，备份策略一般分为以下三种：

直接cp，tar，gunzip 复制数据库文件
mysqldump + 复制 BIN LOGS
lvm2 快照 + 复制 BIN LOGS
xtrabackup

以下几种解决方案分别针对不同的场景：

如果数据量较小，可以使用第一种方式，直接复制数据库文件
如果数据量还行，可以使用第二种方式，先使用 mysqldump 对数据库进行完全备份，然后定期备份 BINARY LOG 达到增量备份的效果
如果数据量一般，又不过分影响业务运行，可以使用第三种方式，使用 lvm2 的快照对数据文件进行备份，而后定期备份 BINARY LOG 达到增量备份的效果
如果数据量很大，而又不过分影响业务运行，可以使用第四种方式，使用 xtrabackup 进行完全备份后，定期使用 xtrabackup 进行增量备份或差异备份

实践

拷贝数据文件

1、向数据库施加读锁

1	FLUSH TABLES WITH READ LOCK;

2、备份数据文件

1 2	mkdir /backup # 在服务器上创建备份文件目录; rsync -avz --progress root@x.x.x.x:/var/lib/mysql/* /backup # 将数据库文件同步至备份服务器上

3、当数据遗失

# 关闭数据库服务
mv /var/lib/mysql /var/lib/mysql_bak  # 将数据文件备份
mkdir -p /var/lib/mysql  # 数据库服务器上创建新的目录
rsync -avz --progress root@x.x.x.x:/backup/* /var/lib/mysql  # 将备份文件同步至数据库服务器
# 同步完成后，更改属主，并启动服务
chown -R mysql.mysql /var/lib/mysql

mysqldump

mysqldump 是一个客户端的逻辑备份工具，可以生成一个重现创建原始数据库和表的 SQL 语句，可以支持所有的存储引擎，对于 InnoDB 支持热备。
在进行备份时，表均为 Myisam 表，应考虑使用 mysqlhotcopy，因为可以更快速的进行备份和恢复。
有3中方式使用 mysqldump：

mysqldump [options] db_name [tables]
mysqldump [options] --database DB1[DB2 DB3...]
mysqldump [options] --all--database  # 如果没有指定任何表或使用了--databse或--all--database选项，则存储整个数据库
# 想要获得mysqldump的支持的选项，执行
mysqldump --help

详细信息参考： mysqldump 参数详解

如果运行 mysqldump 没有 --quick 或 --opt 选项，mysqldump 在存储结果前将整个结果集装入内存，如果转储大数据可能出现问题，该选项默认启用，但可以用 --skip-opt 禁用。如果使用最新版本的 mysqldump 程序生成一个转储重装到很旧版本的 MySQL 服务器中，不应使用 --opt 或 --e 选项。

mysqldump 备份流程

备份流程图

mysqldump 常用于备份单库

mysqldump --opt db_name > backup_file.sql #将备份的数据还原或导入到其他MySQL中
MySQL db_name < backup_file.sql
MySQL -e "source /path-to--backup/backup-file.sql" db_name #备份数据
mysqldump --opt db_name | MySQL --host=remote_host -C db_name #转储数据库
mysqldump --database db_name1 [db_name2 ...] > my_databases.sql #需要备份所有数据，使用--all--databases选项
mysqldump --all-databases > all_databases.sql

增量备份

使用 mysqldump + 复制 BINARY LOG 备份
通过 mysqldump 进行一次完全备份，再修改表中的数据，然后再通过 binary log 进行恢复二进制日志需要在 mysql 配置文件中添加 log_bin=on 开启

如果表保存在 InnoDB 存储引擎中，mysqldump 提供了一种联机备份的途径(参见下面的命令)。该备份只需要在开始转储时对所有表进行全局读锁定(使用 FLUSH TABLES WITH READ LOCK)。获得锁定后，读取二进制日志的相应内容并将锁释放。
因此只有当发出 FLUSH…时正执行一个长的更新语句，MySQL 服务器才停止直到长语句结束，然后转储则释放锁。如果 MySQL 服务器只接收到短(“短执行时间”)的更新语句，即使有大量的语句，也不会注意到锁期间。

1	mysqldump --all-databases --single-transaction > all_databases.sql

对于点对点恢复(也称为“前滚”，当你需要恢复旧的备份并重放该备份以后的更改时)，循环二进制日志或至少知道转储对应的二进制日志内容很有用：

1 2	mysqldump --all-databases --master-data=2 > all_databases.sql mysqldump --all-databases --flush-logs --master-data=2 > all_databases.sql

如果表保存在 InnoDB 存储引擎中，同时使用 --master-data 和 --single-transaction 提供了一个很方便的方式来进行适合点对点恢复的联机备份。

Xtrabackup

【参考资料】

介绍

Xtrabackup 是由 percona 开源的免费数据库热备份软件，能对 InnoDB 数据库和 XtraDb 存储引擎的数据非阻塞的备份（对于 MyISAM 存储引擎备份需要加表锁）；
mysqldump 备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于 50G，mysqldump 备份就不太合适。
Xtrabackup 安装完成后有 4 个可执行文件，其中 2 个比较重要的备份工具是 innobackupex、xtrabackup。

xtrabackup 是专门用来备份 InnoDB 表的，和 MySQL server 没有交互；
innobackupex 是一个分装 xtrabackup 的 Perl 脚本，支持同时备份 innodb 和 myisam，但在对 myisam 备份时需要加一个全局的读锁；
xbcrypt 加密解密备份工具；
xbtream 流传打包传输工具，类似 tar；

Xtrabackup 优点

备份速度快，物理备份可靠
备份过程不会打断正在执行的事务（无需锁表）
能够基于压缩等功能节约磁盘空间和流量
自动备份校验
还原速度快
可以流传将备份传输到另外一台机器上
在不增加服务器负载的情况备份数据
物理备份工具，在同级数据量基础上，都要比逻辑备份性能要好的多。几十 G 到不超过 TB 级别的条件下。物理备份恢复数据有一定优势。

备份原理

拷贝数据文件、拷贝数据页

对于 Innodb 表可以实现热备
- 在数据库还有修改操作的时刻，直接将数据文件备份，此时，备份的数据对于当前来讲是不一致的；
- 将备份过程中的 redo 和 undo 一并备份；
- 为了恢复的时候，只要保证备份出来的数据页 lsn 能和 redo lsn 匹配，将来恢复的就是一致的数据。redo 应用和 sudo 应用。
对于 myisam 表实现自动锁表拷贝文件

备份开始时，首先会开启一个后台检测进程，实时检测 MySQL redo 的变化，一旦发现有新的日志写入，立刻将日志记入后台日志文件 xtrabackup_log 中，之后复制 innodb 的数据文件–系统表空间文件 ibatax ，复制结束后，执行 flush tables with readlock，然后复制 .frmMYI MYD 等文件，最后执行 unlock tables，最终停止 xtrabackup_log。

Xtrabackup 安装

首先，安装依赖关系

# Install the release repository 安装依赖关系库
wget -O /etc/yum.repos.d/epel.repo /http://mirrors.aliyun.com/repo/epel-6.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
# Install the Percona repository 安装库
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm -y
# centos5
#+ wget http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
#+ rpm -ivH percona-release-0.1-4.noarch.rpm
# Testing the repository 查看库
yum list | grep percona
# Install the packages
yum install percona-xtrabackup-24 -y
# 若出现libev.so(4)错误
#+ wget
ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm
#+ rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

xtrabackup 选项

1）innobackupex 参数选项

--defaults-file=[MY.CNF] //指定配置文件：只能从给定的文件中读取默认选项。 且必须作为命令行上的第一个选项；必须是一个真实的文件，它不能是一个符号链接。
    
--databases=# //指定备份的数据库和表，格式为：--database="db1[.tb1] db2[.tb2]" 多个库之间以空格隔开，如果此选项不被指定，将会备份所有的数据库。

--include=REGEXP //用正则表达式的方式指定要备份的数据库和表，格式为 --include=‘^mydb[.]mytb’ ，对每个库中的每个表逐一匹配， 
因此会创建所有的库，不过是空的目录。--include 传递给 xtrabackup --tables。

--tables-file=FILE //此选项的参数需要是一个文件名，此文件中每行包含一个要备份的表的完整名称，格式为databasename.tablename。 
该选项传递给 xtrabackup --tables-file，与--tables选项不同，只有要备份的表的库才会被创建。
注意：部分备份（--include、--tables-file、--database）需要开启 innodb_file_per_table 。

--compact //创建紧凑型备份，忽略所有辅助索引页，只备份data page；通过--apply-log中重建索引--rebuild-indexs。

--compress //此选项指示xtrabackup压缩备份的InnoDB数据文件，会生成 *.qp 文件。

--decompress //解压缩qp文件，为了解压缩，必须安装 qpress 工具。 Percona XtraBackup不会自动删除压缩文件，为了清理备份目录， 
用户应手动删除 * .qp文件：find /data/backup -name "*.qp" | xargs rm。

--no-timestamp //指定了这个选项备份将会直接存储在 BACKUP-DIR 目录，不再创建时间戳文件夹。

--apply-log //应用 BACKUP-DIR 中的 xtrabackup_logfile 事务日志文件。一般情况下，在备份完成后，数据尚且不能用于恢复操作， 
因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处于不一致状态。 
“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件使得数据文件处于一致性状态。

--use-memory=# //此选项接受一个字符参数（1M/1MB,1G/1GB，默认100M），仅与--apply-log一起使用，该选项指定prepare时用于崩溃恢复 
（crash-recovery）的内存。

--copy-back //拷贝先前备份所有文件到它们的原始路径。但原路径下不能有任何文件或目录，除非wget  
https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm 
yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm 指定 --force-non-empty-directories 选项。

--force-non-empty-directories //恢复时指定此选项，可使 --copy-back 和 --move-back 复制文件到非空目录，即原data目录下可以有其他文件， 
但是不能有与恢复文件中同名的文件，否则恢复失败。

--rsync //此选项可优化本地文件（非InnoDB）的传输。rsync工具一次性拷贝所有非InnoDB文件，而不是为每个文件单独创建cp， 
在备份恢复很多数据库和表时非常高效。此选项不能和 --stream 一起使用。

--incremental //这个选项告诉 xtrabackup 创建一个增量备份，而不是完全备份。它传递到xtrabackup 子进程。当指定这个选项， 
可以设置 --incremental-lsn 或 --incremental-basedir。如果这2个选项都没有被指定，--incremental-basedir 传递给xtrabackup默认值， 
默认值为：基础备份目录的第一个时间戳备份目录。

--incremental-basedir=DIRECTORY //该选项接受一个字符串参数，该参数指定作为增量备份的基本数据集的完整备份目录。 
它与 --incremental 一起使用。
--incremental-dir=DIRECTORY //该选项接受一个字符串参数，该参数指定了增量备份将与完整备份相结合的目录，以便进行新的完整备份。 
它与 --incremental 选项一起使用。

--redo-only //在“准备基本完整备份” 和 “合并所有的增量备份(除了最后一个增备)”时使用此选项。它直接传递给xtrabackup的 
xtrabackup --apply-log-only 选项，使xtrabackup跳过"undo"阶段，只做"redo"操作。如果后面还有增量备份应用到这个全备,这是必要的。 
有关详细信息,请参阅xtrabackup文档。

--parallel=NUMBER-OF-THREADS //此选项接受一个整数参数，指定xtrabackup子进程应用于同时备份文件的线程数。 
请注意，此选项仅适用于文件级别，也就是说，如果您有多个.ibd文件，则它们将被并行复制； 如果您的表一起存储在一个表空间文件中，它将不起作用。

2 ) xtrabackup 参数选项

--apply-log-only //这个选项使在准备备份(prepare)时，只执行重做(redo)阶段，这对于增量备份非常重要

Xtrabackup 实践操作

完全备份

# 进行第一全量备份
# 创建备份的路径
mkidr -p /data/backup
innobackupex --defaults-file=/etc/my.cnf --user=root --password=xxx --
socket=/usr/loca/MySQL/MySQL.socket --no-timestamp /data/backup
# 恢复前准备（合并 xtrabackup_log_file 和备份的物理文件）
innobackupex --apply-log --use-memory=32M /data/backup
#+ 查看合并后的 checkpotints 其中的类型变为 full-prepared 即为可恢复
# 恢复(需要将MySQL进程关闭)
innobackupex --copy-back /data/backup
# 恢复后必须****更改文件的属主****
chown -R MySQL:MySQL /usr/local/MySQL
# 恢复操作完成后将MySQL进程拉起

增量备份
innobackupex 增量备份过程中的**“增量”处理，主要是相对 innodb 而言，对 myisam 和其他存储引擎依然是全量备份。增量备份的过程主要是通过拷贝 innodb 中有变更的“页”。增量备份时基于全备的，第一增量备份必须要基于上一次的全备，之后的每次增量都是基于上一次的整备，最终达到一致性的增备。

备份的内容：

可以使用 binlog 作为增量
自带的增量备份，基于上次备份后的变化的数据页，还要备份在备份过程中的 undo 和 redo 变化

# 先进行一次全备
innobackupex --user=root -password=xxx --no0timestamp /data/backup
# 再进行增量备份
innobackupex --user\root --password=xx --incremental --no-timestamp --
incremental-basedir=/data/backup /data/incre
# 恢复前准备
#+ 先应用全备份日志（--apply-log,暂时不需要做回滚操作--redo-only）
innobackupex --apply-log --redo-only /data/backup
#+ 合并增量到全备中（一致性的合并）
innobackupex --apply-log --incremental-dir=/data/incre /data/backup
innobackupex --apply-log /data/backup
# 恢复
innobackupex --copy-back /usr/local/mysql
chown -R mysql.mysql /usr/local/mysql

备份策略
每周一凌晨进行全备；周二至周日每天做一次增量，每周轮询一次。部分备份脚内容如下：

# 全量备份
function full_backup() {
  backup_folder=${full_backup_prefix}_${backup_date}_${backup_time}_${ba
  ckup_week_day}
  mkdir -p $backup_dir/$backup_folder
  $xtrabackup_dir/bin/innobackupex \
  --defaults-file=$MySQL_conf_file \
  --user=$user \
  --password=$password \
  --no-timestamp \
  $backup_dir/$backup_folder > $log_dir/${backup_folder}.log 2>&1
  return $?
}
# 增量备份
function increment_backup() {
  backup_folder=${increment_prefix}_${backup_date}_${backup_time}_${back
  up_week_day}
  incr_base_folder=`sed -n '$p' $index_file | \
  awk -F '[, {}]*' '{print $3}' | \
  awk -F ':' '{print $2}'`
  mkdir -p $backup_dir/$backup_folder
  $xtrabackup_dir/bin/innobackupex \
  --defaults-file=$MySQL_conf_file \
  --user=$user \
  --password=$password \
  --no-timestamp \
  --incremental \
  $backup_dir/$backup_folder \
  --incremental-basedir=$backup_dir/$incr_base_folder \
  > $log_dir/${backup_folder}.log 2>&1
  return $?
}

数据故障恢复总结

恢复思路

确保用于恢复的备份存在
设计快速、安全恢复简单方案，制定突发问题解决方法。

恢复流程

将涉及到的业务停止（通知各方同时后）
准备误操作本周的全备，并 --apply-log --redo-only
合并误操作当天之前的增量， --apply-log --redo-only, 最近一天的增量恢复操作 --apply-log
在内网中恢复数据
需要恢复的数据状态：误操作前的 event 的 position。
从最近的增量恢复到误操作之前的 event 的 position，需要在误操作后立即备份线上此时正在使用的 binlog 文件，以便后续恢复操作时查询误操作前的 position 值。
此恢复步骤必须数据库开启了 bin-log
导出误操作涉及的库，恢复到外网库中，验证数据可用性、完整性
恢复维护