MySQL to Greenplum Data Replication

NineData Data Replication supports schema replication, full data replication, and incremental replication from MySQL-compatible data sources to Greenplum.

Overview

Use this workflow to move operational MySQL data into Greenplum for large-scale analytics, reporting, and data warehouse workloads.

Before you begin

• Add the source and target data sources to NineData. For details, see Add data source.

• The supported MySQL source versions range from 5.1 to 9.0.

• The target database must be Greenplum 6.0 or 5.0.

• For incremental replication, enable Binlog for the source data source and set the following parameters:

  • binlog_format=ROW

  • binlog_row_image=FULL

    tip

    If the source data source is a standby database, enable log_slave_updates to capture the complete Binlog.

Restrictions

• Incremental replication does not support synchronizing DDL operations from the source.

• Data replication applies only to user databases. System databases are not replicated. For MySQL data sources, system databases include information_schema, mysql, performance_schema, and sys.

• The source account must have the following permissions:

  • Schema replication and full replication: SELECT permission on the objects to replicate.
  • Incremental replication: SELECT, REPLICATION CLIENT, and REPLICATION SLAVE permissions on the objects to replicate.

• The target account must have the following permissions on the target objects: CREATE/ALTER/DROP TABLE, SELECT, INSERT, UPDATE, DELETE, REFERENCES, CREATE, TEMPORARY, and USAGE.

• Before data synchronization, evaluate source and target performance, and run synchronization during off-peak hours when possible. Full data initialization consumes read and write resources on both sides and increases database load.

• Make sure each replicated table has a primary key or unique constraint, and that column names are unique. Otherwise, duplicate rows may be synchronized.

Procedure

1. Sign in to the NineData Console.

2. Click Replication > Data Replication from the navigation menu.

3. On the Replication page, click Create Replication.

4. On the Source & Target tab, configure the source and target, and then click Next.

   Parameter	Description
   Name	Enter a meaningful data replication task name for later search and management. Up to 64 characters are supported.
   Source	Select the MySQL data source that contains the objects to replicate.
   Target	Select the Greenplum data source that receives the replicated objects.
   Type	Select what to replicate to the target data source. Schema: Replicate only the source database and table schemas. Data is not replicated. Full: Replicate all selected source objects and data. This is full data replication. Incremental: After full replication completes, replicate incremental changes based on the source logs.
   Incremental Started	Required only when Type is Incremental. From Started: Start incremental replication from the time when the replication task starts. Customized Time: Select a custom time point for incremental replication and the time zone that matches your business location.
   Target Table Preparation	Target Table Exists Data Pre-Check Error and Stop Task: Stop the task if the precheck detects existing data in the target table. Ignore existing target data and append to it.: If the precheck detects existing data in the target table, ignore that data and append new data. Clear target existing data before write: If the precheck detects existing data in the target table, delete the existing data before writing new data.

5. On the Objects tab, configure the replication objects, and then click Next.

   Parameter	Description
   Replication Objects	Select the objects to replicate. Select All Objects to replicate all objects in the source database, or select Customized Object, choose the objects in the Source Object list, and click > to move them to the Target Object list.

6. On the Mapping tab, select the operation based on the selected replication type.

   • If Schema is included: NineData matches the schema with the same name in the target data source by default. If no matching schema exists, select the target schema manually. Map table and column names if the target names must differ from the source names. After you finish mapping, click Save and Pre-Check.

   • If Schema is not included: the table and column names in the target database must match the replication objects. If they do not match, manually map the table and column names. After you finish mapping, click Save and Pre-Check.

7. On the Pre-check tab, wait for NineData to complete the precheck. After the precheck passes, click Launch.

   tip

   • If the precheck fails, click Details in the Actions column for the failed item to view the cause. After fixing the issue, click Check Again to run the precheck again.
   • For items whose Result is Warning, fix the issue or ignore the warning.

8. On the Launch page, the Launch Successfully message appears, and the replication task starts running. Use these actions:

   • Click View Details to view the execution status of each replication phase.
   • Click Back to list to return to the Replication task list.

Result

1. Sign in to the NineData Console.

2. In the navigation menu, click Replication > Data Replication.

