MySQL :: MySQL Cluster Manager 9.0 User Manual :: 4.9 Setting Up MySQL NDB Cluster Replication with MySQL Cluster Manager

MySQL Cluster Manager 9.0 User Manual / Using MySQL Cluster Manager / Setting Up MySQL NDB Cluster Replication with MySQL Cluster Manager

4.9 Setting Up MySQL NDB Cluster Replication with MySQL Cluster Manager

This section provides sample steps for setting up a MySQL NDB Cluster replication with a single replication channel using the MySQL Cluster Manager.

Before trying the following steps, it is recommended that you first read NDB Cluster Replication to familiarize yourself with the concepts, requirements, operations, and limitations of MySQL NDB Cluster replication.

Create and start a source cluster:

mcm> create site --hosts=tonfisk msite;

mcm> add package --basedir=/usr/local/cluster-mgt/cluster-9.0.1 9.0.1;

mcm> create cluster -P 9.0.1 -R \
       ndb_mgmd@tonfisk,ndbmtd@tonfisk,ndbmtd@tonfisk,mysqld@tonfisk,mysqld@tonfisk,ndbapi@*,ndbapi@* \
       source;

mcm> set portnumber:ndb_mgmd=4000 source;

mcm> set port:mysqld:51=3307 source;

mcm> set port:mysqld:50=3306 source;

mcm> set server_id:mysqld:50=100 source;

mcm> set log_bin:mysqld:50=binlog source;

mcm> set binlog_format:mysqld:50=ROW source;

mcm> set ndb_connectstring:mysqld:50=tonfisk:4000 source;

mcm> start cluster source;

Create and start a replica cluster (we begin with creating a new site called “ssite” just for the replica cluster; you can also skip that and put the source and replica cluster hosts under the same site instead):

mcm> create site --hosts=flundra ssite;

mcm> add package --basedir=/usr/local/cluster-mgt/cluster-9.0.1 9.0.1;

mcm> create cluster -P 9.0.1 -R \
      ndb_mgmd@flundra,ndbmtd@flundra,ndbmtd@flundra,mysqld@flundra,mysqld@flundra,ndbapi@*,ndbapi@* \
      replica;

mcm> set portnumber:ndb_mgmd=4000 replica;

mcm> set port:mysqld:50=3306 replica;

mcm> set port:mysqld:51=3307 replica;

mcm> set server_id:mysqld:50=101 replica;

mcm> set ndb_connectstring:mysqld:50=flundra:4000 replica;

mcm> set replica_skip_errors:mysqld=all replica;

mcm> start cluster replica;

Create a replica account (with the user name “myreplica” and password “mypw”) on the source cluster with the appropriate privilege by logging into the source replication client (mysqlM) and issuing the following statements:
```
mysqlM> CREATE USER 'myreplica'@'flundra' IDENTIFIED BY 'mypw';
mysqlM> GRANT REPLICATION SLAVE ON *.* TO 'myreplica'@'flundra';
```

mysqlS> CHANGE REPLICATION SOURCE TO
    -> SOURCE_HOST='tonfisk',
    -> SOURCE_PORT=3306,
    -> SOURCE_USER='myreplica',
    -> SOURCE_PASSWORD='mypw';

Start replication by issuing the following statement with the replica cluster client:
```
mysqlS> START REPLICA;
```

The above example assumes that the source and replica clusters are created at about the same time, with no data on both before replication starts. If the source cluster has already been operating and has data on it when the salve cluster is created, after step 3 above, follow these steps to transfer the data from the source cluster to the replica cluster and prepare the replica cluster for replication:

Back up your source cluster using the backup cluster command of MySQL Cluster Manager:
```
mcm> backup cluster source;
```
Note

Only NDB tables are backed up by the command; tables using other MySQL storage engines are ignored.

Look up the backup ID of the backup you just made by listing all backups for the source cluster:

mcm> list backups source;
+----------+--------+---------+---------------------+-------+---------+
| BackupId | NodeId | Host    | Timestamp           | Parts | Comment |
+----------+--------+---------+---------------------+-------+---------+
| 1        | 1      | tonfisk | 2014-10-17 20:03:23 | 1     |         |
| 1        | 2      | tonfisk | 2014-10-17 20:03:23 | 1     |         |
| 2        | 1      | tonfisk | 2014-10-17 20:09:00 | 1     |         |
| 2        | 2      | tonfisk | 2014-10-17 20:09:00 | 1     |         |
+----------+--------+---------+---------------------+-------+---------+

From the output, you can see that the latest backup you created has the backup ID “2”, and backup data exists for node “1” and “2”.

Using the backup ID and the related node IDs, identify the backup files just created under /mcm_data/clusters/cluster_name/node_id/data/BACKUP/BACKUP-backup_id/ in the source cluster's installation directory (in this case, the files under the /mcm_data/clusters/source/1/data/BACKUP/BACKUP-2 and /mcm_data/clusters/source/2/data/BACKUP/BACKUP-2), and copy them over to the equivalent places for the replica cluster (in this case, /mcm_data/clusters/replica/1/data/BACKUP/BACKUP-2 and /mcm_data/clusters/replica/2/data/BACKUP/BACKUP-2 under the replica cluster's installation directory). After the copying is finished, use the following command to check that the backup is now available for the replica cluster:
```
mcm> list backups replica;
+----------+--------+---------+---------------------+-------+---------+
| BackupId | NodeId | Host    | Timestamp           | Parts | Comment |
+----------+--------+---------+---------------------+-------+---------+
| 2        | 1      | flundra | 2014-10-17 21:19:00 | 1     |         |
| 2        | 2      | flundra | 2014-10-17 21:19:00 | 1     |         |
+----------+--------+---------+---------------------+-------+---------+
```
Restore the backed up data to the replica cluster (note that you need an unused ndbapi slot for the restore cluster command to work):
```
mcm> restore cluster --backupid=2 replica;
```

On the source cluster client, use the following command to identify the correct binary log file and position for replication to start:

mysqlM> SHOW MASTER STATUS\G;
*************************** 1. row ***************************
             File: binlog.000017
         Position: 2857
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:

On the replica cluster client, provide to the replica cluster the information of the source cluster, including the binary log file name (with the SOURCE_LOG_FILE option) and position (with the SOURCE_LOG_POS option) you just discovered in step 5 above:
```
mysqlS> CHANGE REPLICATION SOURCE TO
    -> SOURCE_HOST='tonfisk',
    -> SOURCE_PORT=3306,
    -> SOURCE_USER='myreplica',
    -> SOURCE_PASSWORD='mypw',
    -> SOURCE_LOG_FILE='binlog.000017',
    -> SOURCE_LOG_POS=2857;
```
Start replication by issuing the following statement with the replica cluster client:
```
mysqlS> START REPLICA;
```

As an alternative to these steps, you can also follow the steps described in NDB Cluster Backups With NDB Cluster Replication to copy the data from the source to the replica and to specify the binary log file and position for replication to start.

PREV HOME UP NEXT