xtrabackup全量增量备份+全量增量恢复+binlog增量恢复

---

1.全量备份

#全量备份
xtrabackup --defaults-file=/etc/my.cnf  --host=127.0.0.1 --user=root --password='rootroot' --port=3306 --backup --target-dir=/data/backup/full

#查看备份位置点位
cat /data/backup/full/xtrabackup_binlog_info 
binlog.000002	197	b679baa5-eb07-11ee-875d-525400329a89:1

cat /data/backup/full/xtrabackup_info 
uuid = 3681c2d2-eb08-11ee-875d-525400329a89
name = 
tool_name = xtrabackup
tool_command = --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --password=... --port=3306 --backup --target-dir=/data/backup/full
tool_version = 8.0.28-21
ibbackup_version = 8.0.28-21
server_version = 8.0.28
start_time = 2024-03-26 08:31:37
end_time = 2024-03-26 08:31:40
lock_time = 1
binlog_pos = filename 'binlog.000002', position '197', GTID of the last change 'b679baa5-eb07-11ee-875d-525400329a89:1'
innodb_from_lsn = 0
innodb_to_lsn = 88049580
partial = N
incremental = N
format = file
compressed = N
encrypted = N
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

2.增量备份

2.1 模拟全量备份后数据变化

#模拟数据变化
mysql> create database bbb;
mysql> use bbb;
mysql> create table t1 (id int);
1
2
3
4

2.2 增量备份

#增量备份
xtrabackup --defaults-file=/etc/my.cnf  --host=127.0.0.1 --user=root --password='rootroot' --port=3306 --backup --target-dir=/data/backup/inc --incremental-basedir=/data/backup/full

#查看备份位置点位：
cat /data/backup/inc/xtrabackup_binlog_info 
binlog.000003	197	b679baa5-eb07-11ee-875d-525400329a89:1-3

cat /data/backup/inc/xtrabackup_info 
uuid = 6dc78717-eb08-11ee-875d-525400329a89
name = 
tool_name = xtrabackup
tool_command = --defaults-file=/etc/my.cnf --host=127.0.0.1 --user=root --password=... --port=3306 --backup --target-dir=/data/backup/inc --incremental-basedir=/data/backup/full
tool_version = 8.0.28-21
ibbackup_version = 8.0.28-21
server_version = 8.0.28
start_time = 2024-03-26 08:33:09
end_time = 2024-03-26 08:33:12
lock_time = 1
binlog_pos = filename 'binlog.000003', position '197', GTID of the last change 'b679baa5-eb07-11ee-875d-525400329a89:1-3'
innodb_from_lsn = 88049580
innodb_to_lsn = 88063778
partial = N
incremental = Y
format = file
compressed = N
encrypted = N
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

3.binlog增量备份

3.1 模拟增量备份后数据变化

#模拟数据变化
mysql> use bbb;
mysql> insert into t1 values (1);
mysql> insert into t1 values (2);

#查看位置
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000003 |      739 |              |                  | b679baa5-eb07-11ee-875d-525400329a89:1-5 |
+---------------+----------+--------------+------------------+------------------------------------------+

#切换日志
mysql> flush logs;
mysql> insert into t1 values (3);

#查看位置
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000004 |      468 |              |                  | b679baa5-eb07-11ee-875d-525400329a89:1-6 |
+---------------+----------+--------------+------------------+------------------------------------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

3.2 备份binlog文件

#可以根据前面xtrabackup增量备份的节点，备份增量的binlog文件
cp /data/3306/data/binlog.* /data/backup/binlog/
1
2

4.模拟数据库奔溃

杀掉mysql进程，删除数据目录文件和binlog日志

kill -9 ....
rm -rf /data/3306/data/*
1
2

5.恢复

5.1 准备阶段

#准备阶段：
1 准备全备份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full
2 准备增量备份的日志：
xtrabackup --prepare --target-dir=/data/backup/full --incremental-dir=/data/backup/inc

#注意：
--apply-log-only 此参数的目的是未提交的事务不回滚，在存在多个增量备份时，准备阶段的最后一个增量备份时，不要加此参数
1
2
3
4
5
6
7
8

5.2 拷贝数据回目录

#拷贝数据回数据目录
xtrabackup --host=127.0.0.1 --user=root --password='rootroot' --port=3306 --datadir=/data/3306/data --copy-back --target-dir=/data/backup/full
1
2

5.3 修改权限

2.5 修改数据目录的权限和属性：
chown -R mysql:mysql /data/3306/data
chmod -R 755 /data/3306/data
1
2
3

6.启动数据库

cd /usr/local/mysql/support-files
./mysql.server  start
1
2

7.恢复增量binlog

#根据最后一个增量备份的节点位置，恢复后面的binlog日志
mysqlbinlog /data/backup/binlog/binlog.000003 --start-position=197 | mysql -uroot -p'rootroot'
mysqlbinlog /data/backup/binlog/binlog.000004 | mysql -uroot -p'rootroot'
1
2
3

8.验证

mysql -uroot -p -e "select * from bbb.t1;"
Enter password: 
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

#数据完整，恢复完成
1
2
3
4
5
6
7
8
9
10
11