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:

• MYSQLFABRIC PRINTS UNPARSABLE OUTPUT TO STDOUT 
• MYSQL FABRIC EXIT-CODE ALWAYS 0

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

MySQL Fabric: Server Properties & Scaling out reads

MySQL Replication is a well-known approach to providing high-availability and scaling out read-only operations (i.e. transactions) as well. In order to make it easy to exploit this scalability axis, we have extended both Fabric and the connectors' interface so that an application can express its willingness to execute a read-only operation and have its request redirected to the server that is most capable of handling it.

In this post, we are going to describe how we can use Fabric and the connector python to scale out read-only operations. We need to introduce some concepts first though.

Fabric organizes the servers in high-availability groups, uses the standard MySQL Replication to synchronize the servers which can be classified according to its status, mode and weight. The blog post MySQL Fabric High-Availability Groups from Mats briefly describes these concepts and the background for them.

Status

The status of the server provide information about what the server is currently doing in the group:

• Primary can accept both reads and writes. It is the master in the standard MySQL Replication.
• Secondary can accept only reads. It is a slave in the standard MySQL Replication.
• Spare does not accept reads nor writes, but can be used to replace servers in the event of a failure. It is also a slave in the standard MySQL Replication.
• Faulty is used to denote that a server is not working properly for any reason.

The following table depicts the transitions from one status to the other and the operations that can trigger them:

	Target Status
Source Status	PRIMARY	SECONDARY	SPARE	FAULTY
PRIMARY		promote/demote		report
SECONDARY	pomote/demote		set_status	report
SPARE	promote/demote	set_status		report
FAULTY			set_status

The report commands, which are "threat report_failure" and "threat report_error", can be used by external entities (e.g. connectors) and MySQL Fabric itself to inform that either a server is faulty or that it seems to be faulty, respectively. In other words, Fabric immediately sets a server to faulty after getting a notification through the "threat report_failure" command and only sets a server to faulty after getting notifications from different entities within a time period through the "threat report_error". 

These parameters might be set up in the configuration file under the "failure_tracking" section:

• notifications: Number of reported errors after which a server is considered unstable.
• notification_clients: Number of different sources that must report an error to consider a server unstable.
• notification_interval: Window interval in seconds that is considered to compute the number of reported errors.

The "group promote" command is used to automatically demote the current primary, if there is any, and elect a new one among the healthy slaves. The "group demote" command is used to demote the current primary. Only secondaries are automatically picked by the "group promote" command as possible candidates to replace a primary. On the other hand, spares might be used to replace a primary only if explicitly requested.

Note that if a server is marked as faulty and we want to change its status after it has been repaired, the "server set_status" command must be used. In this case, the only possibility is to set its status from faulty to spare:

mysqlfabric server set_status server-id spare

Secondaries are ready to accept read requests so setting the status to spare avoid serving stale data as the server was down for a period of time and might have missed some updates. If the recently re-enabled spare was once a primary and we want to restore its previous status, the following command would do the task:

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

The group-name and server-id must be replaced by the group which the server belongs to and its real UUID or address, respectively.

Mode

The mode of the server gives information on whether it can be read or written and provides information to the connector on where it should send queries to.

• Offline server does not accept reads nor writes. Any spare server has its mode set to offline.
• Read-only server can accept only reads. Any secondary server has its mode set to read-only.
• Write-only server can accept only writes. It is possible to make a primary accept only writes.
• Read-write server can accept both reads and writes. By default a primary accepts both reads and writes.

The server's mode is automatically changed when the status is changed. However, we can still perform some additional changes through the "server set_mode":

	Possible Mode
Status	OFFLINE	READ_ONLY	WRITE_ONLY	READ_WRITE
PRIMARY			x	x
SECONDARY	x	x
SPARE	x	x
FAULTY

By default a spare server will not be picked by connectors to serve neither reads nor writes. However, if we want to expand the pool of scale-out servers, we can change its mode to read-only as follows:

mysqlfabric server set_mode server-id read_only

Weight

The weight represents the relative power of the server. It is a float number greater than zero and less or equal to one. By default, any server has the weight property equal to 1.0. Although any server may have its weighted changed through the "server set_weight" command in the server group command, this information is only used now to implement a weighted round-robin scheduling in the connectors to distribute reads:

mysqlfabric server set_weight server-id weight

The server-id and weight must be replaced by a real UUID or address and float number greater than zero and less or equal to one.

Scaling out Reads

Using python, an application can set that it wants to execute a read-only transaction as follows:

self.__cnx = mysql.connector.connect(**fabric_params)
self.__cnx.set_property(group=group, mode=MODE_READONLY)

