3.9.3. 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.

  1. If you are using a MySQL Replica as the Primary, stop the replication thread :

    mysql> STOP SLAVE;

  2. 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 reset before proceeding. That will reset the replication position and ignore any previous events on the Primary.

  3. 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

  4. 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.

  5. 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.

  6. If you are using a MySQL Replica as the Primary, restart the replication thread after the dump file as completed loading :

    mysql> START SLAVE;

  7. Monitor replication status on the Primary and Replicas :

    shell> trepctl status