如东网站建设哪家好,福州市建设工程质量监督站网站,公司的网站建设与维护,网站美工设计基础Binlog是什么#xff1f;有哪几种格式#xff1f;推荐使用哪种#xff0c;为什么
Binlog是什么
Binlog二进制日志是MySQL Server层记录所有更改数据库内容的操作日志的二进制文件#xff0c;如操作UPDATE,DELETE,INSERTBinlog不记录SELECT#xff0c;SHOW等查询操作使主从…
Binlog是什么有哪几种格式推荐使用哪种为什么
Binlog是什么
Binlog二进制日志是MySQL Server层记录所有更改数据库内容的操作日志的二进制文件如操作UPDATE,DELETE,INSERTBinlog不记录SELECTSHOW等查询操作使主从复制数据恢复审计追踪的核心通常位于数据目录文件名类似mysql-bin.000001
通过参数bonlog_format设置格式
STATEMENT 记录原始SQL语句默认早期版本使用ROW 记录每一行被修改的具体值行级变更MINED 自动选择使用STATEMENT 或 ROW复杂操作使用 ROW简单操作用 STATEMENT
推荐使用ROW格式描述精确记录数据变更后的具体值不受环境因素影响一致性高主从复制不会出现因 SQL 非确定性引起的数据不一致问题恢复准确更容易基于 binlog 做精准恢复可通过修改MySQL配置文件或使用SQL语句设置Binlog格式修改配置文件
[mysqld]
binlog_format ROW # 可选值STATEMENT、ROW、MIXED使用SQL语句动态修改
SET GLOBAL binlog_format ROW; # 可选值STATEMENT、ROW、MIXED对数据库的操作是先执行还是先写入binlog中为什么
总结
事务性操作如 InnoDB
执行操作 → 记录 redo log → 写入 binlog → 提交事务通过两阶段提交确保 binlog 与数据变更的一致性。
非事务性操作如 MyISAM执行操作 → 写入 binlog存在崩溃导致主从不一致的风险事务性存储引擎如 InnoDB的执行流程
对于支持事务的存储引擎MySQL 采用 两阶段提交 确保 binlog 与数据变更的一致性
执行 SQL 并记录 redo log
执行 SQL 语句修改内存中的数据页Buffer Pool。将变更记录到redo log物理日志确保崩溃恢复时数据不丢失。
准备阶段Prepare
存储引擎将事务标记为PREPARED状态。强制将 redo log 写入磁盘确保持久性。
写入 binlog将事务的变更写入binlog逻辑日志。提交阶段Commit
存储引擎将事务标记为COMMITTED状态。写入commit 标记到 redo log事务完成。关键点
binlog 在事务提交前写入但在存储引擎真正提交前。通过 redo log 和两阶段提交保证 binlog 与数据变更的顺序一致避免主从复制不一致。
二、非事务性存储引擎如 MyISAM的执行流程
对于不支持事务的存储引擎
执行 SQL 并修改数据文件直接执行 SQL修改数据文件和索引。写入 binlog操作完成后将变更记录到 binlog。
风险
如果在执行 SQL 后、binlog 写入前发生崩溃可能导致主从数据不一致主库已执行但从库未复制。如果刚写入binlog数据库出现异常没有写入就重启了会发生什么事情在 MySQL 中若binlog 已写入但数据未提交时发生异常重启InnoDB 存储引擎会通过 崩溃恢复和两阶段提交2PC 机制确保数据一致性binlog 写入后数据库异常重启不会导致数据不一致可能需要通过 redo log 完成未完成的提交操作。是 MySQL 主从复制可靠性的核心保障之一
两阶段提交
准备阶段Prepare
执行 SQL 并记录 redo log。InnoDB 将事务标记为PREPARED并写入 redo log。
提交阶段Commit
MySQL Server 写入 binlog。InnoDB 写入COMMIT标记到 redo log事务完成发生异常的三种场景及处理场景 1binlog 写入后InnoDB 未收到提交指令
现象binlog 已写入磁盘但 InnoDB 未执行COMMIT。恢复流程
重启后InnoDB 通过 redo log 发现事务处于PREPARED状态。检查 binlog
若 binlog 存在该事务InnoDB 自动提交事务与 binlog 保持一致。若 binlog 不存在InnoDB 回滚事务。场景 2binlog 写入失败如磁盘已满
现象binlog 写入中断InnoDB 未收到提交指令。恢复流程
binlog 写入失败时MySQL Server 会回滚事务协调者决定。重启后InnoDB 通过 redo log 发现事务未完成直接回滚。场景 3binlog 和 redo log 部分丢失
现象binlog 或 redo log 文件损坏。恢复流程
若 binlog 完整但 redo log 丢失事务会被重新应用通过 binlog。若两者均损坏可能需要从备份恢复或手动修复极端情况随着时间的推移binglog越来越大怎么操作
可手动清理或自动清理来管理Binlog
方法定期清理旧日志手动 PURGE BINARY LOGS
配置自动过期时间如何强制创建新的binlog文件这个操作有什么实际用途强制切换
FLUSH BINARY LOGS;
用途说明日志归档便于日志管理、切分日志主从同步初始化主库切换 binlog 文件让从库能从新起点同步手动备份配合 binlog 做增量控制增量恢复的起点如何手动清理binlog只保留当前使用的删除其他的自动清理需要如何配置。清除某个 binlog 之前的所有日志
PURGE BINARY LOGS TO mysql-bin.000010;或清除指定时间之前的日志
PURGE BINARY LOGS BEFORE 2025-07-10 00:00:00;不能清除正在被从库读取的日志自动清理配置
在my.cnf中添加
expire_logs_days 7 # 保留7天
# 或新版
binlog_expire_logs_seconds 604800运行代码重启生效binlog 的生命周期和清理机制是什么如何设置自动过期时间
生命周期由参数 binlog_expire_logs_seconds 决定。清理方式分为手动PURGE和自动expire。自动清理是 MySQL 后台线程定期执行的保证磁盘空间控制。如何通过 mysqlbinlog 工具解析 binlog 内容如何只解析某一个时间段的 binlog基本用法
mysqlbinlog /var/lib/mysql/mysql-bin.000001只解析某一时间段
mysqlbinlog --start-datetime2025-07-10 12:00:00 \--stop-datetime2025-07-10 13:00:00 \/var/lib/mysql/mysql-bin.000001可以重定向为SQL文件
mysqlbinlog ... recover.sql配置文件中确保开启了binlog并且使用ROW格式。在my.cnf中配置
[mysqld]
log-binmysql-bin
binlog_formatROW
server-id1然后重启MySQL服务对数据库进行全量备份使用之前创建的任意表格新增3条记录。全量备份 mysqldump
mysqldump -u root -p --all-databases --single-transaction --master-data2 full_backup.sql插入记录
insert into student values (1001,张三,18);
insert into student values (1002,李四,20);
insert into student values (1003,王五,22);drop整个表之后使用binlog还原表包括新增的3条记录。查找 drop 前的 binlog 位置或时间使用 mysqlbinlog 导出 binlog SQL
mysqlbinlog --start-datetime2025-07-10 10:00:00 \--stop-datetime2025-07-10 12:00:00 \/var/lib/mysql/mysql-bin.000003 recover.sql手动编辑文件只保留建表和插入语句执行SQL恢复数据
mysql -u root -p recover.sql主从同步中的binlog与relaylog有什么关系从库 I/O 线程将主库 binlog 拉过来写入 relay log再由 SQL 线程执行
日志类型主库 or 从库作用Binlog主库记录主库的所有更改操作Relay log从库是从库从主库拉过来的 binlog 拷贝mysql主从同步时是如何保障数据一致性的
日志驱动主库写操作记录到 binlog从库 IO 线程拉取 binlog 存为 relay logSQL 线程顺序执行 relay log 语句复刻主库变更。顺序执行从库 SQL 线程串行应用 relay log严格遵循主库事务提交顺序避免乱序导致的数据冲突。GTID可选为事务分配全局唯一 ID精准标记已同步事务防重复执行简化故障切换时的断点定位。故障校验从库通过心跳检测主从连接结合Seconds_Behind_Master监控延迟异常时触发重连 / 修复维持同步链路。
本质是靠日志复制 串行重演 状态校验让从库精准复刻主库数据流转保障最终一致。按照时间顺序描述主从同步的所有步骤主库记录 binlog
所有 DML 操作被写入 binlog。
主库对 DML增删改、DDL建表等操作会按binlog_formatSTATEMENT/ROW/MIXED格式写入 binlog是复制的基础从库 I/O 线程连接主库
发起 COM_BINLOG_DUMP 请求。
从库启动后IO线程通过CHANGE MASTER TO配置的主库信息发起COM_BINLOG_DUMP协议请求拉取 binlog主库传送 binlog 内容
主库的 dump 线程发送 binlog 给从库。
主库响应请求后会创建dump线程持续将 binlog 事件推送给从库从库写入 relay log
I/O 线程把 binlog 内容保存为中继日志relay log。
从库IO线程接收的 binlog会先落地为中继日志relay log避免直接应用时中断丢失SQL 线程读取 relay log
从库 SQL 线程按顺序执行这些操作实现数据同步。
从库SQL线程串行解析 relay log重演 SQL 操作保证主从数据最终一致完成复制binlog 与事务的关联主库写 binlog 时会通过两阶段提交2PC 保证与 InnoDB redo log 一致避免主从数据分裂。relay log 的管理从库会自动清理过期 relay log可通过relay_log_purge等参数控制防止日志膨胀。异常场景处理若主从网络中断IO线程会自动重连若 SQL 线程执行报错如主键冲突需人工介入修复如跳过事务、修正数据
