【Mysql】数据库备份与恢复

目录

一、备份类型

二、备份工具

三、备份策略

3.1? 备份策略分类

3.2? 备份方法

3.2.1? 物理冷备

3.2.2? mysqldump逻辑热备

3.2.3? 增量备份

3.2.4? 断点恢复

3.3? Percona XtraBackup（PXB）

3.3.1? 物理热备

3.3.2? 完全备份恢复

3.3.3? 增量备份恢复

一、备份类型

物理备份：直接对数据库的数据文件、日志文件、索引文件进行备份

逻辑备份：对数据库对象（库、表）以SQL语句的形式导出进行备份

二、备份工具

1、使用tar、gzip等方式压缩打包数据库文件（完全备份、物理冷备）

2、使用mysql自带的备份工具 mysqldump（完全备份、逻辑热备）

3、刷新二进制日志（增量备份）

4、使用第三方备份工具 Percona XtraBackup（简称PXB） （完全备份、增量备份、物理热备）

三、备份策略

3.1 备份策略分类

1、完全备份：也叫全量备份，每次都备份完整的数据库

2、差异备份：只备份上一次完全备份后的更新数据

3、增量备份：只备份上一次完全备份或增量备份后的更新数据，即每次都只备份最新的数据

3.2 备份方法

备份方法我将从物理冷备、mysqldump的逻辑热备、增量备份、断点备份这几个方面介绍使用方法以及语句。

3.2.1 物理冷备

1、备份

1）先关闭mysql服务进程

2）使用tar命令压缩打包备份数据库的数据目录和文件

2、恢复：

1）先关闭mysql服务进程

2）解压备份压缩包文件，将数据目录进行替换

3）启动mysql服务进程

3.2.2 mysqldump逻辑热备

1、备份

mysqldump -u 用户 -p密码 ?--databases 库1 库2 > XXX.sql ?????????#备份一个或多个指定库及库中的所有表

mysqldump -u 用户 -p密码 ?--all-databases > XXX.sql ?????????????#备份所有库及库中的所有表

mysqldump -u 用户 -p密码 ?--all-databases | gzip > XXX.sql.gz

mysqldump -u 用户 -p密码 ?库名 表1 表2 > XXX.sql ????????????????#备份指定库中的一个或多个表（不包含库对象本身）

2、mysqldump常用的选项

–databases（-B）

备份数据库

–all-databases（-A）

备份所有数据库

-d或–no-data

用于备份数据库的结构（即表的创建语句）
但不包括表中的数据

–flush-logs(-F)

在备份开始前刷新 MySQL 服务器的日志文件

–single-transaction

使用单一事务来备份数据

–lock-all-tables

备份过程中锁定所有表

–master-data=2

用于备份二进制日志的位置信息，用于设置主从复制

–set-gtid-purged=OFF

用于关闭 GTID 信息的输出

3、恢复：

先登录mysql数据库，再执行 source XXX.sql文件路径;

mysql -u 用户 -p密码 < XXX.sql
cat XXX.sql | mysql -u 用户 -p密码
zcat XXX.sql.gz | mysql -u 用户 -p密码

注：如果sql文件里只备份了表，需要指定库恢复

mysqldump -u 用户 -p密码 ?库名 表1 表2 > XXX.sql
mysql -u 用户 -p密码 库名 < XXX.sql ???????#指定的库必须存在
cat XXX.sql | mysql -u 用户 -p密码 库名

使用 source 恢复，需要先 use 库名; 切换库再恢复

3.2.3 增量备份

1、通过刷新二进制日志实现增量备份

mysqladmin -u 用户 -p密码 flush-logs

2、查看二进制日志内容

mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制日志(binlog)文件路径

3、使用二进制日志恢复

mysqlbinlog --no-defaults 二进制日志文件路径 | mysql -u 用户 -p密码

3.2.4 断点恢复

1、基于位置点恢复

mysqlbinlog --no-defaults --start-position='起始位置点' --stop-position='结束位置点' ?二进制日志文件路径 | mysql -u 用户 -p密码

2、基于时间点恢复

mysqlbinlog --no-defaults --start-datetime='YYYY-mm-dd HH:MM:SS' --stop-datetime='YYYY-mm-dd HH:MM:SS' ?二进制日志文件路径 | mysql -u 用户 -p密码

3、断点恢复原则：

从开始语句的BEGIN的位置点或时间点start，到结束语句的COMMIT之后的位置点或时间点stop

3.3 Percona XtraBackup（PXB）

3.3.1 物理热备

1、完全备份

xtrabackup --backup --user=用户 --password=密码 --target-dir=完全备份的目录

2、增量备份

xtrabackup --backup -u用户 -p密码 ?--target-dir=增量1备份的目录 ?--incremental-basedir=完全备份的目录 ?????????#第一次增量备份
xtrabackup --backup -u用户 -p密码 ?--target-dir=增量2备份的目录 ?--incremental-basedir=增量1备份的目录 ????????#第二次增量备份

3、差异备份

xtrabackup --backup -u用户 -p密码 ?--target-dir=差异备份的目录 ?--incremental-basedir=完全备份的目录 ??????

3.3.2 完全备份恢复

xtrabackup --prepare --target-dir=完全备份的目录
xtrabackup --copy-back --target-dir=完全备份的目录

3.3.3 增量备份恢复

xtrabackup --prepare --apply-log-only --target-dir=完全备份的目录
xtrabackup --prepare --apply-log-only --target-dir=完全备份的目录 --incremental-dir=增量1备份的目录
#将所有增量备份的数据都合并到完全备份的数据中
xtrabackup --prepare --target-dir=完全备份的目录 --incremental-dir=增量1备份的目录 ??????
#再使用合并后的完全备份数据进行恢复
xtrabackup --copy-back --target-dir=完全备份的目录 ?????