2020-09-05

mysql通过binlog日志来恢复数据

文章目录

有一天朋友突然打过来电话说客户因为不小心删除服务器上mysql数据库，着急想办法恢复数据。都已经2年多的项目了，早不维护了客户自己不知道咋操作的删除跑路，呵呵，咱也不是专业DBA，很少搞过恢复数据的活，不过作为十来年经验码农老手，也不是啥难事。

基础前提

先是搞VPN，连客户服务器，查找数据库安装位置，数据存储文件及数据库账户信息，基础都具备了，开搞吧。

数据现状

数据库被客户搞的剩下一张表了都！

幸好，有binlog日志开启，生成从2年前启动项目到上线项目的binlog日志文件记录。

没有其他任务的定期数据备份。

恢复原理

如果要将数据库恢复到故障点之前，那么需要有数据库全备和全备之后产生的所有二进制日志。

全备作用 ：使用全备将数据库恢复到上一次完整备份的位置；

二进制日志作用：利用全备的备份集将数据库恢复到上一次完整备份的位置之后，需要对上一次全备之后数据库产生的所有动作进行重做，而重做的过程就是解析二进制日志文件为SQL语句，然后放到数据库里面再次执行。

1、对于DML操作，binlog记录了所有的DML数据变化：
–对于insert，binlog记录了insert的行数据
–对于update，binlog记录了改变前的行数据和改变后的行数据
–对于delete，binlog记录了删除前的数据
假如用户不小心误执行了DML操作，可以使用mysqlbinlog将数据库恢复到故障点之前。

2、对于DDL操作，binlog只记录用户行为，而不记录行变化，但是并不影响我们将数据库恢复到故障点之前

mysqlbinlog 命令

作用：

mysqlbinlog 是用来解析mysql的binlog日志的。详细查看mysqlbinlog --help

mysqlbinlog日志无法直接用cat 查看，需要用mysqlbinlog命令转换为普通文件才能查看mysqlbinlog -v master-bin.000054 | grep -i "drop database lijiamandb"或mysqlbinlog -v master-bin.000054 > master-bin.txt语句，也只有用mysqlbinlog命令把binlog日志转化为普通文件之后，才能用这个普通文件进行数据恢复

参数解析：

–start-datetime=datetime

从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读取。datetime值相对于运行mysqlbinlog的机器上的本地时区。该值格式应符合DATETIME或TIMESTAMP数据类型。例如：

–stop-datetime=datetime

从二进制日志中第1个日期时间等于或晚于datetime参量的事件起停止读。关于datetime值的描述参见–start-datetime选项。该选项可以帮助及时恢复。

–start-position=N

从二进制日志中第1个位置等于N参量时的事件开始读。

–stop-position=N

从二进制日志中第1个位置等于和大于N参量时的事件起停止读。

-d 指定恢复binlog日志中的某个库的日志

-D 参数没有生成新的日志记录

恢复方式

客户数据库没有任何时间点的全库备份？怎么办？

通过Bin log恢复数据. 因为我整个Schema都删掉了，又没备份，正好开启了bin log日志，所以把历史的bin-log都重新执行了一遍，重新恢复到误删以前的版本，（我这里总共有26个文件，批量处理的）：（9999999999999：是为了省掉去查找每一个bin-log日志文件的起始结束位置，设的一个无穷大的数字，简化操作.）

恢复步骤

第一种方式：

#mysqlbinlog按时间点或者位置导出数据，同时mysql导入数据(2个步骤放到一起执行)
./mysqlbinlog  -D -d edu /home/edu/bak/mysql-bin.000001 --start-position=4 --stop-position=9999999999999 | ./mysql --defaults-file=/home/edu/soft_install/mysql/mariadb_3306/my.cnf -uroot -pmima -f
#导入binlog恢复过程中有可能有执行错误需要跳过用 -f 参数
./mysqlbinlog  -D -d edu /home/edu/bak/mysql-bin.000002 --start-position=4 --stop-position=9999999999999 | ./mysql --defaults-file=/home/edu/soft_install/mysql/mariadb_3306/my.cnf -uroot -pmima -f
......
./mysqlbinlog  -D -d edu /home/edu/bak/mysql-bin.000026 --start-position=4 --stop-position=9999999999999 | ./mysql --defaults-file=/home/edu/soft_install/mysql/mariadb_3306/my.cnf -uroot -pmima -f

第二种方式：

也可以先导出mysqlbinlog数据，然后再单独导入日志数据执行。

1、生成binlog对应的可执行SQL文件

1	./mysqlbinlog -D /home/edu/bak/mysql-bin.000001 --start-position=4 --stop-position=9999999999999 > /home/edu/bak/binlog_17.sql

2、生成binlog对应的可执行SQL文件

1
2
3

#-v 查看导入的详细信息
#-f 导入过程中遇到错误，跳过，并继续往下执行
./mysql --defaults-file=/home/edu/soft_install/mysql/mariadb_3306/my.cnf -uroot -pmima -v -f < /home/edu/bak/binlog_17.sql

总结结论：

1、切记一定要定期备份，恢复过程中禁止在对数据库进行任何操作；

2、有备份的话恢复也快一点，可以从备份的时间点做增量备份，不需要像我这里从头开始91个文件全部批量跑一遍，当然我用编辑器批量处理的也还算快；

3、另外一定要打开Bin-log日志，如果没做备份也可以通过Bin-log日志恢复。

4、操作要小心，要将当前的binlog日志拷贝到其他目录，以免后续操作对binlog日志产生影响。

5、使用mysqldump全备加binlog日志，可以将数据恢复到故障前的任意时刻。

附录

1、mysqldump备份

1 2	# 备份数据库test到tmpl目录下的test.sql文件中 ./mysql/bin/mysqldump -uroot -padmin test -l -F > /tmp/test.sql

-l 表示读锁，在备份的过程中mysql不能写入数据，从而保证数据的完整性

-f 表示 flust logs,重新生成新的日志文件，当然也包括bin-log日志

2、查看所有binlog日志文件列表

1	show master logs;

3、查看最新的binlog日志

1 2	#查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值 show master status;

4、命令查看binlog日志

#mysqlbinlog方法读取内容较多不易观察，以下命令更为方便查看命令
show binlog events in 'mysql-bin.000003';
#指定查询,从pos点406开始查询
show binlog events in 'mysql-bin.000003' from 406;
#指定查询，从pos点154开始查询，中间跳过2行，查询4条数据
show binlog events in 'mysql-bin.000003' from 154 limit 2,4;
#指定查询某个binlog文件中搜索包含某个单词的记录
show binlog events in 'mysql-bin.000003' | grep -i 'DROP DATABASE'

5、查看是否开启binlog日志

1 2	#查看数据库是否开启binlog日志 show variables like '%log_bin%';

6、查看普通用户全局只读

1 2	#查看普通用户数据权限是否为只读,不能写数据 show variables like '%read_only%';

再三强调

在生产环境误操作之后，应该立刻判断这个表或者库的重要性

1.如果是不常用的数据库或者表，几乎没有数据更新，可以不用锁库，只读

2.如果是很常用的数据库或者表，必须处理，设置为只读，如果更为严重，需要停止mysql服务器。目的就是防止有新的数据写进来，数据混乱

1 2	#设置全局只读，普通权限的用户只读，不能写数据 set global read_only=1;