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.