数据库备份恢复

1. 备份恢复概述

1.1 备份的重要性

数据库备份是系统架构中的生命线，是数据安全的最后一道防线。备份能够有效防止设备故障以及人为误操作带来的数据丢失。

备份的核心价值：

数据保护：防止数据丢失
灾难恢复：快速恢复业务
合规要求：满足法律法规要求
业务连续性：保证业务不中断

1.2 备份与冗余的区别

备份（Backup）

定义：将数据文件保存在远端，能够有效防止设备故障以及人为误操作带来的数据丢失。

特点：

数据副本存储在不同位置
可以恢复到历史时间点
防止人为误操作
需要定期验证

冗余（Redundancy）

定义：数据有多份冗余，但不等于备份，只能防止机械故障丢失的数据。

特点：

数据副本存储在相同位置或同步位置
主要用于高可用性
不能防止人为误操作
不能恢复到历史时间点

示例：

主备模式：主从复制，实时同步
数据库集群：多节点冗余
RAID阵列：磁盘冗余

关键区别：

冗余：防止硬件故障
备份：防止所有类型的故障，包括人为错误

1.3 备份必须考虑的因素

1. 数据的一致性

重要性：备份的数据必须保证一致性，否则恢复后数据可能损坏。

实现方式：

逻辑备份：使用事务保证一致性
物理备份：在一致的时间点进行备份
锁机制：备份期间锁定数据

2. 服务的可用性

重要性：备份过程不应该影响服务的正常运行。

实现方式：

热备份：不停止服务进行备份
在线备份：备份期间服务可用
非阻塞备份：使用快照或增量备份

1.4 数据库备份方式

1. 逻辑备份

定义：备份DDL、DML、DCL语句，将数据转换为SQL语句。

特点：

适用于中小型数据库（1G-10G）
效率相对低下
可读性强，便于查看和修改
跨平台兼容性好

工具：

mysqldump：MySQL官方工具
mydumper：多线程备份工具

适用场景：

数据量较小的数据库
需要跨平台迁移
需要查看备份内容
单表或单库备份

2. 物理备份

定义：直接复制数据库文件，包括数据文件、日志文件等。

特点：

适用于大型数据库（10G、100G、500G+）
效率相对较高
备份文件较大
恢复速度快

工具：

xtrabackup：Percona开源工具（推荐）
innobackupex：xtrabackup的封装
cp/tar：系统命令
LVM snapshot：逻辑卷快照

适用场景：

数据量大的数据库
需要快速备份和恢复
生产环境备份
全库备份

1.5 数据库备份模式

1. 完全备份（Full Backup）

定义：备份所有数据，包括数据库、表、数据等。

特点：

备份完整，恢复简单
备份时间长，占用空间大
适合定期全量备份

频率：通常每周或每月一次

2. 增量备份（Incremental Backup）

定义：只备份自上次备份以来发生变化的数据。

特点：

备份时间短，占用空间小
需要基于前一次备份
恢复时需要依次应用所有增量备份

频率：通常每天一次

3. 差异备份（Differential Backup）

定义：备份自上次完全备份以来发生变化的数据。

特点：

备份时间中等，占用空间中等
基于完全备份，不依赖增量备份
恢复时只需要完全备份+最新的差异备份

频率：通常每天一次

1.6 备份内容

通常数据库备份需要包含：

数据文件：数据库的实际数据
binlog日志文件：二进制日志，用于增量恢复
my.cnf配置文件：MySQL配置文件
权限信息：用户和权限配置

重要原则：

所有的数据库备份数据都应放在非数据库本地
建议备份多份，存放在不同位置
定期验证备份的可用性

1.7 备份验证的重要性

仅备份是没有任何意义的，需要在测试环境中做日常恢复演练，测试备份的可用性。恢复比备份更加重要。

验证内容：

备份文件完整性
恢复流程正确性
恢复时间目标（RTO）
恢复点目标（RPO）

验证频率：

定期恢复演练（每月或每季度）
备份后立即验证
重大变更后验证

2. MySQL逻辑备份与恢复

2.1 逻辑备份工具：mysqldump

