blocks|key|1068754|text|为此，您需要创建一个触发器，该触发器是在给定表上发生INSERT,+UPDATE,+DELETE时触发的事件。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1068755|请查看CREATE+TRIGGER的文档。|1068756|对于您想要做的事情，您可能需要有一个辅助表，它为主表上的每个INSERT/UPDATE插入一行，AFTER它恰好包含更改的行的ID、旧值、新值和一个时间戳。|1068757|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html^0|Q|M|0|3|E|0|0|U|D|1C|5|0^^$0|@$1|2|3|4|5|6|7|R|8|@$9|S|A|T|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|U|8|@]|D|@$9|V|A|W|1|X]]|E|$]]|$1|H|3|I|5|6|7|Y|8|@$9|Z|A|10|B|C]|$9|11|A|12|B|C]]|D|@]|E|$]]|$1|J|3|-4|5|6|7|13|8|@]|D|@]|E|$]]]|K|$L|$5|M|N|O|E|$P|Q]]]]

To do that you need to create a trigger, which is an event that is fired when an <code>INSERT, UPDATE, DELETE</code> occurs on a given table.

Look at the documentation for <a href="http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html" rel="noreferrer">CREATE TRIGGER</a>.

For what you want to do, you probably need to have an auxiliary table that has a row inserted for each <code>INSERT/UPDATE</code> on the main table, <code>AFTER</code> it happens containing the ID of the row changed, the old value the new value and a timestamp.

blocks|key|854349|text|这是我的建议。这可能是一项微不足道的工作，但如果您这样做了，我真的认为您将有一种在MySQL中进行更改数据捕获的可靠方法。请记住，当考虑到锁定和隔离级别等问题时，在数据库中进行更改跟踪实际上是一件很难自己做的事情。我强烈推荐使用这样的过程。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|854350|看看iAnywhere的一款名为MobiLink的产品。您可以从here下载它的免费开发人员版本。MobiLink的用途是允许您将数据更改从MySQL等数据库同步到SQL+Anywhere数据库。当然，您唯一感兴趣的部分是从MySQL获取更改的能力。幸运的是，MobiLink将在您的MySQL数据库中非常有效地创建这些变更数据捕获触发器。事实上，它甚至会为您提供用于执行此操作的sql脚本，以防您再次需要执行此操作。之后，您可以卸载SQL+Anywhere。|offset|length|854351|首先，在安装之后，需要打开一个名为Sybase+Central的产品。打开后，将会有一个向导引导您完成针对MySQL数据库的MobiLink配置。我认为在帮助中有一个演练，如果你卡住了，可以安装它，但我现在无法访问它来验证。|854352|在您运行完向导之后，它将为您的MySQL数据库配置变更数据捕获。如果您查看您的数据库，您还将拥有所需的所有触发器和更改跟踪列。|854353|希望这能帮上忙。|854354|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sybase.com/detail?id=1016644^0|0|W|4|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@$D|V|E|W|1|X]]|A|$]]|$1|F|3|G|5|6|7|Y|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|Z|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|10|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S]]]]

Here is what I would suggest. It may be a tiny bit of work, but if you do it I really think you will have a solid way of doing change data capture in MySQL. Keep in mind that doing change tracking in a database is actually a REALLY hard thing to do yourself when you take into account issues like locking and isolation levels. I highly recommend using a process like this.

Take a look at a product called MobiLink from iAnywhere. You can download a free developer version of it from <a href="http://www.sybase.com/detail?id=1016644" rel="nofollow">here</a>. The purpose of MobiLink is to allow you to sync data changes from databases like MySQL to a SQL Anywhere database. Of course, the only part you are interested in is the ability to get changes from MySQL. Luckily what MobiLink will do is very effectively create these change data capture triggers in your MySQL database. In fact, it will even give you the sql scripts it used to do this in case you ever need to do it again. After that you can uninstall SQL Anywhere.

To get started, after you install it there will be a product called Sybase Central which you will need to open. When this opens there will be a wizard that will walk you through the configuration of MobiLink against your MySQL database. I think there is a walkthrough in the help that is installed if you get stuck but I don't have access to it right now to verify. 

