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.


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.

Cloud Blog

4 Ways To Benefit from…

One of the benefits you get when you work with cloud applications is that you often have quarterly updates that are packed with features. This...

Keep Reading

Artificial Intelligence

Data and Analytics: Cross the…

  Artificial intelligence is a manna sent from digital heaven. That’s how blessed your business can get if you immerse into the AI of things....

Keep Reading

Business Intelligence Blog

How Brand Names Survive in…

  The age of digital marketplace has made it possible for unknown and smaller companies to compete with better-known and well-established brands. Take for example...

Keep Reading

Artificial Intelligence

The Phenomenon That Is Artificial…

  Artificial intelligence is when a machine does cognitive functions that are more associated with humans, such as thinking, learning, problem solving, and reasoning. As...

Keep Reading


Digital Transformation in Banking: Shift…

  The start of a new decade is already shaping up to be exciting for financial services, especially for banks as they continue to compete...

Keep Reading

Business Intelligence Blog

Data Science and Its Economic…

It would seem that artificial intelligence is the focus of businesses that are looking to future proof their organizations and stay competitive. And why not?...

Keep Reading

Data Blog

Ensure Efficient Data Science and…

In an ever-expanding landscape called Internet of Things and the exploding development of artificial intelligence, we are bombarded with complex methods of integrating data science...

Keep Reading

Business Intelligence Blog

Cybersecurity: The Top 5 Expectations…

  If you think about it, cybersecurity is closely tied to human rights, privacy, freedom, and even basic safety. As a whole, we have become...

Keep Reading

Business Intelligence Blog

Tech Security Should Be Easy…

  IT professionals know that tech security is important in everything that they do. And that there are a lot of products, tools, innovations, and...

Keep Reading

Business Intelligence Blog

The Top 6 Success Stories…

Nearly nine out of 10 digital transformation initiatives fail. Digital transformation projects fail for a variety of reasons. It can be because of a lack...

Keep Reading

Live Chat | Emergency