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
- User Steps
- Step 1 - User Creates Source and Destination Database Property Files
- Step 2 - User Creates XTS File from Source Database
- Step 3 - User Creates a Project with Default Project Version
- Step 4 - User Imports Table Schema using created XTS File
- Step 5 - User Creates a G-Migration+ Configuration
- Step 6 - User Adds Customer Table to G-Migration+ Configuration
- Step 7 - User Adds Data Subsetting Conditions
- Step 8 - User Downloads G-Migration+ Configuration
- Step 9 - User Runs Command at Command Line
- Data Subsetting Results
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.
- Source MySQL Database (source_db)
- Target MySQL Database (target_db)
- G-Migration+ Feature
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
The user must complete the following steps to migrate the customer data subset successfully.
Step 1 - User Creates
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.
driver=com.mysql.jdbc.Driver user=root password=root url=jdbc:mysql://localhost:3306/source_db?rewriteBatchedStatements=true batchCount=1000
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
genrocket -xts C:\Users\username\config\source_db.properties
Sample Command (Mac OSx/Linux)
genrocket -xts /home/users/username/config/source_db.properties
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: 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
genrocket -gmigp SubsettingOnlyDemo.gredb
Data Subsetting Results
After data subsetting has been completed, the user sees the following results in the destination/target database.
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.
There are also 100 address records (1 for each customer). The address_id starts at 251 and ends at 350.
There are also 100 account records (1 for each customer). The account_id starts at 251 and ends at 350.