备份

创建一个最低权限的数据库用户

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

创建完整备份

mkdir -p /data/backups/
xtrabackup --backup --target-dir=/data/backups/base --user=bkpuser --password=s3cr%T

查看备份

ls -lh /data/backups/

创建第一次增量备份

xtrabackup --backup --target-dir=/data/backups/inc1 \
--incremental-basedir=/data/backups/base \
--user=bkpuser --password=s3cr%T

创建第二次增量备份

xtrabackup --backup --target-dir=/data/backups/inc2 \
--incremental-basedir=/data/backups/inc1 \
--user=bkpuser --password=s3cr%T

恢复

准备完全备份

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base

准备第一次增量备份

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc1

准备第二次增量备份

xtrabackup --prepare --target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc2

注意：合并除最后一个增量备份之外的增量备份时应使用 --apply-log-only 

停止服务，转移原数据目录

systemctl stop mysql
mkdir /tmp/mysqlbak
mv /var/lib/mysql/* /tmp/mysqlbak/

恢复备份

xtrabackup --copy-back --target-dir=/data/backups/base --datadir=/var/lib/mysql

恢复文件权限

chown -R mysql:mysql /var/lib/mysql

启动服务

systemctl start mysql