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 table students;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hello]> select * from studnets;
ERROR 1146 (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 * from students;

select * from teachers;

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

set sql_log_bin=on

 vim /etc/my.cnf

#skip-networking