Mysql物理备份之Xtrabackup

Linux Mysql
Mysql
发布日期:   2022-06-05
文章字数:   1.9k
阅读时长:   9 分
阅读次数:  

一、Xtrabackup介绍

MySQL冷备、mysqldump、MySQL热拷贝都无法实现对数据库进行增量备份。在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。而Percona-Xtrabackup就是为了实现增量备份而出现的一款主流备份工具，xtrabakackup有2个工具，分别是xtrabakup、innobakupe。

Percona-xtrabackup是 Percona公司开发的一个用于MySQL数据库物理热备的备份工具，支持MySQL、Percona server和MariaDB，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，而不能备份MyISAM数据表。

二、Xtrabackup配置

#安装依赖
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

#下载Xtrabackup
wget "https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm"

#安装
yum localinstall percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

三、测试全量备份恢复

全量备份

innobackupex --no-timestamp /backup/full

备份完成目录

drwxr-x--- 2 root root      122 Nov  9 17:50 backup
-rw-r----- 1 root root      430 Nov  9 17:50 backup-my.cnf
drwxr-x--- 2 root root       56 Nov  9 17:50 binlog
drwxr-x--- 2 root root       50 Nov  9 17:50 blog
drwxr-x--- 2 root root       62 Nov  9 17:50 ceshi
drwxr-x--- 2 root root       54 Nov  9 17:50 chayi
drwxr-x--- 2 root root       56 Nov  9 17:50 chayi2
drwxr-x--- 2 root root       48 Nov  9 17:50 db1
drwxr-x--- 2 root root       48 Nov  9 17:50 db2
drwxr-x--- 2 root root       20 Nov  9 17:50 hhhh
drwxr-x--- 2 root root       92 Nov  9 17:50 huhuhahei
drwxr-x--- 2 root root       20 Nov  9 17:50 huhuhahei1
-rw-r----- 1 root root 79691776 Nov  9 17:50 ibdata1
-rw-r----- 1 root root 52428800 Nov  9 17:50 ibdata2
drwxr-x--- 2 root root       60 Nov  9 17:50 inc1
drwxr-x--- 2 root root       60 Nov  9 17:50 inc2
drwxr-x--- 2 root root     4096 Nov  9 17:50 mysql
drwxr-x--- 2 root root       76 Nov  9 17:50 oldboy
drwxr-x--- 2 root root     4096 Nov  9 17:50 performance_schema
drwxr-x--- 2 root root      160 Nov  9 17:50 slow_query_log
drwxr-x--- 2 root root      284 Nov  9 17:50 test
drwxr-x--- 2 root root       66 Nov  9 17:50 test_binlog
drwxr-x--- 2 root root       76 Nov  9 17:50 wordpress
drwxr-x--- 2 root root      184 Nov  9 17:50 world
-rw-r----- 1 root root       21 Nov  9 17:50 xtrabackup_binlog_info    #记录binlog文件名和binlog的位置点
-rw-r----- 1 root root      117 Nov  9 17:50 xtrabackup_checkpoints    #备份的类型、状态和LSN状态信息文件  
-rw-r----- 1 root root      458 Nov  9 17:50 xtrabackup_info    #备份汇总信息
-rw-r----- 1 root root     2560 Nov  9 17:50 xtrabackup_logfile    #备份的redo文件

模拟mysql数据丢失

#关闭数据库
/etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS

#删除数据目录
rm -rf /application/mysql/data/

准备备份
将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚，模拟CSR的过程

innobackupex --apply-log /backup/full

拷贝数据

innobackupex --copy-back /backup/full

这里需要注意恢复数据时没有指定数据目录 默认会从/etc/my.cnf中寻找 所以配置文件中需要指定数据库目录

给拷贝的data目录权限

chown -R mysql:mysql /application/mysql/data/

启动数据库

/etc/init.d/mysqld start
Starting MySQL.Logging to '/application/mysql/data/db.err'.
SUCCESS!

