Tungsten Clustering

Migrating and Seeding Data

Migrating from MySQL Native Replication 'In-Place'

If you are migrating an existing MySQL native replication deployment to use Tungsten Cluster the configuration of the Tungsten Cluster replication must be updated to match the status of the replica.

Deploy Tungsten Cluster using the model or system appropriate according to "deployment". Ensure that the Tungsten Cluster is not started automatically by excluding the start or start-and-report options from the configuration.

On each replica

Confirm that native replication is working on all replica nodes :

MySQL 8.4+

shell> echo 'SHOW REPLICA STATUS\G' | tpm mysql | \
egrep 'Source_Host| Last_Error| Replica_SQL_Running' 
                Source_Host: tr-ssl1
          Replica_SQL_Running: Yes
                 Last_Error: 

Upto MySQL 8.0

shell> echo 'SHOW SLAVE STATUS\G' | tpm mysql | \
egrep 'Master_Host| Last_Error| Slave_SQL_Running' 
                Master_Host: tr-ssl1
          Slave_SQL_Running: Yes
                 Last_Error: 

On the primary and each replica
Reset the Tungsten Replicator position on all servers :
```
shell> replicator start offline
shell> trepctl -service alpha reset -all -y
```
On the primary
Login and start Tungsten Cluster services and put the Tungsten Replicator online:
```
shell> startall
shell> trepctl online
```
On the primary
Put the cluster into MAINTENANCE mode using cctrl to prevent Tungsten Cluster automatically reconfiguring services:
```
cctrl> set policy maintenance
```
On each replica
MySQL 8.4+
Record the current replica log position (as reported by the Relay_Source_Log_File and Exec_Source_Log_Pos output from SHOW REPLICA STATUS. Ideally, each replica should be stopped at the same position:
```
shell> echo 'SHOW REPLICA STATUS\G' | tpm mysql | \
egrep 'Source_Host| Last_Error| Relay_Source_Log_File| Exec_Source_Log_Pos' 
                Source_Host: tr-ssl1
      Relay_Source_Log_File: mysql-bin.000025
                 Last_Error: Error executing row event: 'Table 'tungsten_alpha.heartbeat' doesn't exist'
        Exec_Source_Log_Pos: 181268
```
Upto MySQL 8.0
Record the current replica log position (as reported by the Relay_Master_Log_File and Exec_Master_Log_Pos output from SHOW SLAVE STATUS. Ideally, each replica should be stopped at the same position:
```
shell> echo 'SHOW SLAVE STATUS\G' | tpm mysql | \
egrep 'Master_Host| Last_Error| Relay_Master_Log_File| Exec_Master_Log_Pos' 
                Master_Host: tr-ssl1
      Relay_Master_Log_File: mysql-bin.000025
                 Last_Error: Error executing row event: 'Table 'tungsten_alpha.heartbeat' doesn't exist'
        Exec_Master_Log_Pos: 181268
```
If you have multiple replicas configured to read from this primary, record the replica position individually for each host. Once you have the information for all the hosts, determine the earliest log file and log position across all the replicas, as this information will be needed when starting Tungsten Cluster replication. If one of the servers does not show an error, it may be replicating from an intermediate server. If so, you can proceed normally and assume this server stopped at the same position as the host is replicating from.

On the primary

Take the replicator offline and clear the THL:

shell> trepctl offline
shell> trepctl -service alpha reset -all -y

On the primary
Start replication, using the lowest binary log file and log position from the replica information determined in step 6.
```
shell> trepctl online -from-event 000025:181268
```
Tungsten Replicator will start reading the MySQL binary log from this position, creating the corresponding THL event data.
On each replica
1. Disable native replication to prevent native replication being accidentally started on the replica.
  MySQL 8.4+
```
shell> echo "STOP REPLICA; RESET REPLICA ALL;" | tpm mysql
```
  Upto MySQL 8.0
```
shell> echo "STOP SLAVE; RESET SLAVE ALL;" | tpm mysql
```
2. If the final position of MySQL replication matches the lowest across all replicas, start Tungsten Cluster services:
```
shell> trepctl online
shell> startall
```
  The replica will start reading from the binary log position configured on the primary.
  If the position on this replica is different, use trepctl online -from-event to set the online position according to the recorded position when native MySQL was disabled. Then start all remaining services with startall.
```
shell> trepctl online -from-event 000025:188249
shell> startall
```
Check that replication is operating correctly by using trepctl reset on the primary and each replica to confirm the correct position, or use cctrl.
Put the cluster back into AUTOMATIC mode:
```
cctrl> set policy automatic
```
Update your applications to use the installed connector services rather than a direct connection.
Remove the master.info file on each replica to ensure that when a replica restarts, it does not connect up to the primary MySQL server again.

Once these steps have been completed, Tungsten Cluster should be operating as the replication service for your MySQL servers. Use the information in "operations" to monitor and administer the service.

Migrating from MySQL Native Replication Using a New Service

When running an existing MySQL native replication service that needs to be migrated to a Tungsten Cluster service, one solution is to create the new Tungsten Cluster service, synchronize the content, and then install a service that migrates data from the existing native service to the new service while applications are reconfigured to use the new service. The two can then be executed in parallel until applications have been migrated.

The basic structure is shown below.

Migration: Migrating Native Replication using a New Service

The migration consists of two steps:

Initializing the new service with the current database state.
Creating a Tungsten Replicator deployment that continues to replicate data from the native MySQL service to the new service.

Once the application has been switched and is executing against the new service, the secondary replication can be disabled by shutting down the Tungsten Replicator in /opt/replicator.

To configure the service:

Stop replication on a replica for the existing native replication installation :

MySQL 8.4+

mysql> STOP REPLICA;

Obtain the current replica position within the primary binary log :

mysql> SHOW REPLICA STATUS\G
...
                Source_Host: host3
      Relay_Source_Log_File: mysql-bin.000002
        Exec_Source_Log_Pos: 559
...

Upto MySQL 8.0

mysql> STOP SLAVE;

Obtain the current replica position within the primary binary log :

mysql> SHOW SLAVE STATUS\G
...
                Master_Host: host3
      Relay_Master_Log_File: mysql-bin.000002
        Exec_Master_Log_Pos: 559
...

Create a backup using any method that provides a consistent snapshot. The MySQL primary may be used if you do not have a replica to backup from. Be sure to get the binary log position as part of your backup. This is included in the output to xtrabackup or using the --master-data=2 option with mysqldump.
Restart the replica using native replication :
MySQL 8.4+
```
mysql> START REPLICA;
```
Upto MySQL 8.0
```
mysql> START SLAVE;
```
On the primary and each replica within the new service, restore the backup data and start the database service
Setup the new Tungsten Cluster deployment using the MySQL servers on which the data has been restored. For clarity, this will be called newalpha.
Configure a second replication service, beta to apply data using the existing MySQL native replication server as the primary, and the primary of newalpha. The information provided in "deployment-replicatorin" will help.
Start the replicator in an offline state:
```
shell> replicator start offline
```
Set the replication position for beta to the point within the binary logs where the backup was taken:
```
shell> trepctl online -from-event 000002:559
```

Once replication has been started, use trepctl to check the status and ensure that replication is operating correctly.

The original native MySQL replication primary can continue to be used for reading and writing from within your application, and changes will be replicated into the new service on the new hardware. Once the applications have been updated to use the new service, the old servers can be decommissioned and replicator service beta stopped and removed.

Seeding Data through MySQL

Once the Tungsten Replicator is installed, it can be used to provision all replicas with the primary data. The replicas will need enough information in order for the installation to succeed and for Tungsten Replicator to start. The provisioning process requires dumping all data on the primary and reloading it back into the primary server. This will create a full set of THL entries for the replica replicators to apply. There may be no other applications accessing the primary server while this process is running. Every table will be emptied out and repopulated so other applications would get an inconsistent view of the database. If the primary is a MySQL replica, then the replica process may be stopped and started to prevent any changes without affecting other servers.

If you are using a MySQL replica as the primary, stop the replication thread :
MySQL 8.4+
```
mysql> STOP REPLICA;
```
Upto MySQL 8.0
```
mysql> STOP SLAVE;
```
Check Tungsten Replicator status on all servers to make sure it is ONLINE and that the appliedLastSeqno values are matching :
```
shell> trepctl status
```
Starting the process before all servers are consistent could cause inconsistencies. If you are trying to completely reprovision the server then you may consider running trepctl restore before proceeding. That will reset the replication position and ignore any previous events on the primary.

Use mysqldump to output all of the schemas that need to be provisioned :

shell> mysqldump --opt --skip-extended-insert -hhost3 -utungsten -P13306 -p \
  --databases db1,db2 > ~/dump.sql

Optionally, you can just dump a set of tables to be provisioned :

shell> mysqldump --opt --skip-extended-insert -hhost3 -utungsten -P13306 -p \
  db1 table1 table2 > ~/dump.sql

If you are using heterogeneous replication all tables on the replica must be empty before proceeding. The Tungsten Replicator does not replicate DDL statements such as DROP TABLE and CREATE TABLE. You may either truncate the tables on the replica or use ddlscan to recreate them.
Load the dump file back into the primary to recreate all data :
```
shell> cat ~/dump.sql | tpm mysql
```
The Tungsten Replicator will read the binary log as the dump file is loaded into MySQL. The replicas will automatically apply these statements through normal replication.
If you are using a MySQL replica as the primary, restart the replication thread after the dump file has completed loading :
MySQL 8.4+
```
mysql> START REPLICA;
```
Upto MySQL 8.0
```
mysql> START SLAVE;
```
Monitor replication status on the primary and replicas :
```
shell> trepctl status
```

Migrating from MySQL Native Replication 'In-Place'​

Migrating from MySQL Native Replication Using a New Service​

Seeding Data through MySQL​

Migrating from MySQL Native Replication 'In-Place'

Migrating from MySQL Native Replication Using a New Service

Seeding Data through MySQL