3.7. Replicating Data from a Cluster into MySQL

If you have an existing cluster and you want to replicate the data out to a separate standalone MySQL server using Tungsten Replicator then you can create a cluster alias, and use a master/slave topology to replicate from the cluster. This allows for THL events from the cluster to be applied to a separate server for the purposes of backup or separate analysis.

Figure 3.5. Topologies: Replicating Data from a Cluster into MySQL

Topologies: Replicating Data Out of a Cluster into MySQL

During the installation process a cluster-alias and cluster-slave are declared. The cluster-alias describes all of the servers in the cluster and how they may be reached. The cluster-slave defines one or more servers that will replicate from the cluster.

The Tungsten Replicator will be installed on the cluster-slave server. That server will download THL data and apply them to the local server. If the cluster-slave has more than one server; one of them will be declared the relay (or master). The other members of the cluster-slave may also download THL data from that server.

If the relay for the cluster-slave fails; the other nodes will automatically start downloading THL data from a server in the cluster. If a non-relay server fails; it will not have any impact on the other members.

Important

If you are replicating to a non-MySQL target, please see Section 3.8, “Replicating from a Cluster to a Datawarehouse” for more information.

3.7.1. Prepare: Replicating Data from a Cluster into MySQL

  1. Identify the cluster to replicate from. You will need the master, slaves and THL port (if specified). Use tpm reverse from a cluster member to find the correct values.

  2. Identify all servers that will replicate from the cluster. If there is more than one, a relay server should be identified to replicate from the cluster and provide THL data to other servers.

  3. Prepare each server according to the prerequisites for the DBMS platform it is serving. If you are working with multiple DBMS platforms; treat each platform as a different cluster-slave during deployment.

  4. Make sure the THL port for the cluster is open between all servers.

3.7.2. Deploy: Replicating Data from a Cluster into MySQL

3.7.2.1. Replicating Data from a Cluster to MySQL (Staging Use Case)

The following Staging-method procedure will install the Tungsten Replicator software onto target node host6, extracting from a cluster consisting of three (3) nodes (host1, host2 and host3) and applying into the target database on host6.

Important

If you are replicating to a non-MySQL target, please see Section 3.8, “Replicating from a Cluster to a Datawarehouse” for more information.

  1. On your staging server, go to the software directory.

    shell> cd /opt/continuent/software
  2. Download the latest Tungsten Replicator version.

  3. Unpack the release package

    shell> tar xvzf tungsten-replicator-6.0.3-599.tar.gz
  4. Change to the unpackaged directory:

    shell> cd tungsten-replicator-6.0.3-599
  5. Execute the tpm command to configure defaults for the installation.

    shell> ./tools/tpm configure defaults \
        --install-directory=/opt/replicator \
        '--profile-script=~/.bashrc' \
        --replication-password=secret \
        --replication-port=13306 \
        --replication-user=tungsten \
        --start-and-report=true \
        --user=tungsten

    The description of each of the options is shown below; click the icon to hide this detail:

    Click the icon to show a detailed description of each argument.

  6. Configure a cluster alias that points to the masters and slaves within the current Continuent Tungsten service that you are replicating from:

    shell> ./tools/tpm configure alpha \
        --master=host1 \
        --slaves=host2,host3 \
        --thl-port=2112 \
        --topology=cluster-alias

    The description of each of the options is shown below; click the icon to hide this detail:

    Click the icon to show a detailed description of each argument.

    Important

    This dataservice cluster-alias name MUST be the same as the cluster dataservice name that you are replicating from.

  7. Create the configuration that will replicate from cluster dataservice alpha into the database on the host specified by --relay=host6:

    shell> ./tools/tpm configure omega \
        --relay=host6 \
        --relay-source=alpha \
        --topology=cluster-slave

    The description of each of the options is shown below; click the icon to hide this detail:

    Click the icon to show a detailed description of each argument.

  8. If you are using a non-standard, possibly offboard, MySQL target like AWS RDS or Google CloudSQL use the following examples to configure the needed additional options for the omega replicator service to run in Offboard mode.

    Important

    If you are replicating to a non-MySQL target, please see Section 3.8, “Replicating from a Cluster to a Datawarehouse” for more information.

    • AWS RDS MySQL/MariaDB Offboard Target

      shell> ./tools/tpm configure omega \
      --replication-host=RDS_ENDPOINT_FQDN_HERE \
      --replication-password=RDS_PASSWORD_HERE \
      --replication-port=3306 \
      --replication-user=tungsten \
      --privileged-slave=false \
      --skip-validation-check=InstallerMasterSlaveCheck \
      --skip-validation-check=MySQLPermissionsCheck \
      --skip-validation-check=MySQLBinaryLogsEnabledCheck
      

      Please see Installing MySQL to Amazon RDS Replication (in [Tungsten Replicator 4.0 Manual]) for more information.

    • Google CloudSQL Offboard Target

      shell> ./tools/tpm configure omega \
      --datasource-type=mysql \
      --privileged-slave=false \
      --replication-host=CLOUDSQL_ENDPOINT_FQDN_HERE \
      --replication-password=CLOUDSQL_PASSWORD_HERE \
      --replication-port=3306 \
      --replication-user=tungsten \
      --skip-validation-check=MySQLPermissionsCheck
      
  9. Once the configuration has been completed, you can perform the installation to set up the Tungsten Replicator services using the tpm command run from the staging directory:

    shell> ./tools/tpm install

