Replicating Data Across Clusters min read


This topic explains how to setup replication across MemSQL clusters, how to monitor the replication status, and how to troubleshoot some common replication errors.

Before proceeding with this topic, you should have a conceptual understanding of replication and durability.

Setting Up Replication

This example will guide you through setting up replication of a database. These instructions assume that you have two MemSQL clusters running; the primary cluster contains the source data that is replicated to the secondary cluster.

Info

To replicate a database, the secondary cluster user must have CREATE DATABASE privileges and the primary cluster user (the one specified in REPLICATE DATABASE) must have REPLICATION privileges on the primary cluster’s master aggregator.

The following host:port combinations represent the master aggregators of the primary and secondary clusters:

  • primary-MA:3306
  • secondary-MA:3306

Note that the primary and secondary clusters need not have identical topologies. MemSQL will automatically manage sharding of replica data on the secondary cluster. In this example, primary-MA has a root user with an empty password.

To begin replicating the database db_name from primary-MA, run the following command on secondary-MA:

REPLICATE DATABASE db_name FROM root:'root_password'@primary-MA:3306;

You always initiate replication from the secondary cluster. You can start replication to a secondary cluster without pausing the primary cluster.

Multiple secondary clusters can replicate from a single primary cluster. To do this, run REPLICATE DATABASE on the master aggregator of each of the replica clusters.

Info

Data is always replicated across clusters asynchronously. Databases are replicated at the leaf level, which implies that a leaf in the secondary cluster replicates data directly from a leaf in the primary cluster; therefore, when connecting a secondary cluster to the primary cluster, the leaves in the primary and secondary cluster must be able to communicate with each other. They should not be blocked by firewall or network rules.

Pausing and Stopping Replication

MemSQL allows you to pause and resume online replication with single commands.

PAUSE REPLICATING db_name;
****
Query OK, 1 row affected (0.06 sec)
CONTINUE REPLICATING db_name;
****
Query OK, 1 row affected (0.96 sec)

PAUSE REPLICATING temporarily pauses replication but maintains the replication relationship between master and secondary databases. To begin replicating from a different primary cluster, you must start a new REPLICATE DATABASE process.

Cluster Failover

If your primary cluster fails and you want to failover to a database on the secondary cluster, run the STOP REPLICATING db_name command on the secondary cluster master aggregator. This command promotes the database on the secondary cluster to the primary database and the promoted database becomes available for reads and writes (DDL and DML). Re-point your application at the master aggregator in the cluster where the promoted database resides.

Info

If your primary cluster fails, and you want to failover to all databases in the secondary cluster, run STOP REPLICATING once for each database.

After running STOP REPLICATING, you cannot resume replicating from the primary cluster.

Failing Back to Your Primary Cluster

The following scenario explains the recommended approach for failing back to your primary cluster; use this approach if your primary cluster fails and you wish to recover it in minimal time.

You are running cluster A, which contains two databases, ExampleDB1 and ExampleDB2. The host name and port of the master aggregator on cluster A is ClusterA-ma:3306. You’ve set up Cluster B to replicate the two databases from cluster A using the following commands:

REPLICATE DATABASE ExampleDB1 FROM root@ClusterA-ma:3306; REPLICATE DATABASE ExampleDB2 FROM root@ClusterA-ma:3306;

The host name and port of the master aggregator on cluster B is ClusterB-ma:3306.

Your application App1 uses ExampleDB1 and ExampleDB2 on Cluster A.

Then, cluster A fails and you take it offline.

To restore the Cluster A as the primary cluster, you follow the steps below in order. In these steps, “writes” refer to write operations initiated using DDL and DML commands.

Info

The steps below do not discuss the case where you use pipelines in your cluster. In this case, stop all pipelines before running the FLUSH TABLES WITH READ ONLY command. After stopping the currently running pipelines, do not create, start, stop, or drop pipelines before the cluster is available for writes.

Step Cluster A Cluster B
1 You run the commands STOP REPLICATING ExampleDB1; and STOP REPLICATING ExampleDB2;. After you run these commands, ExampleDB1 and ExampleDB2 are promoted to primary databases and are available for read and writes.
2 You point App1 to cluster B and App1 writes transactions to ExampleDB1 and ExampleDB2.
3 You resolve the issue that caused cluster A to go offline. You bring cluster A back online.
4 You run the command REPLICATE DATABASE ExampleDB1 WITH FORCE DIFFERENTIAL FROM root@ClusterB-ma:3306;. This command replicates, to cluster A, only the contents of ExampleDB1 on cluster B that are not already in ExampleDB1 on cluster A. You run the same REPLICATE DATABASE command using ExampleDB2. See the REPLICATE DATABASE topic for more information.
5 App1 writes transactions to ExampleDB1 and ExampleDB2.
6 The REPLICATE DATABASE command you ran on the two databases in step 4 returns, indicating that cluster A is up-to-date with cluster B as of right before step 4. Following this, the transactions written during step 5 begin replicating to cluster A.
7 You run FLUSH TABLES WITH READ ONLY; this command completes any in-progress write transactions that are running on the cluster and fails any new writes that are initiated before step 10. As an alternative to running FLUSH TABLES WITH READ ONLY, you could pause all writes from App1 and ensure that any in-process write transactions have completed.
8 You determine when the writes made during step 5 have completed syncing to cluster A. Then you run STOP REPLICATING ExampleDB1; and STOP REPLICATING ExampleDB2;. ExampleDB1 and ExampleDB2 become primary databases on cluster A.
9 You repoint App1 to cluster A and App1 writes transactions to Example1 and Example2.
10 You run UNLOCK TABLES to enable ExampleDB1 and ExampleDB2 for write operations. Note that any writes, made in cluster B following the run of FLUSH TABLE WITH READ ONLY in step 7 and before UNLOCK TABLES, will fail.
11 You run the command REPLICATE DATABASE ExampleDB1 WITH FORCE DIFFERENTIAL FROM root@ClusterA-ma:3306;. This command initiates replication of ExampleDB1 from cluster A to cluster B. Only writes that were initiated after running STOP REPLICATING ExampleDB1 in step 8 are replicated to cluster B, since ExampleDB1 on both clusters contain the same contents prior to that time. You run the same REPLICATE DATABASE command using ExampleDB2. After REPLICATE DATABASE returns, replication continues; any new writes made to ExampleDB1 and ExampleDB2on cluster A are replicated to cluster B.

