mysql有几种备份恢复方式（mysql001）

mysql有几种备份恢复方式（mysql001）所以说，为什么我们要备份？因为我们要做到无所畏惧，有路可退。在风险面前，我们尽能力去规避风险。这些风险，小到不小心在别的服务器执行了 Alter Table，大到服务器硬件出现故障，全机崩溃，软件硬件故障/自然灾害/人为操作等等。时间是往前流动的，人生是不可逆转的，但是数据库能。我想说几个场景你是否还很熟悉？首先，在讲 MySQL 备份之前，我想明确咱们接下来需要探究的问题知识背景为什么我们需要备份？

前言

这周又是上线周。办公桌的头发越来越多了，保温杯都是枸杞，电脑壁纸也换成了应急逃生通道（不要问我为什么是应急通道，因为打算随时跑路）。

因为是新系统要与旧系统之间进行数据同步，清洗，分发。所以，这周任务是不断地核实数据，调试程序，与数据库打交道的占比很高。

一旦要到数据库这个话题，永远也避不开数据安全的问题。所以今天我就来讲讲怎么使用 mysql 的备份与恢复。

抛出本文问题

首先，在讲 MySQL 备份之前，我想明确咱们接下来需要探究的问题

1. 备份这么麻烦，但是为什么值得我们去做？
2. 多得一批的备份术语
3. 我们究竟需要备份什么？
4. 备份需要考虑什么因素？
5. 备份的方案有哪些？
6. 实践

知识背景

为什么我们需要备份？

时间是往前流动的，人生是不可逆转的，但是数据库能。我想说几个场景你是否还很熟悉？

