November 22, 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 : )

  9. Tim says:

    I set up an HA group with two MySQL server A and B and promote A as PRIMARY. Then, I shutdown the mysqld at the server A.

    MySQL Fabric detects the server A is unreachable serveral times, and decides to promote B automatically.
    [INFO] timestamp – Executor-0 – Master has changed from A to B.
    The server B is now PRIMARY and in read_write mode. It can accept write commands (e.g. insert) successfully.

    I wonder whether the server B has replicated all data from the server A before the server A is shut down.
    Will there be any data loss during the procedure?
    Are there any settings of MySQL Fabric to prevent this?

    What commands should I issue when I start the mysqld at server A again to restore the original HA group configuration?
    What I mean is:
    1. Set server A as PRIMARY and in read_write mode again.
    2. Set server B as SECONDARY and in read_only mode again.
    Will the server A synchronize all data modification from server B when the server A is down?
    Will there be any data loss during the HA configuration restore procedure?

  10. @Tim:

    A proper answer to these questions requires a blog post on its own, not just for the length of the replies, but because I need to dig a bit more in the code to get a deeper understanding of all the moving pieces here :) And by ‘requires a blog post on its own’ I mean we’ll write that post, so stay tuned.

    However, I can provide you with some quick replies:
    – Fabric won’t promote any node unless it was up to date with the last changes in the master. I can back this statement up with anecdotical evidence from one test:

    [WARNING] 1406811190.499863 – Executor-1 – Reported issue (2003) for server (95f10d4c-17fc-11e4-a49a-0800274fb806).
    [INFO] 1406811190.516969 – Executor-1 – Master (95f10d4c-17fc-11e4-a49a-0800274fb806) in group (salaries-2) has been lost.
    [INFO] 1406811862.767696 – Executor-1 – Master has changed from 95f10d4c-17fc-11e4-a49a-0800274fb806 to 8a58e1e4-180f-11e4-a516-0800274fb806.

    For this, I introduced artificial lag on the SECONDARY host by stopping replication on it for a bit, while a heavy insert load was being run on the master. Fabric waited for this node to catch up before promoting it to PRIMARY, which means that any MODE_READWRITE connections to the group did not work during the catch up time.

    This was only a test as an end user, without looking at the code to understand what it does, so I have some questions of my own that I want to answer with another blog post. One of them is what happens if the SECONDARY is not able to catch up completely, because some binlog events were lost when the master crashed. Since Fabric requires the enforce-gtid-consistency flag to be set, I suspect this scenario cannot happen, but I need to confirm that suspicion.

    As for the way to restore the original master, you need to:
    – Set it to SPARE, then SECONDARY (Fabric won’t check lag when setting a node to SECONDARY so it may be behind in replication in this state)
    – Set it to PRIMARY. The way I’d recommend to do this is to promote the group, using the –slave_id option with the hosts’ uuid. If it’s behind in replication when you do this, the ‘mysqlfabric group promote’ command will block until it catches up, and only then will it complete the promotion (and the corresponding demotion of the previous PRIMARY).

    That said, typically you’ll want all the nodes on a group to be of equal capacity, so after a failover, the standard practice (as I usually see in the wild, at least) is to just let the PRIMARY role on another node until that one fails/has to be demoted.

    So to wrap up, Fabric takes a lot of steps to prevent any data loss during failover, but I think there are enough details involved to merit a separate blog post to discuss them.

  11. Tim says:

    Thank you a lot!
    Let me give you a big hand!
    I got the overall concept.

    I highly anticipate your blog post about it. Besides, I would like to know how to solve the problem if “SECONDARY is not able to catch up completely” or something like replication errors if it happens.

  12. Sergio says:

    Hi Fernando,

    I can’t understand very well how to config the connector for load balancing.
    In the above example, I need to put the work mode READWRITE or READONLY for each SQL.
    But, I can’t demand change all the code in my application.
    Can I config the connector for automatic load balancing?
    Is not simply change my actual string connection to the MySQL Server by MySQL Fabric?

    Thanks in advance!

    Regards!

  13. Tim says:

    Hi Fernando,

    1.
    I would like to connect to MySQL Fabric with PHP. Therefore, I attempted to check http://dev.mysql.com/doc/mysql-utilities/1.4/en/fabric.html in vain. There are only Connector/Python with MySQL Fabric and Java Connector/J with MySQL Fabric documents.

    Is it possible to connect to MySQL Fabric with PHP?

    2.
    What is the equivalent API in Java to the “fcnx.reset_cache()” in Python? I check http://dev.mysql.com/doc/mysql-utilities/1.4/en/connector-j-fabric-reference-api.html but I have no idea which is the one I need.

  14. @Sergio: Sorry for the delay, I’ve been on vacation for the last couple of weeks. I think what you need is not actually load balancing, but r/w splitting. If my understanding is correct, then that’s not supported by the connectors I’ve tried so far, and, in general, it’s something tricky to get right, since seemingly read only queries may end up changing data as a side effect of triggers or functions, for example. So in summary, if you can’t change application code, I don’t think it will be easy to do load balancing with Fabric right now.

    @Tim:

    1. What you want is the mysqlnd driver (http://dev.mysql.com/downloads/connector/php-mysqlnd/). The documentation to use it with Fabric was a bit lacking the last time I checked, but I’d recommend you to read this blog post: http://blog.ulf-wendel.de/2014/the-early-mysql-fabric-sharding-support-for-php/

    2. I have been unable to use the driver yet due to http://bugs.mysql.com/bug.php?id=72713, but from reading http://dev.mysql.com/doc/mysql-utilities/1.5/en/connector-j-fabric-reference-api.html, my understanding is that what you want is clearServerSelectionCriteria()

  15. Sergio says:

    Hi Fernando!

    No problem! Welcome back :)

    Thanks a lot for your help.
    I have disdained Fabric for load balancing because I think the same, Fabric is not ready for load balancing yet.

    Regards!

  16. mkman says:

    setup is failing. plz help! all i was trying to do was provision and it failed. when i did a teardown, then ran a setup this happened.

    [root@store ~]# mysqlfabric manage setup
    [INFO] 1411890414.551449 – MainThread – Initializing persister: user (fabric), server (localhost:3306), database (fabric).
    Traceback (most recent call last):
    File “/usr/bin/mysqlfabric”, line 429, in
    main()
    File “/usr/bin/mysqlfabric”, line 410, in main
    fire_command(cmd, *cargs)
    File “/usr/bin/mysqlfabric”, line 339, in fire_command
    result = command.dispatch(*(command.append_options_to_args(args)))
    File “/usr/lib/python2.6/site-packages/mysql/fabric/services/manage.py”, line 170, in dispatch
    _persistence.MySQLPersister())
    File “/usr/lib/python2.6/site-packages/mysql/fabric/credentials.py”, line 502, in check_initial_setup
    username = config.get(section, ‘user’)
    File “/usr/lib64/python2.6/ConfigParser.py”, line 532, in get
    raise NoSectionError(section)
    ConfigParser.NoSectionError: No section: ‘protocol.mysql’

  17. @mkman: This is most likely due to the latest Utilities/Fabric release (See http://mysqlhighavailability.com/announcing-mysql-utilities-release-1-5-2/) and some backwards incompatible configuration change.

    I’ll fix the Vagrant environment shortly and will let everyone know on the comments.

  18. @mkman: This should be resolved now. I also forked the repo into the percona org for the future, so you can just clone this too: https://github.com/percona/vagrant-fabric

Speak Your Mind

*