Note the "mode" parameter in the "set_property" method. Read-only transactions are sent to any server that has the "mode" set to read_only or read_write meaning that it might be sent to either a secondary, a spare or a primary. The primary is only used though as the last resort to serve a query. Connectors try to offload the primary which might become overloaded if both read-only and read-write transactions are sent to it. If Using secondaries and spares to process read operations has a drawback though. Read operations may return stale information because the replication protocol is asynchronous which means that changes to the primary are not immediately propagated to slaves. If we want to read updated information, we need to guarantee that requests are sent to the primary (i.e. master) as follows:

self.__cnx = mysql.connector.connect(**fabric_params)
self.__cnx.set_property(group=group, mode=MODE_READWRITE)

Note the "mode" parameter in the "set_property" method. It specifies that the application wants to execute a read-write transaction. However, only the primary accepts writes. So a connection to the primary will be established thus allowing the application to retrieve updated information. We are considering ways to improve this by providing the possibility of returning updated information even when a secondary server is used. See some insights on this in the blog post MySQL Fabric: Musings on Release 1.4.3 from Mats.

To take advantage of all possible secondary servers in the system, the load should be distributed among them. To make this easier, servers in Fabric have a weight property which represents the relative power of the server. When balancing the load between servers, the connector will figure out which servers are eligible for accepting a transaction and then pick one of the servers in such a way that the distribution over time will be proportional to the weight of the server.

Summary and a sketch of an application

After installing and configuring MySQL Fabric (click here for further details on that), there aren't many things we need to do to exploit its ability to scale out reads:

• If there are spares, decide whether they will accept reads or not. If a spare should accept reads, execute the following command:

mysqlfabric server set_mode server-id read_only

• If there are secondaries that should not accept reads, execute the following command:

mysqlfabric server set_mode server-id offline

• Configure the servers' weights according to their capacity to handle requests:

mysqlfabric server set_weight server-id weight

The next step consisting in calling the appropriate functions and methods within the application:

conn = mysql.connector.connect(
    fabric={"host" : "localhost", "username": "admin",
            "password" : "adminpass"
           },
    user="webuser", password="webpass"
)
conn.set_property(group="my_group", mode=fabric.MODE_READONLY)
cur = conn.cursor()
cur.execute(
    "SELECT first_name, last_name FROM employees "
    "WHERE emp_no = %s", (emp_no, )
)
conn.commit()

Writing a Fault-tolerant Database Application using MySQL Fabric - MySQL Fabric 1.4.2 Release Candidate

If we want to run the application presented in "Writing a Fault-tolerant Database Application using MySQL Fabric" with MySQL Fabric 1.4.2 Release Candidate, some changes to the application are required. In the previous post, we used MySQL Fabric 1.4.0 Alpha and many changes have been made since this version. We can find an updated version of the application here:

• Updated application, ready to use with MySQL Fabric 1.4.2 Release Candidate.

Recall that the application creates a simple database, a high availability group, registers the MySQL Servers into Fabric and runs a thread that mimics a client and another one that periodically executes a switch over.

Configuring and running MySQL Fabric

To run the application, we need:

• Python 2.6 or 2.7
• Three or more MySQL Servers:
• One backing store (5.6.10 or later)
• Two managed servers (5.6.10 or later)
• MySQL Fabric 1.4.2 or later
• Connector Python (MySQL Fabric-aware Version 1.2.1 or later) installed

In this post, we omit how all the components are installed and configured and focus only on how to set up MySQL Fabric. We also assume Unix-like systems, specifically Ubuntu as our environment. For further information on installing and configuring other components and different environments, please, check Configuring and running MySQL Fabric.

To run MySQL, we need to configure MySQL Fabric according to our environment.  In this context, change the /usr/local/etc/mysql/fabric.cfg configuration file:

[storage]
address = localhost:13000
user = root
password =
database = fabric

[servers]
user = root
password =

[protocol.xmlrpc]
address = localhost:32274
disable_authentication = no
user= admin
password = adminpass
realm = MySQL Fabric

(Only the relevant information for this post is presented here)

Choose one of the MySQL servers as the backing store. The backing store's address can be found in the "storage" section along with the "user" and "password" that MySQL Fabric will use to access it. The user does not need to be "root" though. Any user with the following privileges is fine:

CREATE USER 'fabric'@'localhost' IDENTIFIED BY 'secret';
GRANT ALL ON fabric.* TO 'fabric'@'localhost';

After configuring a user to access the backing store, we need to set up a user  to access the MySQL servers that will be managed by MySQL Fabric. The user does not need to be "root" though. Any user with the following privileges is fine:

GRANT ALL ON *.* TO 'fabric'@'localhost';
SET PASSWORD FOR 'fabric'@'localhost' = PASSWORD('secret');

