不，并不是这样的。副本可能会下线或者断开连接数小时甚至数天，然后再次连接并追上更新。例如，你可以在拨号连接上建立源/副本关系，在这种情况下，连接只是零星短暂可用。这意味着，在任何给定时间，除非采取特殊措施，否则副本不保证与源保持同步。No, it does not. The replica can go down or stay disconnected for hours or even days, and then reconnect and catch up on updates. For example, you can set up a source/replica relationship over a dial-up link where the link is up only sporadically and for short periods of time. The implication of this is that, at any given time, the replica is not guaranteed to be in synchrony with the source unless you take some special measures.

为了确保已断开连接的副本能够追赶更新，你不能从源中删除包含尚未复制到副本的数据信息的二进制日志文件。只有当副本能够从上次读取事件的位置继续读取二进制日志时，异步复制才能正常工作。To ensure that catchup can occur for a replica that has been disconnected, you must not remove binary log files from the source that contain information that has not yet been replicated to the replicas. Asynchronous replication can work only if the replica is able to continue reading the binary log from the point where it last read events.

是的，源服务器和副本服务器都必须启用网络功能。如果未启用网络功能，副本将无法连接到源服务器并传输二进制日志。请检查配置文件中两个服务器的 skip_networking 系统变量是否未被启用。Yes, networking must be enabled on the source and replica. If networking is not enabled, the replica cannot connect to the source and transfer the binary log. Verify that the skip_networking system variable has not been enabled in the configuration file for either server.

检查 Seconds_Behind_Master 列，该列位于 SHOW REPLICA | SLAVE STATUS 的输出中。参见 第 19.1.7.1 节，“检查复制状态”。Check the Seconds_Behind_Master column in the output from SHOW REPLICA | SLAVE STATUS. See Section 19.1.7.1, “Checking Replication Status”.

当复制 SQL 线程执行从源读取的事件时，它会将自身的时间修改为该事件的时间戳。（这就是为什么 TIMESTAMP 可以很好地被复制。）在 SHOW PROCESSLIST 输出的 Time 列中，显示的复制 SQL 线程的秒数是最后一个已复制事件的时间戳与副本机器的实际时间之间的秒数。您可以使用它来确定最后一个已复制事件的日期。请注意，如果您的副本已经与源断开了一小时，然后重新连接，您可能会立即看到大数值的 Time，例如在 SHOW PROCESSLIST 中复制 SQL 线程显示的 3600 秒。这是因为副本正在执行一小时前的语句。请参见 第 19.2.3 节，“复制线程”。When the replication SQL thread executes an event read from the source, it modifies its own time to the event timestamp. (This is why TIMESTAMP is well replicated.) In the Time column in the output of SHOW PROCESSLIST, the number of seconds displayed for the replication SQL thread is the number of seconds between the timestamp of the last replicated event and the real time of the replica machine. You can use this to determine the date of the last replicated event. Note that if your replica has been disconnected from the source for one hour, and then reconnects, you may immediately see large Time values such as 3600 for the replication SQL thread in SHOW PROCESSLIST. This is because the replica is executing statements that are one hour old. See Section 19.2.3, “Replication Threads”.

使用以下步骤：Use the following procedure:

MySQL 复制目前不支持源服务器和副本之间的任何锁定协议来保证分布式（跨服务器）更新的原子性。换句话说，客户端 A 有可能对协同源 1 进行更新，同时在该更新传播到协同源 2 之前，客户端 B 可能会对协同源 2 进行更新，从而使客户端 A 的更新在协同源 2 上的效果与在协同源 1 上的效果不同。因此，当客户端 A 的更新传到协同源 2 时，即使协同源 2 的所有更新也已传播，它产生的表也可能与协同源 1 上的表不同。这意味着，除非你确定你的更新可以安全地以任何顺序发生，或者你在客户端代码中处理了顺序错乱的更新，否则你不应该将两台服务器连接成双向复制关系。MySQL replication currently does not support any locking protocol between source and replica to guarantee the atomicity of a distributed (cross-server) update. In other words, it is possible for client A to make an update to co-source 1, and in the meantime, before it propagates to co-source 2, client B could make an update to co-source 2 that makes the update of client A work differently than it did on co-source 1. Thus, when the update of client A makes it to co-source 2, it produces tables that are different from what you have on co-source 1, even after all the updates from co-source 2 have also propagated. This means that you should not chain two servers together in a two-way replication relationship unless you are sure that your updates can safely happen in any order, or unless you take care of mis-ordered updates somehow in the client code.

