MySQL复制没有在binlog中运行更新

I have a number mysql servers running version 5.1.63 and whilst running some queries against the slave earlier this week, I noticed some data on the slave that should have been removed using an update statement on the master.

我有一些运行版本5.1.63的mysql服务器,虽然本周早些时候对奴隶运行了一些查询,但我注意到奴隶上的一些数据本应该使用主服务器上的更新语句删除。

My initial thoughts were:

我最初的想法是:

someone on the team was updating the slave, which I have since disproved

团队中有人正在更新奴隶,我已经反驳了

that the column being updated had changed

正在更新的列已更改

So, I investigated by running a mysql show status "table" query. This was run against a test database on each of the servers to see what the data length was, in a lot of cases it was showing me the data length differed between servers, but on an eyeball look at the data I could see the data was the same, so I couldn't use this method to see if there were any differences as it appears to be prone to error.

所以,我通过运行mysql show status“table”查询来调查。这是针对每个服务器上的测试数据库运行的,以查看数据长度是多少,在很多情况下,它显示了服务器之间的数据长度不同,但是在眼球看看数据我可以看到数据是相同,所以我无法使用此方法来查看是否存在任何差异,因为它似乎容易出错。

Next I ran a simple (across all dbs) row count for each table to confirm the row count was the same - it was.

接下来,我为每个表运行一个简单的(跨所有dbs)行计数,以确认行计数是相同的 - 它是。

I then started looking in the bin logs for replication. I could see the update statements that should have run clearly visible in the logs, but the update never ran.

然后我开始查看bin日志以进行复制。我可以看到应该在日志中清晰可见的更新语句,但更新从未运行过。

What I need to know is:

我需要知道的是:

is replication broken? I'm assuming it is

是复制坏了吗?我假设它是

if I create new slave servers, will I encounter the same issue?

如果我创建新的从服务器,我会遇到同样的问题吗?

how do I find out the extent of the issue on my servers?

如何在服务器上找出问题的严重程度?

Any help is appreciated.

任何帮助表示赞赏。

3 个解决方案

#1

If you are using statement based replication then it is easily possible to end up with different results on master and slave due to badly constructed INSERT statements.

如果使用基于语句的复制,则由于构造错误的INSERT语句,很容易在主服务器和从服务器上产生不同的结果。

INSERT SELECT without ORDER BY, or where the ORDER BY can leave non deterministic results will cause the slaves to diverge from master.

没有ORDER BY的INSERT SELECT,或者ORDER BY可以留下非确定性结果的地方将导致从设备与主设备分离。

From the MySQL site http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

来自MySQL站点http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

The order in which rows are returned by a SELECT statement with no ORDER BY clause is not determined. This means that, when using replication, there is no guarantee that such a SELECT returns rows in the same order on the master and the slave; this can lead to inconsistencies between them. To prevent this from occurring, you should always write INSERT ... SELECT statements that are to be replicated as INSERT ... SELECT ... ORDER BY column. The choice of column does not matter as long as the same order for returning the rows is enforced on both the master and the slave. See also Section 16.4.1.15, “Replication and LIMIT”.

未确定没有ORDER BY子句的SELECT语句返回行的顺序。这意味着,在使用复制时,无法保证这样的SELECT在主服务器和从服务器上以相同的顺序返回行;这可能导致它们之间的不一致。为了防止这种情况发生,您应该始终编写要复制为INSERT ... SELECT ... ORDER BY列的INSERT ... SELECT语句。只要在主服务器和从服务器上强制执行返回行的相同顺序,列的选择就无关紧要了。另请参见第16.4.1.15节“复制和限制”。

If this has happened then your replicas have diverged and the only safe way to bring them back in line is to rebuild them from a recent backup of the master DB. The worst part of this is the error may never cause the replication to fail, yet the results are inconsistent. Normally replication fails when an UPDATE or DELETE statement affects a different number of rows than on master, this is confusing as it was not the UPDATE that actually caused the error and the only way I know to fix the issue is to inspect every INSERT query in the code base!

如果发生这种情况,那么您的副本已经发散并且将它们重新排列的唯一安全方法是从最近的主数据库备份重建它们。最糟糕的部分是错误可能永远不会导致复制失败,但结果是不一致的。通常,当UPDATE或DELETE语句影响不同于主数据库的行数时复制失败,这是令人困惑的,因为它不是实际导致错误的UPDATE,而我知道解决问题的唯一方法是检查每个INSERT查询代码库!

#2

Status details are from information_schema which collates data from databases statistics for Mysql instance and it never remained the same at every execution. It can be considered as just a rough estimation of data sizes in bytes but never an exact value as for index and data length. It can be used for estimations but not for cross check. For replication you may check the slave io and sql against the master is running or not. And relay-info you might see the corresponding log details from master and that of slave.

状态详细信息来自information_schema,它整理来自Mysql实例的数据库统计信息的数据,并且在每次执行时都不会保持不变。它可以被视为对字节数据大小的粗略估计,但从不是索引和数据长度的精确值。它可用于估算,但不能用于交叉检查。对于复制,您可以检查主服务器上的slave io和sql是否正在运行。和relay-info你可能会看到master和slave的相应日志详细信息。

Of,course (1) way of doing is count(*) of tables EOD ensures the data in tables on master and slave are consistent or not. But to be accurate either (2) take random value fields and cross check with master and slave. Also if you aren't satisfied with it, (3) you may take them into outfile and take diff or checksum. I prefer (1) and (2). If (1) is not possible (2) still convinces me. ;)

当然,(1)做法是表EOD的计数(*)EOD确保主表和从表中的数据是否一致。但要准确(2)采取随机值字段并与主和从交叉检查。此外,如果你对它不满意,(3)你可以将它们带入outfile并采取差异或校验和。我更喜欢(1)和(2)。如果(1)不可能(2)仍然说服我。 ;)

#3

There is a tool to verify replication named pt-table-checksum

有一个工具可以验证名为pt-table-checksum的复制

#1