MySQL备份与误删除数据恢复

1、安装

1. 安装
1.1 安装依赖包：
[root@db01 ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
[root@db01 ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
1.2 下载软件并安装
[root@db01 ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
[root@db01 ~]# yum -y install percona-xtrabackup-*.rpm


##2、备份命令介绍
[root@db01 ~]# innobackupex --version
xtrabackup: recognized server arguments: --datadir=/data/mysql/data_3306 --server-id=6 --server-id=6 --log_bin=/data/mysql/binlog_3306/mysql-bin 
innobackupex version 2.4.12 Linux (x86_64) (revision id: 170eb8c)
### MySQL5.7一般用2.4版本

备份流程

-- 3、备份方式--物理备份
-- INNODB
（1）进行 checkpoint,将内存已经提交事务的脏页，刷新到磁盘ibd文件，并且记录LSN.
（2）拷贝InnoDB数据（ibd,frm,ibdatal）文件到预定的备份路径。

-- NO-INNODB
（1）FLUSH TABLE WITH READ LOCK（FTWRL） GLOBAL READ LOCK   ##全局锁
（2）拷贝非InnoDB表各种数据
（3）解锁

-- REDO
自动将备份期间的redo变化保存下来，并记录全新的LSN号码。
具体参考：https://www.percona.com/doc/percona-xtrabackup/2.4/how_xtrabackup_works.html

恢复流程

1、prepare  准备过程
   （1）redo将变化前滚
   （2）undo将未提交的事物进行回滚
2、copy-back 恢复过程
    cp
    --copy-back
    授权

4、备份策略介绍

full + Inc ：全备+增量+binlog

增量（inc）-->每天都做上一天的增量（xbk默认） diff(差异)-->每天备份一点点。

full + binlog ：全备+日志

看自己公司都可以

xbk备份工具的使用

[root@db01 backup]# vim /etc/my.cnf    [client]必须添加
......
[client]
socket=/tmp/mysql.sock
......

-- ##  一、全备
[root@db01 ~]# cd /data/backup/
[root@db01 backup]# innobackupex  --user=root --password=123456 /data/backup
-- 全备自定制目录名
[root@db01 backup]# innobackupex  --user=root --password=123456 --no-timestamp /data/backup/full_`date +%F`
[root@db01 backup]# cd full_2019-12-27/
[root@db01 full_2019-12-27]# ll
total 12348
-rw-r----- 1 root root      487 Dec 27 10:18 backup-my.cnf
-rw-r----- 1 root root     8374 Dec 27 10:18 ib_buffer_pool
-rw-r----- 1 root root 12582912 Dec 27 10:17 ibdata1
-rw-r----- 1 root root       64 Dec 27 10:18 xtrabackup_binlog_info
-rw-r----- 1 root root      117 Dec 27 10:18 xtrabackup_checkpoints
-rw-r----- 1 root root      573 Dec 27 10:18 xtrabackup_info
-rw-r----- 1 root root     2560 Dec 27 10:18 xtrabackup_logfile

-- 5.3 备份信息介绍
xtrabackup_binlog_info :  
mysql-bin.000003	4432	411148c1-26bf-11ea-a420-000c298780da:1-32
作用: 二进制日志截取的起点位置.

xtrabackup_checkpoints :
backup_type = full-backuped   
from_lsn = 0
to_lsn = 227111469
last_lsn = 227111478
compact = 0
recover_binlog_info = 0

xtrabackup_info
xtrabackup_logfile


## -- 二、增量备份
(1) 模拟周一白天数据变化:
mysql -uroot -p123456
create database xbk charset utf8mb4;
use xbk;
create table t1 (id int);
create table t2 (id int);
create table t3 (id int);
insert into t1 values(1);
commit;
insert into t2 values(1);
commit;
insert into t3 values(1);
commit;
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

(2) 模拟周一晚上的增量inc1 
[root@db01 backup]# innobackupex  --user=root --password=123 --no-timestamp  --incremental  --incremental-basedir=/data/backup/full_2019-12-27    /data/backup/inc1

### 检查

(3) 模拟周二白天数据变化
mysql -uroot -p123456
use xbk;
insert into t1 values(2);
commit;
insert into t2 values(2);
commit;
insert into t3 values(2);
commit;
mysql> select * from t1
    -> ;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

(4) 模拟周二晚上的增量inc2 
[root@db01 backup]# innobackupex  --user=root --password=123 --no-timestamp  --incremental  --incremental-basedir=/data/backup/inc1    /data/backup/inc2

(5) 模拟周三白天的数据变化
mysql -uroot -p123456
use xbk;
insert into t1 values(3);
commit;
insert into t2 values(3);
commit;
insert into t3 values(3);
commit;

出现故障

-- 出故障了。。。。。。误删除。。。。跑路？
[root@db01 ~]# pkill mysqld
[root@db01 ~]# \rm -rf /data/mysql/data_3306/*

恢复思路:

1.找最近的全备
2.所有增量(inc1, inc2)合并全备(/data/backup/full_ 2019-12-27 )中
3.备份的prepare (应用redo和undo)
4.恢复备份cp ,改权限
5.启动数据库
6.截取周二备份到周三故障点的binlog,进行恢复.

增量合并准备

-- 1、增量合并并准备
## 需要用到的参数    
incremental-dir=/inc1 /full
--apply-log    ---->表示应用日志的过程（自动调用redo，undo（既会做回滚，又会做前滚））， prepare备份
--redo-only    ---->只应用redo，不应用undo   （lsn必须是能够连上的,为了让lsn能够连续)

(1) base_full 的 prepared --apply-log
[root@db01 ~]# innobackupex --apply-log --redo-only /data/backup/full_2019-12-27
## 解释--redo-only :This option should be used when preparing the base full
##                 backup and when merging all incrementals except the last one. 
##                 个人理解：各个备份的,lsn能够连续.

(2) 将inc1 合并到full中
[root@db01 ~]# innobackupex --apply-log --redo-only   --incremental-dir=/data/backup/inc1 /data/backup/full_2019-12-27
(3) 将inc2 合并到 full中 
[root@db01 ~]# innobackupex --apply-log  --incremental-dir=/data/backup/inc2 /data/backup/full_2019-12-27

(4) 整体备份的prepare.
[root@db01 ~]# innobackupex --apply-log  /data/backup/full_2019-12-27

• 启动数据库

[root@db01 inc2]# /etc/init.d/mysqld restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL.Logging to '/data/mysql/data_3306/mysql.log'.
.. SUCCESS! 

2、恢复备份

[root@db01 ~]# cd /data/backup/full_2019-12-27/
[root@db01 full_2019-12-27]# cp -a * /data/mysql/data_3306/
[root@db01 full_2019-12-27]# chown -R mysql.mysql /data
[root@db01 full_2019-12-27]# cd /data/backup/inc2
[root@db01 inc2]# cat /data/backup/inc2/xtrabackup_binlog_info 
mysql-bin.000008	2355	a7c862b0-20dc-11ea-b483-000c29502c0d:1-159
[root@db01 inc2]# mysqlbinlog --skip-gtids --include-gtids='a7c862b0-20dc-11ea-b483-000c29502c0d:159-162' /data/mysql/binlog_3306/mysql-bin.000008 >/data/backup/bin.sql

注意：此处'a7c862b0-20dc-11ea-b483-000c29502c0d是自己 cat /data/backup/inc2/xtrabackup_binlog_info 出来的数据。

### 补充：
mysql> show binlog events in 'mysql-bin.000008';  ##查看上面：159-162的方法如下

恢复binlog

-- 恢复binlog
mysql -uroot -p123456
mysql> set sql_log_bin=0;
mysql> source /data/backup/bin.sql
mysql> set sql_log_bin=1;

• 查看已经恢复成功

mysql> use xbk;
mysql> select * from t1