一、备份:
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的完全恢复已经完成。