xtrabackup 备份mysql

全备命令
innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user backup_user --password 123456 /backup/full_20190527
备份的路径为
/backup/full_20190527

[root@localhost full_20190527.bk]#  ls -al
total 405608
drwxr-x---. 7 root  root      4096 May 26 23:08 .
drwxr-xr-x. 3 mysql root      4096 May 26 23:07 ..
-rw-r-----. 1 root  root       488 May 26 23:08 backup-my.cnf
-rw-r-----. 1 root  root     47047 May 26 23:08 ib_buffer_pool
-rw-r-----. 1 root  root 415236096 May 26 23:07 ibdata1
drwxr-x---. 2 root  root      4096 May 26 23:08 mysql
drwxr-x---. 2 root  root      4096 May 26 23:08 performance_schema
drwxr-x---. 2 root  root      4096 May 26 23:08 sbtest
drwxr-x---. 2 root  root     12288 May 26 23:08 sys
drwxr-x---. 2 root  root      4096 May 26 23:08 test
-rw-r-----. 1 root  root        21 May 26 23:08 xtrabackup_binlog_info
-rw-r-----. 1 root  root       119 May 26 23:08 xtrabackup_checkpoints
-rw-r-----. 1 root  root       535 May 26 23:08 xtrabackup_info
-rw-r-----. 1 root  root      2560 May 26 23:08 xtrabackup_logfile

这个是确认当前备份的binlog位置
[root@localhost full_20190527.bk]# cat xtrabackup_binlog_info 
mysql-bin.000013        640

这个是确认备份的lsn位置
[root@localhost full_20190527.bk]# cat xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 2558549859
last_lsn = 2558549868
compact = 0
recover_binlog_info = 0

将数据文件删除
cd /data/3306/
rm -rf *

恢复数据,注意此时只是恢复数据库，数据文件均还在备份的目录
innobackupex --apply-log /backup/full_20190527
将恢复的数据拷贝回数据库指定的目录
innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /backup/full_20190527

注意，目录权限需要赋予给mysql
[root@localhost 3306]# ls -al
total 516192
drwxr-xr-x. 7 mysql mysql      4096 May 26 23:31 .
drwxr-xr-x. 3 mysql root       4096 Apr 16 23:33 ..
-rw-r-----. 1 root  root      47047 May 26 23:31 ib_buffer_pool
-rw-r-----. 1 root  root  415236096 May 26 23:31 ibdata1
-rw-r-----. 1 root  root   33554432 May 26 23:31 ib_logfile0
-rw-r-----. 1 root  root   33554432 May 26 23:31 ib_logfile1
-rw-r-----. 1 root  root   33554432 May 26 23:31 ib_logfile2
-rw-r-----. 1 root  root   12582912 May 26 23:31 ibtmp1
drwxr-x---. 2 root  root       4096 May 26 23:31 mysql
drwxr-x---. 2 root  root       4096 May 26 23:31 performance_schema
drwxr-x---. 2 root  root       4096 May 26 23:31 sbtest
drwxr-x---. 2 root  root      12288 May 26 23:31 sys
drwxr-x---. 2 root  root       4096 May 26 23:31 test
-rw-r-----. 1 root  root         26 May 26 23:31 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 root  root        539 May 26 23:31 xtrabackup_info
-rw-r-----. 1 root  root          1 May 26 23:31 xtrabackup_master_key_id

