July 31, 2014

High Availability with MySQL Fabric: Part II

This is the third post in our MySQL Fabric series. If you missed the previous two, we started with an overall introduction, and then a discussion of MySQL Fabric’s high-availability (HA) features. MySQL Fabric was RC when we started this series, but it went GA recently. You can read the press release here, and see this blog post from Oracle’s Mats Kindahl for more details. In our previous post, we showed a simple HA setup managed with MySQL Fabric, including some basic failure scenarios. Today, we’ll present a similar scenario from an application developer’s point of view, using the Python Connector for the examples. If you’re following the examples on these posts, you’ll notice that the UUID for servers will be changing. That’s because we rebuild the environment between runs. Symbolic names stay the same though. That said, here’s our usual 3 node setup:

For our tests, we will be using this simple script:

This simple script requests a MODE_READWRITE connection and then issues selects in a loop. The reason it requests a RW connector is that it makes it easier for us to provoke a failure, as we have two SECONDARY nodes that could be used for queries if we requested a MODE_READONLY connection. The select includes a short sleep to make it easier to catch it in SHOW PROCESSLIST. In order to work, this script needs the test.test table to exist in the mycluster group. Running the following statements in the PRIMARY node will do it:

Dealing with failure

With everything set up, we can start the script and then cause a PRIMARY failure. In this case, we’ll simulate a failure by shutting down mysqld on it:

While this happens, here’s the output from the script:

