MySQL-binlog日志

https://www.hyouit.com?aff=418

1、binlog 日志

binlog是server层自己的日志。作用可以用于备份恢复、主从复制、分析大事务。

为什么会有binlog和redo log两份日志呢?

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM,但是 MyISAM 没有 crash-safe 的能力,binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的,既然只依靠 binlog 是没有 crash-safe 能力的,所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

2、binlog记录格式

binlog日志有三种格式,可以通过binlog_format参数指定。

  • statement,记录的内容是SQL语句原文,同步数据时,会执行记录的SQL语句,但是有个问题,如果语句中使用 now()函数,这里会获取当前系统时间,直接执行会导致与原库的数据不一致。

  • row,记录的内容不再是简单的SQL语句了,还包含操作的具体数据,通过mysqlbinlog工具解析出来。但是这种格式,需要更大的容量来记录,比较占用空间,恢复与同步时会更消耗IO资源,影响执行速度。

  • mixed,记录的内容是前两者的混合。MySQL会判断这条SQL语句是否可能引起数据不一致,如果是,就用row格式,否则就用statement格式。

3、写入机制

一个事务的binlog不能被拆开,无论这个事务多大,也要确保一次性写入,所以系统会给每个线程分配一个块内存作为binlog cache。

我们可以通过binlog_cache_size参数控制单个线程binlog cache大小,如果存储内容超过了这个参数,就要暂存到磁盘(Swap)。

事务开始执行时,先把日志写到binlog cache,事务提交后,write到文件系统缓存(page cache)中,最后fsync日志刷盘。

通过 sync_binlog 参数控制写入文件系统缓存和日志刷盘的时机。

  • 0,8.0 之前默认值,表示每次提交事务都只write,由系统自行判断什么时候执行fsync。虽然性能得到提升,但是机器宕机,page cache里面的binglog会丢失。

  • 1,8.0 默认值,表示每次提交事务都会执行fsync。

  • N,N>1,表示每次提交事务都write,但累积N个事务后才fsync。

出现IO瓶颈的场景里,将sync_binlog设置成一个比较大的值,可以提升性能。同样的,如果机器宕机,会丢失最近N个事务的binlog日志。

4、binlog和redo log保持数据一致原理

在执行更新语句过程,会记录redo log与binlog两块日志,以基本的事务为单位,redo log在事务执行过程中可以不断写入,而binlog只有在提交事务时才写入,所以redo log与binlog的写入时机不一样。

为了解决两份日志之间的逻辑一致问题,InnoDB存储引擎使用两阶段提交方案。将redo log的写入拆成了两个步骤prepare和commit,这就是两阶段提交。

简单例子就是,当执行一个更新操作的事务开始时,更新数据,写入redo log日志,处于prepare阶段,当提交事务后,写入binlog日志,redo log设置commit阶段。

使用两阶段提交后,写入binlog时发生异常也不会有影响,因为MySQL根据redo log日志恢复数据时,发现redo log还处于prepare阶段,并且没有对应binlog日志,就会回滚该事务。

redo log设置commit阶段发生异常,则不会回滚事务,虽然redo log是处于prepare阶段,但是能通过事务id找到对应的binlog日志,所以MySQL认为是完整的,就会提交事务恢复数据。

5、binlog 日志管理

在 8.0 中是默认开启,5.x 中默认是关闭的

5.1 查看binlog相关参数

查看binlog是否开启

# 1 为开启,0 为关闭
mysql> select @@log_bin;

查看binlog文件路径

mysql> select @@log_bin_basename;

查看binlog日志存储格式

mysql> select @@binlog_format;

查看当前所有binlog文件列表

mysql> show binary logs;

查看正在使用的binlog文件信息

mysql> show master status;

binlog文件是由一个个的event组成的,查看binlog文件中event 信息,可以看到开始pos和结束pos之间的保存的更改数据库的操作。