MySQL自带的逻辑备份工具mysqldump，可以保证数据备份一致性，以及服务可用性。

核心特性：

一致性保证：使用事务保证数据一致性
服务可用性：备份期间服务正常运行
跨平台：备份文件可在不同平台使用
灵活性强：可以备份单个表、单个库或所有库

2.2 mysqldump命令使用方式

基本语法

1	mysqldump -h 服务器 -u 用户名 -p密码数据库名 > 备份文件.sql

常用参数说明

参数	说明
`-A, --all-databases`	备份所有库
`-B, --databases`	备份多个库
`--single-transaction`	InnoDB一致性，服务可用性
`--master-data=1\|2`	记录binlog日志位置与文件名，追加至备份文件中
`--triggers`	备份触发器
`-F, --flush-logs`	备份之前刷新日志
`-E, --events`	备份事件调度器代码
`-R, --routines`	备份存储过程和存储函数

重要参数详解：

–single-transaction

1	--single-transaction

作用：

在备份开始时启动一个事务
保证备份数据的一致性
只适用于InnoDB存储引擎
备份期间服务可用

原理：

使用START TRANSACTION WITH CONSISTENT SNAPSHOT
在事务中读取数据，保证一致性视图

–master-data

1 2	--master-data=1 # 以注释形式记录 --master-data=2 # 以注释形式记录（推荐）

作用：

记录备份时的binlog位置
用于增量恢复
=1：以CHANGE MASTER命令形式记录
=2：以注释形式记录（推荐）

输出示例：

1	-- CHANGE MASTER TO MASTER_LOG_FILE='bgx.000003', MASTER_LOG_POS=154;

–flush-logs

1	-F, --flush-logs

作用：

备份前刷新日志
生成新的binlog文件
便于增量备份管理

2.3 数据库完整备份与恢复

2.3.1 使用mysqldump完整备份

创建备份目录

1 2	# 创建备份目录 [root@sql ~]# mkdir -p /backup/mysql

执行完整备份

# 本地备份
[root@sql ~]# mysqldump -uroot -p'123' \
    --all-databases \
    --single-transaction \
    --master-data=1 \
    --flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql

命令说明：

--all-databases：备份所有数据库
--single-transaction：保证一致性
--master-data=1：记录binlog位置
--flush-logs：刷新日志
使用时间戳命名备份文件

备份文件命名：

1	2018-05-1203-mysql-all.sql # 2018年5月12日03时的备份

查看binlog日志信息

1
2
3

# 查看备份文件中记录的binlog位置
[root@sql ~]# sed -n "22p" /backup/mysql/2018-05-1203-mysql-all.sql 
CHANGE MASTER TO MASTER_LOG_FILE='bgx.000003', MASTER_LOG_POS=154;

说明：

第22行记录了binlog位置信息
用于后续增量恢复

2.3.2 数据库完整恢复流程

1. 停止数据库

1 2	# 停止MySQL服务 [root@sql ~]# systemctl stop mysqld

2. 删除破损数据库

1 2	# 删除数据目录（模拟数据损坏） [root@sql ~]# rm -rf /var/lib/mysql/*

警告：此操作会删除所有数据，请谨慎操作！

3. 重新初始化数据库

1 2	# 启动MySQL（会自动初始化） [root@sql ~]# systemctl start mysqld

4. 重置密码

启动后需要修改默认密码，否则无法恢复。

# 查看临时密码
[root@sql ~]# grep "password" /var/log/mysqld.log
khy_=i512g=F

# 修改密码
[root@sql ~]# mysqladmin -uroot -p'khy_=i512g=F' password "Bgx123.com"

5. 恢复数据

1 2	# 导入备份数据 [root@sql ~]# mysql -uroot -p'Bgx123.com' < /backup/mysql/2018-05-1203-mysql-all.sql

6. 刷新授权

1
2
3

# 使用恢复后的密码登录
[root@sql ~]# mysql -uroot -p'Bgx123.com'
mysql>

7. 建议：暂停binlog日志记录

在恢复备份时，建议暂停binlog日志记录，避免影响增量恢复。

方法一：修改备份文件

