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:
1 2 3 4 5 6 | [vagrant@store ~]$ mysqlfabric group lookup_servers mycluster Command : { success = True return = [{'status': 'SECONDARY', 'server_uuid': '3084fcf2-df86-11e3-b46c-0800274fb806', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.70.101'}, {'status': 'SECONDARY', 'server_uuid': '35cc3529-df86-11e3-b46c-0800274fb806', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.70.102'}, {'status': 'PRIMARY', 'server_uuid': '3d3f6cda-df86-11e3-b46c-0800274fb806', 'mode': 'READ_WRITE', 'weight': 1.0, 'address': '192.168.70.103'}] activities = } |
For our tests, we will be using this simple script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | import mysql.connector from mysql.connector import fabric from mysql.connector import errors import time config = { 'fabric': { 'host': '192.168.70.100', 'port': 8080, 'username': 'admin', 'password': 'admin', 'report_errors': True }, 'user': 'fabric', 'password': 'f4bric', 'database': 'test', 'autocommit': 'true' } fcnx = None print "starting loop" while 1: if fcnx == None: print "connecting" fcnx = mysql.connector.connect(**config) fcnx.set_property(group='mycluster', mode=fabric.MODE_READWRITE) try: print "will run query" cur = fcnx.cursor() cur.execute("select id, sleep(0.2) from test.test limit 1") for (id) in cur: print id print "will sleep 1 second" time.sleep(1) except errors.DatabaseError: print "sleeping 1 second and reconnecting" time.sleep(1) del fcnx fcnx = mysql.connector.connect(**config) fcnx.set_property(group='mycluster', mode=fabric.MODE_READWRITE) fcnx.reset_cache() try: cur = fcnx.cursor() cur.execute("select 1") except errors.InterfaceError: fcnx = mysql.connector.connect(**config) fcnx.set_property(group='mycluster', mode=fabric.MODE_READWRITE) fcnx.reset_cache() |
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:
1 2 3 | mysql> create database if not exists test; mysql> create table if not exists test.test (id int unsigned not null auto_increment primary key) engine = innodb; mysql> insert into test.test values (null); |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | mysql> select @@hostname; +-------------+ | @@hostname | +-------------+ | node3.local | +-------------+ 1 row in set (0.00 sec) mysql> show processlist; +----+--------+--------------------+------+------------------+------+-----------------------------------------------------------------------+----------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+--------+--------------------+------+------------------+------+-----------------------------------------------------------------------+----------------------------------------------+ | 5 | fabric | store:39929 | NULL | Sleep | 217 | | NULL | | 6 | fabric | node1:37999 | NULL | Binlog Dump GTID | 217 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 7 | fabric | node2:49750 | NULL | Binlog Dump GTID | 216 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 16 | root | localhost | NULL | Query | 0 | init | show processlist | | 20 | fabric | 192.168.70.1:55889 | test | Query | 0 | User sleep | select id, sleep(0.2) from test.test limit 1 | +----+--------+--------------------+------+------------------+------+-----------------------------------------------------------------------+----------------------------------------------+ 5 rows in set (0.00 sec) [vagrant@node3 ~]$ sudo service mysqld stop Stopping mysqld: [ OK ] |
While this happens, here’s the output from the script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | will sleep 1 second will run query (1, 0) will sleep 1 second will run query (1, 0) will sleep 1 second will run query (1, 0) will sleep 1 second will run query sleeping 1 second and reconnecting will run query (1, 0) will sleep 1 second will run query (1, 0) will sleep 1 second will run query (1, 0) |
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:
1 2 3 | fcnx = mysql.connector.connect(**config) fcnx.set_property(group='mycluster', mode=fabric.MODE_READWRITE) fcnx.reset_cache() |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | will run query (1, 0) will sleep 1 second will run query sleeping 1 second and reconnecting will run query Traceback (most recent call last): File "./reader_test.py", line 34, in cur = fcnx.cursor() File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 1062, in cursor self._connect() File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 1012, in _connect exc)) mysql.connector.errors.InterfaceError: Error getting connection: No MySQL server available for group 'mycluster' |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | Traceback (most recent call last): File "./reader_test.py", line 35, in cur.execute("select id, sleep(0.2) from test.test limit 1") File "/Library/Python/2.7/site-packages/mysql/connector/cursor.py", line 491, in execute self._handle_result(self._connection.cmd_query(stmt)) File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 1144, in cmd_query self.handle_mysql_error(exc) File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 1099, in handle_mysql_error self.reset_cache() File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 832, in reset_cache self._fabric.reset_cache(group=group) File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 369, in reset_cache self.get_group_servers(group, use_cache=False) File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 478, in get_group_servers inst = self.get_instance() File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 390, in get_instance if not inst.is_connected: File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 772, in is_connected self._proxy._some_nonexisting_method() # pylint: disable=W0212 File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/xmlrpclib.py", line 1224, in __call__ return self.__send(self.__name, args) File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/xmlrpclib.py", line 1578, in __request verbose=self.__verbose File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 272, in request raise InterfaceError("Connection with Fabric failed: " + msg) mysql.connector.errors.InterfaceError: Connection with Fabric failed: |
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.
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
@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?
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
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?
@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.
@Fernando Ipar 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.
@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 (https://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 🙂
@Fernando Ipar Ipar
Thank you very much. I got it.
I am looking forward to the recorded webinar : )
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?
@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.
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.
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!
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.
@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()
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!
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’
@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.
@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
I have configured mysql fabric as you described. However, when running python code i get this error:
mysql.connector.errors.InterfaceError: Reported faulty server to Fabric (1045 (28000): Access denied for user ‘root’@’ip’ (using password: YES))
What did i miss?
Hello ho_oh:
The error implies you’re attempting to connect to the nodes via fabric using the root account. For that to work, the ‘root’@’ip’ account must have the required privileges on the nodes.
I think you should be creating a dedicated account for clients to connect via fabric (actually, one per app, if this was production), and make sure that has the needed privileges on the data nodes. Using root (or another account, but granting it SUPER privilege) is usually not a good idea, but it’s specially not good in the context of using Fabric for HA, as Fabric uses the read_only flag to prevent writes to nodes in the Secondary role, and by using root, you could inadvertently work around that.