If the installation process fails, check the output of the /tmp/tungsten-configure.log file for more information about the root cause.

The cluster-slave replicator should now be installed and ready to use.

3.7.2.2. Replicating Data from a Cluster to MySQL (INI Use Case)

The following INI-based procedure will install the Tungsten Replicator software onto target node host6, extracting from a cluster consisting of three (3) nodes (host1, host2 and host3) and applying into the target database on host6.

Important

If you are replicating to a non-MySQL target, please see Section 3.8, “Replicating from a Cluster to a Datawarehouse” for more information.

  1. Create the configuration file /etc/tungsten/tungsten.ini on the destination DBMS host, i.e. host6:

    [defaults]
    user=tungsten
    install-directory=/opt/replicator
    replication-user=tungsten
    replication-password=secret
    replication-port=3306
    profile-script=~/.bashrc
    start-and-report=true
    
    [alpha]
    topology=cluster-alias
    master=host1
    members=host1,host2,host3
    thl-port=2112
    
    [omega]
    topology=cluster-slave
    relay=host6
    relay-source=alpha
    

    The description of each of the options is shown below; click the icon to hide this detail:

    Click the icon to show a detailed description of each argument.

    Important

    The cluster-alias name (i.e. alpha) MUST be the same as the cluster dataservice name that you are replicating from.

    Note

    Do not include start-and-report=true if you are taking over for MySQL native replication. See Section 5.11.1, “Migrating from MySQL Native Replication 'In-Place'” for next steps after completing installation.

  2. If you are using a non-standard MySQL target like AWS RDS or Google CloudSQL use the following examples to configure the needed additional options for the replicator service omega to run in Offboard mode.

    Important

    If you are replicating to a non-MySQL target, please see Section 3.8, “Replicating from a Cluster to a Datawarehouse” for more information.

    Append the appropriate code snippet below to the bottom of the existing [omega] stanza:

    • AWS RDS MySQL/MariaDB Offboard Target

      replication-host=RDS_ENDPOINT_FQDN_HERE
      replication-password=RDS_PASSWORD_HERE
      replication-port=3306
      replication-user=tungsten
      privileged-slave=false
      skip-validation-check=InstallerMasterSlaveCheck
      skip-validation-check=MySQLPermissionsCheck
      skip-validation-check=MySQLBinaryLogsEnabledCheck
      

      Please see Installing MySQL to Amazon RDS Replication (in [Tungsten Replicator 4.0 Manual]) for more information.

    • Google CloudSQL Offboard Target

      datasource-type=mysql
      privileged-slave=false
      replication-host=CLOUDSQL_ENDPOINT_FQDN_HERE
      replication-password=CLOUDSQL_PASSWORD_HERE
      replication-port=3306
      replication-user=tungsten
      skip-validation-check=MySQLPermissionsCheck
      
  3. Download and install the latest Tungsten Replicator package (.rpm), or download the compressed tarball and unpack it on host6:

    shell> cd /opt/continuent/software
    shell> tar xvzf tungsten-replicator-6.0.3-599.tar.gz
  4. Change to the Tungsten Replicator staging directory:

    shell> cd tungsten-replicator-6.0.3-599
  5. Run tpm to install the Tungsten Replicator software with the INI-based configuration:

    shell > ./tools/tpm install

    During the installation and startup, tpm will notify you of any problems that need to be fixed before the service can be correctly installed and started. If the service starts correctly, you should see the configuration and current status of the service.

If the installation process fails, check the output of the /tmp/tungsten-configure.log file for more information about the root cause.

The cluster-slave replicator should now be installed and ready to use.

3.7.3. Best Practices: Replicating Data from a Cluster into MySQL