1 2	# 在备份文件中添加关闭binlog的语句 [root@sql mysql]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2018-05-1205-mysql-all.sql

方法二：在MySQL控制台临时修改

-- 临时关闭binlog记录
mysql> SET sql_log_bin=0;

-- 执行恢复
mysql> SOURCE /backup/mysql/2018-05-1203-mysql-all.sql;

-- 恢复后可以重新开启
mysql> SET sql_log_bin=1;

原因：

恢复备份时会产生新的binlog
可能影响后续增量恢复
关闭binlog可以避免这个问题

2.4 数据库增量备份与恢复

2.4.1 实战案例1：完整备份+增量备份

场景：新建数据表，进行了全量备份，随着时间推移，数据库突然崩溃。

1. 环境准备

-- 备份之前的数据
mysql> CREATE DATABASE bgx;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE bgx.t1 (id INT, name VARCHAR(20));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO bgx.t1 VALUES (1,"bgx1");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO bgx.t1 VALUES (2,"bgx2");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM bgx.t1;
+------+------+
| id   | name |
+------+------+
|    1 | bgx1 |
|    2 | bgx2 |
+------+------+
2 rows in set (0.00 sec)

2. 执行完整备份

# 基于当前状态备份
[root@sql ~]# mysqldump -uroot -p'123' \
    --all-databases \
    --single-transaction \
    --master-data=1 \
    --flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql

3. 模拟数据插入操作

-- 备份后继续插入数据
mysql> INSERT INTO bgx.t1 VALUES (3,"bgx3");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO bgx.t1 VALUES (5,"tt");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM bgx.t1;
+------+------+
| id   | name |
+------+------+
|    1 | bgx1 |
|    2 | bgx2 |
|    3 | bgx3 |
|    5 | tt   |
+------+------+
4 rows in set (0.00 sec)

4. 模拟数据库故障

# 停止数据库
[root@sql ~]# systemctl stop mysqld

# 删除数据（模拟故障）
[root@sql ~]# rm -rf /var/lib/mysql/*

# 重新启动（会自动初始化）
[root@sql ~]# systemctl start mysqld

# 修改默认密码
[root@sql ~]# mysqladmin -uroot -p'RA+:>Xu&.6K_' password "Bgx123.com"

5. 恢复全备数据

# 关闭binlog记录
[root@sql mysql]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2018-05-1205-mysql-all.sql

# 恢复全备
[root@sql ~]# mysql -uroot -p'Bgx123.com' < /backup/mysql/2018-05-1205-mysql-all.sql

# 验证恢复结果
[root@sql mysql]# mysql -uroot -p'Bgx123.com' -e "SELECT * FROM bgx.t1;"
+------+------+
| id   | name |
+------+------+
|    1 | bgx1 |
|    2 | bgx2 |
+------+------+

说明：全备只恢复到备份时的状态，备份后的数据需要从binlog恢复。

6. 恢复增量数据

# 查看备份后binlog起始位置点
[root@sql ~]# sed -n '22p' /backup/mysql/2018-05-1205-mysql-all.sql
CHANGE MASTER TO MASTER_LOG_FILE='bgx.000061', MASTER_LOG_POS=154;

# 查找结束位置点，进行恢复
[root@sql bin]# mysqlbinlog --start-position=1490 --stop-position=1652 \
    bgx.000001 | mysql -uroot -p'Bgx123.com'

说明：

--start-position：从备份时的binlog位置开始
--stop-position：到故障发生时的位置
使用mysqlbinlog将binlog转换为SQL并执行

2.4.2 实战案例2：误删除恢复

场景：运维人员误删除了数据库或某一张表。

1. 模拟环境准备

-- 创建数据库和表
mysql> CREATE DATABASE bgxdb;
Query OK, 1 row affected (0.00 sec)

mysql> USE bgxdb;
Database changed

mysql> CREATE TABLE t1 (id INT, name VARCHAR(20));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (1,"ccr");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (2,"tfr");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+------+
| id   | name |
+------+------+
|    1 | ccr  |
|    2 | tfr  |
+------+------+
2 rows in set (0.00 sec)

