如何使用 T-SQL 获取所有 MySQL binlog 事件信息
我正在研究记录对 MySQL 数据库所做的所有更改（包括可能发生的 DDL 语句）并使用该信息的可能性，以便它可以与远程数据库同步。
该应用程序本身是用 C# 编写的，因此迄今为止我所见过的最好的同步技术是 Microsoft Sync Framework。该框架本身提出了一种解决方案，通过添加触发器和附加表来存储已删除的行来跟踪对数据库所做的更改。
对于我的案例来说，这似乎不是一个好主意，因为它涉及更改 4 个以上产品使用的标准数据库的架构。这种方法还有效地将表的数量加倍（通过为每个表的已删除行添加一个新表），这也感觉不太好。
另一方面MySQL有binlog这个伟大的东西，它跟踪所有的变化，并且在大多数情况下还可以使用所谓的混合模式来跟踪语句（这样它们可以在远程DB上再次执行以复制数据）和原始数据当调用非确定性函数（如 NOW()）时，因此更新的数据在两个地方都是相同的。
似乎还有两种标准方法来检索此数据： 1) mysqlbinlog 实用程序 2）调用“SHOW BINLOG EVENTS”
选项 2 对我来说似乎更好，因为它不需要调用另一个外部应用程序，并在数据库计算机上运行应用程序，但它不包括记录的 ROW 格式语句的实际数据（仅像这样的东西： table_id: 47 flags: STMT_END_F 没有告诉我什么）。
所以最后我的问题是：
- 是否有更好的方法来跟踪对 MySQL 数据库所做的更改，而无需更改整个结构并添加大量触发器和表？我也可以更改产品来记录它的更改，但随后我们必须使用此数据库更改所有产品，以确保我们记录所有内容......我认为这几乎不可能说服所有人。
我可以获取有关使用 SHOW BINLOG EVENTS 所做更改的所有信息吗？包括 ROW 数据。
PS我也研究了MySQL Proxy，但是在所有情况下记录语句的问题是不包括非确定性函数中的实际数据。
I am researching the possibility to log all the changes made to a MySQL database including DDL statements that may occur and use that information so it can be synchronized with a remote database.
The application itself is written in C# so the best synchronization technology that I have seen so far to be available is Microsoft Sync Framework. This framework itself proposes a solution to track changes made to the DB by adding triggers and additional tables to store the deleted rows.
This does not seem to be a great idea for my case since it involves changing the schema of a standard DB used by more than 4 products. This method is also effectively doubling the number of tables (by adding a new table for the deleted rows of each table) which also does not feel to good.
On the other side MySQL has this great thing binlog, which tracks all the changes and can also use the so called mixed mode to track statements in most cases (so they can be executed again on the remote DB to replicate data) and the raw data when a non-deterministic function is called (like NOW()) so the data updated is the same on both places.
Also there seems to be 2 standard ways to retrieve this data:
1) The mysqlbinlog utility
2) Calling 'SHOW BINLOG EVENTS'
Option 2 seems the better to me since it does not require calling another external application, and running an application on the DB machine, BUT it does not include the actual data for the logged ROW format statements (only stuff like: table_id: 47 flags: STMT_END_F which tells me nothing).
So finally my questions are:
Is there a better way to track the changes made to a MySQL db without changing the whole structure and adding a ton of triggers and tables? I can change the product to log it's changes too but then we have to change all the products using this db to be sure we log everything ... and I think it's almost impossible to convince everyone.
Can I get all the information about the changes made using SHOW BINLOG EVENTS? Including the ROW data.
P.S. I researched MySQL Proxy too, but the problem in logging statements in all cases is that the actual data in non deterministic functions is not included.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
选项 3 是在您的应用程序中自行解析 bin 日志 - 这样您就可以完全控制检查的频率等，并且您可以看到所有带有实际使用值的语句。
Option 3 would be to parse the bin log yourself from within your app - that way you get total control of how often you check etc, and you can see all the statements with the actual values used.