目錄：
0.故障描述
1.全量備份
2.數(shù)據(jù)修改
3.故障模擬
4.故障后的數(shù)據(jù)變化
5.還原準(zhǔn)備
6.還原
7.恢復(fù)配置
0.故障描述
誤刪庫(kù)操作.jpg
故障說明：每天凌晨?jī)牲c(diǎn)做完全備份，現(xiàn)在下午18:00誤刪除表，18:10并進(jìn)行恢復(fù)，將時(shí)間點(diǎn)恢復(fù)到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.數(shù)據(jù)修改
[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.故障后的數(shù)據(jù)變化
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.還原準(zhǔn)備
5.1禁止用戶訪問
加鎖，skip-networking或者防火墻
建議使用防火墻規(guī)則
5.2查看二進(jìn)制日志位置
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導(dǎo)出二進(jìn)制日志，并將誤操作刪除
[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刪除數(shù)據(jù)庫(kù)
[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還原檢測(cè)
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.恢復(fù)配置
1.開啟二進(jìn)制日志
2.開啟用戶訪問
