一、安装

yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
yum -y install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

二、XBK 的备份逻辑(简单)

  1. 自动判断表的类型
  2. Flush table with read lock (FTWRL)
  3. 非innoDB表进行锁表,然后进行cp
  4. innoDB表,立即CKPT,将提交的脏页刷写到磁盘,进行cp,cp同时将备份过程中产生的redo一起备份走

恢复:

  1. 非innodb直接恢复即可
  2. InnoDB表,需要XBK处理备份后,再进行恢复
    处理备份(prepare)?
    模仿了 自动故障恢复的流程:先redo前滚,再undo回滚

三、innobackupex工具使用

1、全备

1.1 配置文件

  • datadir
  • socket
vim /etc/my.cnf
[client]
socket=/tmp/mysql.sock

1.2 备份

innobackupex --user=root --password=123456  /data/backup/full
--- 查看备份目录
[root@db01 local]# cd /data/backup/full/
[root@db01 full]# ll
total 0
drwxr-x--- 11 root root 294 Sep 16 09:54 2019-09-16_09-54-01
[root@db01 2019-09-16_09-54-01]# pwd
/data/backup/full/2019-09-16_09-54-01
[root@db01 2019-09-16_09-54-01]# ll
total 77876
-rw-r----- 1 root root      488 Sep 16 09:54 backup-my.cnf
drwxr-x--- 2 root root       20 Sep 16 09:54 ha
-rw-r----- 1 root root      767 Sep 16 09:54 ib_buffer_pool
-rw-r----- 1 root root 79691776 Sep 16 09:54 ibdata1
drwxr-x--- 2 root root     4096 Sep 16 09:54 mysql
drwxr-x--- 2 root root       54 Sep 16 09:54 oldboy
drwxr-x--- 2 root root     8192 Sep 16 09:54 performance_schema
drwxr-x--- 2 root root       20 Sep 16 09:54 sch
drwxr-x--- 2 root root      176 Sep 16 09:54 school
drwxr-x--- 2 root root     8192 Sep 16 09:54 sys
drwxr-x--- 2 root root      246 Sep 16 09:54 world
drwxr-x--- 2 root root       46 Sep 16 09:54 x
-rw-r----- 1 root root       63 Sep 16 09:54 xtrabackup_binlog_info
-rw-r----- 1 root root      117 Sep 16 09:54 xtrabackup_checkpoints
-rw-r----- 1 root root      546 Sep 16 09:54 xtrabackup_info
-rw-r----- 1 root root     2560 Sep 16 09:54 xtrabackup_logfile

1.3 备份额外的文件介绍

  • xtrabackup_binlog_info
    • 备份时的binlog的位置点信息,截取binlog的起点信息
[root@db01 2019-09-16_09-54-01]# cat xtrabackup_binlog_info 
mysql-bin.000006        1356    1e91fb5a-cd38-11e9-b843-000c29303796:1-6
  • xtrabackup_checkpoints
    • 备份过程中产生的 LSN信息
[root@db01 2019-09-16_09-54-01]# cat xtrabackup_checkpoints 
backup_type = full-backuped    --- 备份类型和状态,全备还是增量
from_lsn = 0                --- 日志序列号,全备从0开始 ,整个备份起始的LSN
to_lsn = 126113880			--- ckpt时的LSN
last_lsn = 126113889        --- 备份结束时的LSN
  • xtrabackup_info
    • 备份总览
[root@db01 2019-09-16_09-54-01]# cat xtrabackup_info 
uuid = dc09cdeb-d824-11e9-b26e-000c29303796
name = 
tool_name = innobackupex
tool_command = --user=root --password=... /data/backup/full
tool_version = 2.4.12
ibbackup_version = 2.4.12
server_version = 5.7.26-log
start_time = 2019-09-16 09:54:01
end_time = 2019-09-16 09:54:03
lock_time = 0
binlog_pos = filename 'mysql-bin.000006', position '1356', GTID of the last change '1e91fb5a-cd38-11e9-b843-000c29303796:1-6'
innodb_from_lsn = 0
innodb_to_lsn = 126113880
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
  • xtrabackup_logfile
    • 记录备份的过程中产生的redo
[root@db01 2019-09-16_09-54-01]# file xtrabackup_logfile 
xtrabackup_logfile: data

1.4 恢复全备

  • 破坏数据