3. On the Replication page, click the Task ID of the target replication task. The task detail page contains the following areas.

   

   No.	Area	Description
   1	Synchronization Delay	Shows the synchronization delay between the source and target. 0 seconds means there is no delay, which is the expected state before a smooth traffic switch.
   2	Configure Alerts	After alerts are configured, NineData notifies users when the task fails. For more information, see Introduction to operational monitoring.
   3	More	Pause: Pause a task whose status is Running. Terminate: End a task that is not complete or is still being monitored, such as a task in incremental synchronization. A terminated task cannot be restarted. Proceed with caution. If the synchronization objects contain triggers, select the trigger replication option if applicable. Delete: Delete the task. Deleted tasks cannot be recovered. Proceed with caution.
   4	Schema Replication (shown when schema replication is included)	Shows schema replication progress and details. Click Logs to view the schema replication execution log. Click to view the latest information. Click View DDL in the Operation column of a target object to view the replayed DDL.
   5	Full Replication (shown when full replication is included)	Shows full replication progress and details. Click Monitoring to view full replication metrics. During full replication, click Flow Control on the monitoring page to limit the write rate to the target data source. The unit is rows per second. Click Logs to view the full replication execution log. Click to view the latest information.
   6	Incremental Replication (shown when incremental replication is included)	Shows incremental replication metrics. Click View Threads to view currently running operations for the replication task, including: Thread ID: The thread ID. A replication task can use multiple threads for replication operations. Execute SQL: Details of the SQL statement that the current thread is executing. Response Time: The response time of the current thread. If this value increases, the thread may be blocked. Event Time: The timestamp when the current thread started. Status: The current thread status. Click Flow Control to limit the write rate to the target data source. The unit is rows per second. Click Logs to view the incremental replication execution log. Click to view the latest information.
   7	Modify Object	Shows the modification history of replicated objects. Click Modify Synchronized Objects to configure replication objects . Click to view the latest information.
   8	Data Comparison	Shows data comparison results between the source and target. If data comparison is not enabled, click Enable Data Comparison on the page. Click Recompare to compare data between the current source and target again. Click Logs to view the consistency comparison execution log. Click Monitoring to view the comparison RPS trend. Click Details to view earlier records. Click in the Operation column of the comparison list (shown when data is inconsistent) to view source and target comparison details. Click in the Operation column of the comparison list (shown when data is inconsistent) to generate change SQL. Run the SQL on the target data source to fix the inconsistency.
   9	Expand	Shows task details, including Replication Type, Replication Object, and Start Time.

Appendix 1: MySQL to Greenplum data type mapping

During data replication, NineData maps MySQL data types to corresponding Greenplum data types.

Category	MySQL Data Type	Greenplum Data Type
Numeric	TINYINT	SMALLINT
	TINYINT UNSIGNED	SMALLINT
	SMALLINT	SMALLINT
	SMALLINT UNSIGNED	INTEGER
	MEDIUMINT	INTEGER
	MEDIUMINT UNSIGNED	INTEGER
	INT	INTEGER
	INT UNSIGNED	BIGINT
	BIGINT	BIGINT
	BIGINT UNSIGNED	BIGINT
	BIT(M)	BIT(N)
	DECIMAL()	DECIMAL
	NUMERIC	DECIMAL
	FLOAT	FLOAT
	DOUBLE	FLOAT8
	BOOL, BOOLEAN	SMALLINT
DATE AND TIME	DATE	DATE
	DATETIME[(fsp)]	TIMESTAMP[(p)]
	TIMESTAMP[(fsp)]	TIMESTAMP[(p)] with time zone
	TIME[(fsp)]	TEXT
	YEAR[(4)]	INTEGER
STRING	CHAR	CHAR[(n)]
	VARCHAR	VARCHAR
	BINARY/VARBINARY	BYTEA
	TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT	TEXT
	TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB	BYTEA
	ENUM	VARCHAR
	SET	VARCHAR
JSON	JSON	JSON
SPATIAL	GEOMETRY	TEXT
	GEOMETRYCOLLECTION	TEXT
	POINT	TEXT
	LINESTRING	TEXT
	POLYGON	TEXT
	MULTIPOINT	TEXT
	MULTIPOLYGON	TEXT
	MULTILINESTRING	TEXT

Appendix 2: Precheck items

Check item	What NineData checks	Blocks task continuation	Common fix
Source Data Source Connection Test	Connectivity between NineData and the source data source.	Yes	Check firewall, ECS security group, cloud database security group or whitelist, and JDBC username and password settings. Allow NineData service IP addresses where required, and then run the precheck again.
Target Data Source Connection Test	Connectivity between NineData and the target data source.	Yes	Check the target data source security group or whitelist and JDBC username and password settings. Allow NineData service IP addresses where required, and then run the precheck again.
Source Data Source Permission Check	Whether the source account has the required permissions.	Yes	Grant SELECT, REPLICATION CLIENT, and REPLICATION SLAVE permissions to the source account, and then run the precheck again.
Target Data Source Permission Check	Whether the target account has the required permissions.	Yes	Grant CREATE/ALTER/DROP TABLE, SELECT, INSERT, UPDATE, DELETE, REFERENCES, CREATE, TEMPORARY, and USAGE permissions to the target account, and then run the precheck again.
Source Database log_slave_updates Support Check	Whether log_slave_updates is enabled when the source database is a standby database.	Yes	Enable log_slave_updates, and then run the precheck again.
Source Database Binlog Activation Check	Whether Binlog is enabled for the source database.	Yes	Enable Binlog, and then run the precheck again.
Source Database binlog format Support Check (row mode)	Whether the source database binlog_format is ROW.	Yes	Set binlog_format to ROW, and then run the precheck again.
binlog_row_image Check (full)	Whether the source database binlog_row_image is FULL.	Yes	Set binlog_row_image to FULL, and then run the precheck again.
Same Name Object Existence Check	Whether the target contains objects with the same names as the objects to replicate.	Depends on the selected handling strategy.	Modify the same-name object handling strategy or delete the same-name objects, and then run the precheck again.
Data Existence Check	Whether target objects already contain data.	Depends on the selected existing-data handling strategy.	Modify the existing-data handling strategy or clear the existing target data manually, and then run the precheck again.

Next steps

• Introduction to Data Replication