备份

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

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/ --user=bkpuser --password=s3cr%T

查看备份

ls -lh /data/backups/

恢复

准备恢复备份

xtrabackup --prepare --target-dir=/data/backups/

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

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

恢复备份

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

恢复文件权限

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

启动服务

systemctl start mysql