MySQL的备份和恢复

1.备份的原因

1.保证公司数据的安全，避免给公司造成损失
2.备份，就是为了恢复

备份类型

  • 冷备：停库，停服务，备份

  • 温备：不停库，但是备份的同时会锁表

  • 热备：不停库，但是备份的同事不锁表

2.备份工具

逻辑备份

  • binlog
[root@mysql-db02 mysql]# mysqlbinlog --base64-output=decode-rows /application/mysql/data/mysql-bin.000001
  • into outfile
mysql> select * from world.city into outfile '/tmp/world_city.data';
Query OK, 4079 rows affected (0.01 sec)
  • mysqldump
# 下文详细说明
  • replication
  • mysqlbinlog

物理备份

  • 备份data目录
# tar 打包 data数据目录。 拷贝解压到目标机
  • Xtrabackup

备份策略

  • 全量备份：备份所有的数据
  • 增量备份：基于上一次备份之后的新增数据

  • 差异备份：基于全量备份之后的新增数据


    image.png

3.MySQL逻辑备份

mysqldump

-u：指定用户
-p：指定密码
-S：指定socket文件
-h：指定主机域
  • -A：全量备份（备份所有库，所有表）
[root@mysql-db01 ~]# mysqldump -A > /backup/full.sql
  • 不加参数：备份表(不会备份表上一级的库)
[root@mysql-db01 backup]# mysqldump world > /backup/world.sql
[root@mysql-db01 backup]# mysqldump world city > /backup/world_city.sql

#恢复(需要指定恢复的目标库)
[root@mysql-db01 backup]# mysql world < /backup/world.sql
  • -B：指定数据库，做备份（包括了库下所有表）
[root@mysql-db01 backup]# mysqldump -B world > /backup/world_B.sql
[root@mysql-db01 backup]# mysqldump -B world zls > /backup/world_zls.sql

#恢复
[root@mysql-db01 backup]# mysql < /backup/world_B.sql
  • -F：刷新binlog日志(不推荐使用，会刷新多条无用binlog，一张表生成一个binlog)
[root@mysql-db01 backup]# mysqldump -F -B world mysql > /backup/flush.sql
  • --master-data=[n]：打点备份（单独使用，是温备，且锁表）
n 赋值：
- 1 : 打印出change master 语句 不加注释
- 2 ：打印出change master 语句 加注释
- 0 ：关闭，不使用打点功能
  • --single-transaction：快照备份(不锁表)
[root@mysql-db01 backup]# mysqldump -uroot -p123 -A --master-data=2 --singletransaction > /backup/full.sql
  • 扩展参数：(推荐加上)

-R：备份函数和存储过程
--triggers：备份触发器

[root@mysql-db01 backup]# mysqldump -uroot -p123 -A -R --triggers --masterdata=2 --single-transaction > /backup/full.sql
  • 压缩备份 gzip(推荐：节省磁盘空间)
[root@mysql-db01 backup]# mysqldump -uroot -p123 -A -R --triggers --masterdata=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

#恢复(无需解压即可导入)
[root@mysql-db01 backup]# zcat full_2020-02-20.sql.gz | mysql -uroot -p123

忘记它的参数：(不推荐使用)

-t：只备份，表结构
-d：只备份，数据
-x：锁表备份

4.Xtrabackup物理备份

percona官网:https://www.percona.com/
percona物理备份工具下载:https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/

#1.下载Xtrabackup
[root@mysql-db01 ~]# wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.18/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.18-1.el7.x86_64.rpm

