10 years ago

MySQL Fabric’s Database Shards

An example code for querying sharded system.
 

MySQL Fabric allows you to manage MySQL Server farms and one of its features helps you scale out by data sharding.   It supports both range and hash-based sharding and is split between server and client.  For the server, you will be using the XML-RPC server and the mysqlfabric utility.  For the client, you will be using a MySQL Fabric aware connector.

The sharding structure would have the global group and the shard groups.  The global group will be used to store the unpartitioned data and duplicate and replicate the schema changes.  The shard groups will be storing the shards.  These shards are similar to tables that have the exact same structure but each shard would have different sets of data.  These sharding groups are designated by MySQL Fabric as secondary. This way, it will not be able to accept writes.

Fabric sharding requires you to give out the specific sharding key when you execute the query.  Aside from that there are two concepts that you should understand: Shard mapping and shards.

Shard mapping

Shard mapping allows you to bring a database table or object into the Fabric.  It is a way to tell the Fabric that you want a particular sharding technique, whether it is range, hash, list or some other scheme, to be utilized on a table.  You can create a shard mapping by:

  1. Defining a shard mapping to tell Fabric what kind of mechanism to use
  2. Adding a relation between your database object and the mapping

After creating the shard mapping, you can now specify how it should split the tables.  This process is done while you create the shards.

Shards

The shards are nothing but the partitions on the table.  It is done on a database table, utilizing a column or attribute in the table you are sharding.  Because of this, the values in the column that you have specified may influence how the shards are made.  For example, if you have two tables that you want to shard, i.e. grades and students.  You can use range sharding to shard these tables by student ID number, which is a column in both table.  Range-based sharding will give you a shard of these tables based on a range of student ID numbers.

Imagine that you have a database table named “students” and you want to shard it as

  • Shard 1: 1 – 99
  • Shard 2: 100 – 199
  • Shard 3: 200 – 299

Using the range based sharding, you would need to create your own ranges and specify where these would be located.  Then you would need to start all the MySQL Servers on every directory that has been copied, using localhost:1300X.

Then you need to make the groups using these commands

  • shell> mysqlfabric group create group-1
  • shell> mysqlfabric group create group-2
  • shell> mysqlfabric group create group-3

These would give you three HA groups: group-1, group-2 and group-3.  The shards will be located in these groups.

Then you must set up the global group by using this command: shell> mysqlfabric group create group_id-global

Then you have to register the servers to each group (use at least two servers per group) using: shell> mysqlfabric group add group-1 localhost:1300x

After registering the servers to the group, you would need to designate one server as master. You would then need to define a shard mapping to create the shards.

You can also move a shard from one server set to another and split a shard if it becomes overloaded.

Confused? Four Cornerstone can help.  Call us today!

For more information regarding MySQL Fabric’s database sharding, you can read our sources below:

  • Source 1
  • Source 2
  • Source 3

Screenshot courtesy of MySQL.

Scroll to Top