【mysql】mysql之数据误删恢复

  本站以分享各种运维经验和运维所需要的技能为主

《python零基础入门》：python零基础入门学习

《python运维脚本》： python运维脚本实践

《shell》：shell学习

《terraform》持续更新中：terraform_Aws学习零基础入门到最佳实战

《k8》从问题中去学习k8s

《docker学习》暂未更新

《ceph学习》ceph日常问题解决分享

《日志收集》ELK+各种中间件

《运维日常》运维日常

《linux》运维面试100问

《DBA》db的介绍使用（mysql、redis、mongodb...）

数据误删除恢复流程

前期介绍

1.正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。
2.备份策略：每天23：00，计划任务调用mysqldump执行全备脚本 	
3.故障时间点：上午10点开发人员误删除一个核心业务表，如何恢复？

思路

1.停库，避免二次伤害
2.创建新库
3.倒入前一天的全备
4.通过binlog找到前一天23：00到第二天10点之间的数据
5.导入找到的新数据
6.恢复业务（A和B两种方式，按照实际环境选取其一方式即可）
	a.直接使用临时库顶替原生产库，前端应用割接到新库（数据量特别大的时候）
	b.将误删除的表单独导出，然后导入到原生产环境（数据量小的时候）
	

环境模拟

ip	主机名	角色
172.16.1.50	db01	误删除表主机
172.161.51	db02	迁移主机

环境准备

# 1. 查看db01主机数据库信息
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| test               |
+--------------------+

# 2. 查看student表信息
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| student           |
+-------------------+

mysql> select * from student;
+----+-----------+-----+--------+---------------------+
| id | name      | age | gender | cometime            |
+----+-----------+-----+--------+---------------------+
|  1 | 戴佳栋    |  23 | M      | 2020-07-15 06:59:15 |
|  2 | 王张行    |  28 | M      | 2020-07-15 06:59:49 |
|  3 | 王启涵    |  24 | M      | 2020-07-15 07:00:16 |
|  4 | 陈建清    |  21 | M      | 2020-07-15 07:00:38 |
|  5 | 高松平    |  21 | M      | 2020-07-15 07:00:57 |
|  6 | 老掉牙    |  22 | F      | 2020-07-15 07:01:12 |
+----+-----------+-----+--------+---------------------+

# 3. 模拟23点执行全量备份
[root@db01 ~]# mysqldump -A -R --triggers --master-data=2 --single-transaction > /tmp/full.sql

# 4. 模拟23点全量备份后的数据写入（）
mysql> insert into student values(7,'龚晓廖',88,'F','now()');
mysql> insert into student values(8,'好大',44,'F',now());
mysql> insert into student values(9,'邱导',33,'F',now());

# 5. 查看数据
mysql> select * from student;
+----+-----------+-----+--------+---------------------+
| id | name      | age | gender | cometime            |
+----+-----------+-----+--------+---------------------+
|  1 | 戴佳栋    |  23 | M      | 2020-07-15 06:59:15 |
|  2 | 王张行    |  28 | M      | 2020-07-15 06:59:49 |
|  3 | 王启涵    |  24 | M      | 2020-07-15 07:00:16 |
|  4 | 陈建清    |  21 | M      | 2020-07-15 07:00:38 |
|  5 | 高松平    |  21 | M      | 2020-07-15 07:00:57 |
|  6 | 汤采玉    |  22 | F      | 2020-07-15 07:01:12 |
|  7 | 龚晓廖    |  88 | F      | 0000-00-00 00:00:00 |
|  8 | 好大      |  44 | F      | 2020-07-22 15:14:07 |
|  9 | 邱导      |  33 | F      | 2020-07-22 15:14:45 |
+----+-----------+-----+--------+---------------------+

故障重现

# 1.模拟误删除表student
mysql> drop table student;

# 2. 查看student表已经不存在
mysql> show tables;
Empty set (0.00 sec)

故障恢复（A方案）

# 1.因核心表已删除，停止数据库对外服务
[root@db01 tmp]# systemctl stop mysqld

# 2.使用新的临时库db02替代生产库，前段业务割接到新库（将全备数据拷贝到db02）
[root@db01 tmp]# scp full.sql root@172.16.1.52:/tmp

# 3.找到二进制日志中全备23点后到删除数据前的数据，并且导出（查看日志，找到位置点）
[root@db01 data]# mysqlbinlog -d student --start-position=2728 --stop-position=3421 mysql-bin.000001 > /tmp/new.sql