2. 使用mysqldump进行全备

# 执行全备
[root@sql ~]# mysqldump -uroot -p'Bgx123.com' \
    --all-databases \
    --single-transaction \
    --master-data=1 \
    --flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql

3. 再次插入一些数据

-- 继续插入数据
mysql> INSERT INTO t1 VALUES 
    (3,'trl'),
    (4,'zx'),
    (5,'wq'),
    (6,'tj'),
    (7,'gwt');
Query OK, 5 rows affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+------+
| id   | name |
+------+------+
|    1 | ccr  |
|    2 | tfr  |
|    3 | trl  |
|    4 | zx   |
|    5 | wq   |
|    6 | tj   |
|    7 | gwt  |
+------+------+
7 rows in set (0.00 sec)

4. 模拟故障

-- 误删除数据
mysql> DELETE FROM t1 WHERE id ='2';
Query OK, 1 row affected (0.00 sec)

-- 误删除数据库
mysql> DROP DATABASE bgxdb;
Query OK, 0 rows affected (0.00 sec)

5. 恢复全量备份的数据

# 关闭binlog记录
[root@sql ~]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2018-05-1215-mysql-all.sql

# 恢复全备
[root@sql ~]# mysql -uroot -p'Bgx123.com' < /backup/mysql/2018-05-1215-mysql-all.sql

# 验证恢复结果
[root@sql ~]# mysql -uroot -p'Bgx123.com' -e "SELECT * FROM bgxdb.t1;"
+------+------+
| id   | name |
+------+------+
|    1 | ccr  |
|    2 | tfr  |
+------+------+

说明：全备恢复到备份时的状态，包含id=2的记录。

6. 通过mysqlbinlog导出SQL语句

1
2
3

# 针对全量备份后仅产生1个binlog文件的方式
[root@sql bin]# mysqlbinlog --start-position=154 \
    --base64-output="decode-rows" -v bgx.000002 > db.sql

参数说明：

--base64-output="decode-rows"：解码行事件
-v：详细模式，显示SQL语句
> db.sql：输出到文件

如果产生了多个binlog日志：

# 恢复多个binlog文件
mysqlbinlog --base64-output="decode-rows" -v mysql-bin.000021 >> 1.sql
mysqlbinlog --base64-output="decode-rows" -v mysql-bin.000022 >> 1.sql
mysqlbinlog --base64-output="decode-rows" -v mysql-bin.000023 >> 1.sql

7. 查看SQL文件，找到误操作语句

1 2	# 查看SQL文件，找到DROP和DELETE语句的位置 [root@sql ~]# grep -n "DROP\\|DELETE" db.sql

记录位置点：

找到误操作语句的起始位置
找到误操作语句的结束位置
跳过这些位置进行恢复

8. 使用mysqlbinlog进行恢复（跳过误操作）

# 恢复第一个区间（跳过DROP和DELETE）
[root@sql bin]# mysqlbinlog --start-position=154 --stop-position=1648 \
    bgx.000002 | mysql -uroot -p'Bgx123.com'

# 恢复第二个区间（跳过误操作后的数据）
[root@sql bin]# mysqlbinlog --start-position=1692 --stop-position=1723 \
    bgx.000002 | mysql -uroot -p'Bgx123.com'

# 验证恢复结果
[root@sql ~]# mysql -uroot -p'Bgx123.com' -e "SELECT * FROM bgxdb.t1;"
+------+------+
| id   | name |
+------+------+
|    1 | ccr  |
|    2 | tfr  |
|    3 | trl  |
|    4 | zx   |
|    5 | wq   |
|    6 | tj   |
|    7 | gwt  |
+------+------+

恢复成功：所有数据都已恢复，包括误删除的记录。

3. MySQL物理备份与恢复

3.1 Percona XtraBackup简介

XtraBackup是Percona公司开发的开源免费并支持MySQL数据库热备份的软件。

核心特性：

对InnoDB和XtraDB存储引擎的数据库非阻塞地备份
无需暂停服务备份MySQL
支持MySQL增量备份
支持MySQL差异备份
备份MySQL不增加服务器负载
创建replication更加便捷

