同一个事务内修改两次，第二次改回原状会不会产生binlog，改前改后相同会不会产生binlog

最新推荐文章于 2025-01-07 16:50:25 发布

原创最新推荐文章于 2025-01-07 16:50:25 发布 · 3.6k 阅读

1 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

31 篇文章

订阅专栏

本文通过实验展示了在MySQL中执行SQL更新操作时，Binlog文件的记录情况及其与更新内容之间的关联。详细分析了在不同更新操作下，Binlog位点的变化和更新是否能正确地被Binlog捕获。

实验一

1 修改前表内容

mysql> select * from user;
+----------------------------+------+------------+-------+-------+
| gmt_create                 | name | gmt_modify | gmt_4 | gmt_3 |
+----------------------------+------+------------+-------+-------+
| 2015-12-12 12:12:12.000000 | aaa  | NULL       | NULL  | NULL  |
+----------------------------+------+------------+-------+-------+
1 row in set (0.00 sec)

show master status; 的结果，位点是 1655；

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000061 |     1655 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2 执行update操作，但是改前改后的值相同

conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
String sqlDiff = "update user.user set name='aaa';";
stmt.execute(sqlDiff);
conn.commit();

3 执行后执行show master status；结果没有变化-- 说明并没有产生binlog

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000061 |     1655 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

实验二

1 和实验一相同

2 执行脚本

conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
String sqlSame = "update user.user set name='ccc';";
String sqlDiff = "update user.user set name='aaa';";
stmt.execute(sqlSame);
stmt.execute(sqlDiff);

3 在mysql上执行show master status， binlog的位点发生了变化，从1655变成1968

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000061 |     1968 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4 执行mysqlbinlog工具，查看binlog文件内容

mysqlbinlog --host 127.0.0.1  -P3306 -R -uroot   -proot  mysql-bin.000061 -j 4 --base64-output=decode-rows --verbose

查看结果，产生的两次变更都记录到binlog里面。