pkill mysqld 
\rm -rf  /data/mysql/data/*
  • prepare处理备份
[root@db01 ~]# innobackupex  --apply-log /data/backup/full/2019-09-16_09-54-01/

此时查看checkpoints 备份类型已经变成已处理

[root@db01 2019-09-16_09-54-01]# cat xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 126113880
last_lsn = 126113889
compact = 0
recover_binlog_info = 0
  • 恢复备份
[root@db01 2019-09-16_09-54-01]# cp -a * /data/mysql/data
[root@db01 2019-09-16_09-54-01]# chown -R mysql. /data/mysql/data/*

或者

innobackupex --copy-back /data/backup/full/2019-09-16_09-54-01

多实例恢复

[root@db01 /data/mysql/data]# innobackupex --user=root --password=456 --default-file=/data/3307/my.cnf --sock=/data/3307/mysql.sock /data/backup/3307/

远程备份

innobackupex --defaults-file=/etc/my.cnf --no-lock --user 'root' --password 'password123' --stream=tar ./ | ssh root@192.168.2.100 \ "cat - > /home/backup/database/`date +%Y%m%d`/`date +%H-%M`-backup.tar"
echo `date +%Y%m%d-%H%M`:备份结束 >> backup_db.log

手动指定备份文件名字

[root@db01 /data/backup/full/2019-09-16]# innobackupex --user=root --password=456 --no-timestamp /data/backup/full/2019-09-16

**2.1 全备+ 增量 **
故障案例说明:

  • 背景:某大型网站,MySQL5.7.20,数据量800G
  • 备份策略:Xtrabackup ,FULL(周日23:30)+INCN(周一到周六23:30)+binlog(每天中午12.30)
  • 故障:周三上午10点,数据库瘫了

故障案例模拟:

  • 模拟初始数据
--建库
mysql> create database xbk charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
--切换库
mysql> use xbk;
Database changed
-- 建表
mysql> create table t1 (id int)engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.01 sec)
-- 插入数据
mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
-- 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
  • 模拟周日的全备
-- 全备
[root@db01 backup]# innobackupex  --user=root --password=123456 --no-timestamp /data/backup/full
  • 模拟周一数据变化
-- 切换库
mysql> use xbk;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
-- 插入数据
mysql>  insert into t1 values(11),(22),(33);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
-- 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
  • 模拟周一晚上增量
[root@db01 backup]# innobackupex  --user=root --password=123456 --notimestamp --incremental --incremental-basedir=/data/backup/full /data/backup/inc1

查看全备和增量的关系
在这里插入图片描述

  • 模拟周二白天的数据变化
-- 切换库
mysql> use xbk;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
-- 插入数据
mysql>  insert into t1 values(111),(222),(333);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
-- 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   22 |
|   33 |
|  111 |
|  222 |
|  333 |
+------+
9 rows in set (0.00 sec)
  • 周二晚上增量
[root@db01 backup]# innobackupex  --user=root --password=123456 --notimestamp --incremental --incremental-basedir=/data/backup/inc1 /data/backup/inc2
  • 模拟周三白天的数据变化
-- 切换库
mysql> use xbk;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
-- 插入数据
mysql>  insert into t1 values(1111),(2222),(3333);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
-- 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

在这里插入图片描述

  • 破坏数据
pkill mysqld
\rm -fr /bata/mysql/data/*

恢复模拟:

  • 检查备份
    • full+inc1+inc2+binlog
[root@db01 backup]# pwd
/data/backup
[root@db01 backup]# ll
total 0
drwxr-x--- 12 root root 305 Sep 16 10:37 full
drwxr-x---  3 root root  33 Sep 16 10:41 inc1
drwxr-x---  3 root root  33 Sep 16 10:52 inc2
--- binlog
[root@db01 backup]# cd /data/binlog/
[root@db01 binlog]# ll
total 32
-rw-r----- 1 mysql mysql  201 Sep 11 16:13 mysql-bin.000001
-rw-r----- 1 mysql mysql  201 Sep 11 16:13 mysql-bin.000002
-rw-r----- 1 mysql mysql  201 Sep 11 16:14 mysql-bin.000003
-rw-r----- 1 mysql mysql  201 Sep 11 16:14 mysql-bin.000004
-rw-r----- 1 mysql mysql  177 Sep 11 16:58 mysql-bin.000005
-rw-r----- 1 mysql mysql 1379 Sep 16 10:13 mysql-bin.000006
-rw-r----- 1 mysql mysql 1078 Sep 16 10:38 mysql-bin.000007
-rw-r----- 1 mysql mysql  210 Sep 16 10:16 mysql-bin.index
  • 合并并整理备份
    • base_full 进行prepare处理备份
[root@db01 binlog]# innobackupex  --apply-log --redo-only /data/backup/full/ 
  • 合并inc1到full中,处理备份
[root@db01 binlog]# innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1/2019-09-16_10-41-52 /data/backup/full
  • 合并inc2到full中,处理备份
[root@db01 binlog]# innobackupex --apply-log  --incremental-dir=/data/backup/inc2/2019-09-16_10-47-42 /data/backup/full
  • 最后一次整理
[root@db01 binlog]# innobackupex --apply-log  --incremental-dir=/data/backup/inc2/2019-09-16_10-47-42 /data/backup/full
  • 恢复xbk备份
  • 截取日志(起点,终点)
    • 起点
[root@db01 inc2]# cat 2019-09-16_10-47-42/xtrabackup_binlog_info 
mysql-bin.000007        1078    1e91fb5a-cd38-11e9-b843-000c29303796:1-6,
f66f1a5b-d827-11e9-bdf6-000c29303796:1-4
  • 启动数据库
  • 恢复binlog
mysql> sql_log_bin=0;
mysql> source /data/backup/bin.sql;
mysql> sql_log_bin=1;
mysql> use xbk;
mysql> select *from t1;
    -> ;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   22 |
|   33 |
|  111 |
|  222 |
|  333 |
| 1111 |
| 2222 |
| 3333 |
+------+
12 rows in set (0.00 sec)

从全备当中恢复单表,可以用表空间迁移