适用场景：

大型数据库备份（10G+）
生产环境备份
需要快速备份和恢复
需要增量备份

3.2 安装XtraBackup

官方下载地址

1
2
3

# 安装XtraBackup
[root@sql ~]# yum install -y \
    https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.11/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.11-1.el7.x86_64.rpm

注意：根据MySQL版本选择合适的XtraBackup版本。

3.3 数据库完整备份与恢复

3.3.1 完整备份流程

1. 创建备份目录

1 2	# 创建备份目录 [root@sql ~]# mkdir /xtrabackup/

2. 执行全备操作

1 2	# 执行全备 [root@sql ~]# innobackupex --user=root --password="Bgx123.com" /xtrabackup/

命令说明：

innobackupex：XtraBackup的封装工具
--user：数据库用户名
--password：数据库密码
/xtrabackup/：备份目录

3. 检查备份结果

# 查看备份目录
[root@sql ~]# ls /xtrabackup/
2018-05-08_04-34-06

# 查看binlog信息
[root@sql ~]# cat /xtrabackup/2018-05-08_04-34-06/xtrabackup_binlog_info 
bgx.000002      4447

备份目录说明：

目录名格式：YYYY-MM-DD_HH-MM-SS
包含所有数据库文件
包含binlog位置信息

3.3.2 完全备份恢复流程

1. 停止数据库

1 2	# 停止MySQL服务 [root@sql ~]# systemctl stop mysqld

2. 清理环境

1 2	# 删除数据目录 [root@sql ~]# rm -rf /var/lib/mysql/*

3. 重演回滚

1 2	# 应用日志，准备恢复 [root@sql ~]# innobackupex --apply-log /xtrabackup/2018-05-08_04-34-06/

说明：

--apply-log：应用事务日志
将备份文件恢复到一致状态

4. 恢复数据

1 2	# 复制备份文件到数据目录 [root@sql ~]# innobackupex --copy-back /xtrabackup/2018-05-08_04-34-06/

说明：

--copy-back：复制备份文件
自动恢复到数据目录

5. 修改权限

1 2	# 修改文件所有者 [root@sql ~]# chown -R mysql.mysql /var/lib/mysql

6. 启动数据库

# 启动MySQL服务
[root@sql ~]# systemctl start mysqld

# 验证恢复
[root@sql ~]# mysql -uroot -pBgx123.com
mysql>

3.4 数据库增量备份与恢复

3.4.1 增量备份原理

增量备份特点：

每次需要基于前一次的备份
只备份自上次备份以来变化的数据
恢复时需要依次应用所有增量备份

3.4.2 增量备份实战

1. 准备数据（周三）

-- 创建数据库和表
mysql> CREATE DATABASE bgxdb;
mysql> USE bgxdb;
mysql> CREATE TABLE t1(id INT, name VARCHAR(20));
mysql> INSERT INTO t1 VALUES (3,'day3');
mysql> SELECT * FROM t1;
+------+------+
| id   | name |
+------+------+
|    3 | day3 |
+------+------+

2. 使用物理全备（周三）

1 2	# 执行全备 [root@sql ~]# innobackupex --username=root --password=Bgx123.com /xtrabackup/

3. 后续每天进行增量备份

周四增量备份：

-- 插入数据
mysql> INSERT INTO bgxdb.t1 VALUES (4,'day4');
mysql> SELECT * FROM t1;
+------+------+
| id   | name |
+------+------+
|    3 | day3 |
|    4 | day4 |
+------+------+

# 基于周三全备进行增量备份
[root@sql ~]# innobackupex --user=root --password='Bgx123.com' \
    --incremental /xtrabackup/ \
    --incremental-basedir=/xtrabackup/2018-05-08_04-51-04/

# 查看binlog信息
[root@sql ~]# cat /xtrabackup/2018-05-09_00-00-28/xtrabackup_binlog_info 
bgx.000003      1039

周五增量备份：

-- 插入数据
mysql> INSERT INTO bgxdb.t1 VALUES (5,'day5');
mysql> SELECT * FROM bgxdb.t1;
+------+------+
| id   | name |
+------+------+
|    3 | day3 |
|    4 | day4 |
|    5 | day5 |
+------+------+