The ‘sleeping 1 second and reconnecting’ line means the script got an exception while running a query (when the PRIMARY node was stopped, waited one second and then reconnected. The next lines confirm that everything went back to normal after the reconnection. The relevant piece of code that handles the reconnection is this:

If fcnx.reset_cache() is not invoked, then the driver won’t connect to the xml-rpc server again, but will use it’s local cache of the group’s status instead. As the PRIMARY node is offline, this will cause the reconnect attempt to fail. By reseting the cache, we’re forcing the driver to connect to the xml-rpc server and fetch up to date group status information. If more failures happen and there is no PRIMARY (or candidate for promotion) node in the group, the following error is received:

Running without MySQL Fabric

As we have discussed in previous posts, the XML-PRC server can become a single point of failure under certain circumstances. Specifically, there are at least two problem scenarios when this server is down:

  • When a node goes down
  • When new connection attempts are made

The first case is obvious enough. If MySQL Fabric is not running and a node fails, there won’t be any action, and clients will get an error whenever they send a query to the failed node. This is worse if the PRIMARY fails, as failover won’t happen and the cluster will be unavailable for writes. The second case means that while MySQL Fabric is not running, no new connections to the group can be established. This is because when connecting to a group, MySQL Fabric-aware clients first connect to the XML-RPC server to get a list of nodes and roles, and only then use their local cache for decisions. A way to mitigate this is to use connection pooling, which reduces the need for establishing new connections, and therefore minimises the chance of failure due to MySQL Fabric being down. This, of course, is assuming that something is monitoring MySQL Fabric ensuring some host provides the XML-PRC service. If that is not the case, failure will be delayed, but it will eventually happen anyway. Here is an example of what happens when MySQL Fabric is down and the PRIMARY node goes down:

This happens when a new connection attempt is made after resetting the local cache.

Making sure MySQL Fabric stays up

As of this writing, it is the user’s responsibility to make sure MySQL Fabric is up and running. This means you can use whatever you feel comfortable with in terms of HA, like Pacemaker. While it does add some complexity to the setup, the XML-RPC server is very simple to manage and so a simple resource manager should work. For the backend, MySQL Fabric is storage engine agnostic, so an easy way to resolve this could be to use a small MySQL Cluster set up to ensure the backend is available. MySQL’s team blogged about such a set up here. We think the ndb approach is probably the simplest for providing HA at the MySQL Fabric store level, but believe that MySQL Fabric itself should provide or make it easy to achieve HA at the XML-RPC server level. If ndb is used as store, this means any node can take a write, which in turns means multiple XML-PRC instances should be able to write to the store concurrently. This means that in theory, improving this could be as easy as allowing Fabric-aware drivers to get a list of Fabric servers instead of a single IP and port to connect to.

What’s next

In the past two posts, we’ve presented MySQL Fabric’s HA features, seen how it handles failures at the node level, how to use MySQL databases with a MySQL Fabric-aware driver, and what remains unresolved for now. In our next post, we’ll review MySQL Fabric’s Sharding features.

About Fernando Ipar

Fernando is part of Percona's team working as Senior Consultant. Prior to joining Percona, Fernando worked as a consultant for financial services institutions, telcos, and technology providers.

Comments

  1. MWM says:

    Dear Fernando,

    Do we have switchover command in MySQL Fabric like we have in mysqlrpladmin utility.

    Once our down server gets up and become part of the Group as Secondary then deliberately down the primary server is not a good option. We have the option of server-status to change the primary one to secondary and secondary to primary. What is the best approach to resolve this issue.

    Regards
    MWM

  2. @MWM: I think an analogue in Fabric to mysqlrpladmin’s switchover command would be the group promote command, when specifying an explicit slave id.

    For example, suppose the cluster looks like this:

    > [vagrant@store ~]$ mysqlfabric group health mycluster
    > Command :
    > { success = True
    > return = {‘ae27d4c7-e752-11e3-a747-0800274fb806′: {‘status’: ‘SECONDARY’, ‘is_alive’: True, ‘threads’: {}}, ‘c4a19aad-e752-11e3-a747-0800274fb806′: {‘status’: ‘SECONDARY’, ‘is_alive’: True, ‘threads’: {}}, ‘b8816184-e752-11e3-a747-0800274fb806′: {‘status’: ‘PRIMARY’, ‘is_alive’: True, ‘threads’: {}}}
    > activities =
    > }

    You can change the PRIMARY role to the first node in the list with this command:

    > [vagrant@store ~]$ mysqlfabric group promote mycluster –slave_id=ae27d4c7-e752-11e3-a747-0800274fb806
    > Procedure :
    > { uuid = 911e77e3-fafa-4881-a3a9-2fffa57594fc,
    > finished = True,
    > success = True,
    > return = True,
    > activities =
    > }
    > [vagrant@store ~]$ mysqlfabric group health mycluster
    > Command :
    > { success = True
    > return = {‘ae27d4c7-e752-11e3-a747-0800274fb806′: {‘status’: ‘PRIMARY’, ‘is_alive’: True, ‘threads’: {}}, ‘c4a19aad-e752- 11e3-a747-0800274fb806′: {‘status’: ‘SECONDARY’, ‘is_alive’: True, ‘threads’: {}}, ‘b8816184-e752-11e3-a747-0800274fb806′: {‘status’: ‘SECONDARY’, ‘is_alive’: True, ‘threads’: {}}}
    > activities =
    > }

    Does that answer your question?

  3. MWM says:

    Thank you Fernando. Its the answer.

    Few more questions coming into my mind regarding data replication and data integrity / data loss. Let me first experiment few scenarios then ill come back to blog soon. :)

    Regards
    MWM

  4. Tim says:

    How can I transfer my exisitng MySQL database to the HA group managed by MySQL Fabric?
    Is it possible to use the mysqldump utility to dump database into MySQL Fabric?

  5. @Tim:

    Fabric itself is just a framework to manage MySQL servers, it does not actually handle application data, this is still done by individual MySQL Servers.

    A lot of mysqlfabric commands support the –update_only option, which updates Fabric’s metadata but does not make any changes on servers (i.e. does not set up replication), so depending on how your existing set up is, perhaps you can use this to start using Fabric to manage it.

  6. Tim says:

    @Fernando Ipar Thank you for your timely reply.

    What I am confused is that I think we need Fabric aware connectors to access the servers managed by Fabric. Therefore, I think the destination server for the mysqldump is the Fabric manage node and it will assign server to handle SQL commands according to my Fabric configuartion.

    To be clear, the commands in my mind are:
    1. mysqldump -u xxx -h existing_mysql_host src_database > dump.sql
    2. mysql -u xxx -h mysql_fabric_manage_node_host < dump.sql
    .

    Are the commands above feasible?

    Please feel free to correct me if I am wrong.

  7. @Tim:

    You’re right about the connectors, but the reason you need them is to decide the MySQL server to connect to. What I mean by this is that the connectors make routing decision depending on HA and Sharding configuration and status, so, for example, you can ask for a R/W connection to group X, and the connector returns a (normal) MySQL connection to the PRIMARY server in that group.

    Now, that is needed for day to day operations, but for a migration, you’ll usually know what is the PRIMARY server, and you can load a dump directly against it. That said, I think you probably don’t even need to do a dump and restore.

    If you already have a master->replica setup working, you just need to use the mysqlfabric with the relevant commands (group create, group add, etc) and the –update_only option, so that Fabric has information about your nodes on it’s data store. From that point on, you can use the connectors as described, and the mysqlfabric utility to manage the Group too.

    If you have a single server, I’d recommend cloning an instance and setting up as a replica, and then going back to the previous step.

    Hope that answers your question.

    If you’re interested, we’ll be doing a webinar on this on Wednesday (http://www.percona.com/resources/mysql-webinars/putting-mysql-fabric-use) with a Q&A section where I may be able to better answer this by speaking instead of typing :)

  8. Tim says:

    @Fernando Ipar

    Thank you very much. I got it.
    I am looking forward to the recorded webinar : )

Speak Your Mind

*