MariaDB Connector/J failover support - case Amazon Aurora

MariaDB Connector/J has evolved a lot during the year. In this post I will talk about the failover capabilities in the connector and give some guidance on how to use them in some certain cases. One other important new feature that I’ll cover in a later article is the fact that MariaDB Connector/J can do load balancing over several servers now as well.

To start off with we’ll need the connector itself. Do either of the following to get version 1.2.3 of MariaDB Connector/J which is the newest stable version as of writing:

Download the binary from https://mariadb.com/my_portal/download (requires login) . Look for Connectors / Java on the download page once logged in.
The connector is also available through Maven. To instruct Maven to include the MariaDB Connector/J add the following dependency to your application’s pom.xml file:

<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>java-client</artifactId>
    <version>1.2.3</version>
</dependency>

The failover support added to MariaDB Connector/J can be used in several high availability scenarios:

The most typical one, where normal MariaDB or MySQL replication is used and the setup consists of one master host for write operations and several slave hosts for the read operations.
Multi-master setups, such as MariaDB Galera Cluster, where every host is a master
Support for Amazon Aurora and its automatic failover functionality
The connector also supports having several masters in a normal replication topology as described in 1, but the connector will be unaware of the differences between the masters and will just pick one according to the order in which the masters have been given in the JDBC URL.

To demonstrate the failover capability I will focus on scenario 3) and set up an Amazon Aurora cluster and use Connector/J to connect to it and simulate a failover in the cluster to see how the connector reacts to that. In case you want to know more about Aurora please refer to its documentation.

To start off with I’ll create one Aurora instance that will act as the master node for the cluster:

To find Aurora, go to your AWS console, click on RDS under Databases and you should see a button saying “Launch an Aurora DB instance”. Click on it.
Type in the requested information on the Specify DB Details -page. Notice that when you move between the fields there is a good explanation of the field appearing on the right hand side. Once done click “Next Step”
On the “Configure Advanced Settings” the first part is important. Choose VPC and VPC subnet group or create a new VPC. In the VPC Security Group(s) select the VPC security groups that should be able to connect to the Aurora cluster.

Once done you should have one node of Aurora up and running looking like this:

You’ll notice that it says “Writer” in the Replication Role column of the above table.

Let’s add a slave to this master. A slave is called a replica within the Aurora cluster:

To do that click in the checkbox next to the instance in the table above. It will expand the row and you should see a “Instance Actions” button. Click on it and choose “Create Aurora Replica”.
On the next “Create Aurora Replica” page you’ll have to fill in the “DB Instance Identifier*” field. Other than that you can go with the default ones if you don’t want to change any of the default values. Finally hit the “Create Aurora Replica” button.

Repeat the above steps for the amount of replicas you want for your cluster. The maximum amount is 15.

Once done you should have an instance table looking like this:

Failover

The failover documentation for MariaDB Connector/J failover can be found here and the start page of the full documentation for the connector here.

Failover example for Aurora

Next, let’s write a program that uses the failover capabilities of the MariaDB connector. Below you’ll see the Java class TestConnection. Take a moment and read through the code. It’s pretty straightforward and should be easy to follow.

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;

public class TestConnection {

   public static void main(String[] args) {
       Connection conn = null;
       Statement stmt = null;
       String masterServer = "";
       String slaveServer = "";
       String connServer = "";
       SimpleDateFormat dateFormat = new SimpleDateFormat("HH:mm:ss.SSS");
       int i = 0;
       boolean readonly = (args.length >= 1) ? "true".equals(args[0]) : false;
       try {
           // specify connection to three nodes of aurora
           conn = DriverManager.getConnection("jdbc:mysql:aurora://"
                   + "aurora-demo-1.abcdefghijkl.us-east-1.rds.amazonaws.com:3306,"
                   + "aurora-demo-2.abcdefghijkl.us-east-1.rds.amazonaws.com:3306,"
                   + "aurora-demo-3.abcdefghijkl.us-east-1.rds.amazonaws.com:3306",
                   "xxxxxx", "yyyyyyyy");
           conn.setReadOnly(readonly); //read on master or replica
           while (i < 100) {
               try {
                   stmt = conn.createStatement();
                   slaveServer="";
                   // read server information from INFORMATION_SCHEMA
                   ResultSet rs = stmt.executeQuery("SELECT SERVER_ID, SESSION_ID FROM INFORMATION_SCHEMA.REPLICA_HOST_STATUS");
                   while (rs.next()) {
                       if (rs.getString(2).equals("MASTER_SESSION_ID"))
                           masterServer = rs.getString(1); // the node that is the master currently has a session named MASTER_SESSION_ID
                       else
                           slaveServer += (("".equals(slaveServer)) ? "" : ",") + rs.getString(1); // other nodes ares slaves
                   }

                   // get the server that is used by the current connection
                   String url = conn.getMetaData().getURL();
                   connServer = conn.getMetaData().getURL().substring(url.indexOf("//") + 2 , url.indexOf("."));

               } catch (SQLException e) {
                   continue;
               } catch (NullPointerException ne) {
                   ne.printStackTrace();
               }
               // print out timestamp and servers
               System.out.println(dateFormat.format(new Date()) + " connected-to:"
                       + connServer + " master:" + masterServer + " slave:"
                       + slaveServer);
               i++;
               try {
                   Thread.sleep(1000);
               } catch (InterruptedException e) {
                   e.printStackTrace();
               }
           }
       } catch (SQLException e) {
           e.printStackTrace();
       } finally {
           try {
               if (stmt != null) {
                   stmt.close();
               }
               if (conn != null) {
                   conn.close();
               }
           } catch (SQLException sqle) {
               //eat exception
           }
       }
   }
}

