mysql如何利用binlog进行数据恢复详解

前言：

MySQL Binary Log也就是常说的bin-log, ,是mysql执行改动产生的二进制日志文件,其主要作用有两个:

* 数据回复

* 主从数据库。用于slave端执行增删改，保持与master同步

如果线上误操作了一个数据或者被恶意攻击了，由于是直接修改的数据库，所有唯一的恢复方式就在mysql的binlog。binlog使用的是ROW模式，即受影响的每条记录都会生成一个sql。同时利用了binlog2sql项目。

mysqlbinlog 官方操作文档：https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html

注意：

数据库最好要作定时快照备份,避免需要增量更新的数量过多

binlog基本配置和格式

binlog基本配置

binlog需要在mysql的配置文件的mysqld节点中进行配置：

# 日志中的Serverid
server-id = 1
# 日志路径
log_bin  = /var/log/mysql/mysql-bin.log
# 保存几天的日志
expire_logs_days = 10
# 每个binlog的大小
max_binlog_size = 1000M
#binlgo模式
binlog_format=ROW
# 默认是所有记录，可以配置哪些需要记录，哪些不记录
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name

查看binlog状态

• SHOW BINARY LOGS; 查看binlog文件
• SHOW VARIABLES LIKE '%log_bin%' 查看日志状态
• SHOW MASTER STATUS 查看日志文件位置

binlog的三种格式

1.ROW

针对行记录日志，每行修改产生一条记录(即每一行数据操作都会产生独立的sql记录)。

优点：上下文信息比较全，恢复某条误操作时可以直接在日志中查找到原文信息，对于主从复制支持好。

缺点：输出非常大，如果是Alter语句将产生大量的记录

2.STATEMENT

针对sql语句的，每条语句产生一条记录

优点：产生的日志量比较小，主从版本可以不一致

缺点：主从有些语句不能支持，像自增主键和UUID这种类型的

3.MIX

结合了两种的优点，一般情况下都采用STATEMENT模式，对于不支持的语句采用ROW模式

常用操作语法：（要找到能允许mysqlbinlog 执行的位置运行）

数据操作的查看与恢复:

mysql自带的mysqlbinlog

由于binlog是二进制的，所以需要先转换成文本文件，一般可以采用Mysql自带的mysqlbinlog转换成文本。

mysqlbinlog --no-defaults --base64-output='decode-rows' -d room -v mysql-bin.011012 > /root/binlog_2020-04-29

参数说明

• --no-defaults 为了防止报错：mysqlbinlog: unknown variable 'default_character_set=utf8mb4'
• --base64-output='decode-rows' 和-v一起使用， 进行base64解码
  其他有很多用来限定范围的参数，比如数据库，起始时间，起始位置等等。这些参数在查找误操作的时候非常有用。

