13.MySQL故障还原（二）

作者: Stone_説 | 来源:发表于2020-07-24 02:28 被阅读0次

13.MySQL故障还原（二）
MySQL备份还原失败成因 - 视图 views
12.MySQL故障还原（一）
linux磁盘阵列技术7【LVM逻辑快照】
spark(四)checkpoint源码阅读
Win10系统怎么利用U盘创建恢复驱动器？使用方法介绍
9. 备份还原
「AppSo」页面流程及原型还原
还原（二）
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.开启用户访问

13.MySQL故障还原（二）
目录：0.故障描述1.全量备份2.数据修改3.故障模拟4.故障后的数据变化5.还原准备6.还原7.恢复配置 0.故...
MySQL备份还原失败成因 - 视图 views
一、故障现象在还原数据库全备文件时，提示失败。如下：二、故障原因根据提示，找到 1062 行，结合上下文，确...
12.MySQL故障还原（一）
目录：0.故障描述1.二进制日志2.全局备份3.删除数据库4.还原 0.故障描述 1.开启二进制日志二进制日志一...
linux磁盘阵列技术7【LVM逻辑快照】
所谓的快照，就是还原功能！在磁盘设置还原的点，当发生故障的时候，能够及时的恢复！ LVM快照有两个特点 1、容量...
spark(四)checkpoint源码阅读
一.checkpoint的基本使用 Checkpoint 可以还原药水。辅助 Spark 应用从故障中恢复。Spa...
Win10系统怎么利用U盘创建恢复驱动器？使用方法介绍
我们都知道Win10系统有系统备份和还原的功能，可以在系统或者应用程序出现小故障的时候进行系统的还原，来达到之前系...
9. 备份还原
二进制日志文件和数据库文件分离存放，可以进行数据库的还原； 1. 备份和恢复 为什么要备份灾难恢复：硬件故障、软...
「AppSo」页面流程及原型还原
一、业务流程还原二、页面流程还原
还原（二）
酒，可以使人敞开心扉，令人更为真实，使人奔放起来，令情感更为热烈，使你得到鼓舞，胆量徒增，适当饮酒于人的身心善莫大...
13.MySQL锁机制
MySQL锁相对于其他的数据库而言，MySQL的锁机制比较简单，最显著的特点就是不同的存储引擎支持不同的锁机制。...