# 4.将二进制导出数据拷贝到db02
[root@db01 tmp]# scp new.sql root@172.16.1.52:/tmp

# 5.在db02中将全备数据导入（在db02数据库启动后操作）
mysql> source /tmp/full.sql

# 6.查看全备导入数据
mysql> select * from student;
+----+-----------+-----+--------+---------------------+
| id | name      | age | gender | cometime            |
+----+-----------+-----+--------+---------------------+
|  1 | 戴佳栋    |  23 | M      | 2020-07-15 06:59:15 |
|  2 | 王张行    |  28 | M      | 2020-07-15 06:59:49 |
|  3 | 王启涵    |  24 | M      | 2020-07-15 07:00:16 |
|  4 | 陈建清    |  21 | M      | 2020-07-15 07:00:38 |
|  5 | 高松平    |  21 | M      | 2020-07-15 07:00:57 |
|  6 | 汤采玉    |  22 | F      | 2020-07-15 07:01:12 |
+----+-----------+-----+--------+---------------------+

# 7. 将二进制产生数据导入
mysql> source /tmp/new.sql

# 8. 查看二进制后的数据（误删除表的数据已经恢复）
mysql> select * from student;
+----+-----------+-----+--------+---------------------+
| id | name      | age | gender | cometime            |
+----+-----------+-----+--------+---------------------+
|  1 | 戴佳栋    |  23 | M      | 2020-07-15 06:59:15 |
|  2 | 王张行    |  28 | M      | 2020-07-15 06:59:49 |
|  3 | 王启涵    |  24 | M      | 2020-07-15 07:00:16 |
|  4 | 陈建清    |  21 | M      | 2020-07-15 07:00:38 |
|  5 | 高松平    |  21 | M      | 2020-07-15 07:00:57 |
|  6 | 汤采玉    |  22 | F      | 2020-07-15 07:01:12 |
|  7 | 龚晓廖    |  88 | F      | 0000-00-00 00:00:00 |
|  8 | 好大      |  44 | F      | 2020-07-22 15:14:07 |
|  9 | 邱导      |  33 | F      | 2020-07-22 15:14:45 |
+----+-----------+-----+--------+---------------------+

# 9. 因将数据库迁移，所以在前端业务中需将数据库地址重新指定

故障恢复（B方案）

# 此操作仅针对数据量级别不大环境，因删除数据只有一个表，所以只需要将全备单独导入到db02，然后将误删除表单独拿出来备份，就实现了十一点前对数据库的恢复，再将二进制数据导出，就就实现了十一点后的数据恢复。

# 1.将全备数据拷贝致db02
[root@db01 tmp]# scp new.sql root@172.16.1.52:/tmp

# 2. 针对删除的student表进行备份
[root@db02 tmp]# mysqldump student student > /tmp/student.sql

# 3. 将数据student表的备份转移到db01
[root@db02 tmp]# scp student.sql root@172.16.1.51:/tmp

# 4. 在二进制数据中找到数据起始点，导出数据
[root@db01 data]# mysqlbinlog -d student --start-position=2728 --stop-position=3421 mysql-bin.000001 > /tmp/new.sql

# 5. 进入数据库student中，导入数据，实现对十一点前数据的还原
mysql> source /tmp/student.sql

# 6. 将二进制数据导入，实现十一点后的数据还原
mysql> source /tmp/new.sql

# 7. 查看数据（已成功恢复）
mysql> select * from student;
+----+-----------+-----+--------+---------------------+
| id | name      | age | gender | cometime            |
+----+-----------+-----+--------+---------------------+
|  1 | 戴佳栋    |  23 | M      | 2020-07-15 06:59:15 |
|  2 | 王张行    |  28 | M      | 2020-07-15 06:59:49 |
|  3 | 王启涵    |  24 | M      | 2020-07-15 07:00:16 |
|  4 | 陈建清    |  21 | M      | 2020-07-15 07:00:38 |
|  5 | 高松平    |  21 | M      | 2020-07-15 07:00:57 |
|  6 | 汤采玉    |  22 | F      | 2020-07-15 07:01:12 |
|  7 | 龚晓廖    |  88 | F      | 0000-00-00 00:00:00 |
|  8 | 好大      |  44 | F      | 2020-07-22 15:14:07 |
|  9 | 邱导      |  33 | F      | 2020-07-22 15:14:45 |
+----+-----------+-----+--------+---------------------+