Mastering Tungsten Replicator Series: Migration from an Old Database to a New Cluster

The Tungsten Replicator is an extraordinarily powerful and flexible tool capable of moving vast volumes of data from source to target.

In this blog post we will discuss the basics of how to deploy the Tungsten Replicator to pull events from an existing MySQL standalone database into a new cluster during a migration. We nicknamed this replication service "old2new". In the old2new case, the topology would be direct, and there would be only one install of Tungsten Replicator acting as both extractor and applier.

What's Happening Here?

This procedure often happens during a new install of the Tungsten Clustering software.

The proper order of events would be:

1. backup the old database, recording the binanry log position the backup was taken at. This assumes no MyISAM tables, only InnoDB.
2. Restore the database dump to all nodes in the new cluster.
3. Install the Tungsten Clustering software.
4. Install the standalone tungsten Replicator software to pull from the old database and apply to the new cluster via the Tungsten Connector.

Out With the Old

For step 1, we need to extract the data from the old server.

shell> mysqldump -u tungsten -psecret --opt --single-transaction --all-databases --add-drop-database --master-data=2 > backup.sql
shell> head -50 backup.sql | grep MASTER_LOG_POS
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=7599481;

While we are here on the old database server, let's ensure that MySQL has been configured to allow for the Tungsten Replicator to read events. For example:

mysql> drop user tungsten;
mysql> CREATE USER tungsten@'%' IDENTIFIED BY 'secret';
mysql> GRANT RELOAD, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'tungsten'@'%';

In With the New - Restore

In Step 2, we simply want to restore the entire dump to each of the new cluster nodes:

shell> sudo su - tungsten
shell> vim /etc/tungsten/tungsten.ini