The important step in the above example Java program is where the URL is specified:

// specify connection to two nodes of aurora
conn = DriverManager.getConnection("jdbc:mysql:aurora://aurora-demo-1.abcdefghijkl.us-east-1.rds.amazonaws.com:3306,aurora-demo-2.abcdefghijkl.us-east-1.rds.amazonaws.com:3306,aurora-demo-3.abcdefghijkl.us-east-1.rds.amazonaws.com:3306", "xxxxxxxx", "yyyyyyyy");

By giving the keyword aurora in jdbc:mysql:aurora:// we instruct the driver that it should take Aurora failover support into use. Next in the url the three servers are given, aurora-demo-1…, aurora-demo-2… and aurora-demo-3 and finally the username and the password.

Make sure to use instance endpoint (in green) and not cluster endpoint (in red).

If we run the program it will start outputting rows with about 1 second interval looking like this:
16:15:22.467 connected-to:aurora-demo-2 master:aurora-demo-2 slave:aurora-demo-1,aurora-demo-3
16:15:23.579 connected-to:aurora-demo-2 master:aurora-demo-2 slave:aurora-demo-1,aurora-demo-3

First a time indicator, then to which server the connection points currently and then which server is master and which is slave from INFORMATION_SCHEMA. This data in the INFORMATION_SCHEMA is Aurora specific and doesn’t exist for MariaDB or MySQL.

The more interesting part is when we keep the program running and a failover occurs in the Aurora cluster, i.e. the slave becomes the master. Then the output would look like this:
20:56:49.241 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
20:56:50.370 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
20:56:51.501 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
20:56:52.637 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
20:58:19.374 connected-to:aurora-demo-2 master:aurora-demo-2 slave:aurora-demo-1,aurora-demo-3
20:58:20.481 connected-to:aurora-demo-2 master:aurora-demo-2 slave:aurora-demo-1,aurora-demo-3
20:58:21.587 connected-to:aurora-demo-2 master:aurora-demo-2 slave:aurora-demo-1,aurora-demo-3

The interesting rows are highlighted. While running the small java program I forced a failover in the Aurora cluster. As you see the program then waits for a while until it gets a new master connection and then continues. As you can see the original slave aurora-demo-2 has become the master to which also the connections are established after the failover. The only thing visible from application point of view is that there will be a delay until the server responds again.That’s automatic failover!

If the new master’s election would have taken more than 120 seconds (the default value of failoverLoopRetries -parameter), the connector would have thrown an SQLException. There are more parameters with which the failover capabilities can be tuned to meet your needs. You can find them in the documentation.

Failover on slaves for Aurora

MariaDB Connector/J has 2 underlying connections when using failover with either “replication” or in this case the ”aurora” parameter; one connection to master and one to a slave. Using the previous program with a parameter “true”, the driver will use the slave connection.

The output looks like this when suddenly rebooting a slave instance that is in use:
16:27:24.524 connected-to:aurora-demo-3 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:25.638 connected-to:aurora-demo-3 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:26.759 connected-to:aurora-demo-3 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:27.982 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:29.089 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:30.196 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:31.310 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:32.435 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:33.542 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:34.649 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:35.757 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:36.872 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:37.982 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:39.089 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:40.200 connected-to:aurora-demo-2 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:41.301 connected-to:aurora-demo-2 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:42.406 connected-to:aurora-demo-2 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:43.522 connected-to:aurora-demo-2 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3

The driver that was using a connection to the slave aurora-demo-3 switch immediately to the connection to the master aurora-demo-1. When a new slave connection (to aurora-demo-2) is established, the driver will switch another time the connection.

Load-balancing and other capabilities

MariaDB Connector/J also includes load balancing, execution of prepared statements on server side and other interesting new features that I will cover in upcoming blog posts. For properly testing load balancing I’ll set up a connection pool.

3 replies on “MariaDB Connector/J failover support – case Amazon Aurora”

Dhaval Metrani says:

2018-05-04 at 12:13

Thanks for the doc. Is there any way we can make use of cluster endpoint instead of instance endpoints?
If i have to use instance end-points, every time i add a read replica, i have to update the connection string.
1. chris says:
  
  2018-05-15 at 18:48
  
  as stated in the doc at : https://mariadb.com/kb/en/library/failover-and-high-availability-with-mariadb-connector-j/#specifics-for-amazon-aurora
  
  In versions before 1.5.1, cluster endpoint use was discouraged, since when a failover occurs, this cluster endpoint can point for a limited time to a host that isn’t the current master any more. The old recommendation was to list all specific end-points. This kind of url string will still work, but now, recommended url string has to use only cluster endpoint.
  1. Dhaval Metrani says:
    
    2018-09-15 at 15:04
    
    Hi Chris,
    
    Thanks for the reply. I did go through that article and played around with fail-over scenarios using both instance and cluster endpoints using mariadb driver v2.2.3.
    I observed that connection switching from old master to new master during fail-over was quicker / smoother with instance endpoints than with cluster endpoints.
    
    Dhaval

Comments are closed.