13.MySQL故障还原（二）

目录：
0.故障描述
1.全量备份
2.数据修改
3.故障模拟
4.故障后的数据变化
5.还原准备
6.还原
7.恢复配置

0.故障描述

误删库操作.jpg

故障说明：每天凌晨两点做完全备份，现在下午18:00误删除表，18:10并进行恢复，将时间点恢复到18:10

1.全量备份

[root@node08 ~]#  mysqldump -A --master-data=2 > /data/all_`date +%F`.sql
[root@node08 data]# ll
-rw-r--r-- 1 root  root   521635 Jul 24 01:55 all_2020-07-24.sql
[root@node08 data]# vim all_2020-07-24.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=245;

[root@node08 data]# pwd
/data
[root@node08 data]# ll
-rw-r--r-- 1 root  root   521635 Jul 24 01:55 all_2020-07-24.sql
drwxr-xr-x 2 mysql mysql     173 Jul 24 00:57 logbin

2.数据修改

[root@node08 ~]# mysql
MariaDB [(none)]> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
......
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | a             |  20 | F      |    NULL |      NULL |
|    27 | b             |  30 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)

MariaDB [hellodb]> insert students (name,age)values('c',30);
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> insert students (name,age)values('d',40);
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
......
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | a             |  20 | F      |    NULL |      NULL |
|    27 | b             |  30 | F      |    NULL |      NULL |
|    28 | c             |  30 | F      |    NULL |      NULL |
|    29 | d             |  40 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
29 rows in set (0.00 sec)

3.故障模拟

MariaDB [hellodb]> drop table students;
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> select * from students;
ERROR 1146 (42S02): Table 'hellodb.students' doesn't exist

4.故障后的数据变化

MariaDB [hellodb]> insert teachers (name,age)values('stone',28);
Query OK, 1 row affected (0.01 sec)

MariaDB [hellodb]> insert teachers (name,age)values('sky',19);
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | stone         |  28 | NULL   |
|   6 | sky           |  19 | NULL   |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

5.还原准备

5.1禁止用户访问

加锁，skip-networking或者防火墙
建议使用防火墙规则

5.2查看二进制日志位置

MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     30334 |
| mysql-bin.000002 |   1038814 |
| mysql-bin.000003 |      8138 |
| mysql-bin.000004 |     30334 |
| mysql-bin.000005 |   1038814 |
| mysql-bin.000006 |      1292 |
+------------------+-----------+
6 rows in set (0.00 sec)

MariaDB [hellodb]> flush logs;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     30334 |
| mysql-bin.000002 |   1038814 |
| mysql-bin.000003 |      8138 |
| mysql-bin.000004 |     30334 |
| mysql-bin.000005 |   1038814 |
| mysql-bin.000006 |      1335 |
| mysql-bin.000007 |       245 |
+------------------+-----------+
7 rows in set (0.00 sec)

5.3导出二进制日志，并将误操作删除

[root@node08 logbin]# mysqlbinlog  mysql-bin.000006 > /data/inc.sql
[root@node08 logbin]# vim /data/inc.sql
#DROP TABLE `students` /* generated by server */

6.还原

6.1删除数据库

[root@node08 logbin]# rm -rf /var/lib/mysql/*
[root@node08 logbin]# systemctl restart mariadb

6.2还原

[root@node08 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     30334 |
| mysql-bin.000002 |   1038814 |
| mysql-bin.000003 |      8138 |
| mysql-bin.000004 |     30334 |
| mysql-bin.000005 |   1038814 |
| mysql-bin.000006 |      1335 |
| mysql-bin.000007 |       264 |
| mysql-bin.000008 |     30334 |
| mysql-bin.000009 |   1038814 |
| mysql-bin.000010 |       245 |
+------------------+-----------+
10 rows in set (0.00 sec)

MariaDB [(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> source /data/all_2020-07-24.sql
Query OK, 0 rows affected (0.00 sec)
......
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> source /data/inc.sql
Query OK, 0 rows affected (0.00 sec)
......
Query OK, 0 rows affected (0.00 sec)

6.3还原检测

MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
......
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | a             |  20 | F      |    NULL |      NULL |
|    27 | b             |  30 | F      |    NULL |      NULL |
|    28 | c             |  30 | F      |    NULL |      NULL |
|    29 | d             |  40 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
29 rows in set (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | stone         |  28 | NULL   |
|   6 | sky           |  19 | NULL   |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

7.恢复配置

1.开启二进制日志
2.开启用户访问