xtrabackup全量与增量备份

2024-01-23 11:28:47

xtrabackup全量与增量备份

Xtrabackup工具支持对InnoDB存储引擎的增量备份，工作原理如下

1、在InnoDB内部会维护一个redo/undo日志文件，也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。

2、Xtrabackup在启动时会记住log sequence number（LSN），并且复制所有的数据文件。复制过程需要一些时间，所以这期间如果数据文件有改动，那么将会使数据库处于一个不同的时间点。这时，xtrabackup会运行一个后台进程，用于监视事务日志，并从事务日志复制最新的修改。Xtrabackup必须持续的做这个操作，是因为事务日志是会轮转重复的写入，并且事务日志可以被重用。所以xtrabackup自启动开始，就不停的将事务日志中每个数据文件的修改都记录下来。

3、上面就是xtrabackup的备份过程。接下来是准备（prepare）过程，在这个过程中，xtrabackup使用之前复制的事务日志，对各个数据文件执行灾难恢复（就像mysql刚启动时要做的一样）。当这个过程结束后，数据库就可以做恢复还原了，这个过程在xtrabackup的编译二进制程序中实现。程序innobackupex可以允许我们备份MyISAM表和frm文件从而增加了便捷和功能。Innobackupex会启动xtrabackup，直到xtrabackup复制数据文件后，然后执行FLUSH TABLES WITH READ LOCK来阻止新的写入进来并把MyISAM表数据刷到硬盘上，之后复制MyISAM数据文件，最后释放锁。

4、备份MyISAM和InnoDB表最终会处于一致，在准备（prepare）过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始时的点。这个时间点与执行FLUSH TABLES WITH READ LOCK的时间点相同，所以myisam表数据与InnoDB表数据是同步的。类似oracle的，InnoDB的prepare过程可以称为recover（恢复），myisam的数据复制过程可以称为restore（还原）。

5、Xtrabackup 和 innobackupex这两个工具都提供了许多前文没有提到的功能特点。手册上有对各个功能都有详细的介绍。简单介绍下，这些工具提供了如流（streaming）备份，增量（incremental）备份等，通过复制数据文件，复制日志文件和提交日志到数据文件（前滚）实现了各种复合备份方式。

1. 安装xtrabackup

[root@localhost ]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
--2021-04-15 00:08:00--  http://mirrors.aliyun.com/repo/epel-7.repo
正在解析主机 mirrors.aliyun.com (mirrors.aliyun.com)... 112.49.28.242, 112.49.28.238, 112.49.28.243, ...
正在连接 mirrors.aliyun.com (mirrors.aliyun.com)|112.49.28.242|:80... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：664 [application/octet-stream]
正在保存至: “/etc/yum.repos.d/epel.repo”

100%[=====================================================>] 664         --.-K/s 用时 0s      

2021-04-15 00:08:00 (163 MB/s) - 已保存 “/etc/yum.repos.d/epel.repo” [664/664])
[root@localhost ]#  yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
[root@localhost ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
--2021-04-15 00:10:26--  https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
正在解析主机 downloads.percona.com (downloads.percona.com)... 162.220.4.222, 74.121.199.231, 162.220.4.221
正在连接 downloads.percona.com (downloads.percona.com)|162.220.4.222|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：7992904 (7.6M) [application/octet-stream]
正在保存至: “percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm”

100%[=====================================================>] 7,992,904   2.24MB/s 用时 3.4s   

2021-04-15 00:10:31 (2.24 MB/s) - 已保存 “percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm” [7992904/7992904])
[root@localhost ~]# yum -y install percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm

2. xtrabackup全备

