8.3.1 Example: Fabric and Replication

This section presents a quick start for using MySQL replication features in Fabric. To run this example, you should have four server instances (running MySQL version 5.6.10 or later). The commands in this example are executed on the same server host as the backing store (which happens to be the same host where Fabric was installed). You must also have a Fabric node started on that host.

The replication features in Fabric focus on providing high availability. While these features continue to evolve, the most unique feature of Fabric replication is the ability to use a Fabric-aware connector to seamlessly direct reads and writes to the appropriate servers.

This redirection is achieved through the use of one of the central concepts in Fabric: a high-availability group that uses a high-availability solution for providing resilience to failures. Currently, only asynchronous primary backup replication is supported. As long as the primary is alive and running, it handles all write operations. Secondaries replicate everything from the primary to stay up to date and might be used to scale out read operations.

Creating a High-Availability Group

The first step consists of creating a group, here designated my_group. After doing so, you can add servers to the group. In this case, we add three servers, localhost:13001, localhost:13002, and localhost:13003.

Fabric accesses each added server using the user and password provided in the configuration file to guarantee that they are alive and accessible. If these requirements are not fulfilled, the operation fails and the server is not added to the group.

The following demonstrates the commands to execute these steps.

shell> mysqlfabric group create my_group
Procedure :
{ uuid        = d4e60ed4-fd36-4df6-8004-d034202c3698,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}
shell> mysqlfabric group add my_group localhost:13001
Procedure :
{ uuid        = 6a33ed29-ccf8-437f-b436-daf07db7a1fc,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}
shell> mysqlfabric group add my_group localhost:13002
Procedure :
{ uuid        = 6892bc49-3ab7-4bc2-891d-57c4a1577081,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}
shell> mysqlfabric group add my_group localhost:13003
Procedure :
{ uuid        = 7943b27f-2da5-4dcf-a1a4-24aed8066bb4,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}

To show information about the set of servers in a group, use this command:

shell> mysqlfabric group lookup_servers my_group

To get detailed information about the group health, use this command:

shell> mysqlfabric group health my_group

Promoting and Demoting Servers

After executing the steps in setting up a high-availability group, Fabric does not become aware of any replication topology that was already in place. It is necessary to promote one of them to primary (that is, master) and make the remaining servers secondaries (that is, slaves). To do so, execute this command:

shell> mysqlfabric group promote my_group

If there is a primary in a group, any server added subsequently is automatically set as secondary. Setting a different server as primary can be done through the same command, which demotes the current primary and elects a new one. If the current primary has failed, the same command (which can be triggered either manually or automatically) can be used to elect a new one.

Note

A server marked as "faulty" cannot be promoted to a secondary or primary status in one step. The faulty server status must first be changed to the "spare" status. For example, use mysqlfabric server set_status <server-address> spare.

Activating or Deactivating a Failure Detector

If the primary fails, you may want to automatically promote one of the secondaries as primary and redirect the remaining secondaries to the new primary. To do this, execute the following command:

shell> mysqlfabric group activate my_group

If the failure detector discovers that a primary has crashed, it marks it as faulty and triggers a failover routine. This is not done automatically because there may be users who prefer to use an external failure detector or want to do things manually. To deactivate the failure detector, execute the following command:

shell> mysqlfabric group deactivate my_group

Executing Updates and Queries

Executing queries with a Fabric-aware connector is easy. The following example shows a section of code designed to add employees and search for them. Notice that we simply import the fabric package from the Connector/Python library and provide the Fabric connection parameters such as the location of the Fabric node (as specified in the [protocol.xmlrpc] configuration file section) and user credentials.

import mysql.connector
from mysql.connector import fabric

def add_employee(conn, emp_no, first_name, last_name):
    conn.set_property(group="my_group", mode=fabric.MODE_READWRITE)
    cur = conn.cursor()
    cur.execute("USE employees")
    cur.execute(
        "INSERT INTO employees VALUES (%s, %s, %s)",
        (emp_no, first_name, last_name)
        )
    # We need to keep track of what we have executed in order to,
    # at least, read our own updates from a slave.
    cur.execute("SELECT @@global.gtid_executed")
    for row in cur:
        print "Transactions executed on the master", row[0]
        return row[0]

def find_employee(conn, emp_no, gtid_executed):
    conn.set_property(group="my_group", mode=fabric.MODE_READONLY)
    cur = conn.cursor()
    # Guarantee that a slave has applied our own updates before
    # reading anything.
    cur.execute(
        "SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('%s', 0)" %
        (gtid_executed, )
    )
    for row in cur:
        print "Had to synchronize", row, "transactions."
    cur.execute("USE employees")
    cur.execute(
        "SELECT first_name, last_name FROM employees "
        "WHERE emp_no = %s", (emp_no, )
        )
    for row in cur:
        print "Retrieved", row

# Address of the Fabric, not the host we are going to connect to.
conn = mysql.connector.connect(
    fabric={"host" : "localhost", "port" : 32274,
            "username": "admin", "password" : "adminpass"
           },
    user="webuser", password="webpass", autocommit=True
    )

conn.set_property(group="my_group", mode=fabric.MODE_READWRITE)
cur = conn.cursor()
cur.execute("CREATE DATABASE IF NOT EXISTS employees")
cur.execute("USE employees")
cur.execute("DROP TABLE IF EXISTS employees")
cur.execute(
    "CREATE TABLE employees ("
    "   emp_no INT, "
    "   first_name CHAR(40), "
    "   last_name CHAR(40)"
    ")"
    )

gtid_executed = add_employee(conn, 12, "John", "Doe")
find_employee(conn, 12, gtid_executed)

You can copy this code into a Python module named test_fabric_query.py and execute it with the following command:

shell> python ./test_fabric_query.py
(u'John', u'Doe')

Group Maintenance

To find out which servers are in a group, use this command:

shell> mysqlfabric group lookup_servers my_group
Command :
{ success     = True
  return      = [
      {'status': 'PRIMARY', 'server_uuid': 'bbe6f7c1-b6c3-11e3-aaa2-58946b051f64',
       'mode': 'READ_WRITE', 'weight': 1.0, 'address': 'localhost:13001'
      },
      {'status': 'SECONDARY', 'server_uuid': '0c9e67d0-8194-11e2-8a7c-f0def124dcc5',
       'mode': 'READ_ONLY', 'weight': 1.0, 'address': 'localhost:13002'
      },
      {'status': 'SECONDARY', 'server_uuid': '0c67e5b1-8194-11e2-8a7c-f0def124dcc5',
       'mode': 'READ_ONLY', 'weight': 1.0, 'address': 'localhost:13003'
      },
  ]
  activities  = 
}

In this example, there are three servers identified by their UUID values. The server running at localhost:13001 is a primary, whereas the other servers are secondaries.

It is sometimes necessary to take secondaries offline for maintenance. However, before stopping a server, it is important to remove it from the group to avoid having the Fabric failure detector trigger any action. This can be done through the following commands. server_uuid should be replaced with a server UUID value (a value of the form d2369bc9-2009-11e3-93c6-f0def14a00f4).

shell> mysqlfabric group remove my_group server_uuid

A primary cannot be removed from a group. To disable any primary in a group, execute this command:

shell> mysqlfabric group demote my_group

If a group contains no servers, it is empty and can be destroyed (removed) with this command:

shell> mysqlfabric group destroy my_group

It is also possible to force removal of a nonempty group by specifying the parameter --force. This command removes all servers from my_group and removes the group.

shell> mysqlfabric group destroy my_group --force