Master-subordinate replication and read-only instances for MySQL Database on Azure

The MySQL Database in Azure option on the Azure portal supports the use of the replicate function to create subordinate instance for a MySQL instance. All changes to the master instance will be replicated to the subordinate instance. You can use this function as a simple way to implement flexible expansion and overcome the access restrictions on an individual database instance, which reduces the running load and increases availability.

For business intelligence (BI) reports or database warehouse solutions, users generally want to run service report queries on independent read-only instances. A subordinate instance serves this role. You can use the replicate function can also be used to migrate databases between the generation environment and the development environment. You can also use the replicate function to improve the generation environment’s availability to provide disaster tolerance. If an error occurs, you can upgrade read-only instances to replace the faulty master instance and switch the workload to provide availability and continuity of services.

When you create a subordinate instance, Azure first performs a backup of the master instance and then creates a new read-only instance that's based on the backup to serve as the subordinate instance. Subsequently, Azure will continually replicates all changes to the master instance on the subordinate instance.

Create read-only (subordinate) instances

1. Select an existing instance, then click on Create subordinate instance.
2. Enter the subordinate server name, select the server location (for geo-replication, please select another datacenter), and configure the performance. Finally, click on Confirm.

Monitor the replication status of subordinate instances

After you create the subordinate instance, you can monitor the status of replication between the master instance and subordinate instances in a variety of ways. In the Management Portal, the overview page for the master instance shows all of its subordinate instances and their respective statuses.

Implement read/write separation in applications

A Java sample program for read/write separation at the application end is shown here:

package test1;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

import com.mysql.jdbc.Driver;
import com.mysql.jdbc.ReplicationDriver;;

public class ConnectionDemo {

  public static void main(String[] args) throws Exception {

    ReplicationDriver driver = new ReplicationDriver();
    String url = "jdbc:mysql:replication://address=(protocol=tcp)(type=master)(host=masterhost)(port=3306)(user=masteruser),address=(protocol=tcp)(type=slave)(host=slavehost)(port=3306)(user=slaveuser)/yourdb";
    Properties props = new Properties();    
    props.put("password", "yourpassword");
    try (Connection conn = driver.connect(url, props))
    {
        // Perform read/write work on the master
        conn.setReadOnly(false);
        conn.setAutoCommit(false);
        conn.createStatement().executeUpdate("update t1 set id = id+1;");
        conn.commit();    

        // Set up connection to slave;
        conn.setReadOnly(true);

        // Now, do a query from a slave
        try (Statement statement = conn.createStatement())
        {
            ResultSet res = statement.executeQuery("show tables");
            System.out.println("There are below tables:");
            while (res.next()) {
                String tblName = res.getString(1);
                System.out.println(tblName);
            }
        } 
    }
  }
}

A PHP sample program for read/write separation at the application end is shown in the next image.

• You can realize read separation from write can be realized by using PHP MySQL native push with a master and subordinate instances plug-in (PECL/mysqlnd_ms). This plug-in executes read-only statements on the configured subordinate instance, and all other queries are executed against MySQL master instance. For more information, see Read-write splitting.

• A user-defined read-write separator can request to send the built-in logic statement to a specific position and call mysqlnd_ms_is_select().

• For more information about installation of PECL/mysqlnd_ms, see Setup

• Create PECL/mysqlnd_ms plug-in configuration file, as shown here:

  File mysqlnd_ms_plugin.ini:
  {
      "myapp": {
             "master": {
              "master_0": {
                  "host": "<your master host>",
                  "port": "<your master port>",
                  "user": "<your master username>",
                  "password": "<your master password>"
                  }
          },
          "slave": {
              "slave_0": {
                  "host": "<your slave host>",
                  "port": "<your slave port>",
                  "user": "<your slave username>",
                  "password": "<your slave password>"
              }
          }
  }
  

• PHP sample program:

  <?php
  function is_select($query)
  {
    switch (mysqlnd_ms_query_is_select($query))
    {
      case MYSQLND_MS_QUERY_USE_MASTER:
        printf("'%s' should be run on the master.<br>\n", $query);
        break;
      case MYSQLND_MS_QUERY_USE_SLAVE:
        printf("'%s' should be run on a slave.<br>\n", $query);
        break;
      case MYSQLND_MS_QUERY_USE_LAST_USED:
        printf("'%s' should be run on the server that has run the previous query.<br>\n", $query);
        break;
      default:
        printf("No suggestion where to run the '%s', fallback to master recommended.<br>\n", $query);
        break;
    }
  }
  
  if (!($mysqli = new mysqli("myapp", "<your username>", "<your password>", "<your db>")) || mysqli_connect_errno())
  {
    die(sprintf("Failed to connect: [%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));
  }
  $query = "INSERT INTO user(name, num) VALUES ('test', 1)";
  is_select($query);
  
  if (!($res = $mysqli->query($query)))
  {
    printf("Failed to insert: [%d] %s<br>\n", $mysqli->errno, $mysqli->error);
  }
  
  $query = "SELECT * FROM user";
  is_select($query);
  if (!($res = $mysqli->query($query)))
  {
    printf("Failed to query: [%d] %s<br>\n", $mysqli->errno, $mysqli->error);
  }
  else
  {
    for ($i=0; $row = $res->fetch_assoc(); $i++)
    {
      $value[$i] = $row;
    }
    print_r($value);
    printf("<br>\n");
    $res->close();
  }
  
  $query = "/*" . MYSQLND_MS_LAST_USED_SWITCH . "*/SELECT * FROM user limit 1";
  is_select($query);
  if (!($res = $mysqli->query($query)))
  {
    printf("Failed to query: [%d] %s<br>\n", $mysqli->errno, $mysqli->error);
  }
  else
  {
    $value = $res->fetch_assoc();
    print_r($value);
    printf("<br>\n");
    $res->close();
  }
  $mysqli->close();
  ?>
  

Upgrade subordinate instances

You can upgrade a subordinate instance to an online read/write instance. After you upgrade the instance, changes on the master instance will no longer be replicated to this instance. You can perform read/write operations on this instance.

1. Select the subordinate instance that you wish to promote, then press Stop replication.
2. Enter the resource name, then press Confirm.