1. 线上项目因为 Bug 或客户骚操作的问题，导致业务数据缺失，流程无法继续走下去，没有回头了只硬着头皮线上改数据，结果表一多起来，改了那条都不知道了
2. 上线前从旧系统迁移数据，为上线做准备，结果一执行清洗 SQL，哎呀，IS NOT NULL 忘了改回了 IS NULL，含泪全库删除，重新导库清洗；
3. 新同事在服务器执行了技术大佬传授真经命令行 rm -rf /*，结果我赶紧给他发了一张高清的紧急逃生通道...

所以说，为什么我们要备份？因为我们要做到无所畏惧，有路可退。在风险面前，我们尽能力去规避风险。这些风险，小到不小心在别的服务器执行了 Alter Table，大到服务器硬件出现故障，全机崩溃，软件硬件故障/自然灾害/人为操作等等。

所以我们需要备份是为了应对来自各方面的威胁

多得一批的备份术语

说起备份，可能你的头脑里浮现了 热备份/冷备份/增量备份/差异备份/逻辑备份...放弃的声音席卷而来！

其实先不要害怕这些术语，它们都是有专门的由来的。

首先是热备份，温备份和冷备份。热备份指的是不需要停止任何服务即可备份，就好像你备份不用关掉数据库来备份，随时随地可进行;冷备份指的是停止数据库进行数据备份。

然后全量备份和部分备份。

1. 全量备份(类似名字还有全局备份，完全备份)指的是将整个数据库备份下来。显然当项目数据达到一定规模，那么整库备份变得不现实，因为备份时间变得更长，同时需要更多地磁盘资源，机器资源...
2. 部分备份指的是将部分数据集备份下来，例如备份某库某表某个时间段的数据，或者是仅仅备份某库某表的所有数据。部分备份一般不包含完整的数据集，而我们明显可以仅仅备份所更改的数据，这样可以减少服务器的开销/备份时间/备份空间。根据部分备份的概念，我们可以拆分成两种备份方式：增量备份和差异备份，下面使用表格说明：

名称说明增量备份对自上次全备份后所有改变的部分而做的备份差异备份自从任意类型的上次备份后所有修改做的备份

举例说明，假设在周日做了一个全量备份。在周一，对自周日以来所有的改变做一个差异备份。在周二，你有两个选择：备份周日以来所有的改变(差异备份)，或只备份自从周一备份后所有的改变(增量备份)

我们究竟需要备份信息？

可能说到这个问题上，大多数人第一反应就是备份表结构 表数据。恭喜你，你猜对了一半，但是这个方案是备份中最低的要求，因为在数据库中还存在很多被忽略的数据在默默支撑着数据库的正常运行。下面介绍一下数据库哪些值得关注的数据：

类型内容非显著信息二进制日志和 InnoDB 事务日志代码触发器和存储过程复制配置二进制日志/中继日志/日志索引文件/.info 文件服务器配置服务器的配置文件选定的操作系统文件对生产服务器至关重要的外部配置。在 unix 服务器上，可能包括了 cron 任务/用户和组的配置/管理脚本/sudo 规则等

根据业务权衡，备份的数据越多，类型越齐全，就越有利于你恢复到想要的效果

备份我们需要考虑什么因素

其实备份考虑的因素不多，关键的有以下几个

1. 锁时间
2. 备份时间
3. 备份负载
4. 恢复时间

关于锁时间，我们需要考虑是否一定要锁表？锁表时间可接受的范围是多少？如果是热备份，在什么时候进行锁表才不会影响业务？

备份的方案有哪些？

方案名称适用场景mysqldump binlog全量备份 增量备份混合方案xtrabackupInnoDB 支持热备，支持全量备份/增量备份，MyISAM 支持温备，只支持全量备份lvm binlog热备，物理备份

实践

前期准备

1. 创建一个数据库
2. 执行以下 sql，准备好我们的基础数据

-- ---------------------------- -- 创建一个表 -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT `username` varchar(255) DEFAULT NULL PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -- ---------------------------- -- 插入基础数据 -- ---------------------------- INSERT INTO `user` VALUES ('1' '123'); INSERT INTO `user` VALUES ('2' '456');

❤️ 使用 mysqldump binlog 备份

mysqldump 其实是一个 mysql 的一个命令行。binlog 是一个二进制格式的文件 用于记录用户对数据库更新的 SQL 语句信息 例如更改数据库表和更改内容的 SQL 语句都会记录到 binlog 里，对查询等操作并不会记录。

场景模拟

1. 在基础数据下，先做一个全量备份
2. 模拟新增数据操作，增加新数据
3. 然后使用 binlog 做一个增量备份
4. 模拟数据库误操作，将数据表删除
5. 关闭二进制日志，然后恢复全量备份，备份完后开启二进制日志
6. 通过增量备份恢复数据
7. 检查恢复情况

根据场景模拟开始之前，我们需要确认 mysqldump 是否开启。在 SQL 命令行模式下检查是否开启：

// Off 关闭；On 开启 show variables like 'log_bin';

如果没开启，我们打开并编辑 /etc/my.cnf

log-bin=/root/mysql/bin-log/bin-log-File expire-logs-days = 14 max-binlog-size = 500M server-id = 1

保存后重启，再次检查是否开启

第一步

检查目前的 binlog 备份状态，便于

mysql -e 'SHOW MASTER STATUS'

结果

------------------ ---------- -------------- ------------------ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | ------------------ ---------- -------------- ------------------ | mysql-bin.000000 | 45 | | | ------------------ ---------- -------------- ------------------

Position 代表着已经被备份数据的位置，我们需要记住便于接下来从这个位置恢复。

使用 mysqldump 进行全量备份

mysqldump --all-databases --lock-all-tables > user_backerup.sql

第二步

模拟前端新增操作，代表着目前的数据已经发生了变化

INSERT INTO `user` VALUES ('3' '456');

第三步

我们再次查看目前的增量备份文件是多少

show master status

假设结果是

------------------ ---------- -------------- ------------------ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | ------------------ ---------- -------------- ------------------ | mysql-bin.000000 | 80 | | | ------------------ ---------- -------------- ------------------

使用 binlog 进行增量备份，在 sql 命令执行 flush logs 后，会在你之前设的 logbin 文件夹下多一份文件 mysql-bin.000001，那么这份就是增量备份。

第四步

我们可以数据库误操作，例如说不小心删了表，或者删除了一些表数据。我这里通过删表作为误操作

drop table user;

再检查是否真的删除了

show tables;

第五步

因为现在我们已经误操作了，我们需要进行全量备份，然后再增量备份。

关闭二进制日志

SET sql_log_bin=OFF;

然后执行全量备份文件

mysql -uroot -p user < user_backerup.sql

执行完后再次开启二进制日志

SET sql_log_bin=ON;

第六步

这时候，我们应该想到了，还差增量备份的数据。就能返回到了误操作的前面。

所以我们使用 mysqlbinlog 命令执行增量备份文件

mysqlbinlog --start-position=45 --stop-position=80 mysql-bin.000001 | mysql user

第七步

接下来就是检查的情况了

show tables;

❤️ 使用 xtrabackup 备份

xtrabackup 是一款开源的免费数据库热备份软件，实现非阻塞备份 InnoDB 引擎数据库，但是对于 MyISAM 还是需要加表锁备份。

下面是 xtrabackup 的优点

1. 备份速度快，还原速度快，物理备份可靠
2. 无须锁表，实现热备份；支持压缩备份
3. 低负载备份，降低服务器负载
4. 备份文件可跨平台
5. 还原速度快
6. 支持加密备份

环境安装

默认你已经根据自身情况安装了相对的版本的 xtrabackup

我们依旧通过上面的场景模拟，用 xtrabackup 进行全量备份脚本、增量备份恢复

模拟全量备份脚本

1. 执行以下 SQL，准备好我们的基础数据
2. 使用 xtrabackup 进行全量备份
3. 模拟人为数据库误操作
4. 通过 xtrabackup 进行恢复

使用命令行进行全量备份

xtrabackup --backup --target-dir=/root/xtrabackup/bakcups --user=root --password=root

参数解释：

--backup：将备份文件让道 target-dir，也就是说明它和 target-dir 是搭配使用的

--target-dir：备份文件放置文件，当前我使用的文件夹是 /root/xtrabackup/bakcups

如果看到有类似输出，即说明已经成功备份了

190904 14:30:48 [00] Writing xtrabackup_info 190904 14:30:48 [00] ...done xtrabackup: Transaction log of lsn (4417990) to (4417999) was copied. 190904 14:30:49 completed OK!

然后我们执行 SQL，模拟误操作，增删改都可以。我这里就直接删除一个表吧~

drop tables tablesname;

接着通过命令进行全量恢复

xtrabackup --prepare --target-dir=/root/xtrabackup/bakcups

这时候可以打开数据进行检验。

模拟增量备份恢复

增量备份目前仅可用于 InnoDB 或 XtraDB，对于 MyISAM，增量和全量备份同样还是会扫描全表的

通常在做增量备份，先做一个全量备份的(如果需要账号密码登录自行加上)。

xtrabackup --backup --target-dir=/root/xtrabackup/base

在 /data/backups/base 下会生成很多文件。我对于增量备份，我们着重看一个叫 xtrabackup_checkpoints。以下是它的结构：

backup_type = full-backuped // 备份类型 from_lsn = 0 // 初始位置 to_lsn = 15188961605 // 备份位置 last_lsn = 15188961605 // 最后备份位置

也就是说，增量备份会基于全量备份的信息进行备份的。

xtrabackup --backup --target-dir=/root/xtrabackup/inc1 \ --incremental-basedir=/root/xtrabackup/base

刚刚生成的 /root/xtrabackup/inc1 里边包含大多信息，而且这里边也有一个 xtrabackup_checkpoints 文件。我给出一个大概结构的文件

backup_type = incremental from_lsn = 4124244 to_lsn = 6938371 last_lsn = 7110572 compact = 0 recover_binlog_info = 1

现在我们通过 xtrabackup --prepare 进行数据恢复。

innobackupex --defaults-file=/etc/my.cnf --user=root --password='password' /backup/20180423/

接下来就是检查的情况了

关于备份与恢复的一些知识点

1. 有些部分备份不会真正减少服务器的开销。
2. 不要备份没有改变的表。MyISAM 会记录每个表最后修改时间，通过查看磁盘文件或运行 show tables status 来看时间；如果是 InnoDB。，可以利用触发器记录修改时间到一个小的“最后修改时间”表中，帮助追踪最新的修改操作。需要确保只对变更不频繁的表进行跟踪，这样才能降低开销。通过定制脚本可以轻松获得哪些表变更了。
3. 增量备份的缺点是，增加了恢复的复杂度，额外的风险，更长的恢复时间。如果可以做全备，尽量做全备。
4. 建议备份至少一周一次。
5. 但是一般情况下，这个备份是不能用于恢复的，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件处于不一致的状态，我们现在就是要通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
6. 备份文件的命名需要规范起来。例如全量备份的话可以使用特定标识作为前缀；增量备份可以以时间段作为命名