确认数据恢复

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| backup             |
| binlog             |
| blog               |
| ceshi              |
| chayi              |
| chayi2             |
| db1                |
| db2                |
| hhhh               |
| huhuhahei          |
| huhuhahei1         |
| inc1               |
| inc2               |
| mysql              |
| oldboy             |
| performance_schema |
| slow_query_log     |
| test               |
| test_binlog        |
| wordpress          |
| world              |
+--------------------+
22 rows in set (0.00 sec

四、测试增量备份

先进行全量备份

innobackupex --no-timestamp /backup/full

模拟增加数据

mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

mysql> use test1;
Database changed
mysql> create table test1(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

进行增量备份

innobackupex --no-timestamp --incremental --incremental-basedir=/backup/full /backup/test1
--incremental：开启增量备份功能
--incremental-basedir：上一次备份的路径

确认备份是否完整

#全备
[root@db backup]# cat full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 160492594
last_lsn = 160492594
compact = 0
recover_binlog_info = 0

#增备
[root@db backup]# cat test1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 160492594
to_lsn = 160498235
last_lsn = 160498235
compact = 0
recover_binlog_info = 0

可以看到增备的from_lsn是全备的last_lsn 证明数据是完整的

再次模拟增加数据

mysql> create database test2;
Query OK, 1 row affected (0.00 sec)

mysql> use test2;
Database changed
mysql> create table test2(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test2 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

进行第二次增量备份

innobackupex --no-timestamp --incremental --incremental-basedir=/backup/test1 /backup/test2

再次确认备份完整性

#全备
[root@db backup]# cat full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 160492594
last_lsn = 160492594
compact = 0
recover_binlog_info = 0

#增备
[root@db backup]# cat test1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 160492594
to_lsn = 160498235
last_lsn = 160498235
compact = 0
recover_binlog_info = 0

#二次增备
[root@db backup]# cat test2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 160498235
to_lsn = 160503761
last_lsn = 160503761
compact = 0
recover_binlog_info = 0

可以发现数据是完整的

模拟数据丢失

#关闭数据库
/etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS

#删除数据目录
rm -rf /application/mysql/data/

准备备份
1）full+inc1+inc2
2）需要将inc1和inc2按顺序合并到full中
3）分步骤进行–apply-log

第一步：在全备中apply-log时，只应用redo，不应用undo

innobackupex --apply-log --redo-only /backup/full

第二步：合并inc1合并到full中，并且apply-log，只应用redo，不应用undo

innobackupex --apply-log --redo-only --incremental-dir=/backup/test1 /backup/full

第三步：合并inc2合并到full中，redo和undo都应用

innobackupex --apply-log --incremental-dir=/backup/test2 /backup/full

第四步：整体full执行apply-log，redo和undo都应用

innobackupex --apply-log /backup/full

确认数据完整

[root@db backup]# cat full/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 160503761
last_lsn = 160503761
compact = 0
recover_binlog_info = 0

恢复数据

#拷贝数据
innobackupex --copy-back /backup/full
#权限
chown -R mysql:mysql /application/mysql/data
#启动
/etc/init.d/mysqld start

确认数据是否恢复

mysql> select * from test1.test1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec

mysql> select * from test2.test2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

五、测试差异备份

首先还是进行增量备份

innobackupex --no-timestamp /backup/full

模拟数据变更

mysql> create database chayi1;
Query OK, 1 row affected (0.00 sec)

mysql> create table chayi1(id int);
ERROR 1046 (3D000): No database selected
mysql> use chayi1;
Database changed
mysql> create table chayi1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into chayi1 values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from chayi1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

模拟第二次数据变化

mysql> create database chayi2;
Query OK, 1 row affected (0.01 sec)

mysql> use chayi2;
Database changed

mysql> create table chayi22(id int);
Query OK, 0 rows affected (0.00 sec）

mysql> insert into chayi22 values(1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from chayi22;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

确认数据完整性

#全备
[root@db backup]# cat full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 160504901
last_lsn = 160504901
compact = 0
recover_binlog_info = 0

#第一次差异备份
[root@db backup]# cat chayi1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 160504901
to_lsn = 160510578
last_lsn = 160510578
compact = 0
recover_binlog_info = 0

#第二次差异备份
[root@db backup]# cat chayi2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 160504901
to_lsn = 160517449
last_lsn = 160517449
compact = 0
recover_binlog_info = 0

可以发现差异备份第一次和第二次的起点都是901

模拟数据丢失

#关闭数据库
/etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS

#删除数据目录
rm -rf /application/mysql/data/

准备数据

第一步：在全备中apply-log时，只应用redo，不应用undo

innobackupex --apply-log --redo-only /backup/full

第二步：合并最后一次差异备份，redo undo都做

innobackupex --apply-log --incremental-dir=/backup/chayi2 /backup/full

第三步：整体full执行apply-log，redo和undo都应用

innobackupex --apply-log /backup/full

恢复数据

#拷贝数据
innobackupex --copy-back /backup/full
#权限
chown -R mysql:mysql /application/mysql/data
#启动
/etc/init.d/mysqld start

测试数据是否恢复

mysql> select * from chayi1.chayi1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select * from chayi2.chayi22;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
文章作者: huhuhahei
文章链接: https://huhuhahei987.github.io/247e674/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 huhuhahei !
Linux Mysql
赏
评论
  目录