[root@localhost 3306]# chown -R mysql.mysql *
[root@localhost 3306]# ls -al
total 516192
drwxr-xr-x. 7 mysql mysql      4096 May 26 23:31 .
drwxr-xr-x. 3 mysql root       4096 Apr 16 23:33 ..
-rw-r-----. 1 mysql mysql     47047 May 26 23:31 ib_buffer_pool
-rw-r-----. 1 mysql mysql 415236096 May 26 23:31 ibdata1
-rw-r-----. 1 mysql mysql  33554432 May 26 23:31 ib_logfile0
-rw-r-----. 1 mysql mysql  33554432 May 26 23:31 ib_logfile1
-rw-r-----. 1 mysql mysql  33554432 May 26 23:31 ib_logfile2
-rw-r-----. 1 mysql mysql  12582912 May 26 23:31 ibtmp1
drwxr-x---. 2 mysql mysql      4096 May 26 23:31 mysql
drwxr-x---. 2 mysql mysql      4096 May 26 23:31 performance_schema
drwxr-x---. 2 mysql mysql      4096 May 26 23:31 sbtest
drwxr-x---. 2 mysql mysql     12288 May 26 23:31 sys
drwxr-x---. 2 mysql mysql      4096 May 26 23:31 test
-rw-r-----. 1 mysql mysql        26 May 26 23:31 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 mysql mysql       539 May 26 23:31 xtrabackup_info
-rw-r-----. 1 mysql mysql         1 May 26 23:31 xtrabackup_master_key_id

增量备份，增量备份必须居于全量备份的基础上，

mysql> select * from test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

做一次全备
innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user backup_user --password 123456 /backup/full_20190527
做一次增量备份，注意incremental-basedir 指向的是全备
mysql> insert into test values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user backup_user --password 123456 --incremental /backup/full_20190527_1 --incremental-basedir=/backup/full_20190527

第二次增量备份，注意第二次和以后的ncremental-basedir 都是指向上一次的增量
mysql> insert into test values(3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user backup_user --password 123456 --incremental /backup/full_20190527_2 --incremental-basedir=/backup/full_20190527_1


[root@localhost full_20190527_2]# cat ../full_20190527/xtrabackup_binlog_info 
mysql-bin.000001        159
[root@localhost full_20190527_2]# cat ../full_20190527/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 2558553705
last_lsn = 2558553716
compact = 0
recover_binlog_info = 0

[root@localhost full_20190527_2]# cat ../full_20190527_1/xtrabackup_binlog_info 
mysql-bin.000001        919
[root@localhost full_20190527_2]# cat ../full_20190527_1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2558553705
to_lsn = 2558554071
last_lsn = 2558554080
compact = 0
recover_binlog_info = 0


[root@localhost full_20190527_2]# cat xtrabackup_binlog_info 
mysql-bin.000001        1174
[root@localhost full_20190527_2]# cat xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2558554071
to_lsn = 2558554453
last_lsn = 2558554462
compact = 0
recover_binlog_info = 0




数据库恢复
增量备份的恢复大体为3个步骤
恢复完全备份
恢复增量备份到完全备份（开始恢复的增量备份要添加–redo-only参数，到最后一次增量备份去掉–redo-only参数）
对整体的完全备份进行恢复，回滚那些未提交的数据

先将全备的数据恢复
innobackupex --apply-log --redo-only /backup/full_20190527
将第一次增量恢复到全备数据
innobackupex --apply-log --redo-only /backup/full_20190527 --incremental-dir=/backup/full_20190527_1

将第二次增量恢复到全备数据（注意恢复最后一个增量备份时需要去掉–redo-only参数，回滚xtrabackup日志中那些还未提交的数据）
innobackupex --apply-log /backup/full_20190527 --incremental-dir=/backup/full_20190527_2

把所有合在一起的完全备份整体进行一次apply操作，回滚未提交的数据：
innobackupex --apply-log /backup/full_20190527

将恢复的数据拷贝回数据库指定的目录
innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /backup/full_20190527

修改恢复文件的权限
chown -R mysql.mysql *

确认数据
mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

恢复完之后，恢复后的数据库会将binlog重置。 

如果是跨库，则可以追加binlog的方式继续恢复

如果是跨库，可以追加binlog，通过binglog追加归档
查看当前恢复binlog的位置

[root@localhost full_20190527_2]# cat xtrabackup_binlog_info 
mysql-bin.000001        1174


mysql> fulsh logs;

将binlog拷贝到目标库

[root@localhost backup]# mysqlbinlog --no-defaults mysql-bin.000001 --start-position=1174|mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    7 |
+------+
6 rows in set (0.00 sec)