Writing a Fault-tolerant Database Application using MySQL Fabric

In this post, we are going to show how to develop fault-tolerant applications using MySQL Fabric, or simply Fabric, which is an approach to building high availability sharding solutions for MySQL and that has recently become available for download as a labs release (http://labs.mysql.com/). We are going to focus on Fabric's high availability aspects but to find out more on sharding readers may check out the following blog post:

•  http://vnwrites.blogspot.com/2013/09/mysqlfabric-sharding-introduction.html

Servers managed by Fabric are registered in a MySQL Server instance, called backing store, and are organized into high availability groups which deploy some sort of redundancy to increase resilience to failures. Currently, only the traditional MySQL Asynchronous Replication is supported but we will consider other solutions in future such as MySQL Cluster, Windows Server Failover Clustering and Distributed Replicated Block Device. For a great analysis of alternatives for MySQL High Availability Solutions certified and supported by Oracle readers should check out the following white paper:

• http://www.mysql.com/why-mysql/white-papers/mysql-guide-to-high-availability-solutions/ 

Different from a traditional MySQL Client Application, a Fabric-aware Client Application does not specify what server it wants to connect to but the Fabric instance from where it will fetch information on the available servers in a group. So a fabric-aware connector is required for this task. Currently, only the connectors PHP, Java and Python have extensions to access Fabric. In the future, we aim to add Fabric support to other connectors as we are fortunate to have a strong influence over many of the key connectors in the MySQL ecosystem.

class Application(object): 
    def __init__(self): 
        fabric_params = { 
            "fabric" : {"host" : "localhost", "port" : 8080}, 
            "user"   : "oracle", "passwd" : "oracle" 
        } 
        self.__cnx = MySQLFabricConnection(**fabric_params) 

    def start(self): 
        self.__cnx.set_property(group="YYZ") 
        cursor = self.__cnx.cursor() 
        cursor.execute("...")

In this sample code, written in Python, the connector sends a request to a Fabric instance located at address "localhost", port "8080" in order to retrieve information on all servers registered in the "YYZ" group and then creates a connection to the master in the group. The communication between the connector and Fabric is done through the XML-RPC protocol which has been chosen for being a "quick and easy way to make procedure calls over the Internet". However the XML-RPC protocol is not well-known for its performance, and to reduce the overhead of contacting Fabric every time a connector needs information on a group, data is stored in a local cache.

If the current master fails though, the "InterfaceError" exception is raised and the application is responsible for catching the exception and getting a new connection to a new elected master if it wants to carry on its regular operations. See a sample code in what follows: 

 

class Application(object): 
    def __init__(self): 
        fabric_params = { 
            "fabric" : {"host" : "localhost", "port" : 8080}, 
            "user"   : "oracle", "passwd" : "oracle" 
        } 
        self.__cnx = MySQLFabricConnection(**fabric_params) 
        self.__cnx.set_property(group="YYZ") 

    def start(self): 
        while self.__run: 
            try: 
                self.__cnx.start_transaction() 
                cur.execute("...") 
                self.__cnx.commit() 
            except InterfaceError as error: 
                cur = self._get_cursor() 
            time.sleep(1) 

    def _get_cursor(self): 
        return self.__cnx.cursor()

Readers will find a full-fledged application, which shows how all the concepts introduced in this post are used together here. 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 switchover.

To run the application, we need:

• Python 2.6 or 2.7
• Three or more MySQL Servers:
• One backing store (5.6 or later preferable)
• Two managed servers (5.6 or later necessary)
• Fabric running
• Connector Python (Fabric-aware Version) installed 

After configuring the environment appropriately, we can run the application as follows: 

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

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

Note that Fabric is in it early development stages and there is a long way ahead of us. However we have decided to make it public available through a labs release so that you could contribute to the project with comments, feedback or patches. Any feedback or comment is highly appreciated. Leave messages at this blog or contact us through the following forum:

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