Tuesday, May 27, 2014

MySQL Fabric: The --update_only option because one size does not fit all

MySQL Fabric is a distributed framework that has high availability and sharding as targets. It organizes the servers in groups which use the standard MySQL Replication to providing fault-tolerance. Shards are assigned to different groups thus allowing applications to distribute both reads and writes and exploit resilience to failures as well.

Information on groups, servers and shards are stored in a MySQL Instance called state store or backing store. This instance is a repository for all this information and the engine used might be any supported by MySQL, although a transactional engine must be picked to truly provide fault-tolerance. Note though that we have been testing MySQL Fabric with Innodb and currently this the only official engine supported.

Built upon the repository there are several functions that, besides being used to retrieve information from and update the repository, are responsible for the execution of some administrative tasks. For example, adding a server into a group has two steps: creating an entry into the state store and making the server point to the current primary, if there is any.

Promoting a new primary requires to:

  • choose among a set of candidates which one will become the new primary when a candidate is not promptly specified;
  • stop accepting writes on the current primary;
  • synchronize the primary with the secondaries and reconfigure replication;
  • update the repository with the new information.

However, there might be users who want to use their own scripts to execute these administrative tasks and want to update MySQL Fabric to reflect the changes made to the servers. For these users, we have introduce the --update_only option into some commands so that they might continue using their own scripts and take advantage of MySQL Fabric at the same time as well.

Executing commands

MySQL Fabric's command might be invoked as a command-line script as follow:

mysqlfabric group add my_group localhost:13001 --update_only

Or using a MySQL Fabric-aware connector (e.g. Connector Python) through a direct call to a XML-RPC function as follows:

ERROR, SUCCESS = range(1, 3)

conn = mysql.connector.connect(
    fabric={"host" : "localhost",
            "username": "admin", "password" : "adminpass"
    user="webuser", password="webpass"
proxy = conn._fabric.get_instance().proxy
status = proxy.group.add("my_group", "localhost:13001", 5, True)
if status[1][-1]["success"] == ERROR:
    raise Error("Something bad happened %s." % (str(status), ))

Note that the "mysqlfabric" script uses XML-RPC to call the appropriate functions and both examples try to add "localhost:13001" server into the "my_group" group. In the latter case, besides setting the group's name, server's address and the update only arguments, we also need to set the timeout argument as the XML-RPC does not support named/keyword arguments. For a complete list of commands available check the documentation or execute the following command:

mysqlfabric help commands

Currently the command-line solution has two issues:

We are working on fixing these as soon as possible.

Adding a server into a group

Users must set up replication on a new MySQL Server and inform MySQL Fabric about its existence as follows:

mysqlfabric group add group-name server-id --update_only

To avoid cluttering the post with unnecessary details, we are going to write examples using the command-line script. Note though that there are issues with this solution as aforementioned. The group-name and server-id must be replaced by the group which the server belongs to and its real UUID or address, respectively. The remove command does have the option --update_only as it only removes a reference to the server from the repository and does not try to access the server or reconfigure replication.

Promoting a new primary

If users want to promote a new primary, they must first demote the current one as follows:

mysqlfabric group demote group-name --update_only

After that, they should forbid any attempt to write to the old primary, make sure that the primary and secondaries are synchronized, elect a new primary and then reconfigure replication accordingly. How these is done depends on the particularities of their environments and scripts. Finally to inform the connectors that there is a new primary, they should execute the following:

mysqlfabric group promote group-name --slave_id=server-id --update_only


Seth Williams said...

Hey Alfranio, these My SQL codes were really useful. Thanks for the update.We have our own My SQL tutorials as well that may benefit your readers at https://www.linkedin.com/company/firebox-training

Jr. Williams said...

difference between sql server and mysql?
Unlimited MySQL Databases

Jr. Williams said...

Thanks for the update you have nicely covered this topic. keep it up
best domain name registrar