# 基于周四增量备份进行增量备份
[root@sql ~]# innobackupex --user=root --password='Bgx123.com' \
    --incremental /xtrabackup/ \
    --incremental-basedir=/xtrabackup/2018-05-09_00-00-28/

# 查看binlog信息
[root@sql ~]# cat /xtrabackup/2018-05-10_00-01-50/xtrabackup_binlog_info 
bgx.000003      1297

4. 模拟故障

# 停止数据库
[root@sql ~]# systemctl stop mysqld

# 清理环境
[root@sql ~]# rm -rf /var/lib/mysql/*

5. 依次重演（恢复增量备份）

恢复周三全备数据：

1
2
3

# 应用日志（使用--redo-only）
[root@sql ~]# innobackupex --apply-log \
    --redo-only /xtrabackup/2018-05-08_04-51-04/

恢复周四的增量数据：

# 将周四增量应用到全备
[root@sql ~]# innobackupex --apply-log \
    --redo-only /xtrabackup/2018-05-08_04-51-04/ \
    --incremental-dir=/xtrabackup/2018-05-09_00-00-28

恢复周五的增量数据：

# 将周五增量应用到全备（最后一个不需要--redo-only）
[root@sql ~]# innobackupex --apply-log \
    /xtrabackup/2018-05-08_04-51-04/ \
    --incremental-dir=/xtrabackup/2018-05-10_00-23-24

说明：

--redo-only：只应用redo日志，不提交
最后一个增量备份不需要--redo-only

6. 执行回滚

# 恢复数据
[root@sql ~]# innobackupex --copy-back /xtrabackup/2018-05-08_04-51-04/

# 授权
[root@sql ~]# chown -R mysql.mysql /var/lib/mysql

# 启动数据库
[root@sql ~]# systemctl start mysqld

# 查询数据
[root@sql ~]# mysql -uroot -pBgx123.com -e "SELECT * FROM bgxdb.t1;"
+------+------+
| id   | name |
+------+------+
|    3 | day3 |
|    4 | day4 |
|    5 | day5 |
+------+------+

恢复成功：所有增量数据都已恢复。

3.5 数据库差异备份与恢复

3.5.1 差异备份原理

差异备份特点：

基于完全备份，不依赖增量备份
备份自上次完全备份以来变化的数据
恢复时只需要完全备份+最新的差异备份

3.5.2 差异备份实战

1. 完整备份（周三）

-- 创建数据
mysql> CREATE DATABASE bgxdb; 
mysql> USE bgxdb; 
mysql> CREATE TABLE t2(id INT); 
mysql> INSERT INTO t2 VALUES(3); 
mysql> SELECT * FROM t2;
+------+
| id   |
+------+
|    3 |
+------+

# 执行全备
[root@sql ~]# innobackupex --user=root --password='Bgx123.com' /xtrabackup/

# 查看备份信息
[root@sql ~]# cat /xtrabackup/2018-05-08_09-21-06/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 5357964
last_lsn = 5362222
compact = 0
recover_binlog_info = 0

2. 差异备份：周四 -> 周六

周四差异备份：

1 2	-- 插入数据 mysql> INSERT INTO bgxdb.t2 VALUES(4);

# 差异备份（以完整备份为准）
[root@sql ~]# innobackupex --user=root --password='Bgx123.com' \
    --incremental /xtrabackup/ \
    --incremental-basedir=/xtrabackup/2018-05-08_09-21-06/

周五差异备份：

1 2	-- 插入数据 mysql> INSERT INTO bgxdb.t2 VALUES(5);

# 差异备份（以完整备份为准）
[root@sql ~]# innobackupex --user=root --password=Bgx123.com \
    --incremental /xtrabackup/ \
    --incremental-basedir=/xtrabackup/2018-05-08_09-21-06/

周六差异备份：

1 2	-- 插入数据 mysql> INSERT INTO bgxdb.t2 VALUES(6);

