MySQL数据备份

目录

• 内容概述
• 内容详细
  • 1.数据备份简述
  • 2.备份什么？
  • 3.备份的类型
  • 4.备份的工具
  • 5.三种备份策略
  • 6.备份实战
    • 使用cp进行备份
    • 使用mysqldump+复制binary logs备份
      • 参数
    • 物理备份之Xtrabackup
      • Xtrabackup可以做些做什么？
      • Xtrabackup工具支持
      • XtraBackup备份有什么优点
      • Xtrabackup 安装
      • Xtrabackup 备份方式（物理备份）
      • Xtrabackup 参数说明
      • Xtrabackup全量备份与恢复
        • 实例
      • Xtrabackup增量备份与恢复
        • 实例

内容概述

1.数据备份简述
2.备份什么？
3.备份的类型
4.备份的工具
5.三种备份策略
6.备份实战

内容详细

1.数据备份简述

为何要备份数据？
在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失，大概会有以下几种情况：
硬件故障
软件故障
自然灾害
人为误操作（占比最大）

在生产环境中，服务器硬件坏了可以更换或者维修；软件崩溃了可以修复或重新安装，但是数据丢失了如果没有备份的话就真的么得了。
数据对于公司的重要程度不言而喻，生产环境中最重要的就是数据了，为了在数据丢失后我们能有机会恢复数据，我们就需要定期的备份数据，这也是运维三大职责之一。

# 之前我们有学习过InnoDB存储引擎，它有自动的数据恢复功能，在执行一条写操作并且commit成功时InnoDB存储引擎会将新数据写入redo log，如果此时数据库挂掉，重启后依然可以依据redo log来恢复尚未执行完毕的数据，但是！我们这里讲的是数据误删或者干脆没了，redo log可就没法帮你了，它只能帮我们恢复尚未完成的操作，已经完成的操作redo log是无法恢复的。所以为了防止数据意外丢失，我们还是要定期做好数据的备份工作。

2.备份什么？

我们刚才提到了备份，那么我们到底要备份些什么呢？

# 一般情况下，我们需要备份下列数据
数据本身（库，表，记录）

二进制日志（binlog日志），InnoDB事务日志（redo log,undo log）

代码（存储过程、存储函数、触发器以及事件调度器）

服务器的配置文件

3.备份的类型

按照备份时数据库的运行状态，可以分为三种

1.冷备：停库、停服务进行备份，即当数据库进行备份时，数据库不能进行读写操作，即数据库需要下线	# 生产环境中一般不这样进行备份

2.温备：不停库、不停服务进行备份，会（锁表）阻止用户的写入，即当数据库进行备份时，数据库的读操作可以执行，但是不能执行写操作

3.热备（建议）：不停库、不停服务进行备份，也不会（锁表）阻止用户的写入。即当数据库进行备份时，数据库的读写操作均不受影响。

# MySQL中进行不同类型的备份还要考虑存储引擎是否支持
MyISAM:热备不支持，支持冷备和温备
InnoDB：热备，温备和冷备都支持

# 物理与逻辑
按照备份的内容分，可以分为两种
1.物理备份：直接将底层物理文件备份
2.逻辑备份：通过特定的工具从数据库中到处SQL语句或者数据，可能会丢失数据精度

# 全量、差异、增量备份
按照每次备份的数据量，可以分为
1.全量备份/完全备份（Full Backup）：备份整个数据集（即整个数据库）
2.部分备份：备份部分数据集（例如：只备份一个表的变化）
部分备份又可以分为：差异备份和增量备份两种

# 1.差异备份（Differential Backup）
每次备份时，都是基于第一次完全备份的内容，只备份有差异的数据（新增的、修改的、删除的），例如
第一次备份：完全备份
第二次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
第三次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
...

# 2.增量备份（Incremental Backup ）
每次备份时，都是基于上一次备份的内容（这里要注意是上一次，而不是第一次），只备份有差异的数据（新增的、修改的、删除的），所以增量备份的结果是一条链，例如：
第一次备份：完全备份
第二次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
第三次备份：以当前时间节点的数据为基础，备份与第二次备份内容的差异
...

