MySQL复制速度 [英] MySQL replication speed

问题描述

假设我们在局域网上有3台服务器:

Lets say we have 3 servers on a local area network:

APP -php应用服务器

MASTER -主Mysql数据库服务器

MASTER - master Mysql db server

从属-从属Mysql数据库服务器(复制MASTER)

SLAVE - slave Mysql db server (replicating MASTER)

在一个代码块(php)中， APP 插入到 MASTER 中，检索last_insert_id，然后将其用于从 SLAVE .

In the one code block (php) APP inserts into MASTER, retrieving the last_insert_id which it then uses to select the data it just inserted from SLAVE.

问题是，复制是否及时完成，以便 SLAVE 从 MASTER 获得数据?这受 MASTER 或 SLAVE 上的负载影响吗?

So the question is, will the replication have happened in time for SLAVE to have the data from MASTER? Is this influenced by load put on either MASTER or SLAVE?

最后，如果可用数据存在问题，是否有办法确保从 SLAVE 接收到最新数据?

And lastly, if there is a issue with the data being available, is there a way to ensure the latest data is received from SLAVE?

推荐答案

由于MySQL复制时间是不可控制的变量(即使在正确的实现中，也会出现复制停滞或阻塞的问题)，因此最好不要依赖数据复制.您可以将问题分为两种情况:

Because MySQL Replication time is an uncontrollable variable (even in proper implementations, problems can arise that stall replication, or block it up), it's best to not rely on the data replicating. You can break down the problem into two situations:

1)我是用户在执行CUD操作，我希望R操作后缀可以反映我刚才所做的更改.在这种情况下，您绝对需要从主数据库中提取数据，因为用户希望他的更改有效.

1) I'm the user doing a CUD operation, I expect the R operation afterwords to mirror the change I just made. In this case you absolutely need to pull the data off the master, because the user expects that his change had an effect.

2)我是外部用户，我不知道发生了CUD命令，而且我通常不在乎，因此返回过时的数据是可以接受的(在99％的实现中，否则您可能不会使用PHP *).

2) I'm an outside user, I don't know that a CUD command happened and I generally don't care, so returning stale data is acceptable (in 99% of implementations, otherwise you probably wouldn't be using PHP*).

#1的替代方法是通知用户他的更改正在传播，并且在某些情况下是可以接受的，例如许多网站都会告诉您，x可能最多需要y分钟才能出现，这是这种情况.

An alternative to #1 would be to inform the user that his change is propagating, and is acceptable in some situations, e.g. many websites will tell you that x may take up to y minutes to appear, this is this situation in action.

• 如果需要即时更新，则可以尝试写入与(可能不可靠的)DB数据一起轮询的缓存层.诸如memcached之类的东西将是最佳选择.

这篇关于MySQL复制速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！