Aggregator Failover

Primary Cluster Master Aggregator Failure

If the master aggregator on the primary cluster fails, no changes to reference data or database schemas can be made. DML changes to distributed tables can be made through the child aggregators, and these changes are replicated to the secondary cluster.

If you can bring the master aggregator back online, replication of reference data and database schema changes will automatically continue. Otherwise, you can failover to a child aggregator using AGGREGATOR SET AS MASTER.

Secondary Cluster Master Aggregator Failure

If the master aggregator on the secondary cluster fails, no changes to reference data or database schemas on the primary cluster will be replicated to the secondary cluster. DML changes to distributed tables can be made through the child aggregators on the primary cluster, and these changes are replicated to the secondary cluster.

If you can bring the master aggregator back online, replication of reference data and database schema changes from the primary cluster will automatically continue. Otherwise, you can failover to a child aggregator using AGGREGATOR SET AS MASTER.

Upgrading a Primary and Secondary Cluster

The following scenario explains the recommended approach you should take to upgrade a primary and secondary cluster, with minimum downtime and no data loss.

You are running cluster A, which contains two databases, ExampleDB1 and ExampleDB2. The host name of the master aggregator on cluster A is ClusterA-ma:3306. You’ve set up Cluster B to replicate the two databases in cluster A using the following commands:

REPLICATE DATABASE ExampleDB1 FROM root@ClusterA-ma:3306; REPLICATE DATABASE ExampleDB2 FROM root@ClusterA-ma:3306;

The host name and port of the master aggregator on cluster B is ClusterB-ma:3306.

Your application App1 uses ExampleDB1 and ExampleDB2 on Cluster A.

You take Cluster B offline, upgrade it, and bring it back online. ExampleDB1 and ExampleDB2 on cluster A continue to replicate to cluster B. Then, you follow the steps below to upgrade Cluster A. In these steps, “writes” refer to write operations initiated using DDL and DML commands.

Info

The steps below do not discuss the case where you use pipelines in your cluster. In this case, stop all pipelines before running the FLUSH TABLES WITH READ ONLY command. After stopping the currently running pipelines, do not create, start, stop, or drop pipelines before the cluster is available for writes.

Step Cluster A Cluster B
1 You run FLUSH TABLES WITH READ ONLY; this command completes any in-progress write transactions that are running on the cluster and fails any new writes that are initiated. You want to fail any such writes because you will be taking cluster A offline. As an alternative to running FLUSH TABLES WITH READ ONLY, you could pause all writes from App1 and ensure that any in-process write transactions have completed.
2 You run the commands STOP REPLICATING ExampleDB1; and STOP REPLICATING ExampleDB2;. After you run these commands, ExampleDB1 and ExampleDB2 are promoted to primary databases and are available for reads and writes.
3 You point App1 to cluster B and App1 writes transactions to ExampleDB1 and ExampleDB2.
4 You take cluster A offline.
5 You upgrade cluster A.
6 You bring cluster A online.
7 You run the command REPLICATE DATABASE ExampleDB1 WITH FORCE DIFFERENTIAL FROM root@ClusterB-ma:3306;. This command replicates, to cluster A, only the contents of ExampleDB1 on cluster B that are not already in ExampleDB1 on cluster A. You run the same REPLICATE DATABASE command using ExampleDB2. See the REPLICATE DATABASE topic for more information.
8 App1 writes transactions to Example1 and Example2.
9 The REPLICATE DATABASE command you ran on the two databases in step 7 returns, indicating that cluster A is up-to-date with cluster B as of right before step 7. Following this, the transactions written during step 8 begin replicating to cluster A.
10 You run FLUSH TABLES WITH READ ONLY; this command completes any in-progress write transactions that are running on the cluster and fails any new writes that are initiated before step 13. As an alternative to running FLUSH TABLES WITH READ ONLY, you could pause all writes from App1 and ensure that any in-process write transactions have completed.
11 You determine when the writes made during step 8 have completed syncing to cluster A. Then you run STOP REPLICATING ExampleDB1; and STOP REPLICATING ExampleDB2;. ExampleDB1 and ExampleDB2 become primary databases on cluster A.
12 You repoint App1 to cluster A and App1 writes transactions to Example1 and Example2.
13 You run UNLOCK TABLES to enable ExampleDB1 and ExampleDB2 for write operations. Note that any writes, made in cluster B following the run of FLUSH TABLE WITH READ ONLY in step 10 and before UNLOCK TABLES, will fail.
14 You run the command REPLICATE DATABASE ExampleDB1 WITH FORCE DIFFERENTIAL FROM root@ClusterA-ma:3306;. This command initiates replication of ExampleDB1 from cluster A to cluster B. Only writes that were initiated after running STOP REPLICATING ExampleDB1 in step 11 are replicated to cluster B, since ExampleDB1 on both clusters contain the same contents prior to that time. You run the same REPLICATE DATABASE command using ExampleDB2. After REPLICATE DATABASE returns, replication continues; any new writes made to ExampleDB1 and ExampleDB2on cluster A are replicated to cluster B.