Then we need to configure an address where MySQL Fabric will be listening to incoming request to fetch and update information on shards, servers, etc. It is worth mentioning that MySQL Fabric provides an authentication mechanism to check whether users have the necessary privileges to fetch or update any information. However, we will ignore this for now to avoid cluttering the post with too much information and revisit this specific feature in another post. So after changing the necessary options in the configuration file, we are ready to set up the backing store:

$ mysqlfabric manage setup

To run MySQL Fabric, execute the following command:

$ mysqlfabric manage start

Now that MySQL Fabric is up and running, we can start playing with it.

Running the Sample Application

The application can be executed as follows:

python switchover_application.py --user=root --passwd="" --group="YYZ" \
--servers-addresses="localhost:13002 localhost:13003" \
--fabric-user=admin --fabric-passwd="adminpass" \
--fabric-addresses="localhost:8080"

Please, note that MySQL Fabric and the MySQL instances may be running in different addresses in our environment. So change this information accordingly.

In what follows, we are going to present some key changes that have a direct impact on the application.

Change to the error reported while trying to write to a backup server

One of the changes is related to how the ER_OPTION_PREVENTS_STATEMENT error is being handled. In MySQL Fabric 1.4.0 release, an attempt to write to a secondary server would trigger the DatabaseError exception which is used to report a variety of issues. So to figure out whether a server was set to read-only, after a switchover, for example, we had to check the error code. Since the MySQL Fabric 1.4.1 release, the connector hides this logic and reports a MySQLFabricError exception to the application. This makes it easier to write fault-tolerant applications:

try:
    data = [(i, 'name') for i in range(1, 4)]
    cur.executemany(
        "INSERT INTO employee (id, name) VALUES (%s, %s)", data
    )
    self.__cnx.commit()
except MySQLFabricError as error:
    cur = self._get_cursor()

Change to the set_property parameters

Besides specifying the high-availability group which will be accessed, we must set whether we want to execute read-only or read-write transaction(s). This is is done through the mode parameter which accepts MODE_READONLY or MODE_READWRITE (default value), respectively read-only or read-write transactions.

self.__cnx = mysql.connector.connect(**fabric_params)
self.__cnx.set_property(group=group, mode=MODE_READWRITE)

We are going to revisit this in another blog post where we intent to describe how to scale out reads using MySQL Fabric. Stay tuned.

Introduced credentials

fabric_params = {
    "fabric" : {
        "host" : fabric_host, "port" : fabric_port,
        "username" : fabric_user, "password" : fabric_passwd
    },
    "user"   : user, "passwd" : passwd, "database" : "mysql",
}
self.__cnx = mysql.connector.connect(**fabric_params)

We must provide a user and password to access MySQL Fabric. In this example, we have used the default "admin" user who is created when the backing store is set up. We will explain this new feature in another post. Stay tuned.

Changed how to get a reference to Fabric (i.e. a proxy) through the connector

fabric = Fabric(host=fabric_host, port=fabric_port,
    username=fabric_user, password=fabric_passwd
)
fabric.seed()
self.__proxy = fabric.get_instance().proxy

Note that we need to specify a user and password to access a MySQL Fabric instance.

Mats Kindahl on MySQL Fabric

Check out this interview with Mats on MySQL Fabric:

http://www.youtube.com/watch?v=fHs7yV4zCag&feature=youtu.be

Configuring and running MySQL Fabric 1.4.2

To install and run MySQL Fabric, we need:

• Python 2.6 or 2.7
• Connector Python (MySQL Fabric-aware Version 1.2.1 or later)
• One or more MySQL servers installed and running (5.6.10 or later):
• Backing Store
• Managed Servers
• MySQL Utilities 1.4.2 or later

Python 2.6 or 2.7

We can use the following command to check whether python is installed in our machine or not:

$ python --version

If you don't have the appropriate version installed, please, read through the following web page and find out how to download and install python 2.6 or 2.7:

• https://wiki.python.org/moin/BeginnersGuide/Download

MySQL 5.6

Information on how to download and install MySQL can be found at the following web page:

 . http://dev.mysql.com/doc/refman/5.6/en/installing.html

If MySQL is already installed, please, check its version through the following command:

$ mysqld --version

In this post, we assume that the installation provides access to the mysqltest framework.

Connector Python 1.2.1

On Unix-like systems such as Linux distributions, Solaris, Mac OS X, and FreeBSD, we can download Connector Python as a tar archive from:

• http://dev.mysql.com/downloads/connector/python/

To install Connector/Python from the .tar.gz file, download the latest version and follow these steps:

$ tar xvzf mysql-connector-python-1.2.1.tar
$ cd mysql-connector-python-1.2.1
$ sudo python setup.py install

Information on other platforms and other packages can be found also at the aforementioned link.

MySQL Utilities 1.4.2

On Unix-like systems such as Linux distributions, Solaris, Mac OS X, and FreeBSD, we can download MySQL Utilities which contains MySQL Fabric as a tar archive from:

• http://dev.mysql.com/downloads/connector/python/.

$ tar xvzf mysql-utilities-1.4.2.tar.gz
$ cd mysql-utilities-1.4.2
$ sudo python setup.py install

Information on other platforms and other packages can be found also at the aforementioned link.

Configuring MySQL Fabric

Now that all components required to run MySQL Fabric are installed, we can configure MySQL Fabric and run it. The first step consists in running two or more MySQL servers which will be used as a backing store and instances managed by MySQL Fabric.

In order to ease the presentation and avoid cluttering it with too many details, we assume that the mysqltest framework is installed and exploit it to start several MySQL servers. Note though this is used to offer a sneak peek at MySQL Fabric and should never be used in production.

Create the suite/rpl/t/rpl_fabric_gtid.cnf file with the following content:

!include ../my.cnf

[mysqld.1]
innodb
log-slave-updates
gtid-mode=on
enforce-gtid-consistency

[mysqld.2]
innodb
log-slave-updates
gtid-mode=on
enforce-gtid-consistency

...

We can add as many entries as many MySQL severs are required to run our tests. In our very simple example, only two MySQL servers are necessary though.

Create a /rpl/t/rpl_fabric_gtid.test file with the following content:

--source include/have_innodb.inc

Then execute the following command to start a set of MySQL servers:

$ ./mysql-test-run.pl rpl_fabric_gtid --mem --start

From a different terminal, change the /usr/local/etc/mysql/fabric.cfg configuration file:

[storage]
address = localhost:13000
user = root
password =
database = fabric

[servers]
user = root
password =

[protocol.xmlrpc]
address = localhost:32274
disable_authentication = yes
user= admin
password = adminpass
realm = MySQL Fabric

(Only the relevant information for this post is presented here)

Choose one of the MySQL servers as the backing store which is used to store metadata on shards and the servers being managed. The backing store's address can be found in the "storage" section along with the "user" and "password" that MySQL Fabric will use to access it. The user does not need to be "root" though. Any user with the following privileges is fine:

CREATE USER 'fabric'@'localhost' IDENTIFIED BY 'secret';
GRANT ALL ON fabric.* TO 'fabric'@'localhost';

See https://dev.mysql.com/doc/refman/5.6/en/adding-users.html for further details on adding a user account. After configuring a user to access the backing store, we need to set up a user  to access the MySQL servers that will be managed by MySQL Fabric. This user does not need to be "root" either. Any user with the following privileges is fine:

GRANT ALL ON *.* TO 'fabric'@'localhost';
SET PASSWORD FOR 'fabric'@'localhost' = PASSWORD('secret');

Then we need to configure an address where MySQL Fabric will be listening to incoming requests to fetch and update information on shards, servers, etc. It is worth mentioning that MySQL Fabric provides an authentication mechanism to check whether users have the necessary privileges to fetch or update any information. However, we will disable it in order to avoid cluttering the post with too much information and revisit this specific feature in another post. So after changing the necessary options in the configuration file, we are ready to set up the backing store:

$ mysqlfabric manage setup

Running MySQL Fabric

To run MySQL Fabric, execute the following command:

$ mysqlfabric manage start

Now that MySQL Fabric is up and running, we can start playing with it. MySQL Fabric organizes servers into high-availability groups. Currently, only the standard MySQL Replication is available.

In the following example, we create the "my_group" group and add the "localhost:13001" to it.

$ mysqlfabric group create my_group
$ mysqlfabric group add localhost:13001

We can execute the following commands to get some help on the mysqlfabric command-line utility:

$ mysqlfabric help commands
$ mysqlfabric help groups
$ mysqlfabric help group-command command


The "mysqlfabric help commands" list all commands available. Commands in MySQL Fabric are organized in groups and "mysqlfabric help groups" can be used to list all groups available. Finally, to print detailed information on a command, we can use "mysqlfabric help group-command command" where "group-command" and "command" must be substitute for the real group's name and command's name, respectively.

MySQL Fabric 1.4.2 Release Candidate is out

MySQL Utilities 1.4.2 Release Candidate is out and it includes MySQL Fabric as well. We have made MySQL Fabric more solid and worked on stabilizing the connector "interfaces" for load-balancing and scale-out. MySQL Utilities 1.4.2 is available for download from:

http://dev.mysql.com/downloads/tools/utilities/1.4.html

Use the following forum to provide us suggestions, comments or any feedback:

http://forums.mysql.com/list.php?144

If you come across any bug or have any feature request, please, report it through our public bug system category "MySQL Fabric".

Help us make this an awesome shard solution.

Weaving with Fabric

Interview with Mats Kindahl, a MySQL Senior Software Engineer, on MySQL Fabric.