你还应该意识到，就更新而言，双向复制实际上并不会大幅提升性能（如果有的话）。每个服务器必须执行相同数量的更新，就像单台服务器执行一样。唯一的区别是，由于源自另一台服务器的更新在一个复制线程中顺序执行，因此锁争用会稍微减少。即便如此，这种好处可能也会被网络延迟抵消。You should also realize that two-way replication actually does not improve performance very much (if at all) as far as updates are concerned. Each server must do the same number of updates, just as you would have a single server do. The only difference is that there is a little less lock contention because the updates originating on another server are serialized in one replication thread. Even this benefit might be offset by network delays.

将一台服务器设置为源服务器，并将所有写操作定向到该服务器。然后根据预算和机架空间配置尽可能多的副本，并在源服务器和副本之间分配读取操作。你还可以使用 --skip-innodb 选项启动副本，启用 low_priority_updates 系统变量，并将 delay_key_write 系统变量设置为 ALL，以在副本端获得速度提升。在这种情况下，副本使用非事务性的 MyISAM 表而不是 InnoDB 表，通过消除事务开销来提升速度。Set up one server as the source and direct all writes to it. Then configure as many replicas as you have the budget and rackspace for, and distribute the reads among the source and the replicas. You can also start the replicas with the --skip-innodb option, enable the low_priority_updates system variable, and set the delay_key_write system variable to ALL to get speed improvements on the replica end. In this case, the replica uses nontransactional MyISAM tables instead of InnoDB tables to get more speed by eliminating transactional overhead.

请参阅作为扩展解决方案使用复制的指南，第 19.4.5 节，“使用复制进行扩展”。See the guide to using replication as a scale-out solution, Section 19.4.5, “Using Replication for Scale-Out”.

MySQL 复制对于处理频繁读取和不频繁写入的系统最为有利。理论上，通过使用单源/多副本配置，可以通过增加更多副本来扩展系统，直到网络带宽耗尽，或者更新负载增加到源无法处理的程度。MySQL replication is most beneficial for a system that processes frequent reads and infrequent writes. In theory, by using a single-source/multiple-replica setup, you can scale the system by adding more replicas until you either run out of network bandwidth, or your update load grows to the point that the source cannot handle it.

要确定在额外好处开始趋于平稳之前您可以使用多少副本，以及您可以提高网站性能多少，您必须了解您的查询模式，并通过基准测试经验性地确定典型源和典型副本上读写吞吐量之间的关系。这里的示例展示了一个相当简化的计算，用于说明通过复制在假设系统中可以获得的效果。令 reads 和 writes 分别表示每秒的读取和写入次数。To determine how many replicas you can use before the added benefits begin to level out, and how much you can improve performance of your site, you must know your query patterns, and determine empirically by benchmarking the relationship between the throughput for reads and writes on a typical source and a typical replica. The example here shows a rather simplified calculation of what you can get with replication for a hypothetical system. Let reads and writes denote the number of reads and writes per second, respectively.

