本案例主要是通过实验的方式体验xtrabackup 流与压缩功能的结合,共涉及2种方式:
- 流+自带压缩(qpress)
- 流+gzip压缩
一、增量备份(流+自带压缩)
–extra-lsndir:存放本次备份的xtrabackup_checkpoints,后面的增量备份时,–incremental-basedir就指向前一日的extra-lsndir目录便可
–compress 需提前安装qpress解压工具
- 全备
mkdir -p /mysql/backup/xtrabackup/full/backup_full
xtrabackup --defaults-file=/data/mysql/mysql3307/my3307.cnf -uroot -pmysql --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/full --extra-lsndir=/mysql/backup/xtrabackup/full --parallel=4 --compress --compress-threads=2 --stream=xbstream >/mysql/backup/xtrabackup/full/full_com_xbs.xbstream
--2024-04-10T10:22:03.925116+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
- 变更数据
root@local 10:27:00 [test]> select * from tab2;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
+------+------+
6 rows in set (0.00 sec)
root@local 10:27:05 [test]> insert into tab2 values(7,'g');
Query OK, 1 row affected (0.35 sec)
root@local 10:27:20 [test]> commit;
Query OK, 0 rows affected (0.00 sec)
root@local 10:27:23 [test]> quit
Bye
- 第一次增量备份
mkdir -p /mysql/backup/xtrabackup/inc1/backup_inc1
xtrabackup --defaults-file=/data/mysql/mysql3307/my3307.cnf --no-server-version-check -uroot -pmysql --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/inc1 --extra-lsndir=/mysql/backup/xtrabackup/inc1 --incremental-basedir=/mysql/backup/xtrabackup/full --parallel=4 --compress --compress-threads=2 --stream=xbstream >/mysql/backup/xtrabackup/inc1/inc_com_xbs_1.xbstream
--2024-04-10T10:29:43.140080+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
- 变更数据
root@local 10:46:46 [test]> insert into tab2 values(8,'h');
Query OK, 1 row affected (0.37 sec)
root@local 10:47:06 [test]> insert into tab2 values(9,'j');
Query OK, 1 row affected (0.10 sec)
root@local 10:47:12 [test]> commit;
Query OK, 0 rows affected (0.00 sec)
root@local 10:47:17 [test]> select * from tab2;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
| 8 | h |
| 9 | j |
+------+------+
9 rows in set (0.00 sec)
- 第二次增量备份
mkdir -p /mysql/backup/xtrabackup/inc2/backup_inc2
xtrabackup --defaults-file=/data/mysql/mysql3307/my3307.cnf --no-server-version-check -uroot -pmysql --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/inc2 --extra-lsndir=/mysql/backup/xtrabackup/inc2 --incremental-basedir=/mysql/backup/xtrabackup/inc1 --parallel=4 --compress --compress-threads=2 --stream=xbstream >/mysql/backup/xtrabackup/inc2/inc_com_xbs_2.xbstream
--2024-04-10T10:49:26.699918+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
查看日志,确认这一步骤执行成功(最后一行日志显示“completed OK!”):
备份目录结构如下:
|-- full
| |-- backup_full
| |-- full_com_xbs.xbstream
| |-- xtrabackup_checkpoints
| `-- xtrabackup_info
|-- inc1
| |-- backup_inc1
| |-- inc_com_xbs_1.xbstream
| |-- xtrabackup_checkpoints
| `-- xtrabackup_info
`-- inc2
|-- backup_inc2
|-- inc_com_xbs_2.xbstream
|-- xtrabackup_checkpoints
`-- xtrabackup_info
[root@mysql8 xtrabackup]# cat full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 20991668
last_lsn = 20991678
flushed_lsn = 20991668
redo_memory = 0
redo_frames = 0
[root@mysql8 xtrabackup]# cat inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 20991668
to_lsn = 20992046
last_lsn = 20992046
flushed_lsn = 20992046
redo_memory = 0
redo_frames = 0
[root@mysql8 xtrabackup]# cat inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 20992046
to_lsn = 20994404
last_lsn = 20994404
flushed_lsn = 20994404
redo_memory = 0
redo_frames = 0
恢复
- prepare全量 只应用日志,不回滚
cd /mysql/backup/xtrabackup/full
xbstream -x -v --decompress --decompress-threads=2 -C /mysql/backup/xtrabackup/full/backup_full < /mysql/backup/xtrabackup/full/full_com_xbs.xbstream
xtrabackup --no-server-version-check --prepare --apply-log-only --use-memory=2G --target-dir=/mysql/backup/xtrabackup/full/backup_full
2024-04-10T10:57:26.437848+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
[root@mysql8 backup_full]# pwd
/mysql/backup/xtrabackup/full/backup_full
[root@mysql8 backup_full]# cat xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 20991668
last_lsn = 20991678
flushed_lsn = 20991668
redo_memory = 0
redo_frames = 0
- prepare第一次增量
cd /mysql/backup/xtrabackup/inc1
----decompress 需提前安装qpress解压工具
xbstream -x -v --decompress --decompress-threads=2 -C /mysql/backup/xtrabackup/inc1/backup_inc1 < /mysql/backup/xtrabackup/inc1/inc_com_xbs_1.xbstream
xtrabackup --no-server-version-check --prepare --apply-log-only --use-memory=2G --target-dir=/mysql/backup/xtrabackup/full/backup_full --incremental-dir=/mysql/backup/xtrabackup/inc1/backup_inc1
2024-04-10T11:04:18.744005+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
[root@mysql8 backup_full]# cat xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 20992046
last_lsn = 20992046
flushed_lsn = 20992046
redo_memory = 0
redo_frames = 0
- prepare第二次增量
cd /mysql/backup/xtrabackup/inc2
----decompress 需提前安装qpress解压工具
xbstream -x -v --decompress --decompress-threads=2 -C /mysql/backup/xtrabackup/inc2/backup_inc2 < /mysql/backup/xtrabackup/inc2/inc_com_xbs_2.xbstream
xtrabackup --no-server-version-check --prepare --use-memory=2G --target-dir=/mysql/backup/xtrabackup/full/backup_full --incremental-dir=/mysql/backup/xtrabackup/inc2/backup_inc2
2024-04-10T11:10:03.607772+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
[root@mysql8 backup_full]# cat xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 20994404
last_lsn = 20994404
flushed_lsn = 20994404
redo_memory = 0
redo_frames = 0
- 数据恢复
systemctl stop mysql8
不建议在原环境直接恢复,会覆盖掉data内容,建议新环境中恢复,然后在原环境补充数据.
mv /data/mysql/mysql3307/data /data/mysql/mysql3307/data_bak
--拷贝数据文件到从cnf文件中读取datadir路径,也可以指定--datadir -datadir必须为空
--copy-back:拷贝备份文件到datadir
--move-back:移动备份文件到datadir
xtrabackup --defaults-file=/data/mysql/mysql3307/my3307.cnf --copy-back --parallel=10 --target-dir=/mysql/backup/xtrabackup/full/backup_full
2024-04-10T11:12:21.339256+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
chown -R mysql:mysql /data/mysql/mysql3307/data
chmod -R 755 /data/mysql/mysql3307/data
- 验证数据
root@local 11:13:22 [test]> select * from tab2;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
| 8 | h |
| 9 | j |
+------+------+
9 rows in set (0.00 sec)
- 完毕
二、增量备份(流+gzip压缩)
–extra-lsndir:存放本次备份的xtrabackup_checkpoints,后面的增量备份时,–incremental-basedir就指向前一日的extra-lsndir目录便可
yum install gzip
- 全备
mkdir -p /mysql/backup/xtrabackup/full/backup_full
xtrabackup --defaults-file=/data/mysql/mysql3307/my3307.cnf -uroot -pmysql --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/full --extra-lsndir=/mysql/backup/xtrabackup/full --parallel=4 --stream=xbstream 2>/mysql/backup/xtrabackup/full/back_full.log | gzip - > /mysql/backup/xtrabackup/full/full.gz
--2024-04-10T11:23:52.476116+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
- 变更数据
root@local 11:27:16 [test]> delete from tab2 where id = 1;
Query OK, 0 rows affected (0.00 sec)
root@local 11:27:23 [test]> commit;
Query OK, 0 rows affected (0.00 sec)
root@local 11:27:26 [test]> select * from tab2;
+------+------+
| id | name |
+------+------+
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
| 8 | h |
| 9 | j |
+------+------+
8 rows in set (0.00 sec)
- 第一次增量备份
mkdir -p /mysql/backup/xtrabackup/inc1/backup_inc1
xtrabackup --defaults-file=/data/mysql/mysql3307/my3307.cnf --no-server-version-check -uroot -pmysql --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/inc1 --extra-lsndir=/mysql/backup/xtrabackup/inc1 --incremental-basedir=/mysql/backup/xtrabackup/full --parallel=4 --stream=xbstream 2>/mysql/backup/xtrabackup/inc1/back_inc1.log | gzip - > /mysql/backup/xtrabackup/inc1/inc1.gz
--2024-04-10T11:29:13.715394+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
- 变更数据
root@local 11:29:31 [test]> delete from tab2 where id = 2;
^[[AQuery OK, 1 row affected (0.40 sec)
root@local 11:29:34 [test]> delete from tab2 where id = 3;
Query OK, 1 row affected (0.11 sec)
root@local 11:29:37 [test]> commit;
Query OK, 0 rows affected (0.00 sec)
root@local 11:29:40 [test]> select * from tab2;
+------+------+
| id | name |
+------+------+
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
| 8 | h |
| 9 | j |
+------+------+
6 rows in set (0.00 sec)
- 第二次增量备份
mkdir -p /mysql/backup/xtrabackup/inc2/backup_inc2
xtrabackup --defaults-file=/data/mysql/mysql3307/my3307.cnf --no-server-version-check -uroot -pmysql --backup -S /data/mysql/mysql3307/data/mysql3307.sock --target-dir=/mysql/backup/xtrabackup/inc2 --extra-lsndir=/mysql/backup/xtrabackup/inc2 --incremental-basedir=/mysql/backup/xtrabackup/inc1 --parallel=4 --stream=xbstream 2>/mysql/backup/xtrabackup/inc2/back_inc2.log | gzip - > /mysql/backup/xtrabackup/inc2/inc2.gz
--2024-04-10T11:30:26.903226+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
查看日志,确认这一步骤执行成功(最后一行日志显示“completed OK!”):
备份目录结构如下:
.
|-- full
| |-- back_full.log
| |-- backup_full
| |-- full.gz
| |-- xtrabackup_checkpoints
| `-- xtrabackup_info
|-- inc1
| |-- back_inc1.log
| |-- backup_inc1
| |-- inc1.gz
| |-- xtrabackup_checkpoints
| `-- xtrabackup_info
`-- inc2
|-- back_inc2.log
|-- backup_inc2
|-- inc2.gz
|-- xtrabackup_checkpoints
`-- xtrabackup_info
[root@mysql8 xtrabackup]# cat full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 21024814
last_lsn = 21024814
flushed_lsn = 21024814
redo_memory = 0
redo_frames = 0
[root@mysql8 xtrabackup]# cat inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 21024814
to_lsn = 21025372
last_lsn = 21025372
flushed_lsn = 21025372
redo_memory = 0
redo_frames = 0
[root@mysql8 xtrabackup]# cat inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 21025372
to_lsn = 21031272
last_lsn = 21031272
flushed_lsn = 21031272
redo_memory = 0
redo_frames = 0
恢复
- prepare全量 只应用日志,不回滚
cd /mysql/backup/xtrabackup/full
gunzip full.gz
xbstream -x -v --parallel=2 -C /mysql/backup/xtrabackup/full/backup_full < /mysql/backup/xtrabackup/full/full
xtrabackup --no-server-version-check --prepare --apply-log-only --use-memory=2G --target-dir=/mysql/backup/xtrabackup/full/backup_full
--2024-04-10T11:36:14.663804+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
[root@mysql8 backup_full]# cat xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 21024814
last_lsn = 21024814
flushed_lsn = 21024814
redo_memory = 0
redo_frames = 0
- prepare第一次增量
cd /mysql/backup/xtrabackup/inc1
gunzip inc1.gz
xbstream -x -v --parallel=2 -C /mysql/backup/xtrabackup/inc1/backup_inc1 < /mysql/backup/xtrabackup/inc1/inc1
xtrabackup --no-server-version-check --prepare --apply-log-only --use-memory=2G --target-dir=/mysql/backup/xtrabackup/full/backup_full --incremental-dir=/mysql/backup/xtrabackup/inc1/backup_inc1
--2024-04-10T11:39:55.706058+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
[root@mysql8 backup_full]# cat xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 21025372
last_lsn = 21025372
flushed_lsn = 21025372
redo_memory = 0
redo_frames = 0
- prepare第二次增量
cd /mysql/backup/xtrabackup/inc2
gunzip inc2.gz
xbstream -x -v --parallel=2 -C /mysql/backup/xtrabackup/inc2/backup_inc2 < /mysql/backup/xtrabackup/inc2/inc2
xtrabackup --no-server-version-check --prepare --use-memory=2G --target-dir=/mysql/backup/xtrabackup/full/backup_full --incremental-dir=/mysql/backup/xtrabackup/inc2/backup_inc2
--2024-04-10T11:43:44.702384+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
[root@mysql8 backup_full]# cat xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 21031272
last_lsn = 21031272
flushed_lsn = 21031272
redo_memory = 0
redo_frames = 0
- 数据恢复
systemctl stop mysql8
不建议在原环境直接恢复,会覆盖掉data内容,建议新环境中恢复,然后在原环境补充数据.
mv /data/mysql/mysql3307/data /data/mysql/mysql3307/data_bak1
--拷贝数据文件到从cnf文件中读取datadir路径,也可以指定--datadir -datadir必须为空
--copy-back:拷贝备份文件到datadir
--move-back:移动备份文件到datadir
xtrabackup --defaults-file=/data/mysql/mysql3307/my3307.cnf --copy-back --parallel=10 --target-dir=/mysql/backup/xtrabackup/full/backup_full
----2024-04-10T11:44:30.732255+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
chown -R mysql:mysql /data/mysql/mysql3307/data
chmod -R 755 /data/mysql/mysql3307/data
systemctl start mysql8
- 验证数据
root@local 11:45:10 [test]> select * from tab2;
+------+------+
| id | name |
+------+------+
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
| 8 | h |
| 9 | j |
+------+------+
6 rows in set (0.00 sec)
- 完毕