xtrabackup全库备份和binlog增量备份的配合

[root@haha ~]# mkdir -p /opt/mysqlbackup/{full,inc}
##full全备存放的目录
##inc增量备份存放的目录

[root@haha ~]# innobackupex --defaults-file=/etc/my.cnf --socket=/usr/local/mysql/mysql.sock --user=root --password=123456 /opt/mysqlbackup/full

[root@haha ~]# cat /opt/mysqlbackup/full/2021-03-11_18-01-36/xtrabackup_binlog_info mysql_bin.000004	774
##查看完全备份时binlog日志位置（position）

mysql> insert into test values(111,"mm");
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(112,"xx");
Query OK, 1 row affected (0.00 sec)

[root@haha ~]# mysqlbinlog -v --start-position=774 /data/mysql/log/mysql_bin.000004 > /opt/mysqlbackup/inc/$(date +%Y-%m-%d_%H:%M:%S).sql

[root@haha ~]# /etc/inti.d/mysqld stop
##停止数据库

[root@haha ~]# mv /data/mysql/data/ /tmp/ 
##移动数据库，模拟数据库损坏，data目录下非空无法恢复

[root@haha ~]# innobackupex --apply-log /opt/mysqlbackup/full/2021-03-11_18-01-36/

[root@haha ~]# innobackupex --copy-back /opt/mysqlbackup/full/2021-03-11_18-01-36/
##注：datadir必须是为空的，innobackupex  --copy-back不会覆盖已存在的文件，还要注意，还原时需要先关闭服务，如果服务是启动的，那么就不能还原到datadir

[root@haha ~]# chown -R mysql:mysql /data/mysql/data/

[root@haha ~]# /etc/init.d/mysqld start

mysql> source /opt/mysqlbackup/inc/2021-03-11_18:06:01.sql

mysql> select * from haha.test;
+------+------+
| id   | name |
+------+------+
|    1 | zz   |
|  111 | mm   |
|  112 | xx   |
+------+------+
3 rows in set (0.00 sec)
##完全备份id=1，和增量备份id=111，id=112全部恢复