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
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战