# 上述三种备份方案要如何恢复数据？

全量备份的数据恢复
只需找出指定时间点的那一个备份文件即可，即只需要找到一个文件即可

差异备份的数据恢复
需要先恢复第一次全量备份的结果，然后再恢复最近一次差异备份的结果，即只需要找到两个文件即可

增量备份的数据恢复
需要先恢复第一次备份的结果，然后在依次恢复每次的增量备份，直到恢复到当前的位置，即需要找到一条备份链

# 综上，对比三种备份方案
1.占用空间：全量 > 差异 > 增量
2.恢复数据的复杂程度：增量 > 差异 > 全量

4.备份的工具

备份工具	备份速度	恢复速度	便捷性	适用存储引擎	支持的备份类型	功能	应用场景
cp、tar等（物理）	快	快	一般	所有	冷备、全量、差异、增量	很弱	少量数据备份
lvm2快照（物理）	快	快	一般	所有	支持几乎热备（即差不多是热备，哈哈），是借助文件系统管理工具进行的备份	一般	中小型数据量的备份
xtrabackup（物理）	较快	较快	是一款非常强大的热备工具	由percona提供，只支持InnoDB/XtraDB	热备、全量、差异、增量	强大	较大规模的备份
mysqldump（逻辑）	慢	慢	一般	所有	支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备	一般	中小型数据量的备份

如果考虑到增量备份，还需要结合binlog日志（binlog只属于增量恢复），需要用到工具mysqlbinlog,相当于逻辑备份的一种。

5.三种备份策略

针对不同的场景，我们需制定不同的备份策略对数据库进行备份，一般情况下，备份策略应为以下三种

• 直接cp、tar复制数据库的文件
• mysqldump+复制bin logs
• lvm2快照+复制bin logs
• xtrabackup

1.如果数据量较小，可以使用第一种方式，直接复制数据库文件

2.如果数据量有一定的规模，可以采用第二种方式，先使用mysqldump对数据库进行完全备份，然后定期备份binary log达到增量备份的效果

3.如果数据量一般，而又不过分影响业务运行，可以使用第三种方式，使用lvm2的快照对数据文件进行备份，而后定期备份binary log 达到增量备份的效果。

4.如果数据量很大，而又不过分影响业务运行，可以使用第四种方式，使用xtrabackup进行完全备份后，定期使用xtrabackup进行增量备份或差异备份

6.备份实战

使用cp进行备份

备份步骤
# 1.向所有表施加读锁
flush tables with readd lock;

