MySQL 的扩展解决方案（复制、集群）

Scaling solutions for MySQL (Replication, Clustering)

提问by Eran Galperin

At the startupI'm working at we are now considering scaling solutions for our database. Things get somewhat confusing (for me at least) with MySQL, which has the MySQL cluster, replicationand MySQL cluster replication(from ver. 5.1.6), which is an asynchronous version of the MySQL cluster. The MySQL manual explains some of the differences in its cluster FAQ, but it is hard to ascertain from it when to use one or the other.

在我工作的初创公司，我们现在正在考虑为我们的数据库扩展解决方案。MySQL 有MySQL 集群、复制和MySQL 集群复制（从 5.1.6 版开始），这是 MySQL 集群的异步版本，事情变得有些混乱（至少对我而言）。MySQL 手册在其集群 FAQ 中解释了一些差异，但很难从中确定何时使用其中之一。

I would appreciate any advice from people who are familiar with the differences between those solutions and what are the pros and cons, and when do you recommend to use each.

我很感激那些熟悉这些解决方案之间的差异和优缺点的人的任何建议，以及您建议何时使用每种解决方案。

回答by Eran Galperin

I've been doing A LOT of reading on the available options. I also got my hands on High Performance MySQL 2nd edition, which I highly recommend.

我一直在阅读有关可用选项的大量内容。我还接触了 High Performance MySQL 2nd edition，我强烈推荐它。

This is what I've managed to piece together:

这是我设法拼凑起来的：

Clustering

聚类

Clustering in the general sense is distributing load across many servers that appear to an outside application as one server.

一般意义上的集群是在许多服务器之间分配负载，这些服务器对于外部应用程序来说是一台服务器。

MySQL NDB Cluster

MySQL NDB 集群

MySQL NDB Cluster is a distributed, in-memory, shared-nothing storage engine with synchronous replication and automatic data partioning (excuse me I borrow literally from the High Performance book, but they put it very nicely there). It can be a high performance solution for some applications, but web application generally do not work well on it.

MySQL NDB Cluster 是一个分布式的、内存中的、无共享的存储引擎，具有同步复制和自动数据分区（对不起，我从高性能书中借用了字面意思，但他们在那里写得很好）。对于某些应用程序来说，它可以是一种高性能的解决方案，但 Web 应用程序通常不能很好地在它上面工作。

The major problem is that beyond very simple queries (that touch only one table), the cluster will generally have to search for data on several nodes, allowing network latency to creep in and significantly slow down completion time for queries. Since the application treats the cluster as one computer, it can't tell it which node to fetch the data from.

主要问题是，除了非常简单的查询（仅涉及一张表）之外，集群通常还必须在多个节点上搜索数据，从而导致网络延迟蔓延并显着减慢查询的完成时间。由于应用程序将集群视为一台计算机，因此无法告诉它从哪个节点获取数据。

In addition, the in-memory requirement is not workable for many large databases.

此外，内存要求对于许多大型数据库来说是不可行的。

Continuent Sequoia

连续红杉

This is another clustering solution for MySQL, that acts as a middleware on top of the MySQL server. It offers synchronous replication, load balancing and failover. It also ensures that requests always get the data from the latest copy, automatically choosing a node that has the fresh data.

这是 MySQL 的另一个集群解决方案，它充当 MySQL 服务器之上的中间件。它提供同步复制、负载平衡和故障转移。它还确保请求始终从最新副本中获取数据，自动选择具有最新数据的节点。

I've read some good thingson it, and overall it sounds pretty promising.

我读过一些关于它的好东西，总的来说，它听起来很有希望。

Federation

联邦

Federation is similar to clustering, so I tugged it here as well. MySQL offers federation via the federated storage engine. Similar to the NDB cluster solution, it works well with simple queries only - but even worse the the cluster for complicated ones (since network latency is much higher).

联邦类似于聚类，所以我也把它拉到了这里。MySQL 通过联合存储引擎提供联合。与 NDB 集群解决方案类似，它仅适用于简单查询 - 但对于复杂查询的集群更糟糕（因为网络延迟要高得多）。

Replication and load balancing

复制和负载平衡

MySQL has the built in capacity to create replications of a database on different servers. This can be used for many things - splitting the load between servers, hot backups, creating test servers and failover.

