一、备份：
1、数据备份
1）做一次全备：
innobackupex --defaults-file=/mysql/mysql3307/etc/my.cnf --user=backup --password=mysql --no-timestamp --no-lock --parallel=4 /mysql/backup/xtrabackup/fullbackup
2）插入数据：
mysql> select * from t1; +------+ | id | +------+ | 1 | | 1 | | 2 | | 2 | +------+ 4 rows in set (0.00 sec) mysql> insert into t1 values(3); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values(3); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------+ | id | +------+ | 1 | | 1 | | 2 | | 2 | | 3 | | 3 | +------+ 6 rows in set (0.00 sec)
3）第一次增量备份
innobackupex --defaults-file=/mysql/mysql3307/etc/my.cnf --user=backup --password=mysql --no-lock --parallel=4 --incremental-basedir=/mysql/backup/xtrabackup/fullbackup --incremental /mysql/backup/xtrabackup/increbackup
4）插入数据
mysql> select * from t1; +------+ | id | +------+ | 1 | | 1 | | 2 | | 2 | | 3 | | 3 | +------+ 6 rows in set (0.00 sec) mysql> insert into t1 values(4); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values(4); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------+ | id | +------+ | 1 | | 1 | | 2 | | 2 | | 3 | | 3 | | 4 | | 4 | +------+ 8 rows in set (0.00 sec)
5）第二次增量备份（差异备份）
innobackupex --defaults-file=/mysql/mysql3307/etc/my.cnf --user=backup --password=mysql --no-lock --parallel=4 --incremental-basedir=/mysql/backup/xtrabackup/increbackup/2020-08-18_18-09-40 --incremental /mysql/backup/xtrabackup/increbackup
6）插入数据
mysql> select * from t1; +------+ | id | +------+ | 1 | | 1 | | 2 | | 2 | | 3 | | 3 | | 4 | | 4 | +------+ 8 rows in set (0.00 sec) mysql> insert into t1 values(6); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values(6); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------+ | id | +------+ | 1 | | 1 | | 2 | | 2 | | 3 | | 3 | | 4 | | 4 | | 6 | | 6 | +------+ 10 rows in set (0.00 sec)
2、日志备份
binlog实时进行远程备份：
mysqlbinlog --read-from-remote-server --raw --host=172.16.254.163 --socket=/mysql/mysql3307/mysqld.sock --port=3307 --user=backup --password=x xxx --stop-never --result-file=/mysql/backup/binlog/ mysql-bin.000001
二、模拟故障：
模拟故障：删除数据目录下所有文件。
cd /mysql/mysql3307/data/ [mysql@server-254-163 data]$ ll total 1573844 -rw-r-----. 1 mysql mysql 56 Aug 18 16:34 auto.cnf drwxr-x---. 2 mysql mysql 120 Aug 18 16:33 handong drwxr-x---. 2 mysql mysql 48 Aug 18 16:33 hlj drwxr-x---. 2 mysql mysql 12288 Aug 18 16:33 hlj_product -rw-r-----. 1 mysql mysql 937844 Aug 18 18:26 ib_buffer_pool -rw-r-----. 1 mysql mysql 536870912 Aug 18 18:26 ibdata1 -rw-r-----. 1 mysql mysql 536870912 Aug 18 18:26 ib_logfile0 -rw-r-----. 1 mysql mysql 536870912 Aug 18 17:30 ib_logfile1 drwxr-x---. 2 mysql mysql 4096 Aug 18 16:33 mysql drwxr-x---. 2 mysql mysql 8192 Aug 18 16:33 performance_schema drwxr-x---. 2 mysql mysql 50 Aug 18 16:33 sms drwxr-x---. 2 mysql mysql 8192 Aug 18 16:33 sys drwxr-x---. 2 mysql mysql 4096 Aug 18 16:33 sysbenchtest drwxr-x---. 2 mysql mysql 140 Aug 18 16:33 test -rw-r-----. 1 mysql mysql 21 Aug 18 16:33 xtrabackup_binlog_pos_innodb -rw-r-----. 1 mysql mysql 644 Aug 18 16:33 xtrabackup_info -rw-r-----. 1 mysql mysql 1 Aug 18 16:33 xtrabackup_master_key_id [mysql@server-254-163 data]$ rm -rf * [mysql@server-254-163 data]$ ll total 0
三、恢复：
1、数据恢复：
首先进入全备和增备目录查看备份lsn信息
innodb_from_lsn innodb_to_lsn
全备 0 373952419748
增备一 373952419748 373952422065
增备二 373952422065 373952425060
从lsn顺序上看，没有问题，下边开始恢复。
1）prepare阶段（全备）
innobackupex --defaults-file=/mysql/mysql3307/etc/my.cnf --user=backup --password=mysql --apply-log --redo-only /mysql/backup/xtrabackup/fullbackup
2）prepare阶段（增备一）
innobackupex --defaults-file=/mysql/mysql3307/etc/my.cnf --user=backup --password=mysql --apply-log --redo-only --incremental-dir=/mysql/backup/xtrabackup/increbackup/2020-08-18_18-09-40 /mysql/backup/xtrabackup/fullbackup
3）prepare阶段（增备二）
innobackupex --defaults-file=/mysql/mysql3307/etc/my.cnf --user=backup --password=mysql --apply-log --redo-only --incremental-dir=/mysql/backup/xtrabackup/increbackup/2020-08-18_18-19-43 /mysql/backup/xtrabackup/fullbackup
4）restore阶段
innobackupex --defaults-file=/mysql/mysql3307/etc/my.cnf --user=backup --password=mysql --copy-back /mysql/backup/xtrabackup/fullbackup
5） 启动mysql服务
mysqld_safe --defaults-file=/mysql/mysql3307/etc/my.cnf &
6）
验证数据
mysql> select * from t1; +------+ | id | +------+ | 1 | | 1 | | 2 | | 2 | | 3 | | 3 | | 4 | | 4 | +------+ 8 rows in set (0.00 sec)
可以看到：截至第二次增备的数据都已经恢复，但是第二次增备后插入的两条id为8的记录没有恢复回来。
2、根据日志备份做完全恢复
1）查看第二次增备的相关信息
[root@server-254-163 increbackup]# cd 2020-08-18_18-19-43 [root@server-254-163 2020-08-18_18-19-43]# ll total 8592 -rw-r-----. 1 mysql mysql 494 Aug 18 18:20 backup-my.cnf drwxr-x---. 2 mysql mysql 192 Aug 18 18:20 handong drwxr-x---. 2 mysql mysql 73 Aug 18 18:20 hlj drwxr-x---. 2 mysql mysql 20480 Aug 18 18:20 hlj_product -rw-r-----. 1 mysql mysql 551 Aug 18 18:20 ib_buffer_pool -rw-r-----. 1 mysql mysql 327680 Aug 18 18:19 ibdata1.delta -rw-r-----. 1 mysql mysql 60 Aug 18 18:19 ibdata1.meta drwxr-x---. 2 mysql mysql 4096 Aug 18 18:20 mysql drwxr-x---. 2 mysql mysql 8192 Aug 18 18:20 performance_schema drwxr-x---. 2 mysql mysql 76 Aug 18 18:20 sms drwxr-x---. 2 mysql mysql 8192 Aug 18 18:20 sys drwxr-x---. 2 mysql mysql 4096 Aug 18 18:20 sysbenchtest drwxr-x---. 2 mysql mysql 233 Aug 18 18:20 test -rw-r-----. 1 mysql mysql 27 Aug 18 18:20 xtrabackup_binlog_info -rw-r-----. 1 mysql mysql 159 Aug 18 18:20 xtrabackup_checkpoints -rw-r-----. 1 mysql mysql 678 Aug 18 18:20 xtrabackup_info -rw-r-----. 1 mysql mysql 8388608 Aug 18 18:39 xtrabackup_logfile [root@server-254-163 2020-08-18_18-19-43]# cat xtrabackup_binlog_info mysql-bin.000006 327586954
2）应用Binlog
[root@server-254-163 2020-08-18_18-19-43]# cd /mysql/backup/binlog/ [root@server-254-163 binlog]# ll total 974520 -rw-r-----. 1 mysql mysql 1844 Aug 18 17:53 mysql-bin.000001 -rw-r-----. 1 mysql mysql 1181 Aug 18 17:53 mysql-bin.000002 -rw-r-----. 1 mysql mysql 177 Aug 18 17:53 mysql-bin.000003 -rw-r-----. 1 mysql mysql 336533397 Aug 18 17:53 mysql-bin.000004 -rw-r-----. 1 mysql mysql 333768638 Aug 18 17:53 mysql-bin.000005 -rw-r-----. 1 mysql mysql 327587456 Aug 18 18:20 mysql-bin.000006 [root@server-254-163 binlog]# mysqlbinlog mysql-bin.000006 --start-position=327586954 | mysql -u backup -pmysql --socket=/mysql/mysql3307/mysqld.sock mysql: [Warning] Using a password on the command line interface can be insecure.
3
）验证数据
mysql> select * from t1; +------+ | id | +------+ | 1 | | 1 | | 2 | | 2 | | 3 | | 3 | | 4 | | 4 | | 6 | | 6 | +------+ 10 rows in set (0.00 sec)
两条id为6的数据已经恢复。到此利用
xtrabackup+binlog的完全恢复已经完成。