# 2.备份数据文件
mkdir /mysql_backup
tar -czf mysql_backup.tar.gz /mysql_data/*
mv mysql_backup.tar.gz /mysql_backup

使用mysqldump+复制binary logs备份

mysqldump命令
# 语法
mysqldump  -h 服务器  -u用户名  -p密码  选项与参数 > 备份文件.sql

# 选项
-A/-- all-databasses	# 所有库
-B/--databases db1 db2	# 多个数据库
db1					   # 数据库名
db1 t1 t2			    # db1数据库的表t1、t2
-F						# 备份的同时刷新binlog
-R						# 备份存储过程和函数数据（有写就备份，没有就不备份）
--triggers				# 备份触发器数据（现在都是开发写触发器）
-E/--events				# 备份事件调度器
-d						# 仅表结构
-t						# 仅数据

# 完整的语句
mysqldump -uroot -p -A -E -R --triggers > /mysql_backup/MySQL_`date +"%F".sql`
Enter password: 

有时文件太大可以压缩gzip，但gzip不属于mysql独有的命令，所以我们需要管道符
mysqldump -uroot -p -A -E -R --triggers | gzip > /mysql_backup/MySQL_`date +"%F".sql.gz`
Enter password: 

导出数据的时候压缩了，导入的时候就需要解压，可以使用zcat命令
zcat /mysql_backup/MySQL_2021-10-16.sql.gz | mysql -uroot -p
Enter password: 

参数

参数	解释
-A --all-databases	导出全部数据库
-Y --all-tablespaces	导出全部表空间
--add-drop-database	每个数据库创建之前添加drop数据库语句。
--add-drop-table	每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用--skip-add-drop-table取消选项)
--add-locks	在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态，使用--skip-add-locks取消选项)
--comments	附加注释信息。默认为打开，可以用--skip-comments取消
--compact	导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项：--skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys
-c --complete-insert	使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。
-C --compress	在客户端和服务器之间启用压缩传递所有信息
-B --databases	导出几个数据库。参数后面所有名字参量都被看作数据库名。
--debug	输出debug信息，用于调试。
--debug-info	输出调试信息并退出
--default-character-set	设置默认字符集，默认值为utf8
--delayed-insert	采用延时插入方式（INSERT DELAYED）导出数据
-E --events	导出事件。
--master-data	在备份文件中写入备份时的binlog文件。值为1时，binlog文件名和位置没有注释，为2时，则在备份文件中将binlog的文件名和位置进行注释。
--flush-logs	开始导出之前刷新日志。请注意：假如一次导出多个数据库(使用选项--databases或者--all-databases)，将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs。
--flush-privileges	在导出mysql数据库之后，发出一条FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。
--force	在导出过程中忽略出现的SQL错误。
-h --host	需要导出的主机信息
--ignore-table	不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 ……
-x --lock-all-tables	提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables 选项。
-l --lock-tables	开始导出前，锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。请注意当导出多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
--single-transaction	适合innodb事务数据库的备份。保证备份的一致性，原理是设定本次会话的隔离级别为Repeatable read，来保证本次会话（也就是dump）时，不会看到其它会话已经提交了的数据。
-F	刷新binlog，如果binlog打开了，-F参数会在备份时自动刷新binlog进行切换。
-n --no-create-db	只导出数据，而不添加CREATE DATABASE 语句。
-t --no-create-info	只导出数据，而不添加CREATE TABLE 语句。
-d --no-data	不导出任何数据，只导出数据库表结构。
-p --password	连接数据库密码
-P --port	连接数据库端口号
-u --user	指定连接的用户名。
-R	备份存储过程和函数数据（如果开发写了函数和存储过程，就备，没写就不备）
--triggers	备份触发器数据（现在都是开发写触发器）

物理备份之Xtrabackup

Xtrabackup 是一个对 InnoDB 做数据备份的工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具；支持在线热备份(备份时不影响数据读写)，是商业备份工具 InnoDB Hotbackup 的一个很好的替代品。

官方文档：http://www.percona.com/doc/percona-xtrabackup/2.1/

Xtrabackup有两个主要的工具：

1. xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表。
2. innobackupex 是参考了 InnoDB Hotbackup 的 innoback 脚本修改而来的.innobackupex 是一个 perl 脚本封装，封装了 xtrabackup。主要 是为了方便的同时备份InnoDB和MyISAM引擎的表，但在处理myisam时需要加一个读锁。并且加入了一些使用的选项。如slave-info可以记录备份 恢复后，作为slave需要的一些信息，根据这些信息，可以很方便的利用备份来重做slave。

Xtrabackup可以做些做什么？

在线(热)备份整个库的InnoDB、XtraDB表 

在xtrabackup的上一次整库备份基础上做增量备份(innodb only)，以流的形式产生备份，可以直接保存到远程机器上(本机硬盘空间不足时很有用) 

MySQL数据库本身提供的工具并不支持真正的增量备份，二进制日志恢复是point-in-time(时间点)的恢复而不是增量备份

Xtrabackup工具支持

对InnoDB存储引擎进行增量备份的工作原理如下：
1.首先完成一次全量备份，并记录下此时检查点的LSN(Log Sequence Number)

2.在进行增量备份时，比较表空间中每个页的LSN是否大于上次备份时的LSN，如果是，则备份该页；同时记录当前检查点的LSN。

首先，在 logfile 中找到并记录最后一个 checkpoint(“last checkpoint LSN”)，然后开始从 LSN 的位置开始拷贝 InnoDB 的 logfile 到xtrabackup_logfile；接着，开始拷贝全部的数据文件.ibd；在拷贝全部数据文件结束之后，才停止拷贝logfile。 因为logfile里面记录全部的数据修改情况，所以，即时在备份过程中数据文件被修改过了，恢复时仍然能够通过解析xtrabackup_logfile保持数据的一致。 

XtraBackup备份有什么优点

1.无需停止数据库进行InnoDB热备，且快速可靠
2.备份期间不间断事务处理
3.节省硬盘空间以及网络带宽
4.可以自动对备份文件进行验证
5.快速恢复，保障在线运行时间持久性

Xtrabackup 安装

mysql 5.7以下版本，可以采用percona xtrabackup 2.4版本。
mysql 8.0以上版本，可以采用percona xtrabackup 8.0版本，xtrabackup8.0也只支持mysql8.0以上的版本。

比如，接触过一些金融行业，mysql版本还是多采用mysql 5.7，当然oracle官方对于mysql 8.0的开发支持力度日益加大，新功能新特性迭代不止。生产环境采用mysql 8.0的版本比例会日益增加。

下载地址：https://www.percona.com/downloads/

安装方式
下载rpm包
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.24/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.24-1.el7.x86_64.rpm

yum localinstall -y percona-xtrabackup-24-2.4.24-1.el7.x86_64.rpm

# 安装完毕后会生成命令
xtrabackup      以前使用该命令
innobackupex    现在使用该命令

innobackupex是xtrabackup的前端配置工具，使用innobackupex备份时, 会调用xtrabackup备份所有的InnoDB表, 复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件, 同时还会备份触发器和数据库配置文件信息相关的文件, 这些文件会被保存至一个以时间命名的目录。

Xtrabackup 备份方式（物理备份）

1.对于非InnoDB存储引擎（比如MyISAM）是直接锁表复制数据文件，这属于温备
2.对于InnoDB存储引擎（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo log和undo log一并备走，属于热备方式。
3.备份时读取配置文件/etc/my.cnf

Xtrabackup 参数说明

--host     								指定主机
--user     								指定用户名
--password    						指定密码
--port    								指定端口
--databases     					指定数据库
--incremental    					创建增量备份
--incremental-basedir   	指定包含完全备份的目录
--incremental-dir      		指定包含增量备份的目录   
--apply-log        				对备份进行预处理操作。一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
--redo-only      					不回滚未提交事务
--copy-back     					恢复备份目录

Xtrabackup全量备份与恢复

使用innobackupex备份时，它会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件，这些文件会被保存到一个以时间命名的目录当中。

1.创建备份目录，我们将数据库的文件目录中的内容都拷贝到这个备份目录中
mkdir /mysql_backup

2.进行全量备份
innobackupex --user=root  --password=123 /mysql_backup/backup_`date +"%F"`

cd /mysql_backup/backup_2021-10-17/
ll
drwxr-x--- 6 root root 238 10月 17 13:31 2021-10-17_13-31-44
# 我们可以发现，默认会在备份目录下生成一个以时间戳命名的文件夹

我们可以去掉时间戳，让备份的内容直接放置于我们指定的目录中
innobackupex --user=root  --password=123 --no-timestamp /mysql_backup/backup_`date +"%F"`

当然我们也可以创建一个普通用户进行备份

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '123456';　　#创建用户
mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bkpuser';　　#回收此用户所有权限
mysql> GRANT RELOAD,LOCK TABLES,RELICATION CLIENT ON *.* TO 'bkpuser'@'localhost';　　#授权刷新、锁定表、用户查看服务器状态
mysql> FLUSH PRIVILEGES;　　#刷新授权表

实例

1.我们先进行全量备份
2.合并数据，保持数据的一致性
innobackupex --apply-log /mysql_backup/backup_2021-10-17
3.我们将MySQL存放数据文件的目录清空模拟数据丢失
rm -rf /mysql_data/*
4.直接使用--copy-back参数进行恢复
innobackupex --copy-back /mysql_backup/backup_2021-10-17
5.为恢复后的数据授权
chown -R mysql.mysql /mysql_data/
6.恢复完毕后检查一下数据是否恢复

# 需要注意的点：
1.必须要先进行innobackupex全量备份，并指定备份目录路径。
2.在恢复先一定要合并数据，使用--apply-log参数
3.3. 恢复时，直接使用--copy-back参数进行恢复，需要注意的是，在my.cnf中要指定数据文件目录的路径。

Xtrabackup增量备份与恢复

使用innobackupex进行增量备份，每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这便是InnoDB表可以进行增量备份的基础

innobackupex通过备份上次完全备份之后发生改变的页面来实现增量备份，意思就是首先需要进行一次全量备份，第一次增量备份时基于全备的，之后的增量备份都是基于上一次增量备份的。

基于全量备份的增量备份与恢复，做一次增量备份（基于当前最新的全量备份）

--incremental             创建增量备份
--incremental-basedir     指定包含完全备份的目录
--incremental-dir         指定包含增量备份的目录 

# 总结
1.增量备份需要使用参数--incremental指定需要备份到哪个目录，使用incremental-dir指定全备目录

2.进行数据备份时，需要使用参数--apply-log redo-only先合并全备数据目录数据，确保全备数据目录数据的一致性。

3.再将增量备份数据使用参数--incremental-dir合并到全备数据当中

4.最后通过最后的全备数据进行数据的恢复

# 需要注意的是，如果有多个增量备份，需要逐一合并到全备数据当中，再进行恢复

实例

增量备份数据恢复
1.先进行全量备份
innobackupex --user=root --password=123 --no-timestamp /mysql_backup/full

2.向数据库中某张表插入几条数据

3.进行增量备份
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/mysql_backup/full/ /mysql_backup/add1

4.我们模拟数据丢失，直接清空数据库存放文件的目录，并且停止数据库服务

5.恢复数据的准备，第一步合并全量备份数据
innobackupex --apply-log --redo-only /mysql_backup/full/

合并add1到全量备份中（合并最后一个增量备份时不要加--redo-noly）
innobackupex --apply-log --incremental-dir=/mysql_backup/add1/ /mysql_backup/full/

最后一次合并数据
innobackupex --apply-log /mysql_backup/full/

6.恢复数据，并为恢复的数据授予权限
innobackupex --copy-back /mysql_backup/full/
chown -R mysql.mysql /mysql_data/

7.重启数据库服务，登入数据库查看数据恢复是否成功

# 多次增量备份数据恢复
场景：周日进行了全量备份，周一，周二以及周三进行了增量备份，周四数据库宕机，数据丢失一部分（这里我们将数据库的数据目录里的文件都删掉进行模拟）

1.先进行全量备份
innobackupex --user=root --password=123 --no-timestamp /mysql_backup/full

2.进行周一至周三的增量备份
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/mysql_backup/full/ /mysql_backup/add1

innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/mysql_backup/add1 /mysql_backup/add2

innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/mysql_backup/add2 /mysql_backup/add3

3.数据已经丢失，我们要进行数据恢复前的准备
1）整理全量备份
innobackupex --apply-log --redo-only /mysql_backup/full/

2）合并周一增量备份到全量备份中
innobackupex --apply-log --redo-only --incremental-dir=/mysql_backup/add1 /mysql_backup/full/

# 我们可以对比一下add1与dull的LSN号：last_lsn保持一致才行
cat /mysql_backup/full/xtrabackup_checkpoints 
cat /mysql_backup/add1/xtrabackup_checkpoints 

3）合并周二增量备份到全量备份中
innobackupex --apply-log --redo-only --incremental-dir=/mysql_backup/add2 /mysql_backup/full/

4）合并周三增量备份到全量备份中（# 合并最后一个增量备份的时候不要加--redo-only）
innobackupex --apply-log --incremental-dir=/mysql_backup/add3 /mysql_backup/full/

5）最后一次整理增量备份
innobackupex --apply-log /mysql_backup/full/

6）准备就绪，我们恢复数据
innobackupex --copy-back /mysql_backup/full/
# 并为恢复后的数据授权，否则MySQL服务起不来
chown -R mysql.mysql /mysql_data/

7）我们启动数据库，登录数据库，查看数据是否恢复完毕即可