MySQL物理备份工具使用(Percona Xtrabackup)
一、安装
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 的备份逻辑(简单)
- 自动判断表的类型
- Flush table with read lock (FTWRL)
- 非innoDB表进行锁表,然后进行cp
- innoDB表,立即CKPT,将提交的脏页刷写到磁盘,进行cp,cp同时将备份过程中产生的redo一起备份走
恢复:
- 非innodb直接恢复即可
- 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)
从全备当中恢复单表,可以用表空间迁移
本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 J.のblog!
评论