MySQL 具有在不同服务器上创建数据库复制的内置功能。这可以用于许多事情 - 在服务器之间分配负载、热备份、创建测试服务器和故障转移。

The basic setup of replication involves one master server handling mostly writes and one or more slaves handling reads only. A more advanced variation is that of the master-masterconfiguration, which allows to scale writes as well by having several servers writing at the same time.

复制的基本设置涉及一台主服务器处理主要写入和一个或多个从服务器只处理读取。更高级的变体是master-master配置，它允许通过同时写入多个服务器来扩展写入。

Each configuration has its pros and cons, but one problem they all share is replication lag - since MySQL replication is asynchronous, not all nodes have the freshest data at all time. This requires the application to be aware of the replication and incorporate replication-aware queries to work as expected. For some applications this might not be a problem, but if you always need the freshest data things get somewhat complicated.

每种配置都有其优点和缺点，但它们共有的一个问题是复制延迟 - 由于 MySQL 复制是异步的，并非所有节点始终拥有最新数据。这要求应用程序知道复制并合并复制感知查询以按预期工作。对于某些应用程序，这可能不是问题，但如果您总是需要最新的数据，事情就会变得有些复杂。

Replication requires some load balancing to split the load between the nodes. This can be as simple as some modifications to the application code, or using dedicated software and hardware solutions.

复制需要一些负载平衡来在节点之间分配负载。这可以像对应用程序代码进行一些修改一样简单，或者使用专用的软件和硬件解决方案。

Sharding and partioning

分片和分区

Sharding is commonly used approach to scale database solutions. You split the data into smaller shards and spread them around different server nodes. This requires the application to be aware of the modification to the data storage to work efficiently, as it needs to know where to find the information it needs.

分片是扩展数据库解决方案的常用方法。您将数据拆分为更小的碎片，并将它们分布在不同的服务器节点上。这需要应用程序知道对数据存储的修改才能有效地工作，因为它需要知道在哪里可以找到它需要的信息。

