Mysql使用binlog日志恢复数据

Mysql的binlog日志,以事件的形式记录了Mysql执行的除去数据查询语句之外所有的DDL和DML语句，可以通过mysqlbinlog工具进行查看。模式特点优点缺点STATEMENT模式(默认)基于SQL语句的复制(statement-based replication, SBR),仅记录每一条会修改数据的sql语句binlog日志相对较小,减少了磁盘IO,提高了性能。

漠效

1668人浏览 · 2020-08-31 19:05:24

漠效 · 2020-08-31 19:05:24 发布

前言

一般的情况下，线上的数据库操作是由开发人员通过上级或其他人员的人工审核后，交由专门的DBA或运维人员进行操作(当然不同公司流程也有差异)。这样既方便了流程的管理，也可尽量避免误上线导致的问题。但缺点是流程复杂，不方便不同部门人员相互沟通和人员管理

随着devops的发展，SQL审核的流程，越来越便捷化和界面化。出现了一系列优秀的Sql审核平台及工具，方便了审计及数据库回滚等操作，也方便了人员间的沟通，逐渐告别了上个线，邮件审批的情况。但是，数据库的误操作始终无法避免，这就是墨菲定律（总会因为各种人或各种原因，在批量操作、结构更新、手动执行等操作中，出现失误）因此出现数据库误操作后的数据恢复尤为重要。
虽然好像有些平台yarning提供了web界面上的回滚操作，传统的操作方法还是了解一下，防止在平台提供的服务异常后，不会紧急手动操作。

下面是几种之前我们关于数据库常用做法：
在每个项目的数据库部署后，提前准备好备份和恢复的脚本，处理异常可以更节省时间；
在上线某个数据库的库或表前，对这一库或表先临时进行备份，有问题再恢复

以前已经介绍了传统的mysql备份和恢复数据的两种操作，下面再介绍两种常用的mysql使用binlog日志恢复数据的操作,优点是可以指定时间和position 事件位置恢复数据，需要注意的是数据库的备份。

by:
以前有想法的时候写了一半的草稿，怕用脚本用的忘了,修改后发布

binlog日志简介

Mysql的binlog日志,以事件的形式记录了Mysql执行的除去数据查询语句之外所有的DDL和DML语句，可以通过mysqlbinlog工具进行查看。

binlog可以设置的格式(binlog_format)如下：

模式	特点	优点	缺点
STATEMENT模式(默认)	基于SQL语句的复制(statement-based replication, SBR),仅记录每一条会修改数据的sql语句	binlog日志相对较小,减少了磁盘IO,提高了性能	在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
ROW模式	基于行的复制(row-based replication, RBR),仅需记录了哪条数据被修改及其修改的结果	不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制	会产生大量的日志,alter table的时候会让日志暴涨 (由于表结构修改,每条记录都发生改变,该表每一条记录都会记录到日志中)
MIXED模式	混合模式复制(mixed-based replication, MBR),以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog	MySQL会根据执行的SQL语句选择日志的保存方式	当遇到表结构变更,会以statement模式来记录;当使用update/delete等修改数据的语句,则会记录所有行的变更;这种模式在级联复制(master A --> slave B --> slave C)的特殊情况下binlog可能会丢失;当数据量较大时,DML语句(update/delete)+limit,可能会导致主从不一致

Mysql默认是使用Statement日志格式，而线上Mysql通常会采用的 row 模式

至于binlog的使用MIXED模式的导致级联复制时出现问题的相关验证，可自行从网上搜索一下

明确数据库管理的规范,对语句进行限制,禁止不规范Sql的执行,可以提高效率,降低风险。

Mysql的binlog常用设置

下面列出的是设置binlog的示例,仅供参考

[mysqld]
#binlog_format = mixed       #设置日志格式

binlog_format = row          #设置日志格式
log-bin = bin.log            #设置binlog日志，也可配置绝对路径(注意目录需要mysql权限)
expire_logs_days = 7         #设置binlog清理时间
max_binlog_size = 1G         #binlog每个日志文件大小
binlog_cache_size = 4M       #binlog可使用的cache内存大小
max_binlog_cache_size = 16M  #binlog可使用的最大cache内存大小（1G?内存足够，可多给点）

