MySQL Fabric is a new framework that adds High Availability (HA) and/or scaling-out for MySQL. MySQL Fabric achieves scale-out by managing the sharding of table data between multiple MySQL Servers and then having Fabric-aware connectors route queries and transactions to the correct locations – scaling-out will be the subject of a future post and the rest of this article is focused on using MySQL Fabric for HA. It starts with an introduction to HA and how MySQL Fabric delivers it before going on to work through a full example of configuring a HA farm of MySQL Servers together with the code that the application developer needs to write in order to exploit it. Note that at the time of writing, MySQL Fabric is not yet GA but is available as a public alpha.
High Availability – Introduction
High Availability (HA) refers to the ability for a system to provide continuous service – a system is available while that service can be utilized. The level of availability is often expressed in terms of the “number of nines” – for example, a HA level of 99.999% means that the service can be used for 99.999% of the time, in other words, on average, the service is only unavailable for 5.25 minutes per year (and that includes all scheduled as well as unscheduled down-time).
The figure shows the different layers in the system that need to be available for service to be provided.
At the bottom is the data that the service relies on. Obviously, if that data is lost then the service cannot function correctly and so it’s important to make sure that there is at least one extra copy of that data. This data can be duplicated at the storage layer itself but with MySQL, it’s most commonly replicated by the layer above – the MySQL Server using MySQL Replication. The MySQL Server provides access to the data – there is no point in the data being there if you can’t get at it! It’s a common misconception that having redundancy at these two levels is enough to have a HA system but you also need to look at the system from the top-down.
To have a HA service, there needs to be redundancy at the application layer; in itself this is very straight-forward, just load balance all of the service requests over a pool of application servers which are all running the same application logic. If the service were something as simple as a random number generator then this would be fine but most useful applications need to access data and as soon as you move beyond a single database server (for example because it needs to be HA) then a way is needed to connect the application server to the correct data source. In a HA system, the routing isn’t a static function, if one database server should fail (or be taken down for maintenance) the application should be directed instead to an alternate database. Some HA systems implement this routing function by introducing a proxy process between the application and the database servers; others use a virtual IP address which can be migrated to the correct server. When using MySQL Fabric, this routing function is implemented within the Fabric-aware MySQL connector library that’s used by the application server processes.
MySQL Fabric delivers HA by adding a management and monitoring layer on top of MySQL Replication together with a set of Fabric-aware MySQL Connectors that route writes (and consistent reads) to the current master.
MySQL Fabric has the concept of a HA group which is a pool of two or more MySQL Servers; at any point in time, one of those servers is the Primary (MySQL Replication master) and the others are Secondaries (MySQL Replication slaves). The role of a HA group is to ensure that access to the data held within that group is always available.
While MySQL Replication allows the data to be made safe by duplicating it, for a HA solution two extra components are needed and MySQL Fabric provides these:
- Failure detection and promotion – the MySQL Fabric process monitors the Primary within the HA group and should that server fail then it selects one of the Secondaries and promotes it to be the Primary (with all of the other slaves in the HA group then receiving updates from the new master). Note that the connectors can inform MySQL Fabric when they observe a problem with the Primary and the MySQL Fabric process uses that information as part of its decision making process surrounding the state of the servers in the farm.
- Routing of database requests – When MySQL Fabric promotes the new Primary, it updates the state store and notifies the connectors so that they can refresh their caches with the updated routing information. In this way, the application does not need to be aware that the topology has changed and that writes need to be sent to a different destination.
The following steps set up the HA MySQL configuration shown here before running some (Python) code against it and then finally the killing the Primary (replication Master) and observing that one of the slaves is automatically promoted.
Note that this configuration isn’t really HA as all of the MySQL Servers in the HA Group are actually running on the same machine; this configuration has been chosen for this post to illustrate that you can experiment with MySQL Fabric using a small number of machines (in fact, the MySQL Fabric process and its state store (another MySQL Server) could have been run on that same machine). Later posts will use more machines to demonstrate more realistic deployment topologies.
Building the HA MySQL Server Farm
The machines being used already have MySQL 5.6 installed (though in a custom location) and so the only software pre-requisite is to install the MySQL connector for Python from the “Development Releases” tab from the connector download page and MySQL Fabric (part of MySQL Utilities) from the “Development Releases” tab on the MySQL Utilties download page:
[root@fab1 mysql ~]# rpm -i mysql-connector-python-1.2.0-1.el6.noarch.rpm [root@fab1 mysql ~]# rpm -i mysql-utilities-1.4.1-1.el6.noarch.rpm
MySQL Fabric needs access to a MySQL Database to store state and routing information for the farm of servers; if there isn’t already a running MySQL Server instance that can be used for this then it’s simple to set one up:
[mysql@fab1 ~]$ mkdir myfab [mysql@fab1 ~]$ cd myfab/ [mysql@fab1 myfab]$ mkdir data [mysql@fab1 myfab]$ cat my.cnf [mysqld] datadir=/home/mysql/myfab/data basedir=/home/mysql/mysql socket=/home/mysql/myfab/mysqlfab.socket binlog-format=ROW log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 port=3306 report-host=fab1 report-port=3306 server-id=1 log-bin=fab-bin.log [mysql@fab1 mysql]$ scripts/mysql_install_db --basedir=/home/mysql/mysql/ \ --datadir=/home/mysql/myfab/data/ 2014-02-12 16:55:45 1298 [Note] Binlog end 2014-02-12 16:55:45 1298 [Note] InnoDB: FTS optimize thread exiting. 2014-02-12 16:55:45 1298 [Note] InnoDB: Starting shutdown... 2014-02-12 16:55:46 1298 [Note] InnoDB: Shutdown completed; log sequence number 1600607 2014-02-12 16:55:46 1298 [Note] /home/mysql/mysql//bin/mysqld: Shutdown complete [mysql@fab1 ~]$ mysqld --defaults-file=/home/mysql/myfab/my.cnf &
MySQL Fabric needs to be able to access this state store and so a
dedicated user is created (note that the fabric
database hasn’t yet been created – that will be done soon using
the mysqlfabric
command):
[mysql@fab1 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "GRANT ALL ON fabric.* \ TO fabric@localhost";
All of the management requests that we make for MySQL Fabric will
be issued via the mysqlfabric
command. This command
is documented in the MySQL Fabric User Guide but sub-commands can
be viewed from the terminal using the list-commands
option:
[mysql@fab1 /]$ mysqlfabric list-commands group activate Activate a group. group import_topology Try to figure out the replication topology and import it into the state store. group deactivate Deactivate a group. group create Create a group. group remove Remove a server from a group. group add Add a server into group. group lookup_servers Return information on existing server(s) in a group. group check_group_availability Check if any server within a group has failed and report health information. group destroy Remove a group. group demote Demote the current master if there is one. group promote Promote a server into master. group lookup_groups Return information on existing group(s). group description Update group's description. manage list-commands List the possible commands. manage help Give help on a command. manage teardown Teardown Fabric Storage System. manage stop Stop the Fabric server. manage setup Setup Fabric Storage System. manage ping Check whether Fabric server is running or not. manage start Start the Fabric server. manage logging_level Set logging level. server set_weight Set a server's weight which determines the likelihood of a server being chosen by a connector to process transactions or by the high availability service to replace a failed master. server lookup_uuid Return server's uuid. server set_mode Set a server's mode which determines whether it can process read-only, read-write or both transaction types. server set_status Set a server's status. sharding move Move the shard represented by the shard_id to the destination group. sharding lookup_servers Lookup a shard based on the give sharding key. sharding disable_shard Disable a shard. sharding remove_mapping Remove the shard mapping represented by the Shard Mapping object. sharding list_mappings Returns all the shard mappings of a particular sharding_type. sharding add_mapping Add a table to a shard mapping. sharding add_shard Add a shard. sharding list_definitions Lists all the shard mapping definitions. sharding enable_shard Enable a shard. sharding remove_shard Remove a Shard. sharding prune_shard Given the table name prune the tables according to the defined sharding specification for the table. sharding lookup_mapping Fetch the shard specification mapping for the given table sharding split Split the shard represented by the shard_id into the destination group. sharding define Define a shard mapping. event trigger Trigger an event. event wait_for_procedures Wait until procedures, which are identified through their uuid in a list and separated by comma, finish their execution. store dump_shard_maps Return information about all shard mappings matching any of the provided patterns. store dump_shard_index Return information about the index for all mappings matching any of the patterns provided. store dump_servers Return information about all servers. store dump_sharding_information Return all the sharding information about the tables passed as patterns. store dump_shard_tables Return information about all tables belonging to mappings matching any of the provided patterns. store lookup_fabrics Return a list of Fabric servers.
MySQL Fabric has its own configuration file (note that it’s
location can vary depending on your platform and how MySQL
Utilities were installed). The contents of this configuration
file should be reviewed before starting the MySQL Fabric process
(in this case, the mysqldump_program
and
mysqldump_program
settings needed to be changed as
MySQL was installed in a user’s directory):
[root@fab1 mysql]# cat /etc/mysql/fabric.cfg [DEFAULT] prefix = sysconfdir = /etc/mysql logdir = /var/log [logging] url = file:///var/log/fabric.log level = INFO [storage] database = fabric user = fabric address = localhost:3306 connection_delay = 1 connection_timeout = 6 password = connection_attempts = 6 [connector] ttl = 1 [protocol.xmlrpc] threads = 5 address = localhost:8080 [executor] executors = 5 [sharding] mysqldump_program = /home/mysql/mysql/bin/mysqldump mysqlclient_program = /home/mysql/mysql/bin/mysql
The final step before starting the MySQL Fabric process is to create the MySQL Fabric schema within the state store:
[mysql@fab1 ~]$ mysqlfabric manage setup --param=storage.user=fabric [INFO] 1392298030.100127 - MainThread - Initializing persister: \ user (fabric), server (localhost:3306), database (fabric).
An optional step is then to check for yourself that the schema is indeed there:
[mysql@fab1 ~]$ mysql --protocol=tcp -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.6.16-log MySQL Community Server (GPL) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | fabric | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> use fabric;show tables; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed +-------------------+ | Tables_in_fabric | +-------------------+ | checkpoints | | group_replication | | groups | | servers | | shard_maps | | shard_ranges | | shard_tables | | shards | +-------------------+ 8 rows in set (0.00 sec)
The MySQL Fabric process can now be started; in this case the
process will run from the terminal from which it’s started but
the --daemonize
option can be used to make it run as
a daemon.
[mysql@fab1 ~]$ mysqlfabric manage start [INFO] 1392298245.888881 - MainThread - Fabric node starting. [INFO] 1392298245.890465 - MainThread - Initializing persister: user (fabric), server (localhost:3306), database (fabric). [INFO] 1392298245.890926 - MainThread - Loading Services. [INFO] 1392298245.898459 - MainThread - Starting Executor. [INFO] 1392298245.899056 - MainThread - Setting 5 executor(s). [INFO] 1392298245.900439 - Executor-1 - Started. [INFO] 1392298245.901856 - Executor-2 - Started. [INFO] 1392298245.903146 - Executor-0 - Started. [INFO] 1392298245.905488 - Executor-3 - Started. [INFO] 1392298245.908283 - MainThread - Executor started. [INFO] 1392298245.910308 - Executor-4 - Started. [INFO] 1392298245.936954 - MainThread - Starting failure detector. [INFO] 1392298245.938200 - XML-RPC-Server - XML-RPC protocol server \ ('127.0.0.1', 8080) started. [INFO] 1392298245.938614 - XML-RPC-Server - Setting 5 XML-RPC session(s). [INFO] 1392298245.940895 - XML-RPC-Session-0 - Started XML-RPC-Session. [INFO] 1392298245.942644 - XML-RPC-Session-1 - Started XML-RPC-Session. [INFO] 1392298245.947016 - XML-RPC-Session-2 - Started XML-RPC-Session. [INFO] 1392298245.949691 - XML-RPC-Session-3 - Started XML-RPC-Session. [INFO] 1392298245.951678 - XML-RPC-Session-4 - Started XML-RPC-Session.
If the process had been run as a daemon then it’s useful to be able to check if it’s actually running:
[mysql@fab1 ~]$ mysqlfabric manage ping Command : { success = True return = True activities = }
At this point, MySQL Fabric is up and running but it has no MySQL Servers to manage. As shown in the earlier diagram, three MySQL Servers will run on a single machine. Each of those MySQL Servers will need their own configuration settings to make sure that there are no resource conflicts – the steps are shown here but without any detailed commentary as this is standard MySQL stuff:
[mysql@fab2 myfab]$ cat my1a.cnf [mysqld] datadir=/home/mysql/myfab/data1a basedir=/home/mysql/mysql socket=/home/mysql/myfab/mysqlfab1a.socket binlog-format=ROW log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 port=3306 report-host=fab2 report-port=3306 server-id=11 log-bin=fab1a-bin.log [mysql@fab2 myfab]$ cat my1b.cnf [mysqld] datadir=/home/mysql/myfab/data1b basedir=/home/mysql/mysql socket=/home/mysql/myfab/mysqlfab1b.socket binlog-format=ROW log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 port=3307 report-host=fab2 report-port=3307 server-id=12 log-bin=fab1b-bin.log [mysql@fab2 myfab]$ cat my1c.cnf [mysqld] datadir=/home/mysql/myfab/data1c basedir=/home/mysql/mysql socket=/home/mysql/myfab/mysqlfab1c.socket binlog-format=ROW log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 port=3308 report-host=fab2 report-port=3308 server-id=13 log-bin=fab1c-bin.log [mysql@fab2 myfab]$ mkdir data1a [mysql@fab2 myfab]$ mkdir data1b [mysql@fab2 myfab]$ mkdir data1c [mysql@fab2 mysql]$ scripts/mysql_install_db --basedir=/home/mysql/mysql/ \ --defaults-file=/home/mysql/myfab/my1a.cnf \ --datadir=/home/mysql/myfab/data1a/ [mysql@fab2 mysql]$ scripts/mysql_install_db --basedir=/home/mysql/mysql/ \ --defaults-file=/home/mysql/myfab/my1a.cnf \ --datadir=/home/mysql/myfab/data1b/ [mysql@fab2 mysql]$ scripts/mysql_install_db --basedir=/home/mysql/mysql/ \ --defaults-file=/home/mysql/myfab/my1a.cnf \ --datadir=/home/mysql/myfab/data1c/ [mysql@fab2 ~]$ mysqld --defaults-file=/home/mysql/myfab/my1a.cnf & [mysql@fab2 ~]$ mysqld --defaults-file=/home/mysql/myfab/my1b.cnf & [mysql@fab2 ~]$ mysqld --defaults-file=/home/mysql/myfab/my1c.cnf & [mysql@fab2 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "GRANT ALL ON *.* \ TO root@'%'"" [mysql@fab2 ~]$ mysql -h 127.0.0.1 -P3307 -u root -e "GRANT ALL ON *.* \ TO root@'%'"" [mysql@fab2 ~]$ mysql -h 127.0.0.1 -P3308 -u root -e "GRANT ALL ON *.* \ TO root@'%'""
Now that the MySQL Servers are configured and up and running it’s
possible to create the new HA Group (my_group
and
add the three new MySQL Server instances to it):
[mysql@fab1 ~]$ mysqlfabric group create my_group Procedure : { uuid = 3dadcedf-a402-420d-8496-03cb5c17c1b3, finished = True, success = True, return = True, activities = } [mysql@fab1 ~]$ mysqlfabric group add my_group 192.168.56.102:3306 root '' Procedure : { uuid = 2d996228-2a05-490a-9f32-10bea383d72d, finished = True, success = True, return = True, activities = } [mysql@fab1 ~]$ mysqlfabric group add my_group 192.168.56.102:3307 root '' Procedure : { uuid = 00bacfe8-ffc0-4c3c-bf06-d9f991ff2ba2, finished = True, success = True, return = True, activities = } [mysql@fab1 ~]$ mysqlfabric group add my_group 192.168.56.102:3308 root '' Procedure : { uuid = 08b7bc0f-0224-4241-8efb-7d73fc50ad09, finished = True, success = True, return = True, activities = }
The mysqlfabric
command can then be used to confirm
that the HA group now contains the three servers but
that they’re all still tagged as being Secondaries (in other
words there is no MySQL Replication master):
[mysql@fab1 ~]$ mysqlfabric group lookup_servers my_group Command : { success = True return = [['926546e1-94b9-11e3-8cab-08002795076a', \ '192.168.56.102:3306', False, 'SECONDARY'], \ ['9c08ecd6-94b9-11e3-8cab-08002795076a', \ '192.168.56.102:3307', \ False, 'SECONDARY'],\ ['a4a963a1-94b9-11e3-8cac-08002795076a', \ '192.168.56.102:3308', \ False, 'SECONDARY']] activities = } [mysql@fab1 ~]$ mysqlfabric group check_group_availability my_group Command : { success = True return = {'926546e1-94b9-11e3-8cab-08002795076a': {'is_master': False, 'status': 'SECONDARY', 'is_alive': True, \ 'threads': {'is_configured': False}},\ 'a4a963a1-94b9-11e3-8cac-08002795076a': \ {'is_master': False, 'status': 'SECONDARY', \ 'is_alive': True, 'threads': {'is_configured': False}},\ '9c08ecd6-94b9-11e3-8cab-08002795076a': \ {'is_master': False, 'status': 'SECONDARY', \ 'is_alive': True, 'threads': {'is_configured': False}}} activities = }
mysqlfabric group promote
is used to promote one of
the servers within the my_group
HA group to be the
Primary/master:
[mysql@fab1 ~]$ mysqlfabric group promote my_group Procedure : { uuid = 8c1d888e-8120-4be3-8ff6-1e9a810179fd, finished = True, success = True, return = True, activities = }
Note that it would have been possible to include the
uuid
of the specific MySQL Server that should be
promoted but as none was specified, the best way to know which
was selected is to query the state information:
[mysql@fab1 ~]$ mysqlfabric group lookup_servers my_group Command : { success = True return = [['926546e1-94b9-11e3-8cab-08002795076a', \ '192.168.56.102:3306', True, 'PRIMARY'],\ ['9c08ecd6-94b9-11e3-8cab-08002795076a', '192.168.56.102:3307', \ False, 'SECONDARY'],\ ['a4a963a1-94b9-11e3-8cac-08002795076a', '192.168.56.102:3308', \ False, 'SECONDARY']] activities = }
As an extra step, we can confirm that one of the other servers is indeed acting as a replication slave to the master:
[mysql@fab1 ~]$ mysql -h 192.168.56.102 -P3307 -u root -e "SHOW SLAVE STATUS\G" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.102 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: fab1a-bin.000003 Read_Master_Log_Pos: 314 Relay_Log_File: fab2-relay-bin.000002 Relay_Log_Pos: 524 Relay_Master_Log_File: fab1a-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 314 Relay_Log_Space: 727 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 11 Master_UUID: 926546e1-94b9-11e3-8cab-08002795076a Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 926546e1-94b9-11e3-8cab-08002795076a:1 Executed_Gtid_Set: 926546e1-94b9-11e3-8cab-08002795076a:1, 9c08ecd6-94b9-11e3-8cab-08002795076a:1 Auto_Position: 1 1 row in set (0.01 sec)
The final step in configuring the HA system is to have MySQL Fabric start monitoring the servers so that it can promote a new Primary (and send new routing information to the connectors) in the event that the current Primary should fail:
[mysql@fab1 ~]$ mysqlfabric group activate my_group Procedure : { uuid = 2fea76ce-bc6a-4cd3-84ed-fbe3b75128a9, finished = True, success = True, return = True, activities = }
Now it’s the turn of the application developer to start using the new HA database server. Note that while they must make some minor changes to work with the HA group, they don’t need to care about what servers are part of the group or which of them is currently the Primary – this is all handled transparently by MySQL Fabric and the Fabric-aware connectors.
The first piece of example code will create the
subscribers
table within the test
database and add a single subscriber record. Most of this is
fairly standard and so only the MySQL Fabric-specific pieces will
be commented on:
- The
fabric
module frommysql.com
is included - The application connects to MySQL Fabric rather than any of
the MySQL Servers (
{"host" : "localhost", "port" : 8080}
) - The
mode
property for the connection is set tofabric.MODE_READWRITE
– in that way the operations are sent to the Primary server by the connector so that the changes will be replicated to all servers in the HA group.
[mysql@fab1 myfab]$ cat setup_table.py import mysql.connector from mysql.connector import fabric def add_subscriber(conn, sub_no, first_name, last_name): conn.set_property(group="my_group", mode=fabric.MODE_READWRITE) cur = conn.cursor() cur.execute( "INSERT INTO subscribers VALUES (%s, %s, %s)", (sub_no, first_name, last_name) ) conn = mysql.connector.connect( fabric={"host" : "localhost", "port" : 8080}, user="root", database="test", password="", autocommit=True ) conn.set_property(group="my_group", mode=fabric.MODE_READWRITE) cur = conn.cursor() cur.execute( "CREATE TABLE IF NOT EXISTS subscribers (" " sub_no INT, " " first_name CHAR(40), " " last_name CHAR(40)" ")" ) cur.execute( "DELETE FROM subscribers" ) add_subscriber(conn, 72, "Billy", "Fish")
This code can then be run:
[mysql@fab1 myfab]$ python setup_table.py
To check that everything has worked as expected, one of the slave servers can be checked to confirm that the table and data is there:
[mysql@fab2 ~]$ mysql -h 127.0.0.1 -P3308 -u root -e 'SELECT * \ FROM test.subscribers' +--------+------------+-----------+ | sub_no | first_name | last_name | +--------+------------+-----------+ | 72 | Billy | Fish | +--------+------------+-----------+
The next piece of code can then be run to read the record back.
The main thing to note in this sample is the connection’s
mode
property is set to
fabric.MODE_READONLY
which means that the connector
is free to send the query to one of the slaves (optionally, you
can configure MySQL Fabric to include the master in the
connector’s round-robin algorithm).
[mysql@fab1 myfab]$ cat read_table.py import mysql.connector from mysql.connector import fabric def find_subscriber(conn, sub_no): conn.set_property(group="my_group", mode=fabric.MODE_READONLY) cur = conn.cursor() cur.execute( "SELECT first_name, last_name FROM subscribers " "WHERE sub_no = %s", (sub_no, ) ) for row in cur: print row conn = mysql.connector.connect( fabric={"host" : "localhost", "port" : 8080}, user="root", database="test", password="", autocommit=True ) find_subscriber(conn, 72)
This script can then be executed to retrieve the data:
[mysql@fab1 myfab]$ python read_table.py (u'Billy', u'Fish')
The final stage is to check that things work as planned when the Primary server stops; in other words:
- MySQL Fabric will make one of the slaves be the new master
- MySQL Fabric will update the state/routing data to reflect the new Primary server
- The Fabric-aware connector is informed of the change, updates its cache and routes to the correct Primary
Before stopping a MySQL Server, we can confirm which one is currently the Primary before shutting it down:
[mysql@fab1 myfab]$ mysqlfabric group lookup_servers my_group Command : { success = True return = [['926546e1-94b9-11e3-8cab-08002795076a', \ '192.168.56.102:3306', True, 'PRIMARY'],\ ['9c08ecd6-94b9-11e3-8cab-08002795076a', '192.168.56.102:3307', \ False, 'SECONDARY'],\ ['a4a963a1-94b9-11e3-8cac-08002795076a', '192.168.56.102:3308', \ False, 'SECONDARY']] activities = } [mysql@fab2 ~]$ mysqladmin -h 127.0.0.1 -P3306 -u root shutdown
The mysqlfabric
command can then be used to confirm
the state change (promotion of a new Primary server):
[mysql@fab1 myfab]$ mysqlfabric group lookup_servers my_group Command : { success = True return = [['926546e1-94b9-11e3-8cab-08002795076a', \ '192.168.56.102:3306', False, 'FAULTY'],\ ['9c08ecd6-94b9-11e3-8cab-08002795076a', \ '192.168.56.102:3307', True, 'PRIMARY'],\ ['a4a963a1-94b9-11e3-8cac-08002795076a', \ '192.168.56.102:3308', False, 'SECONDARY']] activities = }
The following code reads the data but because it sets the
mode
property to fabric.MODE_READWRITE
the connector will send the query to the Primary (this is how you
can ensure that reads are not accessing stale data from a slave):
[mysql@fab1 myfab]$ cat read_table2.py import mysql.connector from mysql.connector import fabric def find_subscriber(conn, sub_no): conn.set_property(group="my_group", mode=fabric.MODE_READWRITE) cur = conn.cursor() cur.execute( "SELECT first_name, last_name FROM subscribers " "WHERE sub_no = %s", (sub_no, ) ) for row in cur: print row conn = mysql.connector.connect( fabric={"host" : "localhost", "port" : 8080}, user="root", database="test", password="", autocommit=True ) find_subscriber(conn, 72)
[mysql@fab1 myfab]$ python read_table.py (u'Billy', u'Fish')
Typically, the failed MySQL Server would be recovered and then it makes sense to add it back into the HA group:
[mysql@fab2 ~]$ mysqld --defaults-file=myfab/my1a.cnf& [mysql@fab1 myfab]$ mysqlfabric server set_status \ 926546e1-94b9-11e3-8cab-08002795076a SPARE my_group Procedure : { uuid = 2b6e9a93-6f1f-4ed4-944b-21ddd41df337, finished = True, success = True, return = True, activities = } [mysql@fab1 myfab]$ mysqlfabric server set_status \ 926546e1-94b9-11e3-8cab-08002795076a SECONDARY my_group Procedure : { uuid = 2e7aa916-f099-42f0-b79b-f0d25cadb3f3, finished = True, success = True, return = True, activities = } [mysql@fab1 myfab]$ mysqlfabric group check_group_availability my_group Command : { success = True return = {'926546e1-94b9-11e3-8cab-08002795076a': {'is_master': False,\ 'status': 'SECONDARY', 'is_alive': True, 'threads': {}},\ 'a4a963a1-94b9-11e3-8cac-08002795076a': {'is_master': False, \ 'status': 'SECONDARY', 'is_alive': True, 'threads': {}},\ '9c08ecd6-94b9-11e3-8cab-08002795076a': {'is_master': True, \ 'status': 'PRIMARY', 'is_alive': True, 'threads': {}}} activities = }
MySQL Fabric Architecture & Extensibility
MySQL Fabric has been architected for extensibility at a number of levels. For example, in the first release the only option for implementing HA is based on MySQL Replication but in future releases we hope to add further options (for example, MySQL Cluster). We also hope to see completely new applications around the managing of farms of MySQL Servers – both from Oracle and the wider MySQL community.
The following diagram illustrates how new applications and protocols can be added using the pluggable framework.
We really hope that people try out MySQL Fabric and let us know how you get on; one way is to comment on this post, another is to post to the MySQL Fabric forum or if you think you’ve found a bug then raise a bug report.