安装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