Mysql-BinLog数据恢复

转x33g5p2x  于2022-03-28 转载在 Mysql  
字(8.5k)|赞(0)|评价(0)|浏览(504)

介绍

binlog,即二进制日志,它记录了数据库上的所有改变，并以二进制的形式保存在磁盘中；

它可以用来查看数据库的变更历史、数据库增量备份和恢复、Mysql的复制（主从数据库的复制）。

binlog有三种格式：Statement、Row以及Mixed。

–基于SQL语句的复制(statement-based replication,SBR)，
–基于行的复制(row-based replication,RBR)，
–混合模式复制(mixed-based replication,MBR)。

1 Statement
每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题。

ps：相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。

2 Row

5.1.5版本的MySQL才开始支持row level的复制,它不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题.

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

ps:新版本的MySQL中对row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

3 Mixed

从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。

在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

所以使用Mixed模式就行了

配置binlog

mysql5~7

server-id=1 # 集群模式的话这个需要唯一
log-bin=mysql-bin  # 设置binlog
expire_logs_days = 30 # 只保留 30 天的日志文件
binlog_format=MIXED

现在mysql8的binlog都是默认开启的所以无须手动去配置,但是可以设置binglog的保留时间,在mysql8设置日志保留时间的参数改为binlog_expire_logs_seconds

BinLog操作

我以mysql8+为例
查询binlog模式

show variables like 'binlog_format'

设置binlog模式

set binlog_format=MIXED

查询binlog是否开启

-- ON 就是开启了   OFF就是没开启
show variables like 'log_bin'

手动开启binlog(重启mysql后会失效)

-- 开启 
set sql_log_bin=1  

-- 关闭
set sql_log_bin=0

查看日志保存天数 默认30天

show variables like '%binlog_expire_logs_seconds%';

设置日子保存天数

-- 设置为30天有效期
set global binlog_expire_logs_seconds = 60*60*24;

查询日志保存位置

show variables like 'datadir';

查看master上的所有binlog

show master logs

查看当前正在写入的binlog文件

show master status

查看指定binlog文件的内容(简化的)

show binlog events in 'binlog.000041';

查询binglog大小
超过指定大小就会写入新的文件中

show variables like 'max_binlog_size'

默认是1GB

设置binglog文件大小

set global max_binlog_size=1073741824;

将新增内容写入新的log中

flush logs

清空所有binlog日志

reset master;

导出binlog日志
使用mysql自带的mysqlbinlog 命令

/usr/bin/mysqlbinlog  --no-defaults --base64-output=decode-rows -v --set-charset=utf8 --start-position=0  --stop-position=999999999999  /usr/mysql/data/binlog.000041 > /usr/mysql/log/log.binglog

–start-position 是起始位置

–stop-position 结束位置

可以通过show binlog events in 'binlog.000041'; 结果集查询的pos来确认位置

或者指定时间

/usr/bin/mysqlbinlog --no-defaults --start-datetime="2016-07-26 00:00:00" --stop-datetime="2016-07-27 15:00:00" /usr/mysql/data/binlog.000041 >/usr/mysql/log/log.binglog

使用mysqlbinlog导出binglog日志展示

