MySQL to Redshift

NineData Data Replication supports full data replication and incremental replication from MySQL-compatible data sources to Amazon Redshift.

Overview

Use this workflow to load MySQL data into Redshift for analytics and warehouse workloads, and keep the target data updated with incremental source changes.

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 an Amazon Redshift provisioned cluster or Amazon Redshift Serverless.

• For incremental replication, the source data source must have Binlog enabled, with 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.

• Schema replication is not supported. Create target tables in Redshift before you start the task, and make sure the target tables match the source objects to replicate. For MySQL-to-Redshift data type mapping, see Appendix 1.

• 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 SELECT permission on the objects to replicate. For incremental replication, the source account must also have REPLICATION CLIENT and REPLICATION SLAVE permissions. The target account must have DML permissions on the target tables.

• 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. In the navigation menu, click Replication > Data Replication.

3. On the Replication page, click Create Replication.

4. On the Source & Target tab, configure the source and target, 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 Redshift data source that receives the replicated data.
   Type	Select what to replicate to the target data source. 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. Click the icon to clear operation types that you do not want to replicate during incremental synchronization. Cleared operations are ignored during incremental synchronization.
   IAM Role	Enter an IAM role that can execute commands in Redshift. During replication, NineData first stages MySQL data in the NineData Amazon S3 service, and then loads the data from S3 into your Redshift data source. The role authorizes NineData to run Redshift copy commands. For how to obtain the IAM role ARN, see How to ? on the task creation page.
   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, 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.
   Blacklist (optional)	Click Add to add a blacklist rule. Select the databases or objects to exclude from replication. Blacklist rules are used to exclude specific databases or objects when Customized Object or Full Instance replication is selected. Left drop-down list: Select the database to add to the blacklist. Right drop-down list: Select one or more objects in the selected database. Leave this field empty to add the entire database to the blacklist. To exclude multiple databases, click Add again to add more rows.

6. On the Mapping tab, 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. 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.

View replication results

1. Sign in to the NineData Console.

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

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	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 Settings 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.
   5	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 Settings 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.
   6	Modify Object	Shows the modification history of replicated objects. Click Modify Synchronized Object to configure replication objects . Click to view the latest information.
   7	Expand	Shows task details, including Replication Type, Replication Object, and Start Time.

Appendix 1: MySQL to Redshift data type mapping

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

Category	MySQL Data Type	Redshift Data Type
Numeric	TINYINT	SMALLINT
	TINYINT UNSIGNED	SMALLINT
	SMALLINT	SMALLINT
	SMALLINT UNSIGNED	INTEGER
	MEDIUMINT	INTEGER
	MEDIUMINT UNSIGNED	BIGINT
	INT	INTEGER
	INT UNSIGNED	BIGINT
	BIGINT	BIGINT
	BIGINT UNSIGNED	BIGINT
	BIT(M)	BOOL
	DECIMAL	DECIMAL
	NUMERIC	NUMERIC
	FLOAT	REAL
	DOUBLE	REAL
	BOOL, BOOLEAN	BOOL
DATE AND TIME	DATE	DATE
	DATETIME[(fsp)]	TIMESTAMP
	TIMESTAMP[(fsp)]	TIMESTAMPTZ
	TIME[(fsp)]	TIME
	YEAR[(4)]	INT
STRING	CHAR	CHAR
	VARCHAR	VARCHAR
	BINARY/VARBINARY	VARBINARY
	TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT	VARCHAR
	TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB	VARBINARY
	ENUM	VARCHAR
	SET	VARCHAR
JSON	JSON	VARCHAR

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 the required DML permissions to the target account, and then run the precheck again.
Source Binlog check	Whether Binlog is enabled for the source database.	Yes	Enable Binlog, and then run the precheck again.
Source Binlog format check	Whether the source database binlog_format is ROW.	Yes	Set binlog_format to ROW, and then run the precheck again.
Source Binlog row image check	Whether the source database binlog_row_image is FULL.	Yes	Set binlog_row_image to FULL, and then run the precheck again.
Target same-name object 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.
Target 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.

Related documents

• Introduction to Data Replication