[root@localhost ~]# innobackupex --user=root --password=test --no-timestamp /opt/full
[root@localhost ~]# ll /opt/full/
总用量 12340
-rw-r-----. 1 root root      487 4月  15 00:13 backup-my.cnf
-rw-r-----. 1 root root      350 4月  15 00:13 ib_buffer_pool
-rw-r-----. 1 root root 12582912 4月  15 00:13 ibdata1
drwxr-x---. 2 root root     4096 4月  15 00:13 mysql
drwxr-x---. 2 root root     8192 4月  15 00:13 performance_schema
drwxr-x---. 2 root root     8192 4月  15 00:13 sys
drwxr-x---. 2 root root       49 4月  15 00:13 test
-rw-r-----. 1 root root       67 4月  15 00:13 xtrabackup_binlog_info
-rw-r-----. 1 root root      135 4月  15 00:13 xtrabackup_checkpoints
-rw-r-----. 1 root root      555 4月  15 00:13 xtrabackup_info
-rw-r-----. 1 root root     2560 4月  15 00:13 xtrabackup_logfile

2.1 模拟数据库故障

[root@localhost ~]# pkill mysqld
[root@localhost ~]# ls /data/mysql/data/
auto.cnf         error.log       ibtmp1                     performance_schema  sys
ca-key.pem       ib_buffer_pool  localhost.localdomain.pid  private_key.pem     test
ca.pem           ibdata1         master-binlog.000001       public_key.pem
client-cert.pem  ib_logfile0     master-binlog.index        server-cert.pem
client-key.pem   ib_logfile1     mysql                      server-key.pem
[root@localhost ~]# rm -rf /data/mysql/data/*
[root@localhost ~]# ls /data/mysql/data/

2.2恢复完全备份的数据

[root@localhost ~]# innobackupex --apply-log /opt/full/

2.3拷贝数据到/data/mysql/data下及修改权限

[root@localhost ~]# chown -R mysql.mysql /data/mysql/data/*
[root@localhost ~]# innobackupex --apply-log /opt/full/

2.4启动数据库查看数据

[root@localhost ~]# service mysql start
Starting MySQL.Logging to ‘/data/mysql/data/error.log‘.
.. SUCCESS! 
[root@localhost ~]# mysql -uroot -ptest
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.32-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql>

3 .xtrabackup全备+增量+binlog恢复演练

周日晚上23:00全备，周一周二晚上23:00增备，周三早上10:00数据库奔溃。

恢复：周日全备+周一增备+周二增备+（周二晚上十一点至周三早上十点binlog恢复）

先修改my.cnf，指定binlog目录（生产环境上一般binlog需要单独一个目录）

[root@localhost ~]# mkdir /data/mysql/binlog
[root@localhost ~]# chown -R mysql.mysql /data/mysql/binlog/
[root@localhost ~]# vi /etc/my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/data
port = 3306
socket = /tmp/mysql.sock
log-error = error.log
slow_query_log_file = slow.log
character-set-server = utf8
open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000
lower_case_table_names =1
server_id=1
gtid_mode=on
enforce_gtid_consistency=on
#binlog
log_bin=/data/mysql/binlog/binlog
log-slave-updates=1
binlog_format=row

#relay log
skip-slave-start=1
"/etc/my.cnf" 30L, 550C written

重启数据库

[root@localhost ~]# service mysql restart
Shutting down MySQL............ SUCCESS! 
Starting MySQL. SUCCESS! 
[root@localhost ~]#

3.1. 模拟数据

[root@localhost ~]# mysql -uroot -ptest
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.32-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql> create database t1 charset utf8mb4;
Query OK, 1 row affected (0.01 sec)

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

mysql> insert into t1 value(1),(2),(3);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

3.2进行周日的全备

[root@localhost ~]# innobackupex --user=root --password=test --no-timestamp /opt/full_$(date +%F)
[root@localhost ~]# ls /opt/full_2021-04-15/
backup-my.cnf   ibdata1  performance_schema  t1    xtrabackup_binlog_info  xtrabackup_info
ib_buffer_pool  mysql    sys                 test  xtrabackup_checkpoints  xtrabackup_logfile
[root@localhost ~]# cat /opt/full_2021-04-15/xtrabackup_checkpoints 
backup_type = full-backuped //类型全备
from_lsn = 0
to_lsn = 2758998
last_lsn = 2759007
compact = 0
recover_binlog_info = 0
flushed_lsn = 2759007

3.3 模拟周一数据变化

