Mysql-BinLog数据恢复

雨点打透心脏的1/2处 2023-10-02 18:38 49阅读 0赞

### 介绍 ###

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'

![在这里插入图片描述][f586c83f3d2842eab3dba191961cbb87.png]

**设置binlog模式**

set binlog_format=MIXED

**查询binlog是否开启**

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

![在这里插入图片描述][45ad3e57bd97450f8ed4226b5d235667.png]

**手动开启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;

![在这里插入图片描述][918a3e18709f4797a3a1bba80a200c10.png]

**查询日志保存位置**

show variables like 'datadir';

**查看master上的所有binlog**

show master logs

![在这里插入图片描述][a071619a219946ba8483aad2bbb28a01.png]

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

show master status

![在这里插入图片描述][8005d49b0b184888b580ca4edabab8c1.png]

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

show binlog events in 'binlog.000041';

![在这里插入图片描述][a75fd973f683406b91aa524be475f314.png]

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

show variables like 'max_binlog_size'

默认是1GB  
![在这里插入图片描述][1f614024b6284edb9f0267bd0c1cf72a.png]

设置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语句

给大家参考下我自己写的转义效果:  
![在这里插入图片描述][b8e084b1a0b34771a52bfd51ea5e9f3f.png]

我执行recoveryStructures和recoveryData会生成2个文件一个是DDL文件一个是DML文件  
![在这里插入图片描述][458d12bad5ff4e34b2661275bf5166cd.png]  
**DDL文件**  
![在这里插入图片描述][ea9ec3deb9b94f378520df26aaca64b2.png]  
DML文件  
![在这里插入图片描述][360a26ce5007402b89f914870bf93395.png]  
先执行DDL文件将数据库结构进行恢复,然后在执行DML将数据进行恢复

**增量数据生成**  
这个方法就是将所有被删除的数据都转换为增量sql  
![在这里插入图片描述][62447c55f79b4d75a91d88c7316a2a09.png]

效果如下:  
![在这里插入图片描述][a51ff0e111ec4763b62f13ef2af4f8d9.png]

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

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

![在这里插入图片描述][e2af4607da624cff9e7bf546663eaa91.gif]

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

![ceaefc422a4745479fc1e8cfd2bdb74e.jpg][]

[f586c83f3d2842eab3dba191961cbb87.png]: https://img-blog.csdnimg.cn/f586c83f3d2842eab3dba191961cbb87.png
[45ad3e57bd97450f8ed4226b5d235667.png]: https://img-blog.csdnimg.cn/45ad3e57bd97450f8ed4226b5d235667.png
[918a3e18709f4797a3a1bba80a200c10.png]: https://img-blog.csdnimg.cn/918a3e18709f4797a3a1bba80a200c10.png
[a071619a219946ba8483aad2bbb28a01.png]: https://img-blog.csdnimg.cn/a071619a219946ba8483aad2bbb28a01.png
[8005d49b0b184888b580ca4edabab8c1.png]: https://img-blog.csdnimg.cn/8005d49b0b184888b580ca4edabab8c1.png
[a75fd973f683406b91aa524be475f314.png]: https://img-blog.csdnimg.cn/a75fd973f683406b91aa524be475f314.png
[1f614024b6284edb9f0267bd0c1cf72a.png]: https://img-blog.csdnimg.cn/1f614024b6284edb9f0267bd0c1cf72a.png
[b8e084b1a0b34771a52bfd51ea5e9f3f.png]: https://img-blog.csdnimg.cn/b8e084b1a0b34771a52bfd51ea5e9f3f.png
[458d12bad5ff4e34b2661275bf5166cd.png]: https://img-blog.csdnimg.cn/458d12bad5ff4e34b2661275bf5166cd.png
[ea9ec3deb9b94f378520df26aaca64b2.png]: https://img-blog.csdnimg.cn/ea9ec3deb9b94f378520df26aaca64b2.png
[360a26ce5007402b89f914870bf93395.png]: https://img-blog.csdnimg.cn/360a26ce5007402b89f914870bf93395.png
[62447c55f79b4d75a91d88c7316a2a09.png]: https://img-blog.csdnimg.cn/62447c55f79b4d75a91d88c7316a2a09.png
[a51ff0e111ec4763b62f13ef2af4f8d9.png]: https://img-blog.csdnimg.cn/a51ff0e111ec4763b62f13ef2af4f8d9.png
[e2af4607da624cff9e7bf546663eaa91.gif]: https://img-blog.csdnimg.cn/e2af4607da624cff9e7bf546663eaa91.gif
[ceaefc422a4745479fc1e8cfd2bdb74e.jpg]: https://img-blog.csdnimg.cn/ceaefc422a4745479fc1e8cfd2bdb74e.jpg