Accessing Fabric HA Groups from Java

相当值得阅读的一篇文档 https://blogs.oracle.com/jbalint/entry/accessing_fabric_ha_groups_from

It's been almost a year now since the initial release of MySQL Fabric. It is maturing and reached a GA state earlier this year as part of MySQL Utilities 1.4. Developers and architects are evaluating it for their applications, learning the capabilities of the system, and providing feedback for future features and usability enhancements. Connector/J has supported Fabric from the very beginning and continues to support the latest features. This blog post is an introductory level guide to using Fabric for high-availability from Java applications. Sharding features and Java APIs will be covered in a later post. The commands shown and feature descriptions are current for Fabric 1.5.1. In addition, Connector/J 5.1.32 and later support only Fabric 1.5 due to communication protocol differences from Fabric 1.4.

High-availability is a core concept in Fabric and is implemented by HA groups. An HA group is a set of servers configured with MySQL replication in a master/slaves topology. Fabric can take care of the replication configuration, so you only need to have the servers configured properly and Fabric will take care of the rest. The example included here will use three MySQL servers for the application; one master and two slaves. There is an additional database for the backing store for the Fabric node, but it's not considered from the application's perspective. If you plan to follow along, you should setup your Fabric node, MySQL backing store for Fabric and three MySQL 5.6 instances for the application.

Fabric HA Group Creation

You will need three server instances for the basic HA group. Binlogging is required for replication and GTIDs are required for Fabric. I configured my servers with the following properties:

# server id should be different for each instanceserver-id = 1# enable binloglog-bin = mysql-binlog-slave-updates = true# enable GTIDgtid-mode = onenforce-gtid-consistency = true

At this point, we have three isolated MySQL instances. We can use Fabric to arrange them into an HA group and setup the replication streams. Here are the Fabric steps needed to build the HA group:

• Create an empty group: We first create a group in Fabric by giving it a name: mygroup. There is quite a hefty bit of output from this command. The first two lines are the Fabric node ID and the expiration of topology/status information received from the Fabric server. The next few lines are a table showing the status of the command that we executed. In this case it finished successfully. This type of output is seen for most commands that change the state of the Fabric node. Other commands, such as group.lookup_groups will only return a table showing the results of the query. The second table is a diagnostic result showing some of the internal steps of the execution of the requested command.

$ mysqlfabric group create mygroupFabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249eTime-To-Live: 100                                 uuid finished success result------------------------------------ -------- ------- ------c99b4348-9b26-4d79-bccc-0ec42ca89e0e        1       1      1 state success          when                                                   description----- ------- ------------- -------------------------------------------------------------    3       2 1406082130.78 Triggered by <mysql.fabric.events.Event object at 0x151ce90>.    4       2 1406082130.84                             Executing action (_create_group).    5       2 1406082130.98                              Executed action (_create_group).

• Add a master: Now that we have an empty HA group, we have to add our master server. First we add our server to the group and then promote it to the role of master. At this point we have a group with a single server that is designated as master, but no slaves reading from it. If we would run group.lookup_groups, we see the group mygroup with a master server's UUID which correlates to the server_uuidvariable on the master server.
  • Note: If at this stage or while adding other servers, you see an error such as "ServerError: Server (5e26a7ab-de84-11e2-a885-df73a3d95316) does not have the binary log or gtid enabled.", you will need to fix the configuration of your server. You should have binary logging and GTIDs enabled as mentioned above.

$ mysqlfabric group add mygroup 192.168.1.100:3306$ mysqlfabric group promote mygroup$ mysqlfabric group lookup_groups

• Add slaves: The final step is to add the slaves to the group. Fabric will take care of setting up the replication streams to the master we designated. At this point, we have a fully configured HA group managed by Fabric. The group.lookup_servers command will show us that Fabric is managing the three servers we gave it. You can connect to the master server and create some tables and insert some data to verify that the replication configuration is working.

$ mysqlfabric group add mygroup 192.168.1.101:3306$ mysqlfabric group add mygroup 192.168.1.101:3306$ mysqlfabric group lookup_servers mygroup

Fabric HA Group Access