如果binlog不使用mysqlbinlog导出那么就是一个二进制文件,完全是看不懂的

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
DELIMITER /*!*/;
# at 4
#220328  4:49:18 server id 2  end_log_pos 124 CRC32 0x471ab637 	Start: binlog v 4, server v 8.0.16 created 220328  4:49:18 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 124
#220328  4:49:18 server id 2  end_log_pos 155 CRC32 0x58954f2f 	Previous-GTIDs
# [empty]
# at 155
#220328  4:53:44 server id 2  end_log_pos 232 CRC32 0x365b335e 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no	original_committed_timestamp=1648443224786662	immediate_commit_timestamp=1648443224786662	transaction_length=229
# original_commit_timestamp=1648443224786662 (2022-03-28 04:53:44.786662 UTC)
# immediate_commit_timestamp=1648443224786662 (2022-03-28 04:53:44.786662 UTC)
/*!80001 SET @@session.original_commit_timestamp=1648443224786662*//*!*/;
/*!80014 SET @@session.original_server_version=80016*//*!*/;
/*!80014 SET @@session.immediate_server_version=80016*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 232
#220328  4:53:44 server id 2  end_log_pos 384 CRC32 0x56256411 	Query	thread_id=338	exec_time=0	error_code=0	Xid = 6473
use `voidme`/*!*/;
SET TIMESTAMP=1648443224/*!*/;
SET @@session.pseudo_thread_id=338/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=46/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
DROP TABLE `t_role_permission_copy1` /* generated by server */
/*!*/;
# at 384
#220328  4:53:55 server id 2  end_log_pos 463 CRC32 0xfd0b8d62 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes	original_committed_timestamp=1648443235575794	immediate_commit_timestamp=1648443235575794	transaction_length=447
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1648443235575794 (2022-03-28 04:53:55.575794 UTC)
# immediate_commit_timestamp=1648443235575794 (2022-03-28 04:53:55.575794 UTC)
/*!80001 SET @@session.original_commit_timestamp=1648443235575794*//*!*/;
/*!80014 SET @@session.original_server_version=80016*//*!*/;
/*!80014 SET @@session.immediate_server_version=80016*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 463
#220328  4:53:55 server id 2  end_log_pos 549 CRC32 0x3c4686e3 	Query	thread_id=338	exec_time=0	error_code=0
SET TIMESTAMP=1648443235/*!*/;
BEGIN
/*!*/;
# at 549
#220328  4:53:55 server id 2  end_log_pos 624 CRC32 0x48c34f5e 	Table_map: `voidme`.`t_user` mapped to number 160
# at 624
#220328  4:53:55 server id 2  end_log_pos 800 CRC32 0x7c7a8494 	Update_rows: table id 160 flags: STMT_END_F
### UPDATE `voidme`.`t_user`
### WHERE
###   @1=13
###   @2='uh'
###   @3='cfa4e145a550cb21ad63d2a9d416717b'
###   @4='17632242222'
###   @5=0
###   @6='2021-10-25 01:30:14'
###   @7='uh'
###   @8=NULL
### SET
###   @1=13
###   @2='uh11'
###   @3='cfa4e145a550cb21ad63d2a9d416717b'
###   @4='17632242222'
###   @5=0
###   @6='2021-10-25 01:30:14'
###   @7='uh'
###   @8=NULL
# at 800
#220328  4:53:55 server id 2  end_log_pos 831 CRC32 0x9ec7faa3 	Xid = 6483
COMMIT/*!*/;
# at 831
#220328  4:54:09 server id 2  end_log_pos 910 CRC32 0x9fb49724 	Anonymous_GTID	last_committed=2	sequence_number=3	rbr_only=yes	original_committed_timestamp=1648443249097295	immediate_commit_timestamp=1648443249097295	transaction_length=370
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1648443249097295 (2022-03-28 04:54:09.097295 UTC)
# immediate_commit_timestamp=1648443249097295 (2022-03-28 04:54:09.097295 UTC)
/*!80001 SET @@session.original_commit_timestamp=1648443249097295*//*!*/;
/*!80014 SET @@session.original_server_version=80016*//*!*/;
/*!80014 SET @@session.immediate_server_version=80016*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 910
#220328  4:54:09 server id 2  end_log_pos 987 CRC32 0x5f91a97f 	Query	thread_id=338	exec_time=0	error_code=0
SET TIMESTAMP=1648443249/*!*/;
BEGIN
/*!*/;
# at 987
#220328  4:54:09 server id 2  end_log_pos 1062 CRC32 0xae57b699 	Table_map: `voidme`.`t_user` mapped to number 160
# at 1062
#220328  4:54:09 server id 2  end_log_pos 1170 CRC32 0xbc49a368 	Write_rows: table id 160 flags: STMT_END_F
### INSERT INTO `voidme`.`t_user`
### SET
###   @1=14
###   @2='uh1122'
###   @3='cfa4e145a550cb21ad63d2a9d416717b'
###   @4='17632242222'
###   @5=0
###   @6='2021-10-25 01:30:14'
###   @7='uh'
###   @8=NULL
# at 1170
#220328  4:54:09 server id 2  end_log_pos 1201 CRC32 0xb9f0c786 	Xid = 6489
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

扩展

我们想要是用binlog进行数据恢复和回滚以及数据分析需要自己写转义工具或者买一个…
binlog是二进制文件必须使用mysqlbinlog转义为可查看的sql日志文件,然后我们才能利用工具解析这个文件

如果丢失的数据过多那么可能产生多个binlog文件,需要去binlog目录执行ls -l mysql-bin* 将事故范围时间的binlog全部都导出来,尽量多往前导出点,不然可能会遗漏数据

数据库只是部分数据丢失但是表和库都还正常

  1. 指定时间内的所有binlog日志导出到本地(有可能有多个日志)
  2. 将导出来的binlog文件解析为增量数据sql文件
  3. 执行增量数据sql文件

被人删库跑路了或者表少了结构等丢失严重等
一般公司最差也会一个月备份好几次全库和全表,而现在binlog基本上默认都是30天后才清除,所有只要在30天内的东西都能进行恢复的

  1. 拿到最近备份的全量库脚本文件先进行恢复
  2. 然后在拿到从当前日期到全量库脚本文件备份的日期前一天 的所有binlog ,然后在利用mysqlbinlog将日志导出来
  3. 将导出来的binlog文件解析为2种sql文件(DDL和DML) (只要增量)
  4. 执行DDL的sql语句
  5. 执行DMLsql语句

给大家参考下我自己写的转义效果:

我执行recoveryStructures和recoveryData会生成2个文件一个是DDL文件一个是DML文件

DDL文件

DML文件

先执行DDL文件将数据库结构进行恢复,然后在执行DML将数据进行恢复

增量数据生成
这个方法就是将所有被删除的数据都转换为增量sql

效果如下:

当然我还实现了,修改恢复 ,增加恢复,数据回滚,数据库和表恢复,等这些操作,…NICE
注意: 数据回滚是利用反向解析sql 比如insert 解析为delete , 然后在反向执行sql

怕抢别人饭碗,这里代码就不给了,以上给你提供一个思路自己研究下是可以写出来的,

点赞 -收藏-关注-便于以后复习和收到最新内容有其他问题在评论区讨论-或者私信我-收到会在第一时间回复如有侵权，请私信联系我感谢,配合,希望我的努力对你有帮助^_^

版权说明 : 本文为转载文章, 版权归原作者所有 版权申明

原文链接 : https://blog.csdn.net/weixin_45203607/article/details/123791667

内容来源于网络，如有侵权，请联系作者删除！

mysqlbinlog数据库日志

相关文章

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新文章

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com