# 差异备份（以完整备份为准）
[root@sql ~]# innobackupex --user=root --password=Bgx123.com \
    --incremental /xtrabackup/ \
    --incremental-basedir=/xtrabackup/2018-05-08_09-21-06/

3. 模拟备份后错误操作

1
2
3

-- 误操作
mysql> CREATE DATABASE Linux;
mysql> DROP DATABASE Linux;

4. 差异备份恢复流程

# 1.停止数据库
[root@sql ~]# systemctl stop mysqld

# 2.清理环境
[root@sql ~]# rm -rf /var/lib/mysql/*

# 3.重演回滚，回滚全备
[root@sql ~]# innobackupex --apply-log --redo-only /xtrabackup/2018-05-08_09-21-06/

# 4.重演回滚，将差异备份应用至完整备份上（只需要最新的差异备份）
[root@sql ~]# innobackupex --apply-log --redo-only /xtrabackup/2018-05-08_09-21-06/ \
    --incremental-dir=/xtrabackup/2018-05-11_00-01-27/

# 5.应用数据
[root@sql ~]# innobackupex --copy-back /xtrabackup/2018-05-08_09-21-06/

# 6.修改权限
[root@sql ~]# chown -R mysql.mysql /var/lib/mysql

# 7.启动数据库
[root@sql ~]# systemctl start mysqld

# 验证恢复
[root@sql ~]# mysql -uroot -pBgx123.com -e "SELECT * FROM bgxdb.t2;"
+------+
| id   |
+------+
|    3 |
|    4 |
|    5 |
|    6 |
+------+

5. binlog恢复删除的库

# 查看binlog位置
[root@sql ~]# cat /xtrabackup/2018-05-11_00-01-27/xtrabackup_binlog_info 
bgx.000001      1490

# 导出对应的sql，找到drop语句位置
[root@sql ~]# mysqlbinlog --start-position=1490 \
    --base64-output="decode-rows" \
    -v bgx.000001 bgx.000002 > db.sql

# 针对起始点与结束点位置恢复（跳过DROP语句）
[root@sql bin]# mysqlbinlog --start-position=1490 \
    --stop-position=1652 bgx.000001 | mysql -uroot -p'Bgx123.com'

4. 简单命令进行物理备份

4.1 使用tar命令备份

使用tar命令打包来进行物理备份数据库系统。

特点：

备份期间，服务不可用
简单直接
适合小型数据库
需要停止服务

4.2 备份操作过程

# 1.停止数据库
[root@sql ~]# systemctl stop mysqld 

# 2.创建备份目录
[root@sql ~]# mkdir /backup

# 3.tar备份数据
[root@sql ~]# tar -cf /backup/`date +%F`-mysql-all.tar /var/lib/mysql

注意：备份文件应该复制到其它服务器或存储上。

4.3 恢复操作过程

# 1.停止数据库
[root@sql ~]# systemctl stop mysqld

# 2.清理环境
[root@sql ~]# rm -rf /var/lib/mysql/*

# 3.导入备份数据
[root@sql ~]# tar -xf /backup/2018-05-08-mysql-all.tar -C / 

# 4.启动数据库
[root@sql ~]# systemctl start mysqld

# 5.binlog恢复（如果需要）

5. 生产备份思路与实战

5.1 备份方式选择

逻辑备份（mysqldump）

适用场景：

数据量：1G-10G
保证一致性，服务可用性
只能全备或指定某一个数据库备份
效率不是很高，将所有的数据转成SQL语句（DDL、DML、DCL）

优势：

可读性强
跨平台
灵活性强

劣势：

备份时间长
恢复时间长
占用空间大

物理备份（xtrabackup）

适用场景：

数据量：10G、100G、500G+
保证一致性，服务可用性
支持增量、差异、全备

优势：

备份速度快
恢复速度快
支持增量备份

劣势：

备份文件大
需要额外工具

5.2 生产备份策略

5.3 备份注意事项

1. binlog日志非常重要

binlog是增量恢复的关键
必须开启binlog
定期备份binlog文件
不要随意删除binlog

2. 备份时不要记录binlog日志

