20@MySQL数据备份与恢复
mysql数据备份
一、数据备份介绍
1、为何要备份
1> 在生产环境中，服务器的硬件坏了可以维修或者换新，软件崩溃可以修复或重新安装, 但是如果数据没了那可就毁了，生产环境中最重要的应该就是数据了,所以, 为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据
2> 在生产环境中数据库可能会遭遇各种各样的不测从而导致数据丢失,为了以防数据丢失，保证数据的安全性，而进行数据备份
#常见的数据丢失问题：
1） 硬件故障
2） 软件故障
3） 自然灾害
4） 黑客攻击
5） 误操作 （常见的最多的，占比最大）
ps : 在innodb存储引擎，有自动的数据恢复功能，在执行一条写操作并且commit成功时innodb存储引擎会将新数据其写入redo log，此时如果数据库挂掉，重启后仍然可以依据redo log来恢复尚未执行完毕的数据
但是，对于这个的数据备份与恢复是两回事，试问，如果你一张表的数据不小心删掉了，redo log能帮你恢复吗，no，它只能帮你完成你尚未完成的操作，而对于你已经完成的操作，它帮不了你。所以不要混淆，为了防止数据意外丢失，我们还是应该做好定期的数据备份工作，减少数据丢失
2、备份的内容
#一般情况下, 我们需要备份的数据分为以下几种:
1) 数据
2）二进制日志, InnoDB事务日志
3）代码(存储过程、存储函数、触发器、事件调度器）
4）服务器配置文件
3、备份的类型
【冷备、温备、热备】
按照备份时数据库的运行状态分类：
MySQL中进行不同类型的备份还要考虑存储引擎
| 存储类型 | 冷备 | 温备 | 热备 |
|---|---|---|---|
| myisam | √ | √ | x |
| innodb | √ | √ | √ |
1）#冷备：停库、停服务来备份
即当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线
2）#温备：不停库、不停服务来备份，会(锁表)阻止用户的写入
即当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作
3）#热备（建议）：不停库、不停服务来备份，也不会(锁表)阻止用户的写入
即当数据库进行备份时, 数据库的读写操作均不是受影响
【物理与逻辑】
按照备份的内容分，可以分为两种:
1)#物理备份：直接将底层物理文件备份
2)#逻辑备份：通过特定的工具从数据库中导出sql语句或者数据，可能会丢失数据精度
【全量、差异、增量】
按照每次备份的数据量，可以分为:
1)#全量备份/完全备份（Full Backup）：备份整个数据集(即整个数据库 )
2)#部分备份：备份部分数据集 (例如: 只备份一个表的变化)，而部分备份又分为：差异备份和增量备份两种
1> 差异备份（Differential Backup）
每次备份时，都是基于第一次完全备份的内容，只备份有差异的数据(新增的、修改的、删除的)，例如：
第一次备份：完全备份
第二次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
第三次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
第四次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
第五次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
2> 增量备份（Incremental Backup ）
每次备份时，都是基于上一次备份的内容（注意是上一次，而不是第一次），只备份有差异的数据(新增的、修改的、删除的)，所以增量备份的结果是一条链，例如
第一次备份：完全备份
第二次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
第三次备份：以当前时间节点的数据为基础，备份与第二次备份内容的差异
第四次备份：以当前时间节点的数据为基础，备份与第三次备份内容的差异
第五次备份：以当前时间节点的数据为基础，备份与第四次备份内容的差异
【数据的恢复】（全量、差异、增量 ）
1)#全量备份的数据恢复
只需找出指定时间点的那一个备份文件即可，即只需要找到一个文件即可
2)#差异备份的数据恢复
需要先恢复第一次备份的结果，然后再恢复最近一次差异备份的结果，即需要找到两个文件
3)#增量备份的数据恢复
需要先恢复第一次备份的结果，然后再依次恢复每次增量备份，直到恢复到当前位置，即需要找到一条备份链
#综上对比三种备份方案
1) 占用空间： 全量 > 差异 > 增量
2) 恢复数据过程的复杂程度： 增量 > 差异 > 全量
4、备份的工具
增量备份，还需要结合binlog日志（binlog只属于增量恢复），需要用到工具mysqlbinlog，相当于逻辑备份的一种
二、设计备份的策略
1、备份策略设计的参考值
备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略
1> 能够容忍丢失多少数据
2> 恢复数据需要多长时间
3> 需要恢复哪一些数据
2、三种备份策略及应用场景
备份策略一般为以下四种：
1)# 直接cp,tar复制数据库文件
2)# mysqldump+复制BIN LOGS
3)# lvm2快照+复制BIN LOGS
4)# xtrabackup
以上四种方案的应用场景：
1) 如果数据量较小, 可以使用第一种方式, 直接复制数据库文件
2) 如果数据量还行, 可以使用第二种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果
3) 如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果
4) 如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份
三、备份实战
1、使用cp进行备份
基本流程：
1）#对所有表加锁（只能读，不能写）
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)
2）#备份数据文件
mkdir hzl.bak #创建数据存放目录
cp -a /var/lib/mysql/* /hzl.bak/ #备份数据
3）#模拟数据丢失（删除数据）
rm -rf /var/lib/mysql/*
4）#恢复数据
cp -a /egon_bak/* /var/lib/mysql
5）#重启服务
systemctl restart mysqld
2、使用mysqldump+复制binarylogs备份
【mysqldump命令的使用】
#mysqldump使用语法:
mysqldump -h 服务器 -u用户名 -p密码 选项与参数 > 备份文件.sql
#参数选项：
-A或--all-databases #表示所有库
-B或--databases #表示备份bbs db1 db2 多个数据库，db1 使用数据库名，db1 t1 t2 db1 数据库的表t1、t2
-F #备份的同时刷新binlog
-R #备份存储过程和函数数据（如果开发写了函数和存储过程，就备，没写就不备）
--triggers #备份触发器数据（现在都是开发写触发器）
-E或--events #备份事件调度器
-d #仅表结构
-t #仅数据
--master-data=1 #备份文件中 change， master语句是没有注释的，默认为1,用于已经制作好了主从，现在想扩展一个从库的时候使用,如此备份，扩展添加从库时导入备份文件后便不需要再加mater_pos了
change matser to
master_host='10.0.0.111'
master_user='rep'
master_password=123
master_log_pos=120
master_log_file='master-bin.000001'
--master-data=2 #备份文件中 change master语句是被注释的
--lock-all-tables, #提交请求锁定所有数据库中的所有表，以保证数据的一致性,这是一个全局读锁，并且自动关闭-
-single-transaction 和--lock-tables #这两个选项，他们是互斥的,于支持事务的表例如InnoDB和BDB，推荐使用--singletransaction选项，因为它根本不需要锁定表
--single-transaction： #快照备份 （搭配--master-data可以做到热备）该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和--lock-tables 选项是互斥的，不能同时存在，因为LOCK TABLES 会使任何挂起的事务隐含提交
#备份语句的使用
1）#完整语句的使用
mysqldump -uroot -pHzl@20144 -A -R --triggers --master-data=2 –single-transaction > /backup/full.sql
2）#文件太大时可以压缩 gzip ，但是gzip不属于mysql独有的命令，可以利用管道
mysqldump -uroot -pHzl@20144 -A -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full$(date +%F).sql.gz
#导出时压缩了，导入时需要解压，可以使用zcat命令
zcat /backup/full$(date +%F).sql.gz | mysql -uroot -p123
【binlog的文件内容定位】
1)#grep过滤
2)#检查事件：依据End_log_pos的提示，来确定某一个事件的起始位置与结束位置
mysql> show binlog events in 'mybinlog.000001';
#如果事件很多，可以分段查看
mysql> show binlog events in 'mybinlog.000001' limit 0,30;
mysql> show binlog events in 'mybinlog.000001' limit 30,30;
mysql> show binlog events in 'mybinlog.000001' limit 60,30;
3)#利用mysqlbinlog命令
生产中很多库，只有一个库的表被删除，我不可能把所有的库都导出来筛选，因为那样子binlog内容很多，辨别复杂度高，我们可以利用
[root@db01 mysql]# mysqlbinlog -d db1 --start-position=123 --stop-position=154 mybinlog.000001 --base64-output=decode-rows -vvv | grep -v 'SET'
#参数解释：
1）-d #参数接库名
2）--base64-output #显示模式
3）-vvv #显示详细信息
#示列：
mysqlbinlog -d database --base64-output=decode-rows -vvv mysql-bin.000002
【binlog日志恢复数据】
######################## binlog文件模拟创建备份 ####################
1)#创建binlog日志（配置文件添加）
vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=/var/lib/mysql/mybinlog
binlog_format='row' #(row,statement,mixed)
binlog_rows_query_log_events=on
max_binlog_size=100M
2）#登录数据库，插入测试数据
[root@db01 mysql]# mysql -uroot -pHzl@20144
mysql> create database db3;
mysql> use db3;
mysql> create table t1(id int);
mysql> insert t1 values(1),(2),(3);
3）#在命令行执行命令，进行全量备份
[root@db01 mysql]# mysqldump -uroot -pHzl@20144 -A -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full.sql.gz
4）#在命令行执行命令，刷新binlog，便于日后查找
[root@db01 mysql]# mysql -uroot -pHzl@20144 -e"flush logs"
5）#登录数据库，再插入一些数据，模拟增量，这些数据写入了新的binlog
mysql> use db3;
mysql> insert t1 values(4),(5),(6);
6）#模拟数据丢失
mysql> drop database db1; #删除数据库
###################### 数据丢失进行数据恢复 #######################
# 恢复数据
1）#mysql数据导入时，临时关闭binlog，不要将恢复数据的写操作也记入
mysql> set sql_log_bin=0;
2）#先恢复全量
mysql> source /tmp/full.sql
#如果是压缩包呢，使用zcat进行恢复
mysql> system zcat /tmp/full.sql.gz | mysql -uroot -pHzl@20144
3）#再恢复增量（导出：注意导出binlog时不要加选项--base64-output）
#方式一：
[root@db01 mysql]# mysqlbinlog mybinlog.000002 --stop-position=531 > /tmp/last_bin.log
#方式二：在数据导入数据库
mysql> source /tmp/last_bin.log
4）#开启二进制日志
mysql> SET sql_log_bin=ON;
【测试热备份】
准备一个存储过程，让其一直保持写操作，然后进行验证热备
########################### 热备份文件数据创建 ##########################
1)#准备库与表
create database if not exists db1;
use db1;
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
2)#创建存储过程，每隔3秒插入一条
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into s1
values(i,
'egon'
,
'male'
,concat('egon'
,i,
'@oldbo
y'));
select concat('egon'
,i,
'_ok') as
name,sleep(3);
set i=i+1;
end while;
END$$ #$$结束
delimiter ;
3)#查看存储过程
show create procedure auto_insert1\G
########################## 热备份文件数据备份 ###########################
1）#先打开binlog日志
2）#登录数据库，执行存储过程
mysql> use db1;
mysql> call auto_insert1();
若想杀死存储过程
mysql> show processlist; -- #查出id
mysql> kill id号;
3）#在命令行执行下述命令，进行全量备份
[root@egon mysql]# mysqldump -uroot -pHzl@20144 -A -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full.sql.gz
4）#全量备份完毕后的一段时间里，数据依然插入，写入了mybinlog.000001中
# 然后我们在命令行刷新binlog，产生了新的mybinlog.000002
[root@db01 mysql]# mysql -uroot -pHzl@20144 -e "flush logs"
5)#此时数据依然在插入，但都写入了最新的mybinlog.000002中，所以需要知道的是，增量的数据在mysqlbinlog.000001与mybinlog.000002中都有我们登录数据库，杀掉存储过程，观察到最新的数据插到了id=55的行
mysql> show processlist; -- 查出id
mysql> kill id号;
6)#删除数据
drop database db1;
########################## 数据文件全量恢复 ###########################
1）#登录数据库，先恢复全量
mysql> set sql_log_bin=0; #关闭binlog日志
mysql> system zcat /tmp/full.sql.gz | mysql -uroot -pHzl@20144
mysql> select * from db1.s1; -- 查看恢复到了id=28，剩下的去增量里恢复
2）#在命令行导出mybinlog.000001中的增量，然后登录库进行恢复，查找位置，发现@1=29即第一列等于29，即id=29的下一个position是10275
mysql> show binlog events in 'mybinlog.000001'; #查看binlog日志文件
[root@db01 mysql]# mysqlbinlog mybinlog.000001 --start-position=10038 --stop-position=11340 --base64-output=decode-rows -vvv | grep -v 'SET' | less
3)#在命令行中执行导出
[root@db01 mysql]# mysqlbinlog mybinlog.000001 --start-position=10275 > /tmp/1.sql
#在库内执行导入,发现恢复到了39
mysql> source /tmp/1.sql -- #最好是在库内恢复，因为sql_log_bin=0，导入操作不会记录
mysql> select * from db1.s1; -- #查看数据内容
4)#在命令行导出mybinlog.000002中的增量，然后登录库进行恢复上面恢复到了id=39，我们接着找id=40的进行恢复，查找位置
（发现@1=40的position是432，发现@1=55的position是6464）
mysql> show binlog events in 'mybinlog.000002';
[root@db01 mysql]# mysqlbinlog --base64-output=decode-rows -vvv mybinlog.000002|grep -v 'SET'|grep -C20 -w '@1=40'
[root@db01 mysql]# mysqlbinlog --base64-output=decode-rows -vvv mybinlog.000002|grep -v 'SET'|grep -C20 -w '@1=55'
#导出
[root@db01 mysql]# mysqlbinlog mybinlog.000002 --start-position=432 --stop-position=6464 > /tmp/2.sql
5）#在库内执行导入,发现恢复到了55
mysql> source /tmp/2.sql
mysql> select * from db1.s1;
#开启binlog
mysql> SET sql_log_bin=ON;
#能否使用binlog做全量备份的问题：
可以，但直接使用binlog做全量恢复，成本很高，我们只用起来做增量恢复。
#正确的方案是：**全备+binlog增量**
每天或者每周全备一次，全备之后，那个位置点之前的binlog全都可以删除，不可能一年有上百个binlog的库都导出来筛选，因为那样子binlog内容很多，辨别复杂度高，我们可以利用
3、使用lvm2快照备份数据
############################ 部署lvm环境 #############################
1）#添加硬盘; 这里我们直接实现SCSI硬盘的热插拔, 首先
在虚拟机中添加一块硬盘, 无需重启
echo '- - -' > /sys/class/scsi_host/host0/scan
echo '- - -' > /sys/class/scsi_host/host1/scan
echo '- - -' > /sys/class/scsi_host/host2/scan
2）#创建逻辑卷
pvcreate /dev/sdb
vgcreate vg1 /dev/sdb
lvcreate -n lv1 -L 5G vg1
3）#格式化制作文件系统并挂载
mkfs.xfs /dev/mapper/vg1-lv1
mkdir /lv1
mount /dev/mapper/vg1-lv1 /var/lib/mysql
chown -R mysql.mysql /var/lib/mysql
4）#修改mysql配置文件的datadir如下：
[root@node1 ~]# rm -rf /var/lib/mysql/* # 删除
原数据
[root@node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
5）#重启MySQL、完成初始化
[root@node1 ~]# systemctl restart mysqld
6）#往数据库内插入测试数据
create database db3;
use db3;
create table t1(id int);
insert t1 values(1),(2),(3);
######################### 创建快照卷并备份 ############################
1）#先进行锁表操作，禁止使用写操作
mysql> FLUSH TABLES WITH READ LOCK; #锁定所有表
Query OK, 0 rows affected (0.00 sec)
2）#逻辑卷快照创建
[root@node1 lvm_data]# lvcreate -L 1G -s -n
lv1_from_vg1_snap /dev/vg1/lv1 #创建快照卷
mysql> UNLOCK TABLES; #解锁所有表
Query OK, 0 rows affected (0.00 sec)
[root@node1 lvm_data]# mkdir /snap1 #创建文件夹
[root@node1 lvm_data]# mount -o nouuid /dev/vg1/lv1_from_vg1_snap /snap1
[root@localhost snap1]# cd /snap1/
[root@localhost snap1]# tar cf /tmp/mysqlback.tar *
[root@localhost snap1]# umount /snap1/ -l
[root@localhost snap1]# lvremove vg1/lv1_from_vg1_snap
############################ 恢复数据 ###############################
#删除之前的就数据，清空环境
rm -rf /var/lib/mysql/*
#恢复数据
tar xf /tmp/mysqlback.tar -C /var/lib/mysql/
#重启
systemctl restart mysqld
4、物理备份之Xtrabackup
【Xtrabackup介绍】
Xtrabackup是由percona提供的mysql数据库备份工具
据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具
#Xtrabackup的特点：
1）备份过程快速、可靠；
2）备份过程不会打断正在执行的事务；
3）能够基于压缩等功能节约磁盘空间和流量；
4）自动实现备份检验；
5）还原速度快；
#使用xtrabackup
使用InnoDB能够发挥其最大功效, 并且InnoDB的每一张表必须使用单独的表空间, 我们需要在配置文件中添加：
innodb_file_per_table = ON #开启Xtrabackup
【Xtrabackup安装】
版本选择：
1）mysql 5.7以下版本，可以采用percona xtrabackup 2.4版本
2）mysql 8.0以上版本，可以采用percona xtrabackup 8.0版本
#xtrabackup8.0也只支持mysql8.0以上的版本
一些金融行业，mysql版本还是多采用mysql5.7，当然oracle官方对于mysql 8.0的开发支持力度日益加大，新功能新特性迭代不止。生产环境采用mysql 8.0的版本比例会日益增加
########################### 方式一 ##############################
#安装yum仓库
yum install
https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y
#安装XtraBackup命令
yum install percona-xtrabackup-24 -y
########################### 方式二 ##############################
#下载epel源
wget -O /etc/yum.repos.d/epel.repo
https://mirrors.aliyun.com/repo/epel-7.repo
#安装依赖
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
#下载Xtrabackup
wget
https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
#安装
yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
#安装完后会生成命令
xtrabackup # 以前使用该命令
innobackupex # 现在使用该命令
innobackupex是xtrabackup的前端配置工具，使用innobackupex备份时, 会调用xtrabackup备份所有的InnoDB表, 复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件, 同时还会备份触发器和数据库配置文件信息相关的文件, 这些文件会被保存至一个以时间命名的目录.
【Xtrabackup 备份方式】(物理备份)
1)对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。
2)对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。
3)备份时读取配置文件/etc/my.cnf
【Xtrabackup全量备份】
1)#创建备份目录，会把mysql的datadir中的内容备份到改目录中
mkdir /backup
2)#全备
在本地执行下述命令，输入登录数据的本地账号与密码
指定备份目录为/backup下的full目录
innobackupex --user=root --password=123
/backup/full
3)#查看：默认会在备份目录下生成一个以时间戳命名的文件夹
[root@localhost ~]# cd /backup/full/
[root@localhost full]# ls
2021-07-16_16-09-47
[root@localhost full]# ls 2021-07-16_16-09-47/ #备份目录
[root@localhost full]# ls /var/lib/mysql #数据目录
4)#去掉时间戳，让备份数据直接放在备份目录下
我们在写备份脚本和恢复脚本，恢复的时候必须指定上一次备份的目录，如果备份目录带着时间戳，该时间戳我们很难在脚本中确定，无为了让脚本编写更加方便
我们可以使用选项--no-timestamp去掉时间戳，让备份内容直接放置于我们指定的目录下
（ps：金融公司喜欢每天全备，每小时增备，如果备份目录带着时间戳，看似合理，但确实会很让头疼）
[root@localhost full]# rm -rf 2021-07-16_17-45-53/
[root@localhost full]# innobackupex --user=root --password=123 --no-timestamp /backup/full
#补充：关于备份目录下新增的文件说明，可用cat命令查看
xtrabackup_checkpoints 存储系统版本号，增备的时候会用到xtrabackup_info 存储UUID，数据库是由自己的UUID的，如果相同，做主从会有问题xtrabackup_logfile 就是redo
【Xtrabackup增量备份】
1)#基于上一次备份进行增量,参数说明:
--incremental： #开启增量备份功能
--incremental-basedir： #上一次备份的路径
2)# 加上上一次命令
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full/ /backup/xtr
3)#判断数据备份是否衔接
cat /backup/full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1808756
last_lsn = 1808756
compact = 0
recover_binlog_info = 0
flushed_lsn = 1808756
cat /backup/xtra/xtrabackup_checkpoints #查看备份文件
backup_type = incremental
from_lsn = 1808756 # 值应该与全被的to_lsn一致
to_lsn = 1808756
last_lsn = 1808756
compact = 0
recover_binlog_info = 0
flushed_lsn = 1808756
【Xtrabackup + Binlog恢复】（企业实战）
mysql配置文件：数据目录与binlog放在不同的文件夹下
1）#添加配置文件
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
default-storage-engine=innodb
innodb_file_per_table=1
server_id=1
log-bin=/data/binlog/mybinlog
binlog_format='row' #(row,statement,mixed)
binlog_rows_query_log_events=on
max_binlog_size=100M
2）#为binlog日志创建目录
mkdir -p /data/binlog/
chown -R mysql.mysql /data/
3）#重启mysql
systemctl restart mysql
########################### 模拟数据 ##############################
create database full charset utf8mb4;
use full;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
#进行周日的全备
1）#事先创建好备份目录
[root@db01 backup]# rm -rf /backup
[root@db01 backup]# mkdir /backup
2）#全备
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp --parallel=5 /backup/full
######################## 模拟周一的数据变化 #############################
create database inc1 charset utf8mb4;
use inc1;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
#进行周一的增量备份
innobackupex --user=root --password=123 --notimestamp --incremental --incrementalbasedir=/backup/full /backup/inc1
#检查本次备份的LSN
[root@localhost backup]# cat
/backup/full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1817002
last_lsn = 1817002
compact = 0
recover_binlog_info = 0
flushed_lsn = 1817002
[root@localhost backup]# cat
/backup/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1817002
to_lsn = 1825905
last_lsn = 1825905
compact = 0
recover_binlog_info = 0
flushed_lsn = 1825905
[root@localhost backup]#
############################ 模拟周二数据变化 ##########################
create database inc2 charset utf8mb4;
use inc2;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
#周二的增量
innobackupex --user=root --password=123 --no-timestamp --incremental --incrementalbasedir=/backup/inc1 /backup/inc2
#周三的数据变化
create database inc3 charset utf8mb4;
use inc3;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
#模拟上午10点数据库崩溃
systemctl stop mysql # pkill -9 mysqld
rm -rf /var/lib/mysql/*
#恢复思路
1. 停业务,挂维护页
2. 查找可用备份并处理备份:full+inc1+inc2
3. 找到binlog中: inc2 到 故障时间点的binlog
4. 恢复全备+增量+binlog
5. 验证数据
6. 起业务,撤维护页
#恢复前的准备：所有增量必须要按顺序合并到全备当中才能用于恢复
1）#整理full
innobackupex --apply-log --use-memory=3G --redo-only /backup/full
--apply-log： 该选项表示同xtrabackup的--prepare参数,一般情况下,在备份完成后，数据尚且不能用于恢复操作，
因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。
因此，此时数据 文件仍处理不一致状态。--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
2）#合并inc1到full,并整理备份
innobackupex --apply-log --use-memory=3G --redo-only --incrementaldir=/backup/inc1/backup/full
3）#合并后对比inc1与full的LSN号:last_lsn保持一致
cat /backup/full/xtrabackup_checkpoints
cat /backup/inc1/xtrabackup_checkpoints
4）#合并inc2到full,并整理备份 (合并最后一个增量备份时不要加--redo-only)
innobackupex --apply-log --use-memory=3G --incremental-dir=/backup/inc2 /backup/full
5）#合并后对比inc2与full的LSN号:last_lsn保持一致
cat /backup/full/xtrabackup_checkpoints
cat /backup/inc2/xtrabackup_checkpoints
6）#最后一次整理ful
innobackupex --use-memory=3G --apply-log/backup/full
7）#截取二进制日志
#起点
cat /backup/inc2/xtrabackup_binlog_info
输出内容如下
mysql-bin.000031 1997 aa648280-a6a6-11e9-949f-000c294a1b3b:1-17,e16db3fd-a6e8-11e9-aee9-000c294a1b3b:1-9
#终点:
mysqlbinlog /data/binlog/mysql-bin.000031 |grep 'SET' SET @@SESSION.GTID_NEXT= 'e16db3fd-a6e8-11e9-aee9-000c294a1b3b:12'/*!*/;
#导出：
mysqlbinlog --skip-gtids --include-gtids='e16db3fd-a6e8-11e9-aee9-000c294a1b3b:10-12' /data/binlog/mysql-bin.000031>/backup/binlog.sql
或者：
mysqlbinlog /data/binlog/mybinlog.000003 --start-position=1648 > /backup/binlog.sql #恢复备份的数据
cp -a /backup/full/* /var/lib/mysql
chown -R mysql.mysql /var/lib/mysql
systemctl start mysql
mysql -uroot -p123
> set sql_log_bin=0;
> source /backup/binlog.sql
#验证：
select * from full.t1;
select * from inc1.t1;
select * from inc2.t1;
select * from inc3.t1;
四、自动备份脚本
【备份计划】
- 什么时间 2:00
- 对哪些数据库备份
- 备份文件放的位置
【备份脚本】
[root@db01 ~]# vim /mysql_back.sh
#!/bin/bash
back_dir=/backup
back_file=`date +%F`_all.sql
user=root
pass=123
if [ ! -d /backup ];then
mkdir -p /backup
fi
#备份并截断日志
mysqldump -u${user} -p${pass} --events -R --triggers --master-data=2 --single-transaction --all-databases > ${back_dir}/${back_file}
mysql -u${user} -p${pass} -e 'flush logs'
#只保留最近一周的备份
cd $back_dir
find . -mtime +7 -exec rm -rf {} \;
【手动测试】
chmod a+x /mysql_back.sh
chattr +i /mysql_back.sh
bash /mysql_back.sh
【配置计划任务】
3配置cron：
[root@egon ~]# crontab -l
0 2 * * * /mysql_back.sh
五、企业案列
【背景】
1)正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。
2)备份策略：每天23：00，计划任务调用mysqldump执行全备脚本
3)故障时间点：上午10点开发人员误删除一个核心业务表，如何恢复？
【处理思路】
1.停业务避免数据的二次伤害
2.找一个临时的库，恢复前一天的全备
3.截取前一天23：00到第二天10点误删除之间的binlog，恢
复到临时库
4.测试可用性和完整性
5.开启业务前的两种方式
1）直接使用临时库顶替原生产库，前端应用割接到新
2）将误删除的表单独导出，然后导入到原生产环境
6.对外开放业务
【故障模拟】
1)#准备初始数据
#刷新binlog使内容更清晰
flush logs;
#查看当前使用的binlog
show master status;
#准备测试库与数据
create database dbtest;
use dbtest;
create table t1(id int,name varchar(16));
insert t1 values
(1,"hzl1"),
(2,"hzl2"),
(3,"hzl3");
create table t2 select * from t1;
2）#全备
[root@db01 ~]# mkdir /backup
[root@db01 ~]# mysqldump -uroot -pHzl@20144 -A -R --triggers --master-data=2 --single-transaction |gzip > /backup/full.sql.gz # 通常备份文件应该带时间，此处略
3）#模拟23:00到10:00的操作
use dbtest;
create table t3 select * from t1;
select * from t3;
update t1 set name="HZL" where id=2;
delete from t2 where id>2;
4）#模拟10：00删库操作
#删库、跑路
drop database dbtest;
########################### 恢复数据 ###########################
1)#先停生产库,避免数据二次伤害
[root@db01 ~]# systemctl stop mysqld
2）#准备新库，在新库中完成数据恢复操作后再更新给生成库
3）#通过binlog找到23:00到第二天10:00之间新增的数据
1.找到结束位置点：
mysql> show master status;
mysql> show binlog events in'mybinlog.000002';
[root@localhost backup]# cd /var/lib/mysql
[root@localhost mysql]# mysqlbinlog --base64-output=decode-rows -vvv --start-datetime="2021-07-16 02:00:00" mybinlog.000002
2.取出位置点之间新增的数据
[root@db01 ~]# mysqlbinlog --start-position=694 --stop-position=1249
mybinlog.000002 > /backup/xin.sql
4)#将前一天的全备数据和新增的数据拷贝到新数据库
scp /backup/full.sql.gz 172.16.1.52:/tmp/
scp /backup/xin.sql 172.16.1.52:/tmp/
5）#将前一天的全备与增量恢复到新库
mysql> set sql_log_bin=0;
mysql> system zcat /tmp/full.sql.gz | mysql -uroot -pHzl@20144
mysql> source /tmp/xin.sql;
6)#查看表和数据验证数据完整
mysql> use dbtest;
mysql> show tables;
mysql> select * from t1;
mysql> select * from t2;
mysql> select * from t3;
7）#恢复生产环境提供服务
1.将恢复的表导出，导入到生产库（如果核心业务表很小）
1）导出指定表
[root@db02 mysql]# mysqldump dbtest t1 t2 t3 > /tmp/test.sql
2）将sql传输到生产库
[root@db02 mysql]# scp /tmp/test.sql 172.16.1.51:/tmp/
3）指定库导入表
[root@db01 data]# mysql backup < /tmp/test.sql
2.应用服务修改数据库配置连接到新库（如果核心业务表很大）
【逻辑备份之shell脚本】
脚本中对数据库进行了两次备份：
一次是分库备份：即一个数据库(schema)一个备份文件
另一次是分库分表备份：即一个表一个备份文件，这样的备份方式，对于以后还原的时候只还原数据库下面的某一个表十分的方便，而不用在整个数据库的备份文件中摘出来对应的表
#!/bin/sh
#--------------------------------------------------
#
# CreatDate: 2021/07/20
# Desc: 备份数据库脚本
#--------------------------------------------------
BACKUP_DIR=$1
HOSTNAME=$2
USERNAME=$3
USERPASSWORD=$4
MAIL_RECIPIENT=$5
if [[ ! -d $BACKUP_DIR ]]; then
echo "存放备份文件的目录不存在！"
exit 5
elif [[ $HOSTNAME"x" == "x" ]]; then
echo "数据所在主机名称或IP不能为空！"
exit 10
elif [[ $USERNAME"x" == "x" ]]; then
echo "备份数据库的用户名称不能为空！"
exit 15
elif [[ $USERPASSWORD"x" == "x" ]]; then
echo "备份数据库的用户密码不能为空！"
exit 20
elif [[ $MAIL_RECIPIENT"x" == "x" ]]; then
echo "收件人邮箱地址不能为空！"
exit 25
fi
LOGINCOMMAND="mysql -h$HOSTNAME -u$USERNAME -p$USERPASSWORD"
DUMPCOMMAND="mysqldump -h$HOSTNAME -u$USERNAME -p$USERPASSWORD"
# 排除mysql,sys和以_schema结尾的数据库
DATABASE_NAME_LIST=`$LOGINCOMMAND -e "show databases;" |egrep -v "*_schema|mysql|sys" | sed '1d' `
if [[ ! -d $BACKUP_DIR/$(date +%Y%m%d) ]]; then
echo "备份日期目录不存在，需要创建该日期目录"
mkdir -p $BACKUP_DIR/$(date +%Y%m%d)
fi
BACKUP_LOG_FILE=$BACKUP_DIR/$(date +%Y%m%d)/db_backup_$(date +%Y%m%d).log
if [[ ! -f $BACKUP_LOG_FILE ]]; then
touch $BACKUP_LOG_FILE
fi
echo "======================分库备份开始于：$(date '+%Y-%m-%d %H:%M:%S')=======================" >> $BACKUP_LOG_FILE
# 分库备份
for dbname in $DATABASE_NAME_LIST
do
echo "备份$dbname数据库开始于：$(date '+%Y-%m-%d %H:%M:%S')" >> $BACKUP_LOG_FILE
$DUMPCOMMAND --flush-privileges
--master-data=2
--flush-logs
--single-transaction
--triggers
--routines
--events
--databases $dbname | gzip > $BACKUP_DIR/$(date +%Y%m%d)/${dbname}.$(date +%Y%m%d%H%M%S).sql.gz
if [[ $? -ne 0 ]]; then
echo "ERROR: 备份$dbname数据库失败于：$(date '+%Y-%m-%d %H:%M:%S')" >> $BACKUP_LOG_FILE
continue
else
echo "备份$dbname数据库结束于：$(date '+%Y-%m-%d %H:%M:%S')" >> $BACKUP_LOG_FILE
fi
done
echo "======================分库备份结束于：$(date '+%Y-%m-%d %H:%M:%S')=======================" >> $BACKUP_LOG_FILE
echo "======================分库分表备份开始于：$(date '+%Y-%m-%d %H:%M:%S')=======================" >> $BACKUP_LOG_FILE
# 分库分表备份
for dbname in $DATABASE_NAME_LIST
do
TABLE_NAME_LIST=`$LOGINCOMMAND -e "show tables from $dbname;" | sed '1d' `
if [[ ! -d $BACKUP_DIR/$(date +%Y%m%d)/$dbname/ ]]; then
echo "要备份的数据库名称目录不存在，需要创建该目录" >> $BACKUP_LOG_FILE
mkdir -p $BACKUP_DIR/$(date +%Y%m%d)/$dbname
fi
for tabname in $TABLE_NAME_LIST
do
$DUMPCOMMAND --flush-privileges
--master-data=2
--single-transaction
--triggers
--routines
--events
$dbname $tabname | gzip > $BACKUP_DIR/$(date +%Y%m%d)/$dbname/${dbname}.${tabname}.$(date +%Y%m%d%H%M%S).sql.gz
if [[ $? -ne 0 ]]; then
echo "ERROR: 备份$dbname.$tabname的时候失败于：$(date '+%Y-%m-%d %H:%M:%S')" >> $BACKUP_LOG_FILE
continue
fi
done
done
echo "======================分库分表备份结束于：$(date '+%Y-%m-%d %H:%M:%S')=======================" >> $BACKUP_LOG_FILE
#获取日志文件的名称和目录
BACKUP_LOG_FILE_BASENAME=`echo $(basename ${BACKUP_LOG_FILE})`
BACKUP_LOG_FILE_DIRNAME=`echo $(dirname ${BACKUP_LOG_FILE})`
#对生成的日志文件打包压缩，便于以附件的方式发送邮件。
tar -zcvf ${BACKUP_LOG_FILE_BASENAME}.tar.gz -C $BACKUP_LOG_FILE_DIRNAME $BACKUP_LOG_FILE_BASENAME
MAIL_SUBJECT_INFO="INFO: DB backup successfully"
MAIL_CONTENT_INFO="DB backup successfully, detail info pls refer attached log file."
#Send email
echo "$MAIL_CONTENT_INFO" | mail -s "$MAIL_SUBJECT_INFO" -a "${BACKUP_LOG_FILE_BASENAME}.tar.gz" "$MAIL_RECIPIENT"
exit 0
#加入定时任务crontab
30 1 * * * /root/backup_mysql_db.sh /data/mysql_backup_dir 192.168.5.100 root root
#脚本参数注释
BACKUP_DIR： #生成的备份文件存放的目录在哪里
HOSTNAME： #要备份的数据库所在的服务器IP地址
USERNAME： #执行备份的时候，连接到MySQL数据库的用户名称
USERPASSWORD： #执行备份的时候，连接到MySQL数据库的用户对应的密码
MAIL_RECIPIENT： #数据库备份完成后，接受通知邮件的收件人邮箱地址
【逻辑还原之shell脚本】
mysql -uroot -p -D xxxx < xxxx_backup.sql
本文来自博客园，作者：ଲ一笑奈&何，转载请注明原文链接：https://www.cnblogs.com/zeny/p/15121479.html