#2.设置epel源
[root@mysql-db01 ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo

#3.安装Xtrabackup
[root@mysql-db01 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

  • mysqldump性能对比Xtrabackup


    image.png

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

Xtrabackup 全量备份

[root@mysql-db01 ~]# innobackupex --user=root --password=123 /backup/
-rw-r----- 1 root root       16 Feb 21 10:29 xtrabackup_binlog_info     #binlog的信息
-rw-r----- 1 root root      115 Feb 21 10:29 xtrabackup_checkpoints     #数据备份内容
-rw-r----- 1 root root      460 Feb 21 10:29 xtrabackup_info            #Xtrabackup备份所有信息

[root@mysql-db01 2020-02-21_10-29-09]# cat xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 13531280
last_lsn = 13541124

# 全备恢复
前提1：被恢复的目录是空的
前提2：被恢复的数据库的实例是关闭的

# 1. 停库 1293
[root@mysql-db01 2020-02-21_10-29-09]# /etc/init.d/mysqld stop
# 2. 清空data目录
[root@mysql-db01 mysql]# mv data data_bak
# 3. 恢复之前，一定要把redo和undo重做一遍
[root@mysql-db01 ~]# innobackupex --apply-log /backup/2020-02-21_10-29-09/
# 4. 恢复数据
重点：必须在配置文件中写datadir而且必须写在[mysqld]下面，不能写在[server]下面
[root@mysql-db01 backup]# innobackupex --copy-back /backup/2020-02-21_10-29-09/
# 5. 授权
[root@mysql-db01 backup]# chown -R mysql.mysql /application/mysql/data
# 6. 启动数据库
[root@mysql-db01 backup]# /etc/init.d/mysqld start

Xtrabackup 增量备份

1、基于上一次备份进行增量
2、增量备份无法单独恢复，必须基于全备进行恢复
3、所有增量必须要按顺序合并到全备当中
4、手动模拟CSR

--------------------------------------备份---------------------------------------------------
#1.全量备份
[root@mysql-db01 backup]# innobackupex --user=root --password=123 /backup/
[root@mysql-db01 backup]# cat 2020-02-21_11-10-40/xtrabackup_checkpoints）
backup_type = full-backuped
from_lsn = 0             # from_lsn：备份起点
to_lsn = 13635573        # to_lsn：备份终止点。
last_lsn = 13645187      # last_lsn：（不用太关注。终止后任有数据产生）
#2.第一次，增量备份（基于上次全备）
[root@mysql-db01 backup]# innobackupex --user=root --password=123 --incremental --incremental-basedir=/backup/2020-02-21_11-10-40 /backup/
[root@mysql-db01 backup]# cat 2020-02-21_11-12-22/xtrabackup_checkpoints 
backup_type = incremental    # 增量类型
from_lsn = 13635573      #  from_lsn：此次增量的启点，去上次备份终止点相同。
to_lsn = 13668799        # to_lsn：备份终止点。
last_lsn = 13673199
#3.第二次，增量备份（基于第一次增备）
[root@mysql-db01 backup]# innobackupex --user=root --password=123 --incremental --incremental-basedir=/backup/2020-02-21_11-12-22/ /backup
[root@mysql-db01 backup]# cat 2020-02-21_11-15-10/xtrabackup_checkpoints 
backup_type = incremental   # 增量类型
from_lsn = 13668799       #  from_lsn：此次增量的启点，去上次备份终止点相同。
to_lsn = 13699561         # to_lsn：备份终止点
last_lsn = 13720058
#4.第三次，增量备份 （基于第二次增备）
[root@mysql-db01 backup]# innobackupex --user=root --password=123 --incremental --incremental-basedir=/backup/2020-02-21_11-15-10/ /backup
[root@mysql-db01 backup]# cat 2020-02-21_11-17-36/xtrabackup_checkpoints 
backup_type = incremental     # 增量类型
from_lsn = 13699561       #  from_lsn：此次增量的启点，去上次备份终止点相同。
to_lsn = 13749010         # to_lsn：备份终止点
last_lsn = 13749010      # 无数据产生时，数据和to_lsn终止点相同！！！

---------------------------------------恢复数据---------------------------------------------------
#1.full+inc1+inc2
#2.需要将inc1和inc2按顺序合并到full中
#3.分步骤进行--apply-log(模拟CSR,redo和undo重做)

#1.停库   1702
[root@mysql-db01 ~]# /etc/init.d/mysqld stop
#2.保证data目录是空的
[root@mysql-db01 ~]# mv /application/mysql/data /application/mysql/data_ori

#3.前戏1：将全量备份目录，只做redo不做undo
[root@mysql-db01 backup]# innobackupex --apply-log --redo-only /backup/2020-02-21_11-10-40
[root@mysql-db01 backup]# cat /backup/2020-02-21_11-10-40/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 13635573
last_lsn = 13645187

#4.前戏2：将第一次增量合并到全量备份中，只做redo不做undo
[root@mysql-db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/2020-02-21_11-12-22  /backup/2020-02-21_11-10-40
[root@mysql-db01 backup]# cat /backup/2020-02-21_11-10-40/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 13668799
last_lsn = 13673199

#5.前戏3：将第二次增量合并到全量备份中，只做redo不做undo
[root@mysql-db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/2020-02-21_11-15-10  /backup/2020-02-21_11-10-40
[root@mysql-db01 backup]# cat 2020-02-21_11-10-40/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 13699561
last_lsn = 13720058

#6.前戏4：将第三次增量合并到全量备份中，redo和undo都做
[root@mysql-db01 backup]# innobackupex --apply-log  --incremental-dir=/backup/2020-02-21_11-17-36  /backup/2020-02-21_11-10-40
[root@mysql-db01 backup]# cat /backup/2020-02-21_11-10-40/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 13749010
last_lsn = 13749010

#7.前戏5：将所有合并完的，全量备份的目录，整体做一次redo和undo
[root@mysql-db01 backup]# innobackupex --apply-log /backup/2020-02-21_11-10-40\

#8.恢复数据
[root@mysql-db01 backup]# innobackupex --copy-back /backup/2020-02-21_11-10-40/

#9.授权
[root@mysql-db01 backup]# chown -R mysql.mysql /application/mysql/data

#10.启动
[root@mysql-db01 backup]# /etc/init.d/mysqld start

写脚本版物理增量备份

-------------------------备份--------------------------------
#1.第一次全备
[root@mysql-db01 backup]# innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)
[root@mysql-db01 backup]# cat full_2020-02-21/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 13749829
last_lsn = 13788465

#2.第一次增量备份（基于全备）
[root@mysql-db01 backup]# innobackupex --user=root --password=123  --no-timestamp --incremental --incremental-basedir=/backup/full_$(date +%F) /backup/inc_$(date +%F-%H)  # 精确到小时
[root@mysql-db01 backup]# cat inc_2020-02-21-12/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 13749829
to_lsn = 13846130
last_lsn = 13853270

#3.第二次增量备份（基于一次增备）# date +%F-%H -d '-1 hour' 前一小时
[root@mysql-db01 backup]# innobackupex --user=root --password=123  --no-timestamp --incremental --incremental-basedir=/backup/inc_$(date +%F-%H -d '-1 hour') /backup/inc_$(date +%F-%H)    # 基于上一小时做增备
[root@mysql-db01 backup]# cat inc_2020-02-21-13/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 13846130
to_lsn = 13866438
last_lsn = 13866438

---------------------------------数据恢复---------------------------------
#1.停库     （记录其他表数据点2336）
[root@mysql-db01 backup]# /etc/init.d/mysqld stop

#2."清空"data目录
[root@mysql-db01 backup]# mv /application/mysql/data  /application/mysql/data_back

#3.前戏1：将全备目录，只做redo不做undo
[root@mysql-db01 backup]# innobackupex --apply-log --redo-only /backup/full_2020-02-21/
[root@mysql-db01 backup]# cat full_2020-02-21/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 13749829
last_lsn = 13788465

#4.前戏2：将第一次增量合并到全量备份中，只做redo不做undo
[root@mysql-db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc_2020-02-21-12/ /backup/full_2020-02-21/

[root@mysql-db01 backup]# cat full_2020-02-21/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0-d
to_lsn = 13846130
last_lsn = 13853270

#5.前戏3：将第二次增量合并到全量中，redo和undo都做
[root@mysql-db01 backup]# innobackupex --apply-log --incremental-dir=/backup/inc_2020-02-21-1/ /backup/full_2020-02-21/

[root@mysql-db01 backup]# cat full_2020-02-21/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 13866438
last_lsn = 13866438

#6.前戏4：将整个全备做一次redo和undo
[root@mysql-db01 backup]# innobackupex --apply-log  /backup/full_2020-02-21/

#7.恢复数据
[root@mysql-db01 backup]# innobackupex --copy-back /backup/full_2020-02-21/

#8.授权
[root@mysql-db01 backup]# chown -R mysql.mysql /application/mysql/data/

#9.启动
[root@mysql-db01 backup]# /etc/init.d/mysqld start

Xtrabackup 实现差异备份

#1.全量备份
[root@mysql-db01 backup]# innobackupex --user=root --password=123 --no-timestamp /backup/full

[root@mysql-db01 backup]# cat full/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 13937269
last_lsn = 13941387

#2.第一次差异备份
[root@mysql-db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/chayi1

[root@mysql-db01 backup]# cat chayi1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 13937269
to_lsn = 13948766
last_lsn = 13960018

#3.第二次差异备份
[root@mysql-db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/chayi2

[root@mysql-db01 backup]# cat chayi2/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 13937269
to_lsn = 13948766
last_lsn = 13975676

#7.第三次差异备份
[root@mysql-db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/chayi3

[root@mysql-db01 backup]# cat chayi6/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 13937269
to_lsn = 14052833
last_lsn = 14052833

-----------------------------------------数据恢复-------------------------------------------
#1.停库 (数据记录点2798)
[root@mysql-db01 backup]# /etc/init.d/mysqld stop
#2.保证data目录是空的
[root@mysql-db01 backup]# mv /application/mysql/data /application/mysql/data_back
#3.准备恢复数据的前戏：
#前戏1：将全备目录，只做redo不做undo
[root@mysql-db01 backup]# innobackupex --apply-log --redo-only /backup/full/
[root@mysql-db01 backup]# cat full/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 13937269
last_lsn = 13941387

#前戏2：将最后一次差异备份合并到全备目录，redo和undo都做
[root@mysql-db01 backup]# innobackupex --apply-log --incremental-dir=/backup/chayi3 /backup/full/
[root@mysql-db01 backup]# cat full/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 14052833
last_lsn = 14052833

#前戏3：将全备整体做一次redo和undo
[root@mysql-db01 backup]# innobackupex --apply-log  /backup/full/

#4.恢复数据
[root@mysql-db01 backup]# innobackupex --copy-back /backup/full/

#5.授权
[root@mysql-db01 backup]# chown -R mysql.mysql /application/mysql/data

#6.启动
[root@mysql-db01 backup]# /etc/init.d/mysqld start

案例1：

企业中，为减少MySQL服务器压力过大。临时在不停库，不锁表且持续有新数据写入的情况下，做一个主从复制。

#准备测试环境
db02:主库
db01：新环境从库
#数据写入脚本：
[root@mysql-db02 ~]# cat test.sh 
#!/bin/bash
num=1
while true;
do
  mysql -uroot -p123 -e "insert into d1.t1 values($num);commit"
  let num++
  sleep 1
done

#db02库上创建d1库和t1表
mysql> create database d1;
mysql> use d1
mysql> create table t1(id int primary key auto_increment);
#0.启动脚本。新开一个窗口
[root@mysql-db02 ~]# sh test.sh 
#1.mysqldump全备db02主库数据热备，打点
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction > /tmp/full_all.sql
#2.将备份文件发送给db01
[root@mysql-db02 ~]# scp /tmp/full_all.sql root@10.0.0.51:/root/
#3.创建用户主从复制的登录用户
grant replication slave on *.* to rep@'%' identified by '123';
----------------------------------------------------------------------------------------------------------------------------


#1.db01MySQL配置文件需要加入一个server_id（不能同主库相同）
[root@mysql-db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=3
[root@mysql-db01 ~]# service mysqld restart
#2.db01查看全备热备快照点并记录
[root@mysql-db01 ~]# vim /tmp/full_all.sql 
CHANGE MASTER TO MASTER_LOG_FILE='dysql-bin.000016', MASTER_LOG_POS=28762;
#3.将加全备数据导入库
[root@mysql-db01 ~]# mysql < /root/full_all.sql 
#4.利用快照点做主从复制
mysql> CHANGE MASTER TO MASTER_LOG_FILE='dysql-bin.000016', MASTER_LOG_POS=28762,master_host='10.0.0.52',master_user='rep',master_password='123';
#5.开启主从复制
mysql> start slave;
mysql> show slave status\G
#6.检查
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

案例2：

背景:正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。
备份策略：每天23：00，计划任务调用mysqldump执行全备脚本
故障时间点：周五上午10点开发人员误删除一个核心业务表，如何恢复？

  • 思路步骤：
# 1.先停库 或者 尽量停止连接数据库的程序（tomcat php  python）避免数据二次伤害
/etc/init.d/mysqld stop
/etc/init.d/tomcat stop
/etc/init.d/php-fpm stop
/etc/init.d/uwsgi stop

# 2.准备一个新环境
[root@mysql-db02 scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data

# 3.将周四晚上23：00的全备数据恢复到新环境
[root@mysql-db01 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
    
方法一：将全备内容拷贝到新环境中，恢复到新环境 （909记录其他库的数据点）
[root@mysql-db01 ~]# scp /backup/full_2020-02-21.sql.gz 172.16.1.52:/tmp/
[root@mysql-db02 scripts]# zcat /tmp/full_2020-02-21.sql.gz |mysql

方法二：在新环境中创建一个远程连接用户(推荐，节省时间)
mysql> grant all on *.* to zls@'%' identified by '123';
[root@mysql-db01 ~]# zcat /backup/full_2020-02-21.sql.gz |mysql -uzls -p123 -h10.0.0.52

# 4.截取binlog日志（周四23：00到周五10：00）
/*查看binlog的名字和位置点，在全备中*/
[root@mysql-db01 backup]# zcat full_2020-02-21.sql.gz|head -25
MASTER_LOG_FILE='zls.000031'  MASTER_LOG_POS=1082279
/*截取binlog使用less查看*/
[root@mysql-db01 backup]# mysqlbinlog --base64-output=decode-rows -vvv /application/mysql/data/zls.000031|less
    起始位置点：1082279
    结束位置点：1109453
[root@mysql-db01 backup]# mysqlbinlog --start-position=1082279 --stop-position=1109453 /application/mysql/data/zls.000031 > /backup/inc1

/*删除核心业务表后其他表新增数据，截取*/
    起始位置点：1109570
    结束位置点：1142148
[root@mysql-db01 backup]# mysqlbinlog --start-position=1109570 --stop-position=1142148 /application/mysql/data/zls.000031 > /backup/inc2
    
# 5.将新增的数据，恢复到新环境
/*方法二：在新环境中创建一个远程连接用户*/
[root@mysql-db01 backup]# mysql -uzls -p123 -h10.0.0.52 < /backup/inc1 

[root@mysql-db01 backup]# mysql -uzls -p123 -h10.0.0.52 < /backup/inc2     
    
# 6.恢复业务
方法一：导出新环境中的核心业务表，恢复到旧的生产库中
    [root@mysql-db02 scripts]# mysqldump zls jiaoyi > /tmp/jiaoyi.sql
    [root@mysql-db02 scripts]# scp /tmp/jiaoyi.sql 172.16.1.51:/tmp/
    [root@mysql-db01 ~]# mysql zls < /tmp/jiaoyi.sql 
方法二：应用割接
    改代码：
    [root@mysql-db01 ~]# vim chengxu.sh 
    mysql -uzls -p123 -h10.0.0.52 -e "insert into d1.t1 values($num);commit;"

案例3：

背景：某大型网站，mysql数据库，数据量500G，每日更新量100M-200M
备份策略：xtrabackup，每周六0:00进行全备，周一到周五及周日00:00进行增量备份。
故障场景：周三下午2点出现数据库意外删除表操作。

  • 思路流程图


    image.png
#实验模拟：
#周五晚上23：59：59过度到周六晚上00点做全备
[root@mysql-db01 backup]# date -s '2020-01-31 23:59:55'
[root@mysql-db01 backup]# innobackupex --user=root --password=123 --no-timestamp /backup/full

[root@mysql-db01 backup]# cat full/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 14100299
last_lsn = 14101943


#周六晚上23：59：59过度到周日晚上00点做第一次增备
[root@mysql-db01 backup]# date -s '2020-02-01 23:59:55'
[root@mysql-db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1

[root@mysql-db01 backup]# cat inc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 14100299
to_lsn = 14201975
last_lsn = 14214878


#周日晚上23：59：59过度到周一晚上00点做第二次增备
[root@mysql-db01 backup]# date -s '2020-02-02 23:59:55'
[root@mysql-db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2

[root@mysql-db01 backup]# cat inc2/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 14201975
to_lsn = 14238490
last_lsn = 14261554

#周一晚上23：59：59过度到周二晚上00点做第三次增备
[root@mysql-db01 backup]# date -s '2020-02-03 23:59:55'
[root@mysql-db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc2 /backup/inc3

[root@mysql-db01 backup]# cat inc3/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 14238490
to_lsn = 14274200
last_lsn = 14294792

#周二晚上23：59：59过度到周三晚上00点做第四次增备
[root@mysql-db01 backup]# date -s '2020-02-04 23:59:55'
[root@mysql-db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc3 /backup/inc4

[root@mysql-db01 backup]# cat inc4/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 14274200
to_lsn = 14310716
last_lsn = 14325389

#模拟周三下午2点删除数据
[root@mysql-db01 backup]# date -s '2020-02-05 13:59:55'

------------------------------------------数据恢复----------------------------------------------
#1.停库（建议先停止连接数据库的程序）记录一个正常表不断插入的数值4120
[root@mysql-db01 backup]# /etc/init.d/mysqld stop
#2.保证data目录是空的（准备一个新环境）
[root@mysql-db01 backup]# mv /application/mysql/data /application/mysql/data_bak
#3.前戏
#前戏1：将（周五晚上23：59：59过度到周六晚上00点）全备进行CSR，只做redo 不做undo
[root@mysql-db01 backup]# innobackupex --apply-log --redo-only /backup/full/

[root@mysql-db01 backup]# cat full/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 14100299
last_lsn = 14101943

#前戏2：将（周六晚上23：59：59过度到周日晚上00点）第一次增备合并到full中，只做redo 不做undo
[root@mysql-db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1/ /backup/full/

[root@mysql-db01 backup]# cat full/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 14201975
last_lsn = 14214878

#前戏3：将（周日晚上23：59：59过度到周一晚上00点）第二次增备合并到full中，只做redo 不做undo
14238490
[root@mysql-db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2/ /backup/full/

[root@mysql-db01 backup]# cat full/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 14238490
last_lsn = 14261554


#前戏4：将（周一晚上23：59：59过度到周二晚上00点）第三次增备合并到full中，只做redo 不做undo
14274200
[root@mysql-db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc3/ /backup/full/

[root@mysql-db01 backup]# cat full/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 14274200
last_lsn = 14294792


#前戏5：将（周二晚上23：59：59过度到周三晚上00点）第四次增备合并到full中，redo和undo都做
14310716
[root@mysql-db01 backup]# innobackupex --apply-log --incremental-dir=/backup/inc4/ /backup/full/

[root@mysql-db01 backup]# cat full/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 14310716
last_lsn = 14325389

#前戏6：将所有合并后的整体full目录，模拟CSR，redo和undo都做
[root@mysql-db01 backup]# innobackupex --apply-log  /backup/full/

#4.恢复数据
[root@mysql-db01 backup]# innobackupex --copy-back /backup/full/

#5.授权
[root@mysql-db01 backup]# chown -R mysql.mysql /application/mysql/data

#6.启动数据库
[root@mysql-db01 backup]# /etc/init.d/mysqld start

#7.查看binlog的名字和位置点
[root@mysql-db01 backup]# cat full/xtrabackup_binlog_info 
zls.000001  176173

#7.2在旧data_back中的binlog中查找截取点
cd /application/mysql/data_back

#7.2利用mysqlbinlog查看binlog并截取
msyqlbinlog --base64-output=codeco-rows -vvv zls.000001

#8.截取备份后到删除表之前的binlog
起始位置点：176173
结束位置点：216042
[root@mysql-db01 data_bak]# mysqlbinlog --start-position=176173 --stop-position=216042 zls.000001 > /backup/binlog1


#9.截取删除表之后到结束的binlog
起始位置点：216159
结束位置点：242026
[root@mysql-db01 data_bak]# mysqlbinlog --start-position=216159 --stop-position=242026 zls.000001 > /backup/binlog2

#10.恢复到数据库中
[root@mysql-db01 data_bak]# mysql -uroot -p123 < /backup/binlog1

[root@mysql-db01 data_bak]# mysql -uroot -p123 < /backup/binlog2

#11.恢复业务
    - 导出删除的表恢复到旧环境
    - 应用割接
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。
禁止转载，如需转载请通过简信或评论联系作者。

推荐阅读更多精彩内容

  • mysql备份恢复
    1. 运维在数据库备份恢复方面的职责 1.1 设计备份策略 全备 、增量、时间、自动 1.2 日常备份检查 备份存...
    醉舞经阁半卷书A阅读 2,924评论 0赞 0
  • MYSQL 备份恢复
    1、备份工具mysqldump : MDP自带逻辑备份工具,SQL语句(Create database ,Crea...
    你好_请关照阅读 1,475评论 0赞 0
  • 七、MySQL备份恢复
    文/Bruce.Liu1 文章大纲备份概念1.1. 备份目的1.2. 备份方式1.3. 备份类型1.4. 备份对象...
    BruceLiu1阅读 9,286评论 4赞 15
  • mysql备份恢复详解
    mysql备份恢复详解 前言 为什么需要备份数据？ 数据的备份类型 MySQL备份数据的方式 备份需要考虑的问题 ...
    无名_5001阅读 3,599评论 0赞 0
  • MySQL备份和恢复
    1、什么是备份和恢复？ 备份：存储的数据副本；原始数据：持续改变；恢复：把副本应用到线上系统；仅能恢复至备份操作时...
    尛尛大尹阅读 7,787评论 0赞 0