How to partition Mysql across MULTIPLE SERVERS?

Question

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?

It would be nice if people left a comment when they hit "close". IMHO, this question is more than suitable for Stackoverlow. — Till, Oct 25 '09 at 19:13

score 23 · Accepted Answer · answered Oct 25 '09 at 19:12

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!

This is what I ended up doing before I even knew what it was called or what my other options were. I needed a solution fast and this made sense. So I have half my customers on one server, the other half on the other. The problem with it is that it's now difficult to get statistics on all the data put together, or to write an admin site which can manage data on both servers. — Vincent, Feb 25 '19 at 23:00

score 4 · Answer 2 · edited Nov 24 '12 at 01:47

But you need to keep in mind that if you for some reasons want to take this solution to cloud and make it multi tenant then the above configuration might become more challenging. Think about this -

you logged into the system and the DB gets selected (DB swap) based on the user- organization table
This would be the new Master table that you are talking to now with it's own slaves
But the point 2 above also needs sharding now since you want to make sure that your scale out does not become a bottleneck.

So now the question is that you will probably need to think how can you do this sharding in a mster-slave kind of env where slaves are typically for reading and masters for writing.

cheers ! Gary

score 0 · Answer 3 · edited Feb 13 '13 at 14:55

Here what is written at the announce of HSCALE 0.1:

Right now we need to just split up huge tables but later on we want to distribute partitions over multiple MySQL server instances to have real horizontal scale out. The hardest part will be dealing with transactions where we have to use distributed transactions (XA) or disallow transactions involving partitions on different hosts...

Have a look at this project : http://sourceforge.net/projects/hscale/ maybe it will be suitable for you.

How to partition Mysql across MULTIPLE SERVERS?

3 Answers3

Linked