#sync_binlog=0               #当事务提交后,不立即将binlog缓存中的记录同步到磁盘,具体何时同步,让文件系统自行决定,或者cache满了之后才同步。性能最好,风险最大,一旦宕机,binlog缓存中所有的记录都会丢失(默认）
#sync_binlog=1               #即使系统宕机,也只丢失一个事务的操作记录,性能最差,事务每提交一次就会将binlog_cache中的记录同步到磁盘。
#sync_binlog=n               #表示事务n次提交后才将binlog cache中的记录同步到磁盘
#binlog_row_image= FULL      #记录所有的行信息（mysql5.6,5.7中默认为FULL，通常配合row使用）

设置binlog日志格式

永久配置

在该数据库对应的配置文件中，根据需求配置上面的binlog参数后，重启mysql

systemctl restart mysql
or
/etc/init.d/mysqld restart

临时配置

会在重启数据库后丢失

<1>查看binlog日志是否开启(ON为开启)

show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)



<2>查看binlog日志格式

show global variables like '%binlog_format%';

+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+


<3>设置binlog日志格式

SET GLOBAL binlog_format = 'ROW';

mysqlbinlog工具

备注：使用mysqlbinlog工具恢复数据,仅仅是相当于开启binlog日志后，将binlog文件中记录的sql再次在数据库中执行，相当于重做。
通常和mysqldump或其他备份配合使用。例如:先将数据利用备份恢复到昨天(最接近的备份时间点)，再使用mysqlbinlog，恢复上次备份到异常操作前的数据，如果数据量大，操作特别费时间，60G最起码半小时（关键在于性能和网络）,建议提前写好回滚脚本

mysqlbinlog常用参数

--version             查看版本号
--no-defaults        （如查看binlog日志时，有类似如下报错  mysqlbinlog: unknown variable 'default-character-set=utf8' ，使用此参数)
--database            指定数据库

--base64-output       输出base64编码的BINLOG语句
		decode-rows   把基于行的事件解码成一个SQL语句
-v（--verbose）        重新构建伪SQL语句的行信息输出，-v会增加列类型的注释信息
(示例： --base64-output=decode-rows -v)


--start-datetime      从二进制日志中读取指定等于时间戳或者晚于本地服务器的时间
--stop-datetime       从二进制日志中读取指定小于时间戳或者等于本地服务器的时间
--start-position      从二进制日志中读取指定position 事件位置作为开始
--stop-position       从二进制日志中读取指定position 事件位置作为事件截至

备注：

–base64-output=decode-rows -v 将row格式的binlog日志中的binlog语句转换为可读的SQL语句,我们可以很直观的分析 binlog 日志。

但是恢复到数据库中的时候，不能使用–base64-output=decode-rows
在这里插入图片描述

--skip-gtids=true 参数，要根据情况来定；
如果我们是要恢复数据到源数据库或者和源数据库有相同 GTID 信息的实例，那么就要使用该参数。如果不带该参数的话，是无法恢复成功的。因为包含的 GTID 已经在源数据库执行过了，根据 GTID 特性，一个 GTID 信息在一个数据库只能执行一次，所以不会恢复成功

如果是恢复到其他实例的数据库并且不包含源实例的 GTID 信息，那么可以不使用该参数，使用或者不使用都可以恢复成功

删表或意外全表更新

恢复方法：

<1>查看删除操作或误操作时是在哪个binglog的哪个位置

查看当前数据库的binlog位置

show master status ;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000056 | 22350755 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

查找误操作的那条在binlog中的行数

mysqlbinlog 具体的binlog |grep -i -n '错误操作的那条命令'

mysqlbinlog mysql-bin.000056 |grep -i -n 'DROP TABLE IF EXISTS `config`'

456865:DROP TABLE IF EXISTS `widget_field` /* generated by server */

<2>寻找合适的时间点或position

从执行异常命令的行数的前几行开始查看日志

mysqlbinlog mysql-bin.000056 |awk 'NR>456800' |more

# at 22339806
#220313 20:02:12 server id 2  end_log_pos 22339833 	Xid = 5823
COMMIT/*!*/;
# at 22339833
#220313 20:02:12 server id 2  end_log_pos 22339945 	Query	thread_id=8	exec_time=0	error_code
=0
SET TIMESTAMP=1678708932/*!*/;
/*!40000 ALTER TABLE `valuemaps` ENABLE KEYS */
/*!*/;
# at 22339945
#220313 20:02:12 server id 2  end_log_pos 22340065 	Query	thread_id=8	exec_time=0	error_code
/widget_field
...跳过
DROP TABLE IF EXISTS `widget_field` /* generated by server */
/*!*/;
# at 22342069
#220313 20:02:12 server id 2  end_log_pos 22343726 	Query	thread_id=8	exec_time=0	error_code
=0