Using mysqldump to Extract Data From a Secondary database

When mysqldump is run on a secondary database following the instructions in Exporting Data From MemSQL <data_export>, an error will occur. This error happens because mysqldump runs LOCK TABLES which isn’t permitted on a secondary database. mysqldump can be configured to avoid locking tables by passing the option --lock-tables=false. So, to take a consistent mysqldump of a secondary database called secondary_db we recommend the following:

Note that pausing replication is only required if you want a consistent mysqldump when concurrent writes are happening on the master.

Monitoring Replication

SHOW PARTITIONS EXTENDED

Running SHOW PARTITIONS EXTENDED on secondary-MA will display information such as replication role, the location of each partition, if it is locked, and other information.

SHOW PARTITIONS EXTENDED;
****
+---------+-----------+-------+--------+--------+------+------------------+------------+--------------+
| Ordinal | Host      | Port  | Role   | Locked | Info | Last Command     | Last Error | Last Message |
+---------+-----------+-------+--------+--------+------+------------------+------------+--------------+
|       0 | 127.0.0.1 | 20001 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|       1 | 127.0.0.1 | 20002 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|       2 | 127.0.0.1 | 20001 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|       3 | 127.0.0.1 | 20002 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|       4 | 127.0.0.1 | 20001 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|       5 | 127.0.0.1 | 20002 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|       6 | 127.0.0.1 | 20001 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|       7 | 127.0.0.1 | 20002 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|       8 | 127.0.0.1 | 20001 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|       9 | 127.0.0.1 | 20002 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|      10 | 127.0.0.1 | 20001 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|      11 | 127.0.0.1 | 20002 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|      12 | 127.0.0.1 | 20001 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|      13 | 127.0.0.1 | 20002 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|      14 | 127.0.0.1 | 20001 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|      15 | 127.0.0.1 | 20002 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
+---------+-----------+-------+--------+--------+------+------------------+------------+--------------+
Info

Info is NULL for backwards compatibility.

SHOW CLUSTER STATUS

Running SHOW CLUSTER STATUS provides information like log replay position and detailed information about all databases in the cluster.