Now that we've setup an HA group, we need to write a client to access it. Connector/J provides access to Fabric-managed farms with the same configuration approach used for single servers. A typical JDBC URL might look like:

jdbc:mysql://localhost/test?user=simon&password=secret

The above URL points to the test database on the localhost instance authenticating with the simonaccount. To access a Fabric-managed farm, the URL changes to something like:

jdbc:mysql:fabric://localhost/test?user=simon&password=secret&     fabricUsername=app01&fabricPassword=secret&fabricServerGroup=mygroup

We've changed the prefix to include the "fabric" marker and added the username and password to authenticate to the Fabric node. The database is still test and we still authenticate to the MySQL instances as simon. The difference here is that Connector/J will request the set of servers in the HA group mygroup from the Fabric node. Connections to the individual servers will be made as necessary during the processing of transactions.

To illustrate how this is all put together, check out this sample code:

String url = "jdbc:mysql:fabric://localhost/test?user=simon&password=secret&" +    "fabricUsername=app01&fabricPassword=secret&fabricServerGroup=mygroup";Connection con = DriverManager.getConnection(url);PreparedStatment ps = con.prepareStatement("select first_name from" +     " employees where emp_no = ?");

You may notice that this is no different than writing to the standard JDBC APIs. However, a few benefits will come from using a Fabric-aware client:

• Transactions are routed to different physical MySQL instances based on whether the connection is in read-only mode. This can be altered by callingConnection.setReadOnly(boolean). Read-only transactions will be sent to the slaves (if possible) and read/write transactions will be sent to the master. Note: This feature is implemented using load-balancing facilities that have been present in Connector/J for several years and is configurable in the same manner.
• Using the fabricReportErrors property, connection errors can be reported to the Fabric node for failure detection. If the Fabric node receives error reports from clients, it may decide to take a server out of rotation or possibly replace the master. Please consult the Fabric documentation for configuration information and behavior details.
• The Fabric node provides a central point of configuration for the servers in the farm. The client application is only responsible for knowing how to contact the Fabric node.

In addition to property-based configuration, we can also use MySQL-specific APIs to change the way Connector/J handles Fabric connections. The connection object returned from the DriverManager can be cast to an instance of com.mysql.fabric.jdbc.FabricMySQLConnection. This interface provides several APIs to change the HA group and sharding properties of the connection. If you plan to access multiple HA groups from a single connection or wish to explore the sharding features, this is the API to use. Here's an illustration that queries tables from two different HA groups:

String url = "jdbc:mysql:fabric://localhost/test?user=simon&password=secret&" +    "fabricUsername=app01&fabricPassword=secret";Connection con = DriverManager.getConnection(url);com.mysql.fabric.jdbc.FabricMySQLConnection fabricCon =    (com.mysql.fabric.jdbc.FabricMySQLConnection) con;con.setServerGroupName("mygroup");PreparedStatment ps = con.prepareStatement("select first_name from " +     "employees where empno = ?");// this is where you would deal with results, etccon.setServerGroupName("anothergroup");// proceed with additional JDBC code here accessing servers in "anothergroup"

Connector/J supports all features in Fabric and is quite flexible. There is a comprehensive section in the manual detailing the use of Connector/J with MySQL Fabric. The Connector/J package includes several demo programs in the src/demo/fabric directory. TheEmployeesJdbc program is a complete runnable example showing both HA and sharded setups.

Summary

As you've seen, Fabric is a novel method for managing and accessing farms of MySQL servers. Not only can it help configure and manage replication, but it provides an additional layer of abstraction for your application's need to access MySQL instances. The centralized configuration in Fabric means that you can easily make changes to your farm without having to worry about client configuration changes and additional application downtime. In addition to the high-availability features, Fabric provides a complete sharding solution built on HA groups. The Java APIs in Connector/J provide access to the sharding features and will be covered in a later post.

If you plan to attend the 2014 Oracle OpenWorld / JavaOne, check out the Building Sharded Java Applications with MySQL Fabric session on Tuesday afternoon. If you're using Connector/J, you might also be interested in the MySQL Connector/J Internals and Optimization BOF later that evening.