数据库学习案例20240311 -mysql xtrabackup 备份与恢复

下载使用

Software Downloads - Percona

下载需要的依赖包

yum install perl-DBI -y
yum install perl-DBD-MySQL -y
yum install perl-Time-HiRes -y
yum install perl-IO-Socket-SSL -y

yum install perl-TermReadKey.x86_64 -y

下载安装配置完环境变量，然后做一个简单测试既可。

1 mysql数据库备份

以下为测试mysql数据库备份脚本，包括增量备份

xtrabackup --backup -uroot -p123 --target-dir=/backup/full
xtrabackup --backup -uroot -p123 --target-dir=/backup/incre1 --incremental-basedir=/backup/full
xtrabackup --backup -uroot -p123 --target-dir=/backup/incre2 --incremental-basedir=/backup/incre1
xtrabackup --backup -uroot -p123 --target-dir=/backup/incre3 --incremental-basedir=/backup/incre2
xtrabackup --backup -uroot -p123 --target-dir=/backup/incre4 --incremental-basedir=/backup/incre3

2 恢复数据库

xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --apply-log-only --target-dir=/backup/full  --incremental-dir=/backup/incre1
xtrabackup --prepare --apply-log-only --target-dir=/backup/full  --incremental-dir=/backup/incre2
xtrabackup --prepare --apply-log-only --target-dir=/backup/full  --incremental-dir=/backup/incre3
xtrabackup --prepare --target-dir=/backup/full  --incremental-dir=/backup/incre4

3 拷贝回数据目录

xtrabackup --copy-back --target-dir=/backup/full



[root@router data]# ls -lrt
total 84000
-rw-r-----. 1 root root 16777216 Mar 11 05:02 undo_001
-rw-r-----. 1 root root 16777216 Mar 11 05:02 undo_002
drwxr-x---. 2 root root        6 Mar 11 05:02 #innodb_redo
-rw-r-----. 1 root root 12582912 Mar 11 05:02 ibdata1
-rw-r-----. 1 root root      353 Mar 11 05:02 router-bin.000068
-rw-r-----. 1 root root       18 Mar 11 05:02 router-bin.index
drwxr-x---. 2 root root       28 Mar 11 05:02 sys
drwxr-x---. 2 root root      243 Mar 11 05:02 mysql_innodb_cluster_metadata
drwxr-x---. 2 root root       80 Mar 11 05:02 mgr
-rw-r-----. 1 root root 27262976 Mar 11 05:02 mysql.ibd
drwxr-x---. 2 root root      143 Mar 11 05:02 mysql
drwxr-x---. 2 root root     8192 Mar 11 05:02 performance_schema
-rw-r-----. 1 root root     4362 Mar 11 05:02 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Mar 11 05:02 ibtmp1
-rw-r-----. 1 root root      849 Mar 11 05:02 xtrabackup_info
[root@router data]# 

4 授权重启mysql

chown -R mysql:mysql /data/*

systemctl restart mysql.service

5  核心要点

1 需要有一个基础全备份，

[root@router full]# more xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 25942086
last_lsn = 25942086
flushed_lsn = 25942086
redo_memory = 0
redo_frames = 0


[root@router incre1]# more xtrabackup_checkpoints
backup_type = incremental
from_lsn = 25942086
to_lsn = 25942096
last_lsn = 25942096
flushed_lsn = 25942096
redo_memory = 0
redo_frames = 0

[root@router incre2]# more xtrabackup_checkpoints
backup_type = incremental
from_lsn = 25942096
to_lsn = 25942612
last_lsn = 25942612
flushed_lsn = 25942612
redo_memory = 0
redo_frames = 0

2 使用incre恢复阶段会不断提高xtrabackup_checkpoints中的lsn值。

3 备份期间会给全库触发一个轻量级backup锁

mysql 8中新增了一个轻量级的备份锁（https://dev.mysql.com/doc/
refman/8.0/en/lock-instance-for-backup.html），它允许在online备份的时候进行DML操作，同时可防止快照不一致。这个锁禁止的操作很少，它禁止的操作包括：文件的创建、删除、改名，账户的管理和 REPAIR TABLE 、TRUNCATE TABLE、 OPTIMIZE TABLE。备份锁由lock instance for backup和unlock instance语法组成。使用这些语句需要BACKUP_ADMIN权限。