[root@localhost ~]# mysql -uroot -ptest
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.32-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql> create database inc1 charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

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

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

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

3.4进行周一的增量备份

周一的增量基于周日的全备

[root@localhost ~]# innobackupex --user=root --password=test --no-timestamp --incremental-basedir=/opt/full_2021-04-15 --incremental /opt/inc1
[root@localhost ~]# ls /opt/
full  full_2021-04-15  inc1  mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]#

3.5检查备份的LSN

[root@localhost ~]# cat /opt/full_2021-04-15/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 2758998
last_lsn = 2759007
compact = 0
recover_binlog_info = 0
flushed_lsn = 2759007
[root@localhost ~]# cat /opt/inc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2758998
to_lsn = 2766538
last_lsn = 2766547
compact = 0
recover_binlog_info = 0
flushed_lsn = 2766547

3.6模拟周二数据变化

[root@localhost ~]# mysql -uroot -ptest
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.32-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql> create database inc2 charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

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

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

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

3.7进行周二的增量备份

周二的增量基于周一的增量进行备份，余下类推

[root@localhost ~]# innobackupex --user=root --password=test --no-timestamp --incremental-basedir=/opt/inc1/ --incremental /opt/inc2
[root@localhost ~]# ls /opt/
full  full_2021-04-15  inc1  inc2  mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz

3.8检查备份的LSN

[root@localhost ~]# cat /opt/full_2021-04-15/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 2758998
last_lsn = 2759007
compact = 0
recover_binlog_info = 0
flushed_lsn = 2759007
[root@localhost ~]# cat /opt/inc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2758998
to_lsn = 2766538
last_lsn = 2766547
compact = 0
recover_binlog_info = 0
flushed_lsn = 2766547
[root@localhost ~]# cat /opt/inc2/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2766538
to_lsn = 2774211
last_lsn = 2774220
compact = 0
recover_binlog_info = 0
flushed_lsn = 2774220

3.9模拟周三数据变化

[root@localhost ~]# mysql -uroot -ptest
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.32-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql> create database inc3 charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

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

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

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

3.10模拟上午10点数据库奔溃

[root@localhost ~]# pkill mysqld
[root@localhost ~]# rm -rf /data/mysql/data/*

思路：查找可用备份full_2021-04-15+inc1+inc2，周三的增量备份是晚上23:00，那周三早上10:00数据库崩溃了，时间还没到来不及做周三的增量。除了全备，增量1，增量2到故障时间点的binlog。

如何恢复备份？

恢复全备+增量+binlog

3.11恢复过程

（1）整理full

[root@localhost ~]# innobackupex --apply-log --redo-only /opt/full_2021-04-15/

#--redo-only的意思这个选项应该在整理全备和所有增量合并时，除了最后一次增量。

（2）合并inc1到full_2021-04-15，并整理备份

[root@localhost ~]# innobackupex --apply-log --redo-only --incremental-dir=/opt/inc1 /opt/full_2021-04-15/ /
[root@localhost ~]# cat /opt/full_2021-04-15/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 2766538
last_lsn = 2766547
compact = 0
recover_binlog_info = 0
flushed_lsn = 2766547
[root@localhost ~]# cat /opt/inc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2758998
to_lsn = 2766538
last_lsn = 2766547
compact = 0
recover_binlog_info = 0
flushed_lsn = 2766547

（3）合并inc2到full_2021-04-15，并整理备份

[root@localhost ~]#  innobackupex --apply-log --incremental-dir=/opt/inc2 /opt/full_2021-04-15/ 
[root@localhost ~]# cat /opt/full_2021-04-15/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 2774211
last_lsn = 2774220
compact = 0
recover_binlog_info = 0
flushed_lsn = 2774220
[root@localhost ~]# cat /opt/inc2/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2766538
to_lsn = 2774211
last_lsn = 2774220
compact = 0
recover_binlog_info = 0
flushed_lsn = 2774220

（4）最后一次整理full_2021-04-15

[root@localhost ~]# innobackupex --apply-log /opt/full_2021-04-15/

至此，已经恢复到周二晚上23:00的数据了。

码农公寓