MySQL备份删库到恢复

前置操作

数据添加及 binlog 介绍参考下面文章

MySQL 使用 binlog 删库到恢复

备份数据 lanebin

mysqldump -uroot -proot -B -F -R -x --master-data=2 lanebin|gzip >/opt/backup/lanebin_$(date +%F).sql.gz

参数说明：
-B：指定数据库
-F：刷新日志
-R：备份存储过程等
-x：锁表
–master-data：在备份语句里添加 CHANGE MASTER 语句以及 binlog 文件及位置点信息
-----------------
待到数据库备份完成，就不用担心数据丢失了，因为有完全备份数据在！！

由于上面在全备份的时候使用了-F 选项，那么当数据备份操作刚开始的时候系统就会自动刷新 log，这样就会自动产生
一个新的 binlog 日志，这个新的 binlog 日志就会用来记录备份之后的数据库“增删改”操作

定时备份数据

cd /opt/backup
vim backup.sh 
#添加下面的内容
#每个星期日凌晨4:00执行完全备份脚本
/usr/bin/mysqldump -uroot -proot -B -F -R -x --master-data=2 lanebin|gzip >/opt/backup/lanebin_$(date +%F).sql.gz
crontab -e
#添加 
0 4 * * 0 sh /opt/backup/backup.sh
#可变时间必须写在脚本文件中，再定时执行脚本才会生效，否则不生效

先执行一遍

/usr/bin/mysqldump -uroot -p -B -F -R -x --master-data=2 lanebin|gzip >/opt/backup/lanebin_$(date +%F).sql.gz

开始日常操作数据库了

insert into user values(3,'hall');

然后有点上头就执行了下面一句 sql

drop table user;

此时有点慌 😭，索性一不做二不休直接删库，收拾下行李跑路吧！！！

别慌，有备份的

开始恢复全量备份及新增数据

查看 binlog 信息并重新生成一个 binlog

cd /var/lib/mysql/
cp  mysql-bin.000005 /opt/backup/mysql-bin.000005 
cd /opt/backup

#在数据库下执行
flush logs;

可以看到 binlog 多了一个 mysql-bin.000006

开始全量备份的恢复

#跳转位置
cd /opt/backup/
ls
#解压文件
gzip -d lanebin_2021-07-09.sql.gz
#恢复数据
mysql -uroot -p -v < lanebin_2021-07-09.sql

再次查看数据

可以看到虽然数据恢复了，但是最近备份之后的一些数据没有

开始备份之后新增的数据

show binlog events in 'mysql-bin.000005';

选择恢复的位置点

执行 binlog 恢复

#指定数据库执行
mysqlbinlog --no-defaults --start-position=219 --stop-position=513 --database=lanebin  mysql-bin.000005 | mysql -uroot -p -v lanebin
#不指定数据库执行
mysqlbinlog --no-defaults --start-position=219 --stop-position=513  mysql-bin.000005 | mysql -uroot -proot

再次查看下数据是否恢复了

可以看到备份之后的数据已经恢复了

注意

手速一定要够快哦！

刷新 binlog 之后立即恢复全量备份

全量备份恢复之后立即恢复增量备份

然后再看下 bin.000006 是否有需要补充的内容

比如在全量备份 type=0 和增量备份 type=2 之间 修改了 type = 1，应该是type=1实际却是type =2

文章参考

Mysql 数据库之 Binlog 日志使用总结

MySQL 之 binlog 日志说明及利用 binlog 日志恢复数据操作记录

MySQL 备份方案–（利用 mysqldump 以及 binlog 二进制日志）