13.MySQL故障还原（二）
2020-07-24 本文已影响0人
Stone_説
目录：
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.开启用户访问