mysql> show binlog events in 'binlog.000004';
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                             |
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------+
| binlog.000004 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.24, Binlog ver: 4                |
| binlog.000004 | 125 | Previous_gtids |         1 |         156 |                                                  |
| binlog.000004 | 156 | Anonymous_Gtid |         1 |         233 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| binlog.000004 | 233 | Query          |         1 |         341 | create database test /* xid=20 */                |
| binlog.000004 | 341 | Anonymous_Gtid |         1 |         418 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| binlog.000004 | 418 | Query          |         1 |         529 | use `test`; create table t1(id int) /* xid=26 */ |
| binlog.000004 | 529 | Anonymous_Gtid |         1 |         608 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| binlog.000004 | 608 | Query          |         1 |         683 | BEGIN                                            |
| binlog.000004 | 683 | Table_map      |         1 |         731 | table_id: 89 (test.t1)                           |
| binlog.000004 | 731 | Write_rows     |         1 |         771 | table_id: 89 flags: STMT_END_F                   |
| binlog.000004 | 771 | Table_map      |         1 |         819 | table_id: 89 (test.t1)                           |
| binlog.000004 | 819 | Write_rows     |         1 |         859 | table_id: 89 flags: STMT_END_F                   |
| binlog.000004 | 859 | Table_map      |         1 |         907 | table_id: 89 (test.t1)                           |
| binlog.000004 | 907 | Write_rows     |         1 |         947 | table_id: 89 flags: STMT_END_F                   |
| binlog.000004 | 947 | Xid            |         1 |         978 | COMMIT /* xid=30 */                              |
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------+

通过操作系统命令mysqlbinlog可以查看logbin文件更为详细的信息,可以看到某个时间点或位置点执行的数据操作日志。

# --base64-output=decode-rows,将 BINLOG 语句中事件以 base-64 的编码显示,对一些二进制的内容进行屏蔽。
[mysql@localhost data]$ mysqlbinlog binlog.000004
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230218 19:52:10 server id 1  end_log_pos 125 CRC32 0xce1fb0e2 	Start: binlog v 4, server v 8.0.24 created 230218 19:52:10 at startup
ROLLBACK/*!*/;
# at 125
#230218 19:52:10 server id 1  end_log_pos 156 CRC32 0xb94f704d 	Previous-GTIDs
# [empty]
..... 中间省略
# at 947
#230218 20:24:13 server id 1  end_log_pos 978 CRC32 0x8c7690b8 	Xid = 30
COMMIT/*!*/;
# at 978
#230218 20:32:55 server id 1  end_log_pos 1022 CRC32 0x99b8c756 	Rotate to binlog.000005  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
5.2 binlog 数据恢复
  • 通过位置点pos 数据恢复

模拟删除数据

mysql> drop database test;

通过 5.1 中查看方法,可以知道开始pos是233,结束pos是978。

# 截取binlog文件中语句,只涉及到单个binlog文件
[mysql@localhost ~]$ mysqlbinlog --start-position=233 --stop-position=978 ./data/binlog.000004  > /tmp/test.sql
# 加载截取的语句
mysql> set sql_log_bin=0;
mysql> source /tmp/test.sql
mysql> set sql_log_bin=1;

最终验证恢复成功。可是在实际中,截取用于恢复的数据日志内容可能跨多个binlog文件,每个binlog文件中的pos是重复的,只能一个一个binlog文件截取,最终拼接成一个完整的恢复语句。

如果跨多个binlog文件,截取某个数据库的数据操作内容。

[mysql@localhost data]$ mysqlbinlog -d test --base64-output=decode-rows binlog.000004 binlog.000005  > /tmp/test.sql
  • 通过时间数据恢复

按照时间截取

[mysql@localhost data]$ mysqlbinlog --start-datetime='2023-02-18 19:50:00' --stop-datetime='2023-02-18 21:20:00'  binlog.000004 binlog.000005
  • 通过gtid数据恢复

5.6 版本新加的特性。GTID = server_uuid :transaction_id

开启gtid相关参数

gtid_mode=on
enforce_gtid_consistency=on

通过show master status 查看gtid信息。

mysql> show master status;
+---------------+----------+--------------+------------------+----------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+---------------+----------+--------------+------------------+----------------------------------------+
| binlog.000007 |      344 |              |                  | 4dbea3cb-ac74-11ed-bba2-000c29b4faa7:1 |
+---------------+----------+--------------+------------------+----------------------------------------+

查看binlog文件中记录样式,先是记录gtid信息,然后记录该gtid事务操作语句,一直到 SET @@SESSION.GTID_NEXT 结束。