SHOW CLUSTER STATUS;
****
+---------+-----------+-------+------------------------------+-------------+-------------+----------+-------------+-------------+-------------------------+----------------------+----------------------+---------------+-------------------------------------------------+
| Node ID | Host      | Port  | Database                     | Role        | State       | Position | Master Host | Master Port | Metadata Master Node ID | Metadata Master Host | Metadata Master Port | Metadata Role | Details                                         |
+---------+-----------+-------+------------------------------+-------------+-------------+----------+-------------+-------------+-------------------------+----------------------+----------------------+---------------+-------------------------------------------------+
|       1 | 127.0.0.1 | 20000 | cluster                      | master      | online      | 0:37     | NULL        |        NULL |                    NULL | NULL                 |                 NULL | Reference     |                                                 |
|       1 | 127.0.0.1 | 20000 | cluster_17639882876507016380 | async slave | replicating | 0:36     | 127.0.0.1   |       10000 |                       1 | 127.0.0.1            |                10000 | Reference     | stage: packet wait, state: x_streaming, err: no |
|       1 | 127.0.0.1 | 20000 | db_name                      | async slave | replicating | 0:683    | 127.0.0.1   |       10000 |                       1 | 127.0.0.1            |                10000 | Reference     |                                                 |
|       2 | 127.0.0.1 | 20001 | cluster                      | async slave | replicating | 0:36     | 127.0.0.1   |       20000 |                       1 | 127.0.0.1            |                20000 | Reference     | stage: packet wait, state: x_streaming, err: no |
|       2 | 127.0.0.1 | 20001 | cluster_17639882876507016380 | async slave | replicating | 0:36     | 127.0.0.1   |       20000 |                       1 | 127.0.0.1            |                20000 | Reference     | stage: packet wait, state: x_streaming, err: no |
|       2 | 127.0.0.1 | 20001 | db_name                      | async slave | replicating | 0:683    | 127.0.0.1   |       20000 |                       1 | 127.0.0.1            |                20000 | Reference     |                                                 |
|       2 | 127.0.0.1 | 20001 | db_name_0                    | async slave | replicating | 0:30423  | 127.0.0.1   |       10001 |             -2147483646 | 127.0.0.1            |                10001 | Slave         |                                                 |
|       2 | 127.0.0.1 | 20001 | db_name_0_AUTO_SLAVE         | async slave | replicating | 0:30423  | 127.0.0.1   |       20001 |                    NULL | NULL                 |                 NULL | Orphan        |                                                 |
|       2 | 127.0.0.1 | 20001 | db_name_10                   | async slave | replicating | 0:29835  | 127.0.0.1   |       10001 |             -2147483646 | 127.0.0.1            |                10001 | Slave         |                                                 |
|       2 | 127.0.0.1 | 20001 | db_name_10_AUTO_SLAVE        | async slave | replicating | 0:29835  | 127.0.0.1   |       20001 |                    NULL | NULL                 |                 NULL | Orphan        |                                                 |
|       2 | 127.0.0.1 | 20001 | db_name_12                   | async slave | replicating | 0:29773  | 127.0.0.1   |       10001 |             -2147483646 | 127.0.0.1            |                10001 | Slave         |                                                 |
|       2 | 127.0.0.1 | 20001 | db_name_12_AUTO_SLAVE        | async slave | replicating | 0:29773  | 127.0.0.1   |       20001 |                    NULL | NULL                 |                 NULL | Orphan        |                                                 |
|       2 | 127.0.0.1 | 20001 | db_name_14                   | async slave | replicating | 0:29476  | 127.0.0.1   |       10001 |             -2147483646 | 127.0.0.1            |                10001 | Slave         |                                                 |
|       2 | 127.0.0.1 | 20001 | db_name_14_AUTO_SLAVE        | async slave | replicating | 0:29476  | 127.0.0.1   |       20001 |                    NULL | NULL                 |                 NULL | Orphan        |                                                 |
|       2 | 127.0.0.1 | 20001 | db_name_2                    | async slave | replicating | 0:29188  | 127.0.0.1   |       10001 |             -2147483646 | 127.0.0.1            |                10001 | Slave         |                                                 |
|       2 | 127.0.0.1 | 20001 | db_name_2_AUTO_SLAVE         | async slave | replicating | 0:29188  | 127.0.0.1   |       20001 |                    NULL | NULL                 |                 NULL | Orphan        |                                                 |
|       2 | 127.0.0.1 | 20001 | db_name_4                    | async slave | replicating | 0:30611  | 127.0.0.1   |       10001 |             -2147483646 | 127.0.0.1            |                10001 | Slave         |                                                 |
|       2 | 127.0.0.1 | 20001 | db_name_4_AUTO_SLAVE         | async slave | replicating | 0:30611  | 127.0.0.1   |       20001 |                    NULL | NULL                 |                 NULL | Orphan        |                                                 |
|       2 | 127.0.0.1 | 20001 | db_name_6                    | async slave | replicating | 0:30573  | 127.0.0.1   |       10001 |             -2147483646 | 127.0.0.1            |                10001 | Slave         |                                                 |
|       2 | 127.0.0.1 | 20001 | db_name_6_AUTO_SLAVE         | async slave | replicating | 0:30573  | 127.0.0.1   |       20001 |                    NULL | NULL                 |                 NULL | Orphan        |                                                 |
|       2 | 127.0.0.1 | 20001 | db_name_8                    | async slave | replicating | 0:29812  | 127.0.0.1   |       10001 |             -2147483646 | 127.0.0.1            |                10001 | Slave         |                                                 |
|       2 | 127.0.0.1 | 20001 | db_name_8_AUTO_SLAVE         | async slave | replicating | 0:29812  | 127.0.0.1   |       20001 |                    NULL | NULL                 |                 NULL | Orphan        |                                                 |
|       3 | 127.0.0.1 | 20002 | cluster                      | async slave | replicating | 0:36     | 127.0.0.1   |       20000 |                       1 | 127.0.0.1            |                20000 | Reference     | stage: packet wait, state: x_streaming, err: no |
|       3 | 127.0.0.1 | 20002 | cluster_17639882876507016380 | async slave | replicating | 0:36     | 127.0.0.1   |       20000 |                       1 | 127.0.0.1            |                20000 | Reference     | stage: packet wait, state: x_streaming, err: no |
|       3 | 127.0.0.1 | 20002 | db_name                      | async slave | replicating | 0:683    | 127.0.0.1   |       20000 |                       1 | 127.0.0.1            |                20000 | Reference     |                                                 |
|       3 | 127.0.0.1 | 20002 | db_name_1                    | async slave | replicating | 0:30454  | 127.0.0.1   |       10002 |             -2147483645 | 127.0.0.1            |                10002 | Slave         |                                                 |
|       3 | 127.0.0.1 | 20002 | db_name_11                   | async slave | replicating | 0:30546  | 127.0.0.1   |       10002 |             -2147483645 | 127.0.0.1            |                10002 | Slave         |                                                 |
|       3 | 127.0.0.1 | 20002 | db_name_11_AUTO_SLAVE        | async slave | replicating | 0:30546  | 127.0.0.1   |       20002 |                    NULL | NULL                 |                 NULL | Orphan        |                                                 |
|       3 | 127.0.0.1 | 20002 | db_name_13                   | async slave | replicating | 0:30048  | 127.0.0.1   |       10002 |             -2147483645 | 127.0.0.1            |                10002 | Slave         |                                                 |
|       3 | 127.0.0.1 | 20002 | db_name_13_AUTO_SLAVE        | async slave | replicating | 0:30048  | 127.0.0.1   |       20002 |                    NULL | NULL                 |                 NULL | Orphan        |                                                 |
|       3 | 127.0.0.1 | 20002 | db_name_15                   | async slave | replicating | 0:30609  | 127.0.0.1   |       10002 |             -2147483645 | 127.0.0.1            |                10002 | Slave         |                                                 |
|       3 | 127.0.0.1 | 20002 | db_name_15_AUTO_SLAVE        | async slave | replicating | 0:30609  | 127.0.0.1   |       20002 |                    NULL | NULL                 |                 NULL | Orphan        |                                                 |
|       3 | 127.0.0.1 | 20002 | db_name_1_AUTO_SLAVE         | async slave | replicating | 0:30454  | 127.0.0.1   |       20002 |                    NULL | NULL                 |                 NULL | Orphan        |                                                 |
|       3 | 127.0.0.1 | 20002 | db_name_3                    | async slave | replicating | 0:30158  | 127.0.0.1   |       10002 |             -2147483645 | 127.0.0.1            |                10002 | Slave         |                                                 |
|       3 | 127.0.0.1 | 20002 | db_name_3_AUTO_SLAVE         | async slave | replicating | 0:30158  | 127.0.0.1   |       20002 |                    NULL | NULL                 |                 NULL | Orphan        |                                                 |
|       3 | 127.0.0.1 | 20002 | db_name_5                    | async slave | replicating | 0:30342  | 127.0.0.1   |       10002 |             -2147483645 | 127.0.0.1            |                10002 | Slave         |                                                 |
|       3 | 127.0.0.1 | 20002 | db_name_5_AUTO_SLAVE         | async slave | replicating | 0:30342  | 127.0.0.1   |       20002 |                    NULL | NULL                 |                 NULL | Orphan        |                                                 |
|       3 | 127.0.0.1 | 20002 | db_name_7                    | async slave | replicating | 0:29773  | 127.0.0.1   |       10002 |             -2147483645 | 127.0.0.1            |                10002 | Slave         |                                                 |
|       3 | 127.0.0.1 | 20002 | db_name_7_AUTO_SLAVE         | async slave | replicating | 0:29773  | 127.0.0.1   |       20002 |                    NULL | NULL                 |                 NULL | Orphan        |                                                 |
|       3 | 127.0.0.1 | 20002 | db_name_9                    | async slave | replicating | 0:30270  | 127.0.0.1   |       10002 |             -2147483645 | 127.0.0.1            |                10002 | Slave         |                                                 |
|       3 | 127.0.0.1 | 20002 | db_name_9_AUTO_SLAVE         | async slave | replicating | 0:30270  | 127.0.0.1   |       20002 |                    NULL | NULL                 |                 NULL | Orphan        |                                                 |
+---------+-----------+-------+------------------------------+-------------+-------------+----------+-------------+-------------+-------------------------+----------------------+----------------------+---------------+-------------------------------------------------+

