备份恢复--innobackex

percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：
mysql> CREATE USER ’bkpuser’@’localhost’ IDENTIFIED BY ’s3cret’;
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ’bkpuser’;
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON . TO ’bkpuser’@’localhost’;
mysql> FLUSH PRIVILEGES;

1. 全备
   innobackupex --user=root --password=123 /home/mysqladmin/backup

2、准备(prepare)一个完全备份
innobackupex --apply-log /home/mysqladmin/backup/2018-12-12_10-12-08

3、从一个完全备份中恢复数据

innobackupex --copy-back /home/mysqladmin/backup/2018-12-12_10-12-08

增量备份：

innobackupex --user=root --password=123 /home/mysqladmin/backup

[mysqladmin@hadoop01 2018-12-12_10-34-50]$ cat xtrabackup_binlog_info
mysql-bin.000022 496
[mysqladmin@hadoop01 2018-12-12_10-34-50]$ cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 11250089
last_lsn = 11250089
compact = 0
recover_binlog_info = 0

innobackupex --user=root --password=123 --incremental /home/mysqladmin/backup --incremental-basedir=/home/mysqladmin/backup/2018-12-12_10-34-50

[mysqladmin@hadoop01 2018-12-12_10-37-33]$ cat xtrabackup_binlog_info
mysql-bin.000022 872
[mysqladmin@hadoop01 2018-12-12_10-37-33]$ cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 11250089
to_lsn = 11252053
last_lsn = 11252053
compact = 0
recover_binlog_info = 0

innobackupex --user=root --password=123 --incremental /home/mysqladmin/backup --incremental-basedir=/home/mysqladmin/backup/2018-12-12_10-37-33

backup_type = incremental
from_lsn = 11252053
to_lsn = 11254020
last_lsn = 11254020
compact = 0
recover_binlog_info = 0

准备恢复：

于是，操作就变成了：
innobackupex --user=root --password=123 --apply-log --redo-only /home/mysqladmin/backup/2018-12-12_10-34-50

接着执行：
innobackupex --user=root --password=123 --apply-log --redo-only /home/mysqladmin/backup/2018-12-12_10-34-50 --incremental-dir=/home/mysqladmin/backup/2018-12-12_10-37-33

而后是第二个增量：
innobackupex --user=root --password=123 --apply-log --redo-only /home/mysqladmin/backup/2018-12-12_10-34-50 --incremental-dir=/home/mysqladmin/backup/2018-12-12_10-40-15

mysqlbinlog --start-position=1248 >1.sql

进行恢复：
innobackupex --user=root --password=123 --copy-back /home/mysqladmin/backup/2018-12-12_10-34-50

• cp -r 也可以