<3>
已知实例错误操作的节点是end_log_pos 22340065 时间是220313 20:02:12

因此要恢复数据，pos或时间点应选择这个之前的一个时间节点
时间:220313 20:02:12 end_log_pos 22339945

<4>使用最近时间的备份进行恢复

如果是mysqldump备份的sql文件，通常比较小，导入数据库即可

如果是xtrabackup的全备，数据通常比较大，恢复的时候，看一下binlog和当前差了多少条

<5>mysqlbinlog重做数据

mysqlbinlog --stop-position=22339945  mysql-bin.000056 |mysql -uroot -p'密码' 
这条没指定--start-position，不指定的表示从头开始数据恢复
如果不指定--stop-position，将会把整个binlog全部恢复

or
mysqlbinlog --stop-position=22339945  mysql-bin.000056 > recovery.sql
mysql -uroot -p'密码' < recovery.sql
可导出sql查看后，再导入

mysqlbinlog --start-position=456 --stop-position=22339945  mysql-bin.000056 |mysql -uroot -p'密码'
mysqlbinlog --start-position=22340080 --stop-position=22340152  mysql-bin.000056 |mysql -uroot -p'密码'
这种2个参数都指定的是恢复某个position点到某个position点的数据

or
mysqlbinlog --start-datetime="2022-03-13 09:26:10" --stop-datetime="2022-03-13 20:03:25" mysql-bin.000056 |mysql -uroot -p'密码' 
这种是知道初始时间到结束时间，使用方式同第一条

补充：

如果最近的备份的binlog和当前数据库的binlog的差距很多，需要将差的那些binlog的数据也按顺序恢复

#!/bin/bash

TIME=`date +%F`

#/backup_3306为备份的目录
#查看备份中的xtrabackup_binlog_pos_innodb文件，记录做备份时候的二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position
binlog_name=`cat /backup_3306/${TIME}/xtrabackup_binlog_pos_innodb |awk '{print $1}' `
binlog_pos=`cat /backup_3306/${TIME}/xtrabackup_binlog_pos_innodb |awk '{print $2}' `

#列出运行的数据库中的binlog文件（备份是昨天，binlog一定少于当前运行的数据库）
"ls /var/lib/mysql/mysql-bin.0*" > /tmp/binlog.txt


#把文件中的备份时的binlog包括之后的binlog,拷贝到备份目录或其他位置
for file in `sed  -n  "/${binlog_name}/,//p" /tmp/binlog.txt`
do
	echo $file
	cp -r ${file} /data/backup_3306/${TIME}/
done

#使用昨天的全备恢复数据
sh recorey_mysql.sh


#把当时备份时的二进制文件binlog，以备份时的pos点为起点，整个binlog导成sql;把之后运行的binlog整体导成sql
for binlog in `sed  -n  "/${binlog_name}/,//p" /tmp/binlog.txt|awk -F"/" '{print $NF}'`
do
  echo $binlog
  if [ "$binlog"  = "${binlog_name}" ]
  then
	  mysqlbinlog --start-position=${binlog_pos} /data/backup_3306/${TIME}/$binlog >  /data/backup_3306/${TIME}/${binlog}.sql
  else
	  echo $binlog
	  mysqlbinlog  /data/backup_3306/${TIME}/$binlog  >  /data/backup_3306/${TIME}/${binlog}.sql

  fi
done

#把导出的sql循环的按顺序依次导入

#取消外键约束
#在导入数据前关闭唯一性校验可以提高导入的效率

for binlog_sql in `ls  /data/backup_3306/${TIME}/mysql-bin.0*.sql`
do
    echo $binlog_sql
    sed -i 's/foreign_key_checks=1/foreign_key_checks=0/g' $binlog_sql 
    sed -i 's/session.unique_checks=1/session.unique_checks=0/g' $binlog_sql 
    mysql -S /tmp/mysql.sock -uroot -p'slfw@$gdsSEx' <  $binlog_sql
   
done