MySQL备份恢复

本文主要是介绍MySQL备份恢复，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

MySQL备份恢复

前言

我们在什么情况下要用到备份恢复呢？ 
在MySQL数据损坏了，我们要用到备份恢复。

MySQL数据损坏的两种类型：
1、物理损坏：
	磁盘损坏： 硬件，磁道损坏，dd，格式化
	文件损坏： 数据文件损坏，redo损坏
2、逻辑损坏：
	drop
	delete
	truncate
	update

一、工作职责

做为运维工程师，我们面对一些突发的情况要快速的去恢复数据
1、设计备份、容灾策略
1.1 备份策略：
	备份工具的选择
	备份周期设计
	备份监控方法
1.2容灾策略：
	备份：	增量，全量，日志，演示主从
	架构：	高可用，演示从库，灾备库
2、定期备份、容灾检查
	每周 
3、定期的故障恢复演练
4、数据损坏时的恢复
5、数据迁移的工作

二、常用工具

2.1 逻辑备份方式
mysqldump		*****
replication
mydumper
load data in file

2.2 物理备份方式
MySQL Enterprise Backup(企业版)
Percona Xtrabackup (PBK,XBK) *****

三、mysqldump （MDP）应用

3.1 介绍

逻辑备份工具。备份的是SQL语句。

选择场景：
	优点： 可读性比较强，压缩比，节省空间，不需要下载安装。
	缺点： 备份时间相比较长，恢复时间长。
	数据量较少，建议mysqldump。100G以内。
	分布式架构，数据量较大时候，可以采用分布式备份，也可以选择mysqldump。

3.2 备份方式及参数：

3.2.1 InnoDB表
InnoDB可以采取快照备份的方式。
开启一个独立的事务，获取当前最新的一致性快照。
将快照数据，放在临时表中，转换成SQL（create datebase ，create table ，insert），保存SQL文件中。

3.2.2 非InnoDB表
需要锁表备份，触发FTWRL，全局锁表。转换成SQL（create datebase ，create table ，insert）保存到SQL文件中

3.3 mysqldump的核心参数
3.3.1 连接参数
-u	指定用户
-p	指定密码
-h	指定远程连接的IP地址
-s	指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock

3.3.2 备份参数
-A	导出全部数据库
[root@mysql-106 ~]# mkdir  /mysql/data/backup
[root@mysql-106 ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock -A >/mysql/data/backup/text.sql
[root@mysql-106 ~]# vim /mysql/data/backup/text.sql

-B 备份单库或者多个库
[root@mysql-106 ~]# mysqldump -uroot -p123 -B gtid_text3 world >/mysql/data/backup/text2.sql
[root@mysql-106 ~]# vim /mysql/data/backup/text2.sql

[root@mysql-106 ~]# cat .my.cnf 
[mysql]
user=root
password=123
[mysqldump]
user=root
password=123

单表和多表的库
[root@mysql-106 ~]# mysqldump -uroot -p123  world city country >/mysql/data/backup/table.sql

请添加图片描述

面试题：以下两条命令的备份结果和区别？
mysqldump -uroot -p123 -B  world >/mysql/data/backup/world1.sql
应用时，world库不存在，自动创建
create database world;
use world;

mysqldump -uroot -p123 world >/mysql/data/backup/world2.sql
应用时，world库不存在，需要手动创建，并且use到world库下再恢复。

vimdiff /mysql/data/backup/world1.sql /mysql/data/backup/world2.sql

请添加图片描述

3.3 备份高级参数`--master-data=2`

3.3.3 备份高级参数
--master-data=2
--single-transaction
-R -E --triggers
--max_allowed_packet=64M

3.3.3.1 --master-data=2
场景：
每周日 23:00 全备，周1-6 binlog备份。所有备份是完整的。
周三时，有一个核心运维人员进行删库操作。 
那么你给怎么办？？？

解决思路：
恢复全备 + 所有需要的binlog恢复
痛点：	binlog的截取
起点查找比较困难：
方法一：	备份开始时，切割日志。 -F
方法二：	备份开始，自动记录日志文件信息 --master-data=2	
终点：	drop之前的位置点

--master-data=2
功能：
	1. 备份时自动记录binlog信息
	2. 自动锁表和解锁
	3. 配合single transction 可以减少锁表时间
使用演示：
# --master-data=2   是加注释的（建议使用）
mysqldump -uroot -p123 -A --master-data=2 >/mysql/data/backup/full.sql

# --master-data=1   不加注释的
mysqldump -uroot -p123 -A --master-data=1 >/mysql/data/backup/full2.sql
# 使用vimdiff可以看出
vimdiff /mysql/data/backup/full.sql /mysql/data/backup/full2.sql

请添加图片描述

3.3.3.2 `--single-transaction`

3.3.3.2 --single-transaction
须知：
- 生产环境必须要加的，不加这个参数就是全局锁表了，备份恢很慢，会影响我们其他事物的并发

功能：
	1. 对于InnoDB引擎表备份是，开启一个独立事务，获取一致性快照，进行备份
	2. 也可以理解为是热备，所谓的热备就是能减少对数据库的影响

语句演示：
mysqldump -uroot -p123 -A --master-data=2 --single-transaction >/mysql/data/backup/full3.sql

3.3.3.3 `-R -E --triggers`

3.3.3.3 -R -E --triggers
须知：
	- 环境中必备参数

功能：
-R	在备份过程中要一起备份存储过程和函数
-E	备份事件
--triggers	备份触发器

语句演示：
mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers >/mysql/data/backup/full4.sql

3.3.3.4 `--max_allowed_packet=64M`

请添加图片描述

MySQL会根据配置文件会限制server接受的数据包的大小。如果写入大数据时，因为默认的配置太小，插入和更新操作会因为 max_allowed_packet 参数限制，而导致失败。

查看传输数据包大小
mysql> select @@max_allowed_packet;

语句演示：
mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers  --max_allowed_packet=64M >/mysql/data/backup/full5.sql

请添加图片描述

故障恢复案例

一、基于`mysqldump`+`binlog`故障案例

案例场景：
	基础环境: Centos 7.6 +mysql 5.7.28 ,LNMP网站业务，数据量100G，每天增长5-10M数据
	备份策略: 使用mysqldump每天进行全备,binlog实时备份。
	故障模拟: 周三上午10点数据故障，核心业务库被误删。

恢复思路:
	1、挂维护页。
	2、找测试库。
	3、恢复周二全备。
	4、截取周二全备 ----> 周三上午10点误删除之前的binlog，并恢复。
	5、测试业务功能正常
	6、恢复业务：
		方案一： 故障库导回到源生产。
		方案二： 直接用测试库称当生产，先跑着。

模拟数据损坏及恢复：
1、模拟原始数据
	create database mdp charset utf8mb4;
	use mdp;
	create table t1 (id int);
	begin;
	insert into t1 values(1),(2),(3),(4),(5);
	commit;

2、模拟周二晚上的全备
mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers  --max_allowed_packet=64M >/mysql/data/backup/full_`date +%F`.sql

3、模拟周三白天数据变化
	use mdp;
	create table t2 (id int);
	begin;
	insert into t1 values(1),(2),(3);
	commit;

4、删库
	drop database mdp;

5、开始恢复
5.1 检查全备
[root@mysql-106 backup]# vim full_2021-07-22.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin_log.000013', MASTER_LOG_POS=892;

5.2 恢复全备
mysql> set sql_log_bin=0;
mysql> source /mysql/data/backup/full_2021-07-22.sql

5.3 截取binlog
起点：892
[root@mysql-106 backup]# grep "\-- CHANGE MASTER TO" /mysql/data/backup/full_2021-07-22.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin_log.000013', MASTER_LOG_POS=892;
终点：1328
mysql> show master status;
mysql> show binlog events in 'mysql_bin_log.000013';
	mysql_bin_log.000013 | 1328 | Query |   6 |   1417 | drop database mdp
截取：
mysqlbinlog --skip-gtids --start-position=892 --stop-position=1328 /mysql/logs/binlog/mysql_bin_log.000013 > /tmp/binlog_mdp.sql

5.4 恢复binlog
mysql> set sql_log_bin=0;
mysql> source /tmp/binlog_mdp.sql
mysql> set sql_log_bin=1;

gtid截取binlog（参考下图）：
起点：a96dbf35-e08c-11eb-a5c5-000c29feac00:75
[root@mysql-106 backup]# vim /mysql/data/backup/full_2021-07-22.sql
SET @@GLOBAL.GTID_PURGED='a96dbf35-e08c-11eb-a5c5-000c29feac00:1-74';
终点：a96dbf35-e08c-11eb-a5c5-000c29feac00:76
mysql> show binlog events in 'mysql_bin_log.000013';
| mysql_bin_log.000013 | 1280 | Gtid    |  6 |   1328 | SET @@SESSION.GTID_NEXT= 'a96dbf35-e08c-11eb-a5c5-000c29feac00:77' |
| mysql_bin_log.000013 | 1328 | Query  |  6 |  1417 | drop database mdp|

5.5 检查数据书否恢复
mysql> select * from mdp.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
8 rows in set (0.00 sec)

5.6 gtid恢复binlog
mysqlbinlog --skip-gtids --include-gitds='a96dbf35-e08c-11eb-a5c5-000c29feac00:75-76' --start-position=892 --stop-position=1328 /mysql/logs/binlog/mysql_bin_log.000013 > /tmp/binlog_mdp1.sql

mysql> set sql_log_bin=0;
mysql> source /tmp/binlog_mdp1.sql
mysql> set sql_log_bin=1;

5.7 检查数据书否恢复
mysql> select * from mdp.t1;

请添加图片描述

Percona XtraBackup

一、`Percona XtraBackup`工具的安装流程

1.1 安装软件源
[root@mysql-106 ~]# wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-7.repo

1.2 安装依赖包
[root@mysql-106 ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
1.3 安装所有Percona XtraBackup开头的软件包
[root@mysql-106 ~]# yum -y install percona-xtrabackup-*.rpm      
    
1.4 手动下载安装并安装Percona XtraBackup工具:
	最新版本"Percona XtraBackup 2.4"工具下载地址:
            https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/
	如下图所示，下载"percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm"软件包即可，而后基于yum命令安装
            wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm

1.5本地安装"Percona-XtraBackup-2.4.21"工具
	[root@mysql-106 ~]#ll -h percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm 
            -rw-r--r-- 1 root root 7.7M 11月 11 15:45 percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm

	[root@mysql-106 ~]# yum -y localinstall percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm

二、介绍

物理备份工具，拷贝数据文件

Percona XtraBackup备份时对InnoDB表和非InnoDB表的区别：
InnoDB表：
热备份：业务正常发生的时候，影响较小的备份的方式。
1. checkpoint,将已提交的数据页刷新到磁盘。记录一个LSN号码。
2. 拷贝InnoDB表相关的文件（ibdaral,frm,ibd..）
3. 备份期间生产新的数据变化的redo也会备份走。

非InnoDB:
温备份：锁表备份
1. FTWRL,触发全局锁
2. 拷贝非InnoDB表的数据。
3. 解锁

再次统计LSN，写入到专用文件。
记录二进制日志位置记录下来。
所有备份文件统一存放在一个目录下。

三、`XBK`应用

3.1 前提
1. 数据库启动
2. 能连上数据
vim /etc/my.cnf
[client]
socket=/tmp/mysql.sock
3. 默认会读取[mysqld]---->datadir=/mysql/data/mysql
4. 服务器端工具

3.2 全备 
# 自定生成目录名
[root@mysql-106 ~]# innobackupex --user=root --password=123 /mysql/logs/xbk
# 指定目录名
[root@mysql-106 ~]# innobackupex --user=root --password=123 --no-timestamp /mysql/logs/xbk/full_`date +%F`

3.3 查看备份结果
[root@mysql-106 ~]# ll /mysql/logs/xbk
total 8
drwxr-x--- 21 root root 4096 Jul 23 10:47 2021-07-23_10-47-52
drwxr-x--- 21 root root 4096 Jul 23 10:52 full_2021-07-23

xtrabackup_binlog_info	# 记录备份后binlog位置点信息，binlog的截取点。
xtrabackup_checkpoints  # 备份过程中LSN记录，方面做增量备份。

请添加图片描述

四、全备的恢复演练

破坏：
[root@mysql-106 mysql]# pkill mysqld
[root@mysql-106 mysql]# rm -rf /mysql/data/mysql/*

备份处理： prepare
redo 前滚，undo 回滚，模仿CSR过程。
[root@mysql-106 ~]# innobackupex --apply-log /mysql/logs/xbk/full_2021-07-23/

数据恢复：
[root@mysql-106 ~]# cp -a /mysql/logs/xbk/full_2021-07-23/* /mysql/data/mysql/
[root@mysql-106 ~]# chown -R mysql.mysql /mysql/data/mysql/
[root@mysql-106 ~]# systemctl start mysqld

五、`XBK`全备功能

innobackupex  ---> xtrabackup

前提：
	1. 用户授权
	2. socker ---> [client] ---> socker=/tmp/mysql.sock
	3. 数据有关 ---> [msyqld] ---> 相关参数

备份：
	innobackupex --user=root --password=123 --no-timestamp /mysql/logs/xbk/full_`date +%F`
	xtrabackup_checkpoints
	xtrabackup_binlog_info
	
恢复：
	1. prepare
	innobackupex --apply-log /mysql/logs/xbk/full_2021-07-23
	2. copy-back
	cp -r 备份 恢复位置点
	chown -R mysql.mysql /mysql/data/mysql/

六、`XBK`的增量备份恢复

说明：
备份时：
增量必须依赖与全备。
每次增量都是参照上次备份的LSN号码（xtrabackup_checkpoints），在此基础上变化的数据页，备份走并且，会将备份过程中生产新的变化的redo一并备份走。
恢复时：
将所有需要Inc备份，按照顺序合并到全备中
并且需要将每个备份进行prepare.

请添加图片描述

七、增量备份实践

7.1 基础环境模拟
mysql> create database xbk;
mysql> use xbk
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3)；
mysql> commit;
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

7.2 模拟周日全备
rm -rf /mysql/logs/xbk/*
innobackupex --user=root --password=123 --no-timestamp /mysql/logs/xbk/full_`date +%F`

7.3 模拟周一的数据变化
mysql> use xbk
mysql> create table t2(id int);
mysql> insert into t2 values(1),(2),(3);
mysql> commit;
mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

7.4 模拟周一晚上增量备份inc1
[root@mysql-106 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/mysql/logs/xbk/full_2021-07-23 /mysql/logs/xbk/inc1

--incremental			# 开启增量备份的开关
--incremental-basedir 	# 增量备份基目录 基于谁来做增量

7.5 模拟周二的数据变化
mysql> use xbk
mysql> create table t3(id int);
mysql> insert into t3 values(1),(2),(3),(4);
mysql> commit;
mysql> select * from t3;

7.6 模拟周二晚上增量备份
[root@mysql-106 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/mysql/logs/xbk/inc1 /mysql/logs/xbk/inc2

7.7 模拟周三的数据变化
use xbk
create table t4(id int);
insert into t4 values(1),(2),(3),(4);
commit;
select * from t4;

use xbk
create table t5(id int);
insert into t5 values(1),(2),(3),(4);
commit;
select * from t5;

7.8 搞破坏
[root@mysql-106 ~]# pkill mysqld
[root@mysql-106 ~]# rm -rf /mysql/data/mysql/*

7.9 确认备份完整性
7.10 xbk full + inc + binlog 备份恢复手段
7.10.0 恢复思路
	1. 合并，prepare所有Inc备份到全备
	2. 恢复数据，启动数据库
	3. 截取binlog日志
	4. 恢复日志

7.10.1 恢复过程
	1. 合并，prepare所有Inc备份到全备
	# 基础全备整理
	innobackupex --apply-log --redo-only /mysql/logs/xbk/full_2021-07-23
	# 合并，prepare inc1 到full
    innobackupex --apply-log --redo-only --incremental-dir=/mysql/logs/xbk/inc1 /mysql/logs/xbk/full_2021-07-23
    # 合并，prepare inc2 到 full
    innobackupex --apply-log --redo-only --incremental-dir=/mysql/logs/xbk/inc2 /mysql/logs/xbk/full_2021-07-23
    # 整体再次prepare整个备份
    innobackupex --apply-log /mysql/logs/xbk/full_2021-07-23

7.11 修复数据库
	# 授权
	chown -R mysql.mysql /mysql/logs/
	# 修改配置文件/etc/my.cnf 的以下内容，启动数据库
	[root@mysql-106 xbk]# vim /etc/my.cnf
	datadir=/mysql/logs/xbk/full_2021-07-23
	# 启动数据库，此时我们恢复到了周二晚上的数据
	[root@mysql-106 xbk]# systemctl restart mysqld

7.12 截取日志并恢复
起点：1496
[root@mysql-106 inc2]# cat /mysql/logs/xbk/inc2/xtrabackup_binlog_info
mysql_bin_log.000015	1496	6c9dd304-eb6b-11eb-aca7-000c29feac00:1-7,
a96dbf35-e08c-11eb-a5c5-000c29feac00:1-80
终点：文件末尾
[root@mysql-106 xbk]# mysqlbinlog --skip-gtids --start-position=1496 /mysql/logs/binlog/mysql_bin_log.000026 >/tmp/text.sql
mysql> set sql_log_bin=0;
mysql> source /tmp/text.sql
mysql> set sql_log_bin=1;
mysql> show tables;
+---------------+
| Tables_in_xbk |
+---------------+
| t1            |
| t2            |
| t3            |
| t4            |
| t5            |
+---------------+
5 rows in set (0.00 sec)

这篇关于MySQL备份恢复的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

MySql教程

MySQL备份恢复

MySQL备份恢复

前言

一、工作职责

二、常用工具

三、mysqldump （MDP）应用

3.1 介绍

3.2 备份方式及参数：

3.3 备份高级参数`--master-data=2`

3.3.3.2 `--single-transaction`

3.3.3.3 `-R -E --triggers`

3.3.3.4 `--max_allowed_packet=64M`

故障恢复案例

一、基于`mysqldump`+`binlog`故障案例

Percona XtraBackup

一、`Percona XtraBackup`工具的安装流程

二、介绍

三、`XBK`应用

四、全备的恢复演练

五、`XBK`全备功能

六、`XBK`的增量备份恢复

七、增量备份实践

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯

MySQL备份恢复

MySQL备份恢复

前言

一、工作职责

二、常用工具

三、mysqldump （MDP）应用

3.1 介绍

3.2 备份方式及参数：

3.3 备份高级参数--master-data=2

3.3.3.2 --single-transaction

3.3.3.3 -R -E --triggers

3.3.3.4 --max_allowed_packet=64M

故障恢复案例

一、基于mysqldump+binlog故障案例

Percona XtraBackup

一、Percona XtraBackup工具的安装流程

二、介绍

三、XBK应用

四、全备的恢复演练

五、XBK全备功能

六、XBK的增量备份恢复

七、增量备份实践

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯

3.3 备份高级参数`--master-data=2`

3.3.3.2 `--single-transaction`

3.3.3.3 `-R -E --triggers`

3.3.3.4 `--max_allowed_packet=64M`

一、基于`mysqldump`+`binlog`故障案例

一、`Percona XtraBackup`工具的安装流程

三、`XBK`应用

五、`XBK`全备功能

六、`XBK`的增量备份恢复