SHOW DATABASES EXTENDED

SHOW DATABASES EXTENDED is another useful command for monitoring replication status. The output summarizes the replication status and other information about the state of the databases present in a MemSQL cluster.

SHOW DATABASES EXTENDED;
****
+------------------------------+---------+-------------+-------------+----------+---------+-------------+------------+-----------------+-------------------+------------------+----------------+------+--------------+--------------+
| Database                     | Commits | Role        | State       | Position | Details | AsyncSlaves | SyncSlaves | ConsensusSlaves | CommittedPosition | HardenedPosition | ReplayPosition | Term | LastPageTerm | Memory (MBs) |
+------------------------------+---------+-------------+-------------+----------+---------+-------------+------------+-----------------+-------------------+------------------+----------------+------+--------------+--------------+
| cluster                      |      34 | master      | online      | 0:37     |         |           2 | 0          |               0 | 0:37              | 0:37             | NULL           |    2 |            0 |         0.00 |
| cluster_17639882876507016380 |      16 | async slave | replicating | 0:37     |         |           2 | 0          |               0 | 0:36              | 0:37             | 0:36           |    2 |            2 |         0.00 |
| db_name                      |       8 | async slave | replicating | 0:683    |         |           2 | 0          |               0 | 0:683             | 0:683            | 0:683          |    1 |            0 |         0.00 |
| information_schema           |      87 | master      | online      | 0:10     |         |           0 | 0          |               0 | 0:10              | 0:10             | NULL           |    1 |            0 |         0.00 |
| memsql                       |      18 | master      | online      | 0:1085   |         |           0 | 0          |               0 | 0:1085            | 0:1085           | NULL           |    2 |            0 |         0.00 |
+------------------------------+---------+-------------+-------------+----------+---------+-------------+------------+-----------------+-------------------+------------------+----------------+------+--------------+--------------+
5 rows in set (0.00 sec)