There are abstraction frameworks available to help deal with data sharding, such as Hibernate Shards, an extension to the Hibernate ORM (which unfortunately is in Java. I'm using PHP). HiveDBis another such solution which also supports shard rebalancing.

有一些抽象框架可用于帮助处理数据分片，例如Hibernate Shards，它是 Hibernate ORM 的扩展（不幸的是它是在 Java 中。我使用的是 PHP）。HiveDB是另一个这样的解决方案，它也支持分片重新平衡。

Others

其他

Sphinx

狮身人面像

Sphinxis a full-text search engine, that can be used for far more than test searches. For many queries it is much faster than MySQL (especially for grouping and sorting), and can query remote systems in parallel and aggregate the results - which make it very useful in use with sharding.

Sphinx是一个全文搜索引擎，其用途远不止测试搜索。对于许多查询，它比 MySQL 快得多（尤其是在分组和排序方面），并且可以并行查询远程系统并聚合结果 - 这使得它在与分片一起使用时非常有用。

In general sphinx should be used with other scaling solutions to get more of the available hardware and infrastructure. The downside is that again you need the application code to be aware of sphinx to use it wisely.

一般来说，sphinx 应该与其他扩展解决方案一起使用，以获得更多可用的硬件和基础设施。缺点是您再次需要应用程序代码来了解 sphinx 才能明智地使用它。

Summary

概括

Scaling solutions differ depending on the needs of the application that needs it. For us and for most web-applications, I believe that replication (probably multi-master) is the way to go with a load balancer distributing the load. Sharding of specific problem areas (huge tables) is also a must for being able to scale horizontally.

扩展解决方案因需要它的应用程序的需求而异。对于我们和大多数 Web 应用程序，我相信复制（可能是多主）是负载均衡器分配负载的方法。特定问题区域（大表）的分片也是能够水平扩展的必要条件。

I'm also going to give a shot to Continuent Sequoia and see if it can really do what it promises to since it will involve the least amount of changes to application code.

我还将试一试Continent Sequoia，看看它是否真的能做到它所承诺的，因为它将对应用程序代码进行最少的更改。

回答by nathan

Disclaimer: I have not used MySQL Cluster, so I'm only going from what I've heard.

免责声明：我没有使用过 MySQL Cluster，所以我只是从我所听到的。

MySQL Cluster is an HA (high availability) solution. It's fast, because it's all in memory, but it's real selling point is the availability. There is no single point of failure. With replication, on the other hand, if the master goes down, you have to actually switch to the replica, and there may be a small amount of down time. (although the DRBD solution is another alternative that has high availability)

MySQL Cluster 是一种 HA（高可用性）解决方案。它很快，因为它都在内存中，但它的真正卖点是可用性。没有单点故障。另一方面，使用复制，如果主服务器宕机，你必须真正切换到复制服务器，而且可能会有少量宕机时间。（虽然 DRBD 解决方案是另一种具有高可用性的替代方案）

Cluster requires that your entire database fit in memory. That means that each machine in the cluster needs to have enough memory to store the entire database. So this is not a feasible solution for very large databases (or at least it's a very expensive solution).

集群要求您的整个数据库适合内存。这意味着集群中的每台机器都需要有足够的内存来存储整个数据库。所以这对于非常大的数据库来说不是一个可行的解决方案（或者至少它是一个非常昂贵的解决方案）。

I think that unless HA is super important (read: probably not), it's more hassle (and money) than it's worth. Replication is more often the better way to go.

我认为除非 HA 非常重要（阅读：可能不是），否则它比它的价值更麻烦（和金钱）。复制通常是更好的方法。

Edit:I forgot to mention also that Cluster does not allow foreign keys, and range scans are slower than on other engines. Here is a link that talks about Known Limitations of MySQL Cluster

编辑：我也忘了提到 Cluster 不允许外键，并且范围扫描比其他引擎慢。这是一个讨论MySQL Cluster 的已知限制的链接

回答by acrosman

There are some good discussions about how the folks that maintain drupal.org have structured their database servers:

关于维护 drupal.org 的人如何构建他们的数据库服务器，有一些很好的讨论：

• Dries Buytaert's blog
• WorkHabits blog

• Dries Buytaert 的博客
• 工作习惯博客

Both are from 2007, so the Clustering support may be stronger now, but at the time they chose replication.

两者都是 2007 年的，所以现在 Clustering 支持可能更强大，但当时他们选择了复制。

回答by Zak

The cool thing about doing replication is that it's easy. Just set up 2 mysql boxes, change the serverID on the second box, and then point the second box at the first using the change master to command.

复制很酷的地方在于它很容易。只需设置2个mysql框，更改第二个框上的serverID，然后使用change master to命令将第二个框指向第一个。

Here is the relevant sample slave my.cnf config

这是相关的示例从站 my.cnf 配置

#
#       Log names
#

log-bin=binlog
relay-log=relaylog
log-error=errors.log

#
#       Log tuning
#

sync_binlog = 1
binlog_cache_size = 1M

#
#       Replication rules (what are we interested in listening for...)
#
#       In our replicants, we are interested in ANYTHING that isn't a permission table thing
#

replicate-ignore-db =      mysql
replicate-wild-ignore-table=mysql.%

#
#       Replication server ID
#

server-id      =        2

So make sure each slave gets a serverID incremented by 1 (so next slave is server 3)

因此，请确保每个从站的 serverID 递增 1（因此下一个从站是服务器 3）

set up a username and password that the slave can connect on, Then run change master to MASTER_HOST = 'x.x.x.x'; change master to MASTER_PASSWORD = "xxxxx";

设置一个slave可以连接的用户名和密码，然后运行change master to MASTER_HOST = 'xxxx'; 将主更改为 MASTER_PASSWORD = "xxxxx";

and so on.

等等。

finally, run "start slave;"

最后，运行“启动奴隶；”

Up comes your slave and starts replicating. sweet huh!

你的奴隶出现并开始复制。甜啊！

This assumes you start with 2 empty servers. Then you can dump your db into the master server, and as it loads there, it will also load on the slave.

这假设您从 2 个空服务器开始。然后你可以将你的数据库转储到主服务器，当它在那里加载时，它也会加载到从服务器上。

You can check the slave status by running:

您可以通过运行来检查从站状态：

show slave status \G

显示从属状态 \G

Have fun with it.. soooo easy...

玩得开心……太容易了……

回答by Adi

While doing High Availability study i came across many solutions and probably in our case which was more write intensive system, i found DRBD cluster better than the NDB cluster as it provides more number of transactions per second.

在进行高可用性研究时，我遇到了许多解决方案，在我们的案例中可能是写入密集型系统，我发现 DRBD 集群比 NDB 集群更好，因为它每秒提供更多的事务。

Mysql Replication can provide you a backup machine which can either be used as read slave or can be used in case of disaster recovery.

Mysql Replication 可以为您提供一台备份机，既可以用作读从机，也可以用于灾难恢复。

With different modes on transaction management provided by DRBD you can some what reduce the performance hit by device level replication of data over the network. For reliable system which should not lose any transaction in case of failure use C mode, else go for B.

通过 DRBD 提供的不同事务管理模式，您可以在一定程度上降低网络上设备级数据复制对性能的影响。对于在发生故障时不应丢失任何事务的可靠系统，请使用 C 模式，否则使用 B。

I tried to list some of the learnings i did during setting up the DRBD cluster at http://www.techiegyan.com/?p=132

我试图在http://www.techiegyan.com/?p=132上列出我在设置 DRBD 集群期间所做的一些学习

It works really well on dedicated connection for replication i.e. reserve separate high speed interfaces on both the machines just for drbd replication. Heartbeat can control the cluster nicely with all the services one by one i.e. IP addresses, partitions, drbd and mysql.

它在用于复制的专用连接上非常有效，即在两台机器上保留单独的高速接口仅用于 drbd 复制。Heartbeat 可以很好的控制集群的所有服务，即IP 地址、分区、drbd 和mysql。

I am yet to discover the Master-Master configuration on DRBD. Will update as and when I get success in that.

我还没有发现 DRBD 上的 Master-Master 配置。当我在这方面取得成功时会更新。

Thanks.

谢谢。

回答by Muzaaya Joshua

in my view, the confusion here just sends me back to Mnesia. With fragmentation, declarative and pragmatic way of handling indexes, Location transparency of Database Replicas e.t.c

在我看来，这里的混乱只会让我回到 Mnesia。具有碎片化、声明式和实用的索引处理方式、数据库副本的位置透明性等

In our setup, We run both MySQL Cluster and Mnesia. Our data is kinda seasonal. So what happens is after sometime, we relieve mnesia of data that's no longer used and throw it into MYSQL cluster. This keeps our mnesia efficient. Also we have applications implemented in the main stream languages (Python, Clojure e.t.c) that use data direct from MySQL.

在我们的设置中，我们同时运行 MySQL Cluster 和 Mnesia。我们的数据有点季节性。所以发生的事情是在一段时间之后，我们解除不再使用的数据的 mnesia 并将其扔到 MYSQL 集群中。这使我们的 mnesia 保持高效。此外，我们还有使用直接来自 MySQL 的数据的主流语言（Python、Clojure 等）实现的应用程序。

In a nutshell, we run mnesia on top of MySQL Cluster. The MySQL Cluster can handle large data sets, a database can grow to 50GB plus. We have mnesia powering the Erlang/OTPapplications. Javaand PHPaccess data from mnesia over tailored REST(recently Thrift) APIs using JSON and XML as exchange formats.

简而言之，我们在 MySQL Cluster 之上运行 mnesia。MySQL Cluster 可以处理大型数据集，一个数据库可以增长到 50GB 以上。我们有 mnesia 为Erlang/OTP应用程序提供支持。Java和PHP通过使用 JSON 和 XML 作为交换格式的定制REST（最近是Thrift）API从 mnesia 访问数据。

The data access layer has abstracted access to data in Mnesia and old shipped data in MySQL Cluster if its needed. Mnesia is here essentially to power the Erlang/OTP applications.Once it gets hogged up with data, we throw it into MYSQL Cluster. The data access layer can access both data in mnesia and MySQL in an abstracted API on behalf of all applications.

如果需要，数据访问层已经抽象了对 Mnesia 中数据和 MySQL Cluster 中旧数据的访问。Mnesia 在这里本质上是为 Erlang/OTP 应用程序提供动力。一旦它被数据占用，我们就将它扔到 MYSQL 集群中。数据访问层可以代表所有应用程序以抽象的 API 访问 mnesia 和 MySQL 中的数据。

What i can say here is that Mnesia has been the best option for us. The tables are highly fragmented and indexed, queries perform very well and the database is replicated across 2 locations, connected over a tunnel.

我在这里可以说的是，Mnesia 一直是我们的最佳选择。表高度碎片化和索引，查询性能非常好，数据库在 2 个位置复制，通过隧道连接。

Earlier on, we feared that mnesia may not handle as many records as possible due to table size limitation. But we found this statement wrong. With good tuning (fragmentation), our mnesia databases do hold an average of about 250 million records per year.

早些时候，我们担心由于表大小限制，mnesia 可能无法处理尽可能多的记录。但是我们发现这个说法是错误的。通过良好的调整（碎片化），我们的 mnesia 数据库确实每年平均保存大约 2.5 亿条记录。

We have benefited from Erlang's complex data structure and the fact that Mnesia can swallow it up unchanged. The Erlang /OTP applications are most efficient of all other apps in legacy languages and with our system we are planning on migrating it all to Erlang/OTP technology. From Erlang we seemlessly access data from MySQL Cluster and execute queries onto its servers very wonderfully, In fact, we have deduced that its Erlang/OTP that can fully use the MySQL server resources because of its (Erlang) massive concurrency.

我们受益于 Erlang 复杂的数据结构以及 Mnesia 可以原封不动地吞下它的事实。Erlang/OTP 应用程序是所有其他使用传统语言的应用程序中最高效的，我们计划在我们的系统中将其全部迁移到 Erlang/OTP 技术。从Erlang我们无缝地访问MySQL Cluster的数据并在其服务器上执行查询非常精彩，事实上，我们已经推断出它的Erlang/OTP可以充分利用MySQL服务器资源，因为它的（Erlang）海量并发。

Mnesia has worked for us very well.Mnesia has completely changed the way we look at databases because of its thrilling performance. Our Solaris server CPU Cores are kept busy at an average of about 48% usage at peak hours.

Mnesia 为我们工作得非常好。Mnesia 因其令人兴奋的性能而彻底改变了我们看待数据库的方式。我们的 Solaris 服务器 CPU 内核在高峰时段保持繁忙，平均使用率约为 48%。

I advise you to check out mnesia and who knows, it may answer a number of your distribution or replication needs.

我建议您查看 mnesia，谁知道呢，它可能会满足您的许多分发或复制需求。

回答by MarkR

MySQL cluster is a strange beastie and every time we've evaluated it's either performed very badly or been unreliable.

MySQL 集群是一个奇怪的野兽，每次我们评估它时，要么表现非常糟糕，要么不可靠。

It's horribly complicated to set up (you need at least three nodes, possibly more). Also there is no provision for having clients fail over, so you have to do that yourself (Or use something else to act as a proxy etc).

设置起来非常复杂（您至少需要三个节点，可能更多）。也没有让客户端故障转移的规定，所以你必须自己做（或者使用其他东西作为代理等）。

It's extremely clever, because it does automatic hash partitioning on the primary key which allows you to scale writes, and also because it has no single point of failure.

它非常聪明，因为它对主键进行了自动散列分区，这允许您扩展写入，并且还因为它没有单点故障。

But I really think it's better suited to the very special purpose cases it was designed for. It cannot in most cases replace another database engine (e.g. InnoDB) in either performance or features.

但我真的认为它更适合它设计的非常特殊的用途。在大多数情况下，它不能在性能或功能方面替换另一个数据库引擎（例如 InnoDB）。

回答by Brent

The "in memory" limitation prevents us from using MySQL cluster for our nearly 50Gb of data, so we are using DRBD plus linux Heartbeat.

“内存中”限制阻止我们使用 MySQL 集群处理我们将近 50Gb 的数据，因此我们使用DRBD 和 linux Heartbeat。

It's kind of like a raid array between two (or more) boxes that keeps the databases / logs / configs in sync (but only one server can be "live" at a time). Failover is automatic, uses the same IP address, and is quick as a mysql restart, so that's been a good solution for us.

它有点像两个（或更多）盒子之间的一个raid阵列，可以使数据库/日志/配置保持同步（但一次只能“活动”一个服务器）。故障转移是自动的，使用相同的 IP 地址，并且像 mysql 重启一样快，所以这对我们来说是一个很好的解决方案。

回答by Javier

I haven't used them, but from the docs i'd say that replication is the preferred solution if the biggest load is reading from the database.

我没有使用过它们，但是从文档中我会说如果最大的负载是从数据库中读取，那么复制是首选的解决方案。