mysql第三方工具Percona XtraBackup

安装xtrabackup

yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
 
 
yum -y install percona-xtrabackup-80

创建备份用户并设置权限

CREATE USER 'mysqlbackup'@'localhost' IDENTIFIED BY 's3cr%T';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mysqlbackup'@'localhost';
GRANT SELECT ON performance_schema.log_status TO 'mysqlbackup'@'localhost';
GRANT SELECT ON performance_schema.keyring_component_status TO mysqlbackup@'localhost'; 
FLUSH PRIVILEGES;

完全备份

mkdir -p /data/backup/wanquan 
 
xtrabackup --user=mysqlbackup --password=s3cr%T --backup --target-dir=/data/backup/wanquan --datadir=/usr/local/mysql/data/
 
参数说明：
--user: 指定备份用户
--password：指定备份用户密码
--backup：表示执行备份操作
--target-dir：指定备份数据的存储目录
--datadir：指定MySQL的数据目录

增量备份

查看完全备份lsn
[root@localhost ~]# cat /data/backup/wanquan/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 19643963
last_lsn = 19643963
flushed_lsn = 19643963
redo_memory = 0
redo_frames = 0

基于全量备份的增量备份

 xtrabackup --user=mysqlbackup --password=s3cr%T --backup --target-dir=/data/backup/zengliang --incremental-basedir=/data/backup/wanquan --datadir=/usr/local/mysql/data/
 
--incremental-basedir：指定增量备份的基准目录

查看增量备份lsn

[root@localhost ~]# cat /data/backup/zengliang/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 19643963
to_lsn = 19643973
last_lsn = 19643973
flushed_lsn = 19643973
redo_memory = 0
redo_frames = 0

将增量备份应用于完整备份

xtrabackup --prepare --apply-log-only --target-dir=/data/backup/wanquan
 
xtrabackup --prepare --target-dir=/data/backup/wanquan --incremental-dir=/data/backup/zengliang

恢复

xtrabackup --user=mysqlbackup --password=s3cr%T --copy-back --target-dir=/data/backup/wanquan