SHOW REPLICATION STATUS

Running SHOW REPLICATION STATUS on a node shows the status of every replication process running on that node. The following is an example of the output of SHOW REPLICATION STATUS run on secondary-MA. Note that this example follows the naming conventions established in Setting Up Replication.

SHOW REPLICATION STATUS;
****
+-------------+------------------------------+-------------------------+--------------+------------------+--------------------+------------------+----------------+----------------+-----------+----------------------------------------------+-------------+-----------------+-------------------+-----------------+---------------+---------------+
| Role        | Database                     | Master_URI              | Master_State | Master_CommitLSN | Master_HardenedLSN | Master_ReplayLSN | Master_TailLSN | Master_Commits | Connected | Slave_URI                                    | Slave_State | Slave_CommitLSN | Slave_HardenedLSN | Slave_ReplayLSN | Slave_TailLSN | Slave_Commits |
+-------------+------------------------------+-------------------------+--------------+------------------+--------------------+------------------+----------------+----------------+-----------+----------------------------------------------+-------------+-----------------+-------------------+-----------------+---------------+---------------+
| master      | cluster                      | NULL                    | online       | 0:37             | 0:37               | 0:0              | 0:37           |             34 | yes       | 127.0.0.1:20002/cluster                      | replicating | 0:36            | 0:37              | 0:36            | 0:37          |            17 |
| master      | cluster                      | NULL                    | online       | 0:37             | 0:37               | 0:0              | 0:37           |             34 | yes       | 127.0.0.1:20001/cluster                      | replicating | 0:36            | 0:37              | 0:36            | 0:37          |            17 |
| async slave | cluster_17639882876507016380 | 127.0.0.1:10000/cluster | online       | 0:37             | 0:37               | 0:0              | 0:37           |             33 | yes       | NULL                                         | replicating | 0:36            | 0:37              | 0:36            | 0:37          |            16 |
| master      | cluster_17639882876507016380 | NULL                    | replicating  | 0:36             | 0:37               | 0:36             | 0:37           |             16 | yes       | 127.0.0.1:20002/cluster_17639882876507016380 | replicating | 0:36            | 0:37              | 0:36            | 0:37          |            16 |
| master      | cluster_17639882876507016380 | NULL                    | replicating  | 0:36             | 0:37               | 0:36             | 0:37           |             16 | yes       | 127.0.0.1:20001/cluster_17639882876507016380 | replicating | 0:36            | 0:37              | 0:36            | 0:37          |            16 |
| async slave | db_name                      | 127.0.0.1:10000/db_name | online       | 0:683            | 0:683              | 0:683            | 0:683          |              8 | yes       | NULL                                         | replicating | 0:683           | 0:683             | 0:683           | 0:683         |             8 |
| master      | db_name                      | NULL                    | replicating  | 0:683            | 0:683              | 0:683            | 0:683          |              8 | yes       | 127.0.0.1:20002/db_name                      | replicating | 0:683           | 0:683             | 0:683           | 0:683         |             8 |
| master      | db_name                      | NULL                    | replicating  | 0:683            | 0:683              | 0:683            | 0:683          |              8 | yes       | 127.0.0.1:20001/db_name                      | replicating | 0:683           | 0:683             | 0:683           | 0:683         |             8 |
+-------------+------------------------------+-------------------------+--------------+------------------+--------------------+------------------+----------------+----------------+-----------+----------------------------------------------+-------------+-----------------+-------------------+-----------------+---------------+---------------+

8 rows in set (0.03 sec)

In this example, the first line describes replication of the sharding database on primary-MA to the sharding_6683bab69542952c database on secondary-MA. The sharding database exists on the master aggregator and stores metadata that defines how data is partitioned. REPLICATE DATABASE automatically creates a sharding_[hash] database on the secondary cluster which stores partition metadata about the primary cluster. The second line describes replication of metadata and reference tables for the db_name database. This data is replicated synchronously to all aggregators and asynchronously to all leaves. The third and fourth lines describe replication of db_name metadata and reference tables from secondary-MA to the secondary cluster’s two leaf nodes (secondary-L1 and secondary-L2).

NetworkPosition uses the format [log file ordinal]:[byte offset into log file].

The following is the output of SHOW REPLICATION STATUS run on secondary-L1. In this example, db_name_[ordinal] refers to a partition of the sharded db_name database.

