$ xtrabackup --user=DVADER --password=14MY0URF4TH3R --backup
–target-dir=/data/bkps/
创建备份用户
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’;
FLUSH PRIVILEGES;
设备配置路径
/etc/my.cnf
[xtrabackup]
target_dir = /data/backups/mysql/
全量备份
xtrabackup --backup --target-dir=/data/backups/
基于全量备份的恢复
xtrabackup --prepare --target-dir=/data/backups/
xtrabackup --copy-back --target-dir=/data/backups/
rsync -avrP /data/backup/ /var/lib/mysql/
恢复后修改目录权限
chown -R mysql:mysql /var/lib/mysql
增量备份
xtrabackup --backup --target-dir=/data/backups/base
xtrabackup --backup --target-dir=/data/backups/inc1
–incremental-basedir=/data/backups/base
xtrabackup --backup --target-dir=/data/backups/inc2
–incremental-basedir=/data/backups/inc1
/data/backups/base
/data/backups/inc1
/data/backups/inc2
基于增量备份的恢复
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
Note: --apply-log-only should be used when merging all incrementals except the last one. That’s why the
previous line doesn’t contain the --apply-log-only option. Even if the --apply-log-only was used on the
last step, backup would still be consistent but in that case server would perform the rollback phase