mysql清空表怎么找回数据库_MySQL中备份数据还原

假设不是系统损坏，而是人为损坏，如手误删除表

前提条件：

假设备份的条件是每天凌晨2点开始备份，周一备份正常，现在是周一下午18点，此时你的同事删除了hellodb数据库的中students表，而其他表没有损坏，可以正常访问，此时时间18点20分，用户表示不能访问了，

一、现在开始备份

mysqldump -A --master-data=2 > /data/backup_`date +%F_%T`.sql

ll /data/backup_2021-01-07_22\:02\:56.sql

-rw-r--r-- 1 root root 8445056 Jan 7 22:02 /data/backup_2021-01-07_22:02:56.sql

注释：-A 全局备份  --master-data=2  备份的时候多加一个注释，注释内容为当前的二进制日志文件的位置

二、模拟数据库使用中，被删除students表

MariaDB [hello]> drop tablestudents;

Query OK,0 rows affected (0.00sec)

MariaDB[hello]> select * fromstudnets;

ERROR1146 (42S02): Table 'hello.studnets' doesn't exist

三、其他表正常使用，模拟teachers表正在新增数据

MariaDB [hello]> insert teachers (name,age)values('WoShiCeSi',100);

注释：此处可以看到students表已经找不到了，而teachers表汇总被新增了一条数据

四、开始恢复数据

1、第一步：通过防火墙或者数据库配置做限制用户访问(此处数据库配置文件中修改为只能使用本地套接字连接)

vim /etc/my.cnf

skip-networking

2、第二步：查看之前二进制日志文件的位置在哪

vim /data/backup_2021-01-07_22\:02\:56.sql

--CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=245;

注释：此处用vim不用cat 是因为cat直接显示的是最后一页，但我们需要的信息在第一页，可以直接查看到需要的信息

3、第三步：生成一个新的日志文件

flush logs;

show master logs;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 1175 |

| mysql-bin.000002 | 30379 |

| mysql-bin.000003 | 1038814 |

| mysql-bin.000004 | 1565 |

| mysql-bin.000005 | 264 |

| mysql-bin.000006 | 245 |

| mysql-bin.000007 | 30379 |

| mysql-bin.000008 | 1038814 |

| mysql-bin.000009 | 833 |

| mysql-bin.000010 | 245 |

+------------------+-----------+

注释：刚刚记录的日志位置是在mysql-bin.000009的245位置，而mysql-bin.000010是刚刚新生成的，只需要恢复这中间的信息就行。

4、第四步：把刚刚的定位的二进制日志文件从245开始给他导出来

mysqlbinlog --start-position=245 /data/logbin/mysql-bin.000009 > /data/inc.sql

5、第五步：以防万一的话可以备份一下这个导出的二进制日志文件(也可以省略这一步)

cp /data/inc.sql /data/inc_backup.sql

6、第六步：修改之前导出的二进制文件，找到中间删除表的那行

vim /data/inc.sql

通过vim中带的命令来查看出/DROP 开头的命令(此处不确定是用大写还是小写删除的，可以两个都试一下)

/DROP或者/drop

7、第七步：找到了删除表的这行命令，加#号注销掉或者删除这行命令

8、第八步：下面开始还原：

比较简单的方式是直接把整个库删干净在还原，

直接还原，是覆盖库，如果里面有表的话会删除掉现存的表，然后在恢复过去

rm -rf /var/lib/mysql/*

注释：此处直接删除掉了数据库里的数据文件

9、第九步：重新启动服务

systemctl restart mariadb

10、第十步：进入数据库中先临时关闭继续生成新的二进制日志文件

MariaDB [(none)]> set sql_log_bin=0;

11、第十一步：开始还原数据

MariaDB [test]> source /data/backup_2021-01-07_22:02:56.sql

MariaDB[hello]> source /data/inc.sql

注释：先恢复数据库文件，在恢复二进制日志里的文件，此处在哪个库中并没有关系

五、查看数据是否恢复(包括students表被删除后，这段空白时间的数据)

select * fromstudents;select * from teachers;

六、恢复完成，先让二进制日志文件继续记录，然后关掉之前的用户访问限制

set sql_log_bin=on

vim /etc/my.cnf

#skip-networking