After you have run through the wizard it will have configured your MySQL database for change data capture. If you look at your database you will also have all the triggers and change tracking columns you would ever need.

Hope that helps....

blocks|key|854400|text|触发器并不总是在数据库中记录更改事件的最佳方式。一旦表的模式发生更改，As触发器就会失效，这反过来会导致实际的表操作失败。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|854401|对于mysql，您需要从二进制日志文件中捕获更改事件。Mysql提供了读取二进制日志的API+(5.6版后)。|854402|您可能还需要查看Flexviews，它提供了类似于Oracle的实体化视图日志来记录更改事件。|offset|length|854403|entityMap|0|LINK|mutability|MUTABLE|url|https://code.google.com/p/flexviews/^0|0|0|8|9|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@$F|S|G|T|1|U]]|A|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]]]

Triggers are not always the best way to record change events in database. As triggers can get invalidated once the schema of table changes, and it would in turn cause the actual table operation to fail.

For mysql, you need to capture the change events from binary log file. Mysql provides API to read binary log ( post version 5.6). 

You may also want to look into <a href="https://code.google.com/p/flexviews/" rel="nofollow">Flexviews</a> , which provides Oracle like Materialized View logs to record the change events.

blocks|key|854460|text|我推荐你看看Debezium，这是一个开源的CDC平台，在其他平台中，还有supports+MySQL。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|854461|您可以使用Debezium将更改流式传输到Apache+Kafka中，但通过其embedded+mode，您还可以将Debezium连接器用作Java应用程序中的库，并轻松地将数据更改传播到其他流式API，如Kinesis等。|854462|免责声明:我是Debezium项目的负责人。|854463|entityMap|0|LINK|mutability|MUTABLE|url|http://debezium.io/|1|http://debezium.io/docs/connectors/mysql/|2|http://debezium.io/docs/embedded/^0|6|8|0|11|E|1|0|13|D|2|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]|$A|X|B|Y|1|Z]]|C|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@$A|11|B|12|1|13]]|C|$]]|$1|F|3|G|5|6|7|14|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|15|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]|P|$5|K|L|M|C|$N|Q]]|R|$5|K|L|M|C|$N|S]]]]

I recommend you take a look at <a href="http://debezium.io/" rel="noreferrer">Debezium</a> an open-source CDC platform which, amongst others, also <a href="http://debezium.io/docs/connectors/mysql/" rel="noreferrer">supports MySQL</a>.

You can use Debezium to stream changes into Apache Kafka, but by means of its <a href="http://debezium.io/docs/embedded/" rel="noreferrer">embedded mode</a> you can also use the Debezium connectors as a library in your Java applications and easily propagate data changes to other streaming APIs such as Kinesis etc.

Disclaimer: I'm the Debezium project lead.

blocks|key|854516|text|很抱歉回答得太晚了，我想提一下在另一个答案maxwell中还没有调用的另一个工具，它是一个CDC，它读取binlog并在Kafka上发送消息。此解决方案的最大优势在于，它不需要对MySQL服务器进行任何更改(无需创建触发器..)。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|854517|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/zendesk/maxwell^0|L|7|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

Sorry for the very late answer, I would like to mention an other tool that hasn't been invoked in the other answer <a href="https://github.com/zendesk/maxwell" rel="nofollow noreferrer">maxwell</a> it's a CDC that reads the binlogs and sends messages on Kafka. The big advantage to this solution is the fact that it doesn't require any change to MySQL servers (no triggers to create ..).

In my database I want to track the value of a column whenever it is changed .In a way such that every time value of a field changes we can know what was the previous value .It would be awesome to assign timestamps to each change.I am using MySQL as the database server.

Change Data Capture in MySQL

日志文件

SQL数据库

数据库

服务器

MySQL

Kafka

Java

在我的数据库中，我想要跟踪列的值，无论它何时被更改，这样每次字段的值更改时，我们都可以知道前一个值是什么，如果能为每次更改分配时间戳，.It将是非常棒的。我使用MySQL作为数据库服务器。

问MySQL中的变更数据捕获
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL中的变更数据捕获EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL中的变更数据捕获
EN