如何跨多个服务器分区Mysql? [英] How to partition Mysql across MULTIPLE SERVERS?

问题描述

我知道水平分区...您可以创建许多表.

如何对多台服务器执行此操作? 这将允许Mysql扩展.

在X服务器上创建X表?

有人在乎解释吗?或者有一个很好的初学者教程(分步介绍)，教您如何在多个服务器之间进行分区?

解决方案

使用MySQL，人们通常会执行基于基于应用程序的分片 .

简而言之，您将在多个数据库服务器上拥有相同的数据库结构.但是它不会包含相同的数据.

例如:

Users 1 - 10000: server A
Users 10001 - 20000: server B

共享(当然)不是一种备份技术，而是用于在群集中分布读写.

用于分片的技术例如是MySQL代理.这不是HScale发明的，它或多或少是一个简单的LUA脚本，该脚本将读写分配到不同的后端服务器. MySQL forge上应该有很多示例.

另一个工具(基于MySQL Proxy)是 SpockProxy .完全针对分片而量身定制.他们还摆脱了Lua，并且致力于各种事情，以使其比代理更快.到目前为止，我只测试了SpockProxy，但从未在生产环境中运行过它.

现在除了这些代理服务器之外，您还可以分片自己.需要一个主表，例如:

-------------------
| userA | server1 |
| userB | server2 |
| userC | server1 |
-------------------

然后构造对服务器的读取和写入.不是很漂亮，但是行得通.下一个障碍是使其变得更加松懈.因此，例如server1，server2和server3都应该是一个小型群集.

最后但同样重要的是，另一种有趣的跨服务器分区数据和索引的方法是Digg的 IDDB .我不确定他们是否曾发布过其代码，但是他们的博客文章提供了有关其功能的详细信息.

让我知道这是否有帮助！

I know that horizontal partitioning...you can create many tables.

How can you do this with multiple servers? This will allow Mysql to scale.

Create X tables on X servers?

Does anyone care to explain, or have a good beginner's tutorial (step-by-step) that teaches you how to partition across multiple servers?

解决方案

With MySQL, people generally do what is called application based sharding.

In a nutshell, you will have the same database structure on multiple database servers. But it won't contain the same data.

So for example:

Users 1 - 10000: server A
Users 10001 - 20000: server B

Sharding (of course) is not a backup technique, it's meant to distribute reads and writes across a cluster.

Techniques employed to shard are the MySQL-Proxy, for example. This is nothing that HScale invented, it's more or less a simple LUA script which distributes reads and writes to different backend servers. There should be plenty of examples on the MySQL forge.

Another tool (based on MySQL Proxy) is SpockProxy. Completely tailored towards sharding. They also got rid off Lua, and they worked on various things to make it speedier than the proxy. So far, I have only tested SpockProxy, but never ran it in production.

Now aside from those proxies, you can shard yourself as well. Required would be a master table, e.g.:

-------------------
| userA | server1 |
| userB | server2 |
| userC | server1 |
-------------------

Then construct your reads and writes towards the server. Not very pretty but that works. The next obstactle would be to make it more falt tolarant. So for example, server1, server2 and server3 each should be a small cluster.

And last but not least, another interesting approach to partition data and indices across servers is Digg's IDDB. I'm not sure if they ever released its code, but their blog posts gives great details on what it does.

Let me know if this helps!

这篇关于如何跨多个服务器分区Mysql?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！