SHOW REPLICATION STATUS;
****
+-------------+------------------------------+----------------------------------------------+--------------+------------------+--------------------+------------------+----------------+----------------+-----------+----------------------------+-------------+-----------------+-------------------+-----------------+---------------+---------------+
| Role        | Database                     | Master_URI                                   | Master_State | Master_CommitLSN | Master_HardenedLSN | Master_ReplayLSN | Master_TailLSN | Master_Commits | Connected | Slave_URI                  | Slave_State | Slave_CommitLSN | Slave_HardenedLSN | Slave_ReplayLSN | Slave_TailLSN | Slave_Commits |
+-------------+------------------------------+----------------------------------------------+--------------+------------------+--------------------+------------------+----------------+----------------+-----------+----------------------------+-------------+-----------------+-------------------+-----------------+---------------+---------------+
| async slave | cluster                      | 127.0.0.1:20000/cluster                      | online       | 0:37             | 0:37               | 0:0              | 0:37           |             34 | yes       | NULL                       | replicating | 0:36            | 0:37              | 0:36            | 0:37          |            17 |
| async slave | cluster_17639882876507016380 | 127.0.0.1:20000/cluster_17639882876507016380 | replicating  | 0:36             | 0:37               | 0:36             | 0:37           |             16 | yes       | NULL                       | replicating | 0:36            | 0:37              | 0:36            | 0:37          |            16 |
| async slave | db_name                      | 127.0.0.1:20000/db_name                      | replicating  | 0:683            | 0:683              | 0:683            | 0:683          |              8 | yes       | NULL                       | replicating | 0:683           | 0:683             | 0:683           | 0:683         |             8 |
| async slave | db_name_0                    | 127.0.0.1:10001/db_name_0                    | online       | 0:30423          | 0:30423            | 0:30423          | 0:30423        |           1778 | yes       | NULL                       | replicating | 0:30423         | 0:30423           | 0:30423         | 0:30423       |          1778 |
| master      | db_name_0                    | NULL                                         | replicating  | 0:30423          | 0:30423            | 0:30423          | 0:30423        |           1778 | yes       | 127.0.0.1:20001/db_name_0  | replicating | 0:30423         | 0:30423           | 0:30423         | 0:30423       |          1778 |
| async slave | db_name_0_AUTO_SLAVE         | 127.0.0.1:20001/db_name_0_AUTO_SLAVE         | replicating  | 0:30423          | 0:30423            | 0:30423          | 0:30423        |           1778 | yes       | NULL                       | replicating | 0:30423         | 0:30423           | 0:30423         | 0:30423       |          1778 |
| async slave | db_name_10                   | 127.0.0.1:10001/db_name_10                   | online       | 0:29835          | 0:29835            | 0:29835          | 0:29835        |           1766 | yes       | NULL                       | replicating | 0:29835         | 0:29835           | 0:29835         | 0:29835       |          1766 |
| master      | db_name_10                   | NULL                                         | replicating  | 0:29835          | 0:29835            | 0:29835          | 0:29835        |           1766 | yes       | 127.0.0.1:20001/db_name_10 | replicating | 0:29835         | 0:29835           | 0:29835         | 0:29835       |          1766 |
| async slave | db_name_10_AUTO_SLAVE        | 127.0.0.1:20001/db_name_10_AUTO_SLAVE        | replicating  | 0:29835          | 0:29835            | 0:29835          | 0:29835        |           1766 | yes       | NULL                       | replicating | 0:29835         | 0:29835           | 0:29835         | 0:29835       |          1766 |
| async slave | db_name_12                   | 127.0.0.1:10001/db_name_12                   | online       | 0:29773          | 0:29773            | 0:29773          | 0:29773        |           1747 | yes       | NULL                       | replicating | 0:29773         | 0:29773           | 0:29773         | 0:29773       |          1747 |
| master      | db_name_12                   | NULL                                         | replicating  | 0:29773          | 0:29773            | 0:29773          | 0:29773        |           1747 | yes       | 127.0.0.1:20001/db_name_12 | replicating | 0:29773         | 0:29773           | 0:29773         | 0:29773       |          1747 |
| async slave | db_name_12_AUTO_SLAVE        | 127.0.0.1:20001/db_name_12_AUTO_SLAVE        | replicating  | 0:29773          | 0:29773            | 0:29773          | 0:29773        |           1747 | yes       | NULL                       | replicating | 0:29773         | 0:29773           | 0:29773         | 0:29773       |          1747 |
| async slave | db_name_14                   | 127.0.0.1:10001/db_name_14                   | online       | 0:29476          | 0:29476            | 0:29476          | 0:29476        |           1736 | yes       | NULL                       | replicating | 0:29476         | 0:29476           | 0:29476         | 0:29476       |          1736 |
| master      | db_name_14                   | NULL                                         | replicating  | 0:29476          | 0:29476            | 0:29476          | 0:29476        |           1736 | yes       | 127.0.0.1:20001/db_name_14 | replicating | 0:29476         | 0:29476           | 0:29476         | 0:29476       |          1736 |
| async slave | db_name_14_AUTO_SLAVE        | 127.0.0.1:20001/db_name_14_AUTO_SLAVE        | replicating  | 0:29476          | 0:29476            | 0:29476          | 0:29476        |           1736 | yes       | NULL                       | replicating | 0:29476         | 0:29476           | 0:29476         | 0:29476       |          1736 |
| async slave | db_name_2                    | 127.0.0.1:10001/db_name_2                    | online       | 0:29188          | 0:29188            | 0:29188          | 0:29188        |           1696 | yes       | NULL                       | replicating | 0:29188         | 0:29188           | 0:29188         | 0:29188       |          1696 |
| master      | db_name_2                    | NULL                                         | replicating  | 0:29188          | 0:29188            | 0:29188          | 0:29188        |           1696 | yes       | 127.0.0.1:20001/db_name_2  | replicating | 0:29188         | 0:29188           | 0:29188         | 0:29188       |          1696 |
| async slave | db_name_2_AUTO_SLAVE         | 127.0.0.1:20001/db_name_2_AUTO_SLAVE         | replicating  | 0:29188          | 0:29188            | 0:29188          | 0:29188        |           1696 | yes       | NULL                       | replicating | 0:29188         | 0:29188           | 0:29188         | 0:29188       |          1696 |
| async slave | db_name_4                    | 127.0.0.1:10001/db_name_4                    | online       | 0:30611          | 0:30611            | 0:30611          | 0:30611        |           1798 | yes       | NULL                       | replicating | 0:30611         | 0:30611           | 0:30611         | 0:30611       |          1798 |
| master      | db_name_4                    | NULL                                         | replicating  | 0:30611          | 0:30611            | 0:30611          | 0:30611        |           1798 | yes       | 127.0.0.1:20001/db_name_4  | replicating | 0:30611         | 0:30611           | 0:30611         | 0:30611       |          1798 |
| async slave | db_name_4_AUTO_SLAVE         | 127.0.0.1:20001/db_name_4_AUTO_SLAVE         | replicating  | 0:30611          | 0:30611            | 0:30611          | 0:30611        |           1798 | yes       | NULL                       | replicating | 0:30611         | 0:30611           | 0:30611         | 0:30611       |          1798 |
| async slave | db_name_6                    | 127.0.0.1:10001/db_name_6                    | online       | 0:30573          | 0:30573            | 0:30573          | 0:30573        |           1797 | yes       | NULL                       | replicating | 0:30573         | 0:30573           | 0:30573         | 0:30573       |          1797 |
| master      | db_name_6                    | NULL                                         | replicating  | 0:30573          | 0:30573            | 0:30573          | 0:30573        |           1797 | yes       | 127.0.0.1:20001/db_name_6  | replicating | 0:30573         | 0:30573           | 0:30573         | 0:30573       |          1797 |
| async slave | db_name_6_AUTO_SLAVE         | 127.0.0.1:20001/db_name_6_AUTO_SLAVE         | replicating  | 0:30573          | 0:30573            | 0:30573          | 0:30573        |           1797 | yes       | NULL                       | replicating | 0:30573         | 0:30573           | 0:30573         | 0:30573       |          1797 |
| async slave | db_name_8                    | 127.0.0.1:10001/db_name_8                    | online       | 0:29812          | 0:29812            | 0:29812          | 0:29812        |           1735 | yes       | NULL                       | replicating | 0:29812         | 0:29812           | 0:29812         | 0:29812       |          1735 |
| master      | db_name_8                    | NULL                                         | replicating  | 0:29812          | 0:29812            | 0:29812          | 0:29812        |           1735 | yes       | 127.0.0.1:20001/db_name_8  | replicating | 0:29812         | 0:29812           | 0:29812         | 0:29812       |          1735 |
| async slave | db_name_8_AUTO_SLAVE         | 127.0.0.1:20001/db_name_8_AUTO_SLAVE         | replicating  | 0:29812          | 0:29812            | 0:29812          | 0:29812        |           1735 | yes       | NULL                       | replicating | 0:29812         | 0:29812           | 0:29812         | 0:29812       |          1735 |
+-------------+------------------------------+----------------------------------------------+--------------+------------------+--------------------+------------------+----------------+----------------+-----------+----------------------------+-------------+-----------------+-------------------+-----------------+---------------+---------------+

