首页 › MySQL论坛 › MySQL中的binlog日志操作示例

哈尔滨 發表於 2026-1-12 14:32:44

MySQL中的binlog日志操作示例

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、什么是binlog？</a></li><li><a href="#_label1">二、为什么需要binlog？</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_0">2-1、binlog的主要用途有三个：</a></li><li><a href="#_lab2_1_1">2-2、binlog的三种格式</a></li><ul class="third_class_ul"><li><a href="#_label3_1_1_0">1、STATEMENT格式（记录SQL语句）</a></li><li><a href="#_label3_1_1_1">2、ROW格式（记录行数据变化）</a></li><li><a href="#_label3_1_1_2">3、MIXED格式（混合模式）</a></li></ul></ul><li><a href="#_label2">三、如何启用binlog？</a></li><ul class="second_class_ul"></ul><li><a href="#_label3">四、实际操作示例</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_2">4-1、一个完整的场景理解</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label4">五、三个日志的作用</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_3">5-1、三种日志的层次归属</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_4">5-2、详细对比三种日志</a></li><ul class="third_class_ul"><li><a href="#_label3_4_4_3">1. Binlog（二进制日志）</a></li><li><a href="#_label3_4_4_4">2. Undo Log（回滚日志）</a></li><li><a href="#_label3_4_4_5">3. Redo Log（重做日志）</a></li><li><a href="#_label3_4_4_6">只有 InnoDB 需要 Redo/Undo Log 吗？</a></li></ul></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>一、什么是binlog？</h2>
<blockquote><p>binlog（二进制日志）是MySQL用来记录<strong>所有<span>数据变更</span>操作</strong>的文件。</p></blockquote>
<p>就像一个&quot;监控录像&quot;，MySQL把<span>每一个INSERT、UPDATE、DELETE</span>操作都记录下来。</p>
<p class="maodian"><a name="_label1"></a></p><h2>二、为什么需要binlog？</h2>
<p>想象一个场景：你的电商系统突然宕机了，已经处理的订单数据丢失。这时候有了binlog就像有了&quot;黑匣子&quot;，你可以用它来恢复丢失的数据。</p>
<p class="maodian"><a name="_lab2_1_0"></a></p><h3>2-1、binlog的主要用途有三个：</h3>
<p><strong>1. 数据恢复</strong> - 系统故障时，可以用binlog恢复到某个时间点的数据状态</p>
<p><span><strong>2. 主从复制</strong> - 从库通过读主库的binlog来保持数据同步</span>。这是构建高可用系统的基础</p>
<p><strong>3. 审计追踪</strong> - 可以查看谁在什么时间修改了什么数据</p>
<p class="maodian"><a name="_lab2_1_1"></a></p><h3>2-2、binlog的三种格式</h3>
<p class="maodian"><a name="_label3_1_1_0"></a></p><h4>1、STATEMENT格式（记录SQL语句）</h4>
<div class="jb51code"><pre class="brush:sql;">比如你执行：UPDATE users SET age = age + 1 WHERE id &gt; 100
binlog就记录这条SQL语句原文</pre></div>
<p>优点：<span>记录量小，节省空间</span></p>
<p>缺点：某些函数（如NOW()）<span>可能导致从库数据不一致</span></p>
<p class="maodian"><a name="_label3_1_1_1"></a></p><h4>2、ROW格式（记录行数据变化）</h4>
<div class="jb51code"><pre class="brush:sql;">不记录SQL语句，而是记录"id=101的用户age字段从20变成21"
详细记录每一行数据变化前后的值</pre></div>
<p>优点：最安全，不会有数据不一致的问题</p>
<p>缺点：记录量大，占用空间多</p>
<p class="maodian"><a name="_label3_1_1_2"></a></p><h4>3、MIXED格式（混合模式）</h4>
<div class="jb51code"><pre class="brush:plain;">MySQL智能选择：普通操作用STATEMENT格式，可能有问题的操作用ROW格式</pre></div>
<p class="maodian"><a name="_label2"></a></p><h2>三、如何启用binlog？</h2>
<p>编辑MySQL配置文件 <code>my.cnf</code>：</p>
<div class="jb51code"><pre class="brush:plain;">
server-id = 1
log-bin = /var/log/mysql/mysql-bin
binlog-format = ROW
expire-logs-days = 7</pre></div>
<p>然后重启MySQL服务。</p>
<p class="maodian"><a name="_label3"></a></p><h2>四、实际操作示例</h2>
<p><strong>查看binlog是否启用：</strong></p>
<div class="jb51code"><pre class="brush:sql;">SHOW VARIABLES LIKE 'log_bin';
-- 结果：ON 表示已启用</pre></div>
<p><strong>查看当前binlog文件列表：</strong></p>
<div class="jb51code"><pre class="brush:sql;">SHOW BINARY LOGS;
-- 显示：mysql-bin.000001, mysql-bin.000002 等</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026011214312240.jpg" /></p>
<p><strong>查看binlog内容（以可读的方式）：</strong></p>
<div class="jb51code"><pre class="brush:plain;">mysqlbinlog /var/log/mysql/mysql-bin.000001</pre></div>
<p>输出会显示类似这样的内容：</p>
<div class="jb51code"><pre class="brush:sql;"># at 154
#250107 10:30:45 server id 1end_log_pos 235
Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1704597045/*!*/;
INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25)</pre></div>
<p class="maodian"><a name="_lab2_3_2"></a></p><h3>4-1、一个完整的场景理解</h3>
<p>假设你有个电商系统：</p>
<div class="jb51code"><pre class="brush:plain;">10:00 - 用户购买商品，订单状态改为"已支付"
10:05 - 系统宕机了
10:10 - 系统恢复，但这5分钟的数据丢了
此时你可以：
1. 查看binlog中10:00-10:05的所有操作
2. 用mysqlbinlog工具恢复这部分数据
3. 系统恢复正常</pre></div>
<p class="maodian"><a name="_label4"></a></p><h2>五、三个日志的作用</h2>
<ul><li><strong>undo log</strong>：用于事务回滚，记录数据修改前的<span>旧值</span></li><li><strong>redo log</strong>：用于崩溃恢复，记录数据修改后的<span>新值</span></li><li><strong>binlog</strong>：用于主从复制和数据恢复，记录所有数据变更</li></ul>
<p class="maodian"><a name="_lab2_4_3"></a></p><h3>5-1、三种日志的层次归属</h3>
<table><thead><tr><th>日志类型</th><th>所属层次</th><th>文件后缀</th><th>负责模块</th></tr></thead><tbody><tr><td><strong>Binlog</strong></td><td><strong>Server层</strong></td><td><code>.000001</code>,&nbsp;<code>.000002</code>...</td><td>MySQL Server</td></tr><tr><td><strong>Undo Log</strong></td><td><strong>存储引擎层</strong>&nbsp;(InnoDB)</td><td><code>ibdata1</code>,&nbsp;<code>undo001</code></td><td>InnoDB 引擎</td></tr><tr><td><strong>Redo Log</strong></td><td><strong>存储引擎层</strong>&nbsp;(InnoDB)</td><td><code>ib_logfile0</code>,&nbsp;<code>ib_logfile1</code></td><td>InnoDB 引擎</td></tr></tbody></table>
<p class="maodian"><a name="_lab2_4_4"></a></p><h3>5-2、详细对比三种日志</h3>
<p class="maodian"><a name="_label3_4_4_3"></a></p><h4>1. Binlog（二进制日志）</h4>
<p>归属：Server层（所有引擎共用）<br />作用：主从复制 + 数据恢复</p>
<p>特点：<br />&bull; 逻辑日志：记录SQL语句或行变化（ROW格式记录变化前后的值）<br />&bull; 追加写入：<span>一直往后写，文件满了切下一个</span><br />&bull; 所有引擎共享：不管用InnoDB还是MyISAM，都会记录</p>
<blockquote><p><strong>为什么 Binlog 在 Server 层？</strong></p>
<p>设计目标：支持<span>所有存储引擎</span>的主从复制</p></blockquote>
<p class="maodian"><a name="_label3_4_4_4"></a></p><h4>2. Undo Log（回滚日志）</h4>
<p>归属：存储引擎层（InnoDB特有）<br />作用：事务回滚 + MVCC多版本控制</p>
<p>特点：<br />&bull; 物理日志：记录数据修改前的旧版本<br /><span>&bull; 循环使用：空间会被复用</span><br />&bull; 实现MVCC：提供事务的隔离性（可重复读）</p>
<p class="maodian"><a name="_label3_4_4_5"></a></p><h4>3. Redo Log（重做日志）</h4>
<p>归属：存储引擎层（InnoDB特有）<br />作用：崩溃恢复 + 保证持久性<br />特点：<br />&bull; 物理日志：记录数据页的物理变化<br /><span>&bull; 循环写入：固定大小文件循环写</span><br />&bull; Write-Ahead Logging：先写日志，后写数据</p>
<blockquote><p><strong>为什么 Redo/Undo 在引擎层？</strong></p>
<p>设计目标：事务实现是引擎自己的事</p>
<p><span>不同引擎的事务实现不同</span>：<br />&bull; InnoDB：需要 Redo/Undo<br />&bull; MyISAM：根本没有事务，不需要这些日志</p></blockquote>
<p class="maodian"><a name="_label3_4_4_6"></a></p><h4>只有 InnoDB 需要 Redo/Undo Log 吗？</h4>
<p>是的！</p>
<ul><li>MyISAM：没有事务，崩溃可能丢数据</li><li>Memory：数据在内存，重启就丢</li></ul>

查看完整版本: MySQL中的binlog日志操作示例