Description

This article provides a basic use case example of migrating a subset of data from a source MySQL database to an identical MySQL database using the G-Migration+ feature. 


In This Article


User Story

A power user (e.g., Test Data Engineer/TDE) wants to migrate a subset of customer records from one MySQL database to another identical MySQL database. The subset must contain 100 customer records and all associated address/account records.


Actors

  • Source MySQL Database (source_db)
  • Target MySQL Database (target_db)
  • G-Migration+ Feature
  • XTS File
  • Config.properties Files


Source Database Tables/Records

  • Customer - 1000 Customer Records
  • Address - 1 Address Record per Customer Record (1000 total)
  • Account - 1 Account Record per Customer Record (1000 total)


Data Subset Requirements

  • The user wants the starting customer record of the data subset to be greater than '250' and limit the number of rows in the subset to '100'.

  • The result should be:
    • Customer Records 251 to 350 migrated to Target Database
    • Associated Address Records migrated to Target Database (100 total)
    • Associated Account Records migrated to Target Database (100 total)


Entity Relationship (ER) Diagram


User Steps

The user must complete the following steps to migrate the customer data subset successfully.


Step 1 - User Creates Source and Destination Database Property Files

For GenRocket to perform data subsetting, configuration files must be present to connect to the source and destination databases via JDBC. Two properties files must be present on the user's local computer and contain the appropriate database connection information. For example, they can be called source.properties and destination.properties. 


These files can be placed within a subdirectory and must be present to connect to each database during the data migration. For more information on how to create these configuration files, please click here.


For this example, the following files will be used and placed in a Config subdirectory within the path specified for the user's resource.JDBC directory resource in the GenRocket web platform.


source_db.properties

driver=com.mysql.jdbc.Driver
user=root
password=root
url=jdbc:mysql://localhost:3306/source_db?rewriteBatchedStatements=true
batchCount=1000


target_db.properties

driver=com.mysql.jdbc.Driver
user=root
password=root
url=jdbc:mysql://localhost:3306/target_db?rewriteBatchedStatements=true
batchCount=1000


Note: Please note that 'root' has been entered for the user name and password information. The user will need to use each database's actual user and password information.

Step 2 - User Creates XTS File from Source Database

A database schema must be created using the genrocket -xts command. If this has not already been done, please do so before continuing. 


Sample Command (Windows Machine)

genrocket -xts C:\Users\username\config\source_db.properties


Sample Command (Mac OSx/Linux)

genrocket -xts /home/users/username/config/source_db.properties


Created File


Note: Users can specify a schema, tables, etc., as part of the genrocket -xts command. Click here to learn more.


Step 3 - User Creates a Project with Default Project Version

A project with the default project version must be created and available to perform basic data subsetting between databases.


Note: Click here to learn more about creating Projects in the GenRocket web platform. 


Step 4 - User Imports Table Schema using created XTS File

  • The user selects G-Migration+ in Self Service Menu for Project Version.
  • The user clicks the Import Table Schema button and selects the XTS file. 
  • The XTS is imported, and the user can view the Table Schema.


    Note: Click     here to see the complete steps for importing a table schema. 


Step 5 - User Creates a G-Migration+ Configuration

  • The user clicks Create G-Migration+ Config button.


  • The user enters/selects the following information: 
    • Name = SubsettingOnlyDemo
    • Description = Migrate Subset of Customer Records
    • DataDumpSubDir = dump
    • Partition Receiver = MySQLPartitionReceiver
    • Source Database
      • Name = source_db
      • JDBCSubDirectory = Config
      • JDBCConfigName = source_db.properties
    • Destination Database
      • Name = target_db
      • JDBCSubDirectory = Config
      • JDBCConfigName = target_db.properties
  • The user clicks Save.



Step 6 - User Adds Customer Table to G-Migration+ Configuration

Next, the user adds the Customer Table to the G-Migration+ configuration. It must be added before the user can add data subsetting conditions. 

  • The user clicks the Manage Tables button.


  • The user selects the Customer table and clicks Save.


  • The user clicks Cancel for sensitive column selection.


    Note: Click     here for step-by-step instructions on how to add tables for data subsetting.


Step 7 - User Adds Data Subsetting Conditions

Please note that subsetting conditions can only be applied to one table. The user adds the required subsetting conditions to the customer table by completing the following steps: 

  • The user clicks on the Edit (Pencil) icon for the customer table.



  • The user enters the following Where clause:
    • customer.id > 250
  • The user selects "number" for the row limit and enters "100".
  • The user clicks Save.



  • Note: The Traverse Full Hierarchy option informs G-Migration+ to migrate records for other children (and their parents) of ancestor tables and is not used in this example. To learn more about this option, click here. 

  • Note: Click here for step-by-step instructions on configuring data subsetting conditions for a table. 


Step 8 - User Downloads G-Migration+ Configuration

  • The user downloads the G-Migration+ Configuration.
  • The user downloads the Default Scenario.



  • The user places the Default Scenario in the path defined for the resource.output.directory.



  • Please note that this is the default path that is automatically configured for the chosen Partition Receiver (e.g., MySQLPartitionReceiver) and is where it will look for the Default Scenario.


Step 9 - User Runs Command at Command Line

The user enters the following command using the name of their G-Migration+ configuration: 

genrocket -gmigp <G-Migration+ Name>.gredb

Example

genrocket -gmigp SubsettingOnlyDemo.gredb



Data Subsetting Results

After data subsetting has been completed, the user sees the following results in the destination/target database. 


Customer Records

The first customer record starts at '251,' and the last customer ends at '350'. There are a total of '100' customer records in the customer table for the target_db database.



Address Records

There are also 100 address records (1 for each customer). The address_id starts at 251 and ends at 350.


Account Records

There are also 100 account records (1 for each customer). The account_id starts at 251 and ends at 350.


G-Migration+ Traverse Full Hierarchy Option