利用XtraBackup做全量备份、增量备份、数据目录相同恢复、数据目录不同恢复、单表备份恢复

全量备份：
innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --password=123 /home/backup/

增量备份：（目录为前一次全量备份生成的目录）
innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --password=123 --incremental-basedir=/home/backup/2017-05-10_02-20-19 --incremental /home/backup/

备份恢复：
数据目录相同做恢复：
service mysql stop
rm -rf /data/mysql/data/*
innobackupex --apply-log --redo-only /home/backup/2017-05-10_02-20-19/
innobackupex --apply-log  /home/backup/2017-05-10_02-20-19/ --incremental-dir=/home/backup/2017-05-10_02-47-04/
innobackupex --copy-back /home/backup/2017-05-10_02-20-19/
报错：Error: datadir must be specified.
需要指定datadir：
innobackupex --defaults-file=/usr/local/mysql/my.cnf --copy-back /home/backup/2017-05-10_02-20-19/
chown -R mysql:mysql /data/mysql/data/
service mysql start

数据目录不同做恢复：
service mysql stop
mkdir -p /data/mysql/data_test/
vim /usr/local/mysql/my.cnf：
修改 datadir=/data/mysql/data_test
innobackupex --defaults-file=/usr/local/mysql/my.cnf --copy-back /home/backup/2017-05-10_02-20-19/
chown -R mysql:mysql /data/mysql/data_test/
service mysql start
2017-05-10T08:30:13.938449Z mysqld_safe mysqld from pid file /usr/local/mysql5711/mysqld.pid ended
查找日志，发现日志里面并没有记录有用信息，于是上网查找别人的经验，发现硬盘满了。。
[root@host-192-168-1-21 mysql5711]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
                       18G   17G     0 100% /
tmpfs                 1.9G     0  1.9G   0% /dev/shm
/dev/vda1             477M   25M  427M   6% /boot
将硬盘清理一下，然后再执行一遍。
mysql> show variables like '%datadir%';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /data/mysql/data_test/ |
+---------------+------------------------+
1 row in set (0.00 sec)
为新的数据目录。

单表备份恢复:
mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | tim  |
|    2 | jack |
|    3 | lisa |
+------+------+
3 rows in set (0.00 sec)
mysql> delete from test where id in (2,3);
Query OK, 2 rows affected (0.04 sec)
mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | tim  |
+------+------+
1 row in set (0.00 sec)
mysql> alter table test discard tablespace;#舍弃现在的ibd文件
Query OK, 0 rows affected (0.02 sec)
[root@host-192-168-1-21 sbtest]# ls
db.opt  test.frm  xiyouji.frm  xiyouji.ibd #test.ibd文件没有了
将备份的test.ibd文件拷贝到对应数据目录下：
cp /home/backup/2017-05-10_02-20-19/sbtest/test.ibd /data/mysql/data_test/sbtest/
chown mysql:mysql /data/mysql/data_test/sbtest/test.ibd
导入ibd文件：
mysql> alter table test import tablespace;
Query OK, 0 rows affected, 1 warning (0.15 sec)
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                     |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './sbtest/test.cfg', will attempt to import without schema verification |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | tim  |
|    2 | jack |
|    3 | lisa |
+------+------+
3 rows in set (0.00 sec)
单表备份：
mkdir -p /home/backup/table_only
innobackupex --defaults-file=/usr/local/mysql/my.cnf --include='sbtest.test' --user=root --password=123 /home/backup/table_only/
[root@host-192-168-1-21 2017-05-10_05-13-27]# ls /home/backup/table_only/2017-05-10_05-13-27/  #只备份了sbtest的test表
backup-my.cnf  ib_buffer_pool  IBdata1  IBdata2  sbtest  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile
[root@host-192-168-1-21 2017-05-10_05-13-27]# cat xtrabackup_info 其中有一行 partial = Y 
apply-log之前：[root@host-192-168-1-21 2017-05-10_05-13-27]# cat xtrabackup_checkpoints 第一行为backup_type = full-backuped
apply-log之后变为backup_type = full-prepared
innobackupex --apply-log --export /home/backup/table_only/2017-05-10_05-13-27/
[root@host-192-168-1-21 sbtest]# ls /home/backup/table_only/2017-05-10_05-13-27/sbtest/
test.cfg  test.exp  test.frm  test.ibd
mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | tim  |
|    2 | jack |
|    3 | lisa |
+------+------+
3 rows in set (0.00 sec)
mysql>  delete from test where id in (2,3);
Query OK, 2 rows affected (0.02 sec)
mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | tim  |
+------+------+
1 row in set (0.00 sec)
mysql> alter table test discard tablespace;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from test;
ERROR 1814 (HY000): Tablespace has been discarded for table 'test'
cp /home/backup/table_only/2017-05-10_05-13-27/sbtest/test.{cfg,ibd} /data/mysql/data_test/sbtest/
chown mysql:mysql /data/mysql/data_test/sbtest/test.*
mysql> alter table test import tablespace;
Query OK, 0 rows affected (0.15 sec)
mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | tim  |
|    2 | jack |
|    3 | lisa |
+------+------+
3 rows in set (0.00 sec)

to_lsn、last_lsn的区别？

参考文章：
http://blog.chinaunix.net/xmlrpc.php?r=blog/article&uid=10661836&id=4144677
http://blog.itpub.net/23249684/viewspace-1386946/
https://my.oschina.net/geekwolf/blog/313654
http://www.cnblogs.com/wangxin37/p/6398764.html
https://segmentfault.com/a/1190000005668516