基于Mysql binlog恢复线上数据

Lokie.Wang
2020-06-20 22:29:09
DevOps
mysql

周五晚上下班前正在赶着写一些重要的代码，突然两个同事跑过来和我说线上的程序炸了，因为其中一个同事自己导入数据到了线上生产环境的时候做了误操作导致重要数据库中一个非常重要的表数据丢失，并且数据结构损坏，当场炸了。其中一个同事尝试用了一个比较旧的数据库表数据暂时恢复了程序的可用性，但是将近一个月的数据丢失。由于之前的备份机制，仅仅每周做一次全量备份，所以最新的全量备份也在上周，如果贸然用全量数据恢复数据这个表会造成巨大问题，先不说业务影响有多大，但ElasticSearch索引需要清洗、用户数据的清洗都是一个巨大无比的工作，没办法只有恢复数据到之前才是正途。

Mysql恢复数据库的方式是通过binlog来恢复这是在binlog开启的情况下，如果线上服务器没有开启过binlog也没有近期备份，那基本就是等死了，因为基于innodb的事务日志来恢复的难度非常非常高几乎是不可能的。

那首先什么是binlog，binlog是mysql 在 server层中提供的一种日志技术，主要记录DML、DDL语句对数据的变更操作，binlog是mysql实现复制的基础也是在灾难恢复中最容易使用的东西。由于是在Server层而不是引擎层实现，所以和具体的存储引擎无关。

先来看下和binlog有关的重要参数和怎么样开启binlog

log_bin  # 开启binlog功能
server_id = 2  # Server唯一ID，用于主从复制

max_binlog_size = 100M # 单个binlog文件的最大大小

binlog_format = ROW  # binlog的格式

expire_logs_days=30  # binlog文件超时天数

其中binlog_format是非常重要的参数，他有三种可选值

此时mysql会采用ROW方式保存数据追踪数据的每行变化，这种方式下binlog文件会比较大，而且很快就会切分，但是好处也十分明显就是闪回的实现，可以通过一些工具方便恢复数据行到出错时间点上。

Statement

记录执行过的DDL语句和DML语句，这种方式下存储量相对小，但是无法闪回数据。所有数据的恢复需要从一个特定的全量备份点开始，如果有GTIDs的话可以从某个GTIDs点开始

Mixed

让mysql自己决定该如何存储。同样这种方式不能实现数据的闪回操作。但是好处是数据相对较少。

对于这三种模式的选择很多团队会建议使用ROW格式因为闪回。通过美团的MyFlash和mysqlbinlog都可以实现数据的闪回，但是也有专业DBA建议使用Mixed，因为Sql语句的存在也方便了另一种场景通过SQL语句同步数据的可能，Maxwell之前我介绍过的工具就不能解析ROW格式，所以请按照场景决定。

言归正传就是恢复，由于采用了MIX格式，所以只有从备份恢复数据一个出路了。步骤如下：

另外找一个新的Mysql服务器，这样做的好处就是可以不影响线上数据。
完全还原上一次全量备份的数据到新服务器中。
执行mysqlbinlog mysql-binlog.** --stop-date-time="事故发生前最后系统正常的时间" --skip-gtids=true -d datebase 名字 > newsql.sql 由于开启了gtid方式所以跳过导出所有的gtid
导入数据到新服务器中，这样就有了事故发生前所有的数据。
将数据导入生产环境，根据业务逻辑合数据或者直接使用新数据，看业务而定。

对于binlog和mysql备份机制，这里给出一个结合全量备份和每日增量日志备份的脚本方案：


#! /bin/sh

echo 'begin to backup db'

yesterday=$(date "+%Y-%m-%d" -d "yesterday")

starttime="$yesterday 00:00:00"
stoptime="$yesterday 23:59:59"

backuphome='/external-data/backup/db/'

binlogfile="$backuphome$yesterday-binlog.sql.gz"


/usr/local/mysql/bin/mysqlbinlog /database/macco-mysql-bin.* --start-datetime="$starttime" --stop-datetime="$stoptime" | /bin/gzip -c | cat > $binlogfile


time=$(date "+%Y%m%d")


dbs=( infodb appdb filedb uploaddb productdb makeupeffectdb makeupmirror scheduledb userdb contentdb)

# bump all database 
for(( i=0;i<${#dbs[@]};i++)) do
  dbname="${dbs[i]}"
  backuppath="$backuphome${dbs[i]}_$time.sql.gz"
  /usr/local/mysql/bin/mysqldump -R $dbname | /bin/gzip -c | cat > $backuppath
done;

可以通过加入contab每天凌晨进行备份

警示语：应该从制度上严格规范数据操作，先备份并且所有操作应该在线下和预生产演练后进行，特别对于没有经验的同事尤其重要。
另外数据库权限的严格管理也非常重要。

本文为Lokie.Wang原创文章,转载无需和我联系,但请注明来自lokie博客http://lokie.wang