记一次数据库删表事件

目前我司正处于一种混乱的开发环境中；
对于数据库字段增修都是开发人员直上服务器数据库修改（我多次强调）；
我未入职前，是直接通过 SFTP 连接服务器，对项目进行开发和修改（话说造成代码覆盖都不知道是谁干的，多次建议开发使用 Git）；
入职后我写一个开发规范文档：软件开发手册（欢迎各位大佬提出意见）
虽然对开发规范有所改变，但还是因为不可抗力因素无法执行，也是造成我最近想跳槽的原因；

原因
某天晚上，开发经理突然打电话：大剩，我不小心删表了，能帮我恢复吗（线上环境）？
我：好的，我看下，我开启了二进制日志，应该恢复数据没有问题；

开始操作
1. 输出文件
将日志文件输出到 mysql_5.log（方便做其他操作）

#此处可能有多个日志文件，查看日志日期，可依个输出到不同文件中;
mysqlbinlog mysql-bin.000005 >> /home/mysql_5.log
2. 查找删除的语句
#查找带有删除语句的行数
grep -n 'DROP' /home/mysql_5.log
#output：878441:DROP TABLE `wtzx`

#扩大一点区间查看删除语句在多少行下文at1970819是删除行语句的行号
sed -n '878430,878500p' /home/mysql_5.log
# output：
# at 1970819
#200629 19:47:28 server id 1 end_log_pos 1970950 CRC32 0xc6b08770 Query thread_id=4276 exec_time=0 error_code=0
# use `htrans_demo`/*!*/;
# SET TIMESTAMP=1593431248/*!*/;
# SET @@session.pseudo_thread_id=4276/*!*/;
# DROP TABLE `wtzx`
3. 进行恢复
mysqlbinlog mysql-bin.000005 --stop-position=1970819 | mysql -u root -p
天真我以为这样就可以恢复；
其实是对 Mysql 二进制日志不了解；
Mysql 二进制日志有点类似于 Redis 的 AOF；
开启二进制日志后记录操作日志，没有记录之前的数据则无法恢复（如创表语句等..）；

结合全量备份进行恢复
辛亏我往服务器上，设置每天凌晨一点的定时备份，对重要的库进行全量备份（数据量少，可以使用全量；数据量多的可以考虑全量 + 增量结合备份）；

1. 将备份脚本恢复
# 恢复备份
cat db_test_db1.sql |mysql -uroot -p
2. 恢复误删数据或者表（结合上面步骤）
# 恢复误删的数据
mysqlbinlog mysql-bin.000005 --stop-position=1970819 | mysql -u root -p
至此也就大功告成～～

开启定时（全量 \ 增量）备份 + Mysql 二进制日志防止数据误操作方法
1. 编写 Shell 脚本
#!/bin/sh

# 查找三十天前的备份的文件进行删除
find /home/mysql-backups -mtime +30 -name "*.*" -exec rm -Rf {} \;

# 数据库配置信息
DB_USER="root"
DB_PASS="123"
DB_HOST="127.0.0.1"

# 需要备份数据库数组
DB_NAME=("test_db1","test_db2")

# 其他配置
BIN_DIR="/usr/bin/" # Mysql bin
BCK_DIR="/home/mysql-backups" #备份文件目录
DATE=`date +%F`

# 创建文件
mkdir -p $BCK_DIR/$DATE

# 执行代码
for var in ${DB_NAME[@]};
do
$BIN_DIR/mysqldump --opt --single-transaction --master-data=2 -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME > $BCK_DIR/$DATE/db_$var.sql
done
# TODO
我目前是做的全量备份，因为项目小。可以考虑全量 + 增量结合备份；
可加上邮件发送备份的文件到自己的邮箱，也可用上传第三方 Oss；

2. 设置定时任务
# crontab -e
# 设置每天凌晨一点备份
0 1 * * * /home/sh/dump.sh
总结
利用定时备份 + 开启 Mysql 二进制日志能更好的保证误删恢复（建议加上发送邮件，或者上传 OSS）。这个是小公司做法，当然还有更多更好的方法；

————————————————
原文作者：陈大剩
转自链接：https://learnku.com/articles/46568
版权声明：著作权归作者所有。商业转载请联系作者获得授权，非商业转载请保留以上作者信息和原文链接。

记一次数据库删表事件

有话要说