In this sample output, the first line refers to replication of the reference database (metadata) for db_name. This data is replicated from primary-MA to secondary-MA from which is is replicated to each leaf node in the secondary cluster. The remaining lines refer to replication of the partitions of the sharded database db_name. As you can see, the data is replicated directly from leaf nodes in the primary cluster to leaf nodes in the secondary cluster. In this example, secondary-L1 is receiving data from both primary-L1 and primary-L2.

Finally, note that MemSQL will automatically take the steps necessary to ensure the secondary cluster is consistent with the primary cluster. For example, if a leaf node in a primary cluster with redundancy 2 and a replica partition on the secondary cluster gets ahead of a replica partition on the primary cluster (due to network or other irregularity), MemSQL will automatically drop and reprovision the replica partition on the secondary cluster such that it will be consistent with the recently promoted master partition on the primary cluster. Please note that the dropped or reprovisioning replica partition will not appear in the SHOW REPLICATION STATUS output.

Replication Compatibility Between Different Cluster Versions

In general, you may replicate data between two different versions of MemSQL if the MemSQL version on the source cluster is earlier than the version on the destination cluster. However, there are some exceptions:

  • You may not replicate data from a source cluster running MemSQL 5.8 or earlier to a destination cluster running MemSQL 6.0 or later. This is due to how replication changed in MemSQL 6.0. You must first upgrade your source cluster to MemSQL 6.0 or later. Refer to Upgrading to MemSQL 6.0 for more information.

  • You may not replicate data from a source cluster running MemSQL 6.8 or earlier to a destination cluster running MemSQL 7.0 or later. This is due to how replication changed in MemSQL 7.0. You must first upgrade your source cluster to MemSQL 7.0 or later. Refer to Upgrading to MemSQL 7.0 for more information.

See the Verify your cluster is ready for upgrade section of the Upgrade guide for how to upgrade with replicated (DR) databases.

Replication Commands