假设系统负载由 10% 写入和 90% 读取组成，并且我们通过基准测试确定 reads = 1200 - 2 * writes。换句话说，系统在没有写入的情况下每秒可以执行 1,200 次读取，平均写入的速度是平均读取的两倍，并且这种关系是线性的。假设源服务器和每个副本的容量相同，并且我们有一个源服务器和 N 个副本。那么对于每台服务器（源服务器或副本）有：Let's say that system load consists of 10% writes and 90% reads, and we have determined by benchmarking that reads is 1200 - 2 * writes. In other words, the system can do 1,200 reads per second with no writes, the average write is twice as slow as the average read, and the relationship is linear. Suppose that the source and each replica have the same capacity, and that we have one source and N replicas. Then we have for each server (source or replica):

reads = 1200 - 2 * writes

reads = 9 * writes / (N + 1) （读取被分配，但写入会复写到所有副本）reads = 9 * writes / (N + 1) (reads are split, but writes replicated to all replicas)

9 * writes / (N + 1) + 2 * writes = 12009 * writes / (N + 1) + 2 * writes = 1200

writes = 1200 / (2 + 9/(N + 1))writes = 1200 / (2 + 9/(N + 1))

最后的方程表示，对于 N 个副本，在最大可能的每秒 1,200 次读取速率和每次写入对应九次读取的比例下，最大写入次数。The last equation indicates the maximum number of writes for N replicas, given a maximum possible read rate of 1,200 per second and a ratio of nine reads per write.

这个分析得出以下结论：This analysis yields the following conclusions:

这些计算假设网络带宽无限，并忽略了其他一些在您的系统上可能很重要的因素。在许多情况下，您可能无法执行类似上述计算那样准确地预测在增加 N 个副本时您的系统会发生什么。然而，回答以下问题应该有助于您决定复制是否以及在多大程度上可能提高系统性能：These computations assume infinite network bandwidth and neglect several other factors that could be significant on your system. In many cases, you may not be able to perform a computation similar to the one just shown that accurately predicts what happens on your system if you add N replicas. However, answering the following questions should help you decide whether and by how much replication may improve the performance of your system:

如何实现冗余完全取决于你的应用程序和具体情况。高可用性解决方案（带自动故障切换）需要主动监控，并且需要自定义脚本或第三方工具来提供从原始 MySQL 服务器到副本的故障切换支持。How you implement redundancy is entirely dependent on your application and circumstances. High-availability solutions (with automatic failover) require active monitoring and either custom scripts or third party tools to provide the failover support from the original MySQL server to the replica.

若手动处理此过程，你应该能够通过修改应用程序以连接到新服务器，或通过调整 MySQL 服务器的 DNS 从故障服务器切换到新服务器，从而从故障源切换到预配置的副本。To handle the process manually, you should be able to switch from a failed source to a pre-configured replica by altering your application to talk to the new server or by adjusting the DNS for the MySQL server from the failed server to the new server.

有关更多信息和一些示例解决方案，请参见 第 19.4.8 节，“故障切换期间切换源”。For more information and some example solutions, see Section 19.4.8, “Switching Sources During Failover”.

检查 binlog_format 系统变量的值：Check the value of the binlog_format system variable:

mysql> SHOW VARIABLES LIKE 'binlog_format';

显示的值始终是 STATEMENT、ROW 或 MIXED 之一。对于 MIXED 模式，默认使用基于语句的日志记录，但在某些情况下（如不安全的语句），复制会自动切换为基于行的日志记录。有关可能发生这种情况的详细信息，请参见 第 7.4.4.3 节，“混合二进制日志格式”。The value shown is always one of STATEMENT, ROW, or MIXED. For MIXED mode, statement-based logging is used by default but replication switches automatically to row-based logging under certain conditions, such as unsafe statements. For information about when this may occur, see Section 7.4.4.3, “Mixed Binary Logging Format”.

副本会自动知道使用哪种格式。Replicas automatically know which format to use.

启动服务器时使用 --replicate-wild-ignore-table=mysql.% 选项，以忽略对 mysql 表的复制。Start the server with the --replicate-wild-ignore-table=mysql.% option to ignore replication for tables in the mysql database.

可以。Yes.

可以。Yes.