SET @@SESSION.GTID_NEXT= '4dbea3cb-ac74-11ed-bba2-000c29b4faa7:5'/*!*/;
# at 815
#230218 22:31:01 server id 1  end_log_pos 922 CRC32 0xdd3a8435 	Query	thread_id=9	exec_time=1	error_code=0	Xid = 41
SET TIMESTAMP=1676730661/*!*/;
drop database test1
/*!*/;
# at 922
#230218 22:32:17 server id 1  end_log_pos 999 CRC32 0x351cd215 	GTID	last_committed=2	sequence_number=3	rbr_only=no	original_committed_timestamp=1676730737099177	im
mediate_commit_timestamp=1676730737099177	transaction_length=181
# original_commit_timestamp=1676730737099177 (2023-02-18 22:32:17.099177 CST)
# immediate_commit_timestamp=1676730737099177 (2023-02-18 22:32:17.099177 CST)
/*!80001 SET @@session.original_commit_timestamp=1676730737099177*//*!*/;
/*!80014 SET @@session.original_server_version=80024*//*!*/;
/*!80014 SET @@session.immediate_server_version=80024*//*!*/;
SET @@SESSION.GTID_NEXT= '4dbea3cb-ac74-11ed-bba2-000c29b4faa7:6'/*!*/;
# at 999
#230218 22:32:17 server id 1  end_log_pos 1103 CRC32 0xfcce9c02 	Query	thread_id=9	exec_time=0	error_code=0	Xid = 44
SET TIMESTAMP=1676730737/*!*/;
drop database test
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

按照gtid数据恢复

# --skip-gtids  因为gtid有幂等性,当截取文件中包含gtid信息,数据库判断就不再执行了,去除截取文件中的gtid信息
$ mysqlbinlog --skip-gtids --include-gtids='4dbea3cb-ac74-11ed-bba2-000c29b4faa7:1-6' --exclude-gtids='4dbea3cb-ac74-11ed-bba2-000c29b4faa7:5','4dbea3cb-ac74-11ed-bba2-000c29b4faa7:6' binlog.000006 binlog.000007 binlog.000008 binlog.000009 > /tmp/test.sql
5.3 binlog 清理

自动清理,通过参数 binlog_expire_logs_seconds 默认30天,8.0 中新增加的参数,优先级高于参数 expire_logs_days 。

mysql> show variables like '%expire%';
+--------------------------------+---------+
| Variable_name                  | Value   |
+--------------------------------+---------+
| binlog_expire_logs_seconds     | 2592000 |
| disconnect_on_expired_password | ON      |
| expire_logs_days               | 0       |
+--------------------------------+---------+

手动清理

# 删除 binlog.000015 之前的binlog日志文件
mysql> purge binary logs to 'binlog.000015';
# 删除时间 1 天之前的binlog日志文件
mysql> purge binary logs before now() - interval 1 day;
# 删除所有的binlog日志文件,从binlog.00001重新开始
mysql> reset master;
# 手动日志文件滚动,数据库重启日志文件自动滚动
mysql> flush logs;

6、binlog2sql应用

安装配置binlog2sql工具

[root@localhost ~]# git clone https://github.com/danfengcao/binlog2sql.git
[root@localhost ~]# yum install python3
[root@localhost ~]# cd binlog2sql && pip3 install -r requirements.txt
[root@localhost ~]# pip3 show pymysql
# MySQL8.0更新了很多字符集,但是这些字符集长度超过255了,所以旧版的PyMySQL不支持长度超过255的字符,所以更新该工具包。
[root@localhost ~]# pip3 install --upgrade PyMySQL

在使用binlog2sql工具时遇到如下问题

ModuleNotFoundError: No module named 'pymysql.util'
处理方式:
[root@localhost ~]# pip3 uninstall pymysql
[root@localhost ~]# pip3 install pymysql==0.9.3

工具使用方式

# 输入该binlog日志文件的记录执行语句
[root@localhost ~]# python3 binlog2sql.py -h127.0.0.1 -p3306 -uroot -p -d log -t t1 --start-file='binlog.000017';
# 按照SQL语句类型过滤输出日志文件的记录执行语句
[root@localhost ~]# python3 binlog2sql.py -h127.0.0.1 -p3306 -uroot -p -d log -t t1 --start-file='binlog.000017' --sql-type=delete
# 按照位置点过滤输出日志文件记录的执行语句
[root@localhost ~]# python3 binlog2sql.py -h127.0.0.1 -p3306 -uroot -p -d log -t t1 --start-file='binlog.000017' --start-position=1235 --stop-position=1396
Password: 
DELETE FROM `log`.`t1` WHERE `id`=3 LIMIT 1; #start 1235 end 1396 time 2023-03-22 21:37:17
# 使用参数 -B 生成逆向还原SQL语句
[root@localhost ~]# python3 binlog2sql.py -h127.0.0.1 -p3306 -uroot -p -d log -t t1 --start-file='binlog.000017' --start-position=1235 --stop-position=1396 -B
Password: 
INSERT INTO `log`.`t1`(`id`) VALUES (3); #start 1235 end 1396 time 2023-03-22 21:37:17
posted @   原来是你~~~  阅读(172)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示