Friday, September 27, 2013

MySQL Fabric - Sharding - Shard Maintenance


Let us start by listing the scenarios in which we will need to perform shard maintenance. In addition to periodic maintenance operations the following situations might mandate performing shard maintenance.

A shard is physically a server group. A single non-performant server can impact the latency of requests for the entire group. Hence we need to be able to pull out this non-performant server and replace it by a spare, without impacting the accessibility of the entire shard.

Maintenance activity might involve performing software upgrades on the different servers in the server group of the shard.

We might also need to upgrade the hardware on all the machines in the server group of a shard without impacting the accessibility of the entire data.

What if we want to form an alternate group and migrate the entire shard there? For example during times of heavy load we will want to create a group using more powerful servers, while during light load we might want to migrate to a group of more economical, less power consuming and cheaper machines.

Let us look at what options are available to do the above.


Sharding Topology

The topology we create has three shards. There are four server groups. One of the server groups serves as the Global group for the setup. Each of the server group has two MySQL servers, one serving as the master and another as the slave.

The assumption is that you have already gone through the following related blogs to create the above configuration.

Pulling out a non-performant server

We use the intrinsic HA management capabilities built into a Fabric server group to manage moving servers in and out seamlessly out of the shards.

case 1: Server is a master in the group

In the above setup assume we want to retire the MySQL Server running on host3:port3 (master in GROUPID1).

Step 1: Find the UUID of the server


mysqlfabric server lookup_uuid <host:port> <user_id> <password>


host:port – host:port of the server whose uuid we want to lookup (in the above

case host3:port3). 

user_id, password – credentials for the MySQL Server.


mysqlfabric group lookup_servers <group-id>


group-id – The group-id of the Group whose servers we want to lookup (in the 

above case GROUPID1).

Step 2: Choose another server in the group and promote that server into 



mysqlfabric group promote <group-id><server-uuid>


group-id – The group-id of the Group in which we want to promote a server to 

master (in the above case GROUPID1).

server-id – The server-id of the server which we want to promote as master.

Step 3: Remove the server from the group


mysqlfabric group remove <group-id> <server-uuid>


group-id – The groupid of the Group from which we want to remove a server (in 

the above case GROUPID1).

server-uuid – The uuid of the server which we want to remove from the group.

Now the server is safely out of the group. Now we can perform maintanence operations on the server and restore it later.

case 2: Server is a slave in the group

Step 1: Remove the server from the group
mysqlfabric group remove <group-id> <server-uuid>
group-id – The group-id of the group from which we want to remove a server (in the above case GROUPID1).
Server-uuid – The server UUID of the server we want to remove.


Performing software and hardware updates on a server

The same operation as above can be performed to remove the servers one by one and perform upgrades on them (or) we can move the entire shard to an alternate server group, upgrade all the servers in the current server group and move the
shard back.

Moving shards is explained below.

Moving the shard into another group

The sharding implementation supports a Move operations that allows moving a shard into another server group (a group with GROUPID4). The original server group is detached from the global group and will not receive any updates and its servers will not be reflected during lookups either.

While the move operation is in progress transactions can carry on without being affected.

For example to move a shard with shard-id=1 into a new group with new-group-id=GROUPID4. (The shard-id can be found using the command – mysqlfabric store dump_sharding_information)

mysqlfabric sharding move <shard-id> <new-group-id>

shard-id – The shard-id of the shard that needs to be moved (in this case 1)
new-group-id – The group-id of the server group into which this shard needs to be moved. (in this case GROUPID4).

Now we can cleanup and use the servers in GROUPID1 in whichever way we want.