• 操作选项：

  Option Name	Description	Introduced
  --base64-output	Print binary log entries using base-64 encoding
  --bind-address	Use specified network interface to connect to MySQL Server
  --binlog-row-event-max-size	Binary log max event size
  --character-sets-dir	Directory where character sets are installed
  --connection-server-id	Used for testing and debugging. See text for applicable default values and other particulars	5.6.20
  --database	List entries for just this database
  --debug	Write debugging log
  --debug-check	Print debugging information when program exits
  --debug-info	Print debugging information, memory, and CPU statistics when program exits
  --default-auth	Authentication plugin to use
  --defaults-extra-file	Read named option file in addition to usual option files
  --defaults-file	Read only named option file
  --defaults-group-suffix	Option group suffix value
  --disable-log-bin	Disable binary logging
  --exclude-gtids	Do not show any of the groups in the GTID set provided
  --force-if-open	Read binary log files even if open or not closed properly
  --force-read	If mysqlbinlog reads a binary log event that it does not recognize, it prints a warning
  --help	Display help message and exit
  --hexdump	Display a hex dump of the log in comments
  --host	Host on which MySQL server is located
  --include-gtids	Show only the groups in the GTID set provided
  --local-load	Prepare local temporary files for LOAD DATA in the specified directory
  --login-path	Read login path options from .mylogin.cnf
  --no-defaults	Read no option files
  --offset	Skip the first N entries in the log
  open_files_limit	Specify the number of open file descriptors to reserve
  --password	Password to use when connecting to server
  --plugin-dir	Directory where plugins are installed
  --port	TCP/IP port number for connection
  --print-defaults	Print default options
  --protocol	Connection protocol to use
  --raw	Write events in raw (binary) format to output files
  --read-from-remote-master	Read the binary log from a MySQL master rather than reading a local log file
  --read-from-remote-server	Read binary log from MySQL server rather than local log file
  --result-file	Direct output to named file
  --secure-auth	Do not send passwords to server in old (pre-4.1) format	5.6.17
  --server-id	Extract only those events created by the server having the given server ID
  --server-id-bits	Tell mysqlbinlog how to interpret server IDs in binary log when log was written by a mysqld having its server-id-bits set to less than the maximum; supported only by MySQL Cluster version of mysqlbinlog
  --set-charset	Add a SET NAMES charset_name statement to the output
  --shared-memory-base-name	Name of shared memory to use for shared-memory connections
  --short-form	Display only the statements contained in the log
  --skip-gtids	Do not print any GTIDs; use this when writing a dump file from binary logs containing GTIDs
  --socket	Unix socket file or Windows named pipe to use
  --ssl-crl	File that contains certificate revocation lists
  --ssl-crlpath	Directory that contains certificate revocation-list files
  --ssl-mode	Desired security state of connection to server	5.6.30
  --start-datetime	Read binary log from first event with timestamp equal to or later than datetime argument
  --start-position	Decode binary log from first event with position equal to or greater than argument
  --stop-datetime	Stop reading binary log at first event with timestamp equal to or greater than datetime argument
  --stop-never	Stay connected to server after reading last binary log file
  --stop-never-slave-server-id	Slave server ID to report when connecting to server
  --stop-position	Stop decoding binary log at first event with position equal to or greater than argument
  --to-last-log	Do not stop at the end of requested binary log from a MySQL server, but rather continue printing to end of last binary log
  --user	MySQL user name to use when connecting to server
  --verbose	Reconstruct row events as SQL statements
  --verify-binlog-checksum	Verify checksums in binary log
  --version	Display version information and exit

binlog的基本块如下：

# at 642
#200429  9:43:32 server id 3347870753  end_log_pos 716 CRC32 0x6910cffb 	Query	thread_id=176224	exec_time=0	error_code=0

SET TIMESTAMP=1588124612/*!*/;
BEGIN
/*!*/;
# at 716
# at 895
# at 1895
#200429  9:43:32 server id 3347870753  end_log_pos 1926 CRC32 0x48ba5b4a 	Xid = 512601748
COMMIT/*!*/;

1、# at 642

指明的当前位置相对文件开始的偏移位置，这个在mysqlbinlog命令中可以作为--start-position的参数

2、#181007 1:50:38 server id 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd Query thread_id=440109962 exec_time=0 error_code=0

3、SET TIMESTAMP=1538877038/!/;

BEGIN

具体执行的语句

SET TIMESTAMP=1588124612/*!*/;
BEGIN
/*!*/;
# at 716
# at 895
# at 1895
#200429  9:43:32 server id 3347870753  end_log_pos 1926 CRC32 0x48ba5b4a 	Xid = 512601748
COMMIT/*!*/;

一行记录产生的日志如上所示。以SET TIMESTAMP=1539136238;开始，以COMMIT;结尾。我们可以根据两个at指明的位置来限定范围。

注意一条记录开始的SET TIMESTAMP之前的# at $position 和结尾的COMMIT之后的# at $position

根据binlog文件进行数据的恢复

格式：mysqlbinlog 日志文件 参数 | mysql -u用户名 -p密码

mysqlbinlog liangck.000001 | mysql -uroot –p123456  表示 全部恢复
 
mysqlbinlog liangck.000002 --start-pos=417 --stop-pos=773 | mysql -uroot -p123456 表示 从417至773位置
 
mysqlbinlog liangck.000002 --stop-pos=773 | mysql -uroot -p123456  表示 从头至773位置
 
mysqlbinlog liangck.000002 --start-pos=417 | mysql -uroot -p123456 表示 从417至尾 位置 
 
mysqlbinlog liangck.000002 --start-datetime="2004-12-25 11:25:56"  | mysql -uroot -p123456 日期参数和pos同理

还有一些好用的工具例如

利用binlog2sqlbinlog2sql官网介绍：从MySQL binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。

下一章我们会讲到