-- 备份前关闭binlog记录
SET sql_log_bin=0;
-- 执行备份恢复
-- 备份后开启binlog记录
SET sql_log_bin=1;

3. 备份内容完整性

备份不单单只是数据文件，还包括：

数据文件：数据库的实际数据
binlog日志文件：二进制日志
my.cnf配置文件：MySQL配置
权限信息：用户和权限

5.4 备份验证

1. 定期恢复演练

每月或每季度进行一次完整恢复演练
验证备份的可用性
测试恢复流程
记录恢复时间

2. 备份文件验证

# 检查备份文件完整性
ls -lh /backup/mysql/
ls -lh /xtrabackup/

# 检查备份文件权限
ls -l /backup/mysql/

3. 备份监控

监控备份任务执行状态
监控备份文件大小
监控备份存储空间
设置备份失败告警

6. 总结

6.1 备份恢复的核心价值

数据保护：防止数据丢失
灾难恢复：快速恢复业务
业务连续性：保证业务不中断
合规要求：满足法律法规要求

6.2 备份策略选择

小型数据库：逻辑备份（mysqldump）
大型数据库：物理备份（xtrabackup）
生产环境：物理备份+增量备份
开发环境：逻辑备份

6.3 架构师建议

备份优先：备份是数据安全的生命线
定期验证：定期进行恢复演练
多重备份：备份多份，存放在不同位置
监控告警：监控备份任务，及时发现问题
文档维护：记录备份恢复流程和策略

6.4 备份检查清单

备份策略是否合理
备份任务是否正常执行
备份文件是否完整
是否定期进行恢复演练
备份存储空间是否充足
备份文件是否异地存储
binlog是否正常备份
配置文件是否备份

相关文章：

第433集数据库备份恢复

数据库备份恢复

1. 备份恢复概述

1.1 备份的重要性

1.2 备份与冗余的区别

备份（Backup）

冗余（Redundancy）

1.3 备份必须考虑的因素

1. 数据的一致性

2. 服务的可用性

1.4 数据库备份方式

1. 逻辑备份

2. 物理备份

1.5 数据库备份模式

1. 完全备份（Full Backup）

2. 增量备份（Incremental Backup）

3. 差异备份（Differential Backup）

1.6 备份内容

1.7 备份验证的重要性

2. MySQL逻辑备份与恢复

2.1 逻辑备份工具：mysqldump

2.2 mysqldump命令使用方式

基本语法

常用参数说明

–single-transaction

–master-data

–flush-logs

2.3 数据库完整备份与恢复

2.3.1 使用mysqldump完整备份

创建备份目录

执行完整备份

查看binlog日志信息

2.3.2 数据库完整恢复流程

1. 停止数据库

2. 删除破损数据库

3. 重新初始化数据库

4. 重置密码

5. 恢复数据

6. 刷新授权

7. 建议：暂停binlog日志记录

2.4 数据库增量备份与恢复

2.4.1 实战案例1：完整备份+增量备份

1. 环境准备

2. 执行完整备份

3. 模拟数据插入操作

4. 模拟数据库故障

5. 恢复全备数据

6. 恢复增量数据

2.4.2 实战案例2：误删除恢复

1. 模拟环境准备

2. 使用mysqldump进行全备

3. 再次插入一些数据

4. 模拟故障

5. 恢复全量备份的数据

6. 通过mysqlbinlog导出SQL语句

7. 查看SQL文件，找到误操作语句

8. 使用mysqlbinlog进行恢复（跳过误操作）

3. MySQL物理备份与恢复

3.1 Percona XtraBackup简介

3.2 安装XtraBackup

官方下载地址

3.3 数据库完整备份与恢复

3.3.1 完整备份流程

1. 创建备份目录

2. 执行全备操作

3. 检查备份结果

3.3.2 完全备份恢复流程

1. 停止数据库

2. 清理环境

3. 重演回滚

4. 恢复数据

5. 修改权限

6. 启动数据库

3.4 数据库增量备份与恢复

3.4.1 增量备份原理

3.4.2 增量备份实战

1. 准备数据（周三）

2. 使用物理全备（周三）

3. 后续每天进行增量备份

4. 模拟故障