Configuring a Job for Synchronizing Data from MySQL to GaussDB(DWS)

Supported Source and Destination Database Versions

Database Account Permissions

Before you use DataArts Migration for data synchronization, ensure that the source and destination database accounts meet the requirements in the following table. Different types of synchronization tasks require different permissions. For details, see Table 2.

GRANT SELECT, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'Username'@'%';

Supported Synchronization Objects

Table 3 lists the objects that can be synchronized in different scenarios.

Notes

In addition to the constraints on supported data sources and versions, connection account permissions, and synchronization objects, you also need to pay attention to the notes in Table 4.

Procedure

This section uses real-time synchronization from RDS for MySQL to GaussDB(DWS) as an example to describe how to configure a real-time data migration job. Before that, ensure that you have read the instructions described in Check Before Use and completed all the preparations.

1. Create a real-time migration job by following the instructions in Creating a Real-Time Migration Job and go to the job configuration page.
2. Select the data connection type. Select MySQL for Source and DWS for Destination.
   Figure 1 Selecting the data connection type
   

3. Select a job type. The default migration type is Real-time. The migration scenarios include Entire DB and Database/Table partition.
   Figure 2 Setting the migration job type
   
   

   For details about synchronization scenarios, see Synchronization Scenarios.

4. Configure network resources. Select the created MySQL and GaussDB(DWS) data connections and the migration resource group for which the network connection has been configured.
   Figure 3 Selecting data connections and a migration resource group
   
   

   If no data connection is available, click Create to go to the Manage Data Connections page of the Management Center console and click Create Data Connection to create a connection. For details, see Configuring DataArts Studio Data Connection Parameters.

5. Configure source parameters.

   Select the databases and tables to be synchronized based on the following table.

   Table 5 Selecting the databases and tables to be synchronized

   Scenario	Configuration
   Entire DB	Select synchronization objects. Table-level synchronization: Synchronize multiple tables in multiple databases of a MySQL instance. Database-level synchronization: Synchronize all tables in multiple databases of a MySQL instance. Select the MySQL databases and tables to be migrated. Figure 4 Selecting databases and tables Both databases and tables can be customized. You can select one database and one table, or multiple databases and tables.
   Database/Table partition	Add a logical table. Logical Table Name: Enter the name of the table to be written to DWS. Source Database Filter: You can enter a regular expression to filter all the database shards to be written to the destination GaussDB(DWS) aggregation table. Source Table Filter: You can enter a regular expression to filter all the table shards in the source database shard to be written to the destination GaussDB(DWS) aggregation table. Figure 5 Adding a logical table You can click Preview in the Operation column to preview an added logical table. When you preview a logical table, the more the source tables, the longer the waiting time. Figure 6 Previewing a logical table

6. Configure destination parameters.
   • Set Database and Table Matching Policy.

     For details about the matching policy between source and destination databases and tables in each synchronization scenario, see the following table.

     Table 6 Database and table matching policy

     Synchronization Scenario	Configuration Method
     Entire DB	Schema matching policy Same name as the source database: Data will be synchronized to the GaussDB(DWS) schema with the same name as the source MySQL database. Custom: Data will be synchronized to the GaussDB(DWS) schema you specify. Table matching policy Same name as the source table: Data will be synchronized to the GaussDB(DWS) table with the same name as the source MySQL table. Custom: Data will be synchronized to the GaussDB(DWS) table you specify. Figure 7 Database and table matching policy in the entire database migration scenario NOTE: When you customize a matching policy, you can use built-in variables #{source_db_name} and #{source_table_name} to identify the source database name and table name. The table matching policy must contain #{source_table_name}.
     Database/Table shard	Destination Database Name: Data will be synchronized to the specified GaussDB(DWS) schema. Table Matching Policy: By default, the value is the same as the name of the logical table entered in the source configuration. Figure 8 Database and table matching policy in the sharding scenario

   • Configure GaussDB(DWS) parameters.

     For details, see the following table.

     Figure 9 GaussDB(DWS) parameters
     

     Table 7 GaussDB(DWS) parameters

     Parameter	Default Value	Unit	Description
     Write Mode	UPSERT MODE	N/A	UPSERT MODE: batch update COPY MODE: DWS-dedicated high-performance batch import
     Maximum Data Volume for Batch Write	50000	Count	Number of data records written to GaussDB(DWS) in a batch. You can adjust the value based on the table data size and job memory usage.
     Scheduled Batch Write Interval	3	Second	Interval at which data is written to GaussDB(DWS)
     Advanced Settings	N/A	N/A	Some advanced functions can be configured using parameters. For details, see GaussDB(DWS) advanced parameters.

     Table 8 GaussDB(DWS) advanced parameters

     Parameter	Type	Default Value	Unit	Description
     sink.buffer-flush.max-size	int	512	MB	Maximum number of bytes in each batch of data written to GaussDB(DWS). You can adjust the value based on the memory and data size configured for the job.
     sink.keyby.enable	boolean	true	N/A	Whether to enable data distribution. If this function is enabled in multi-concurrency scenarios, data can be distributed to different processes based on specific rules and written to the destination, which improves the write performance.
     sink.keyby.mode	string	table	N/A	Data distribution mode. The following modes are available: pk: Data is distributed by primary key value. table: Data is distributed by table name. NOTE: In multi-concurrency scenarios, if DDL is enabled, data can be distributed only by table name. Otherwise, data may be inconsistent. If there is no DDL, you can select pk, which improves the write performance in multi-concurrency scenarios.
     sink.field.name.case-sensitive	boolean	true	N/A	Whether to enable case sensitivity for data synchronization. If this function is enabled, the database names, table names, and field names are case sensitive during data synchronization.
     sink.verify.column-number	boolean	false	N/A	Whether to verify the number of data columns. By default, the link synchronizes data in the same-name mapping mode. The system does not check whether all columns are synchronized. If this function is enabled and the number of columns at the source is different from that at the destination, the system determines that data is inconsistent. As a result, the job is abnormal.
     sink.server.timezone	string	Local time zone	N/A	Session time zone specified for connecting to the destination database. The standard time zone format is supported, for example, UTC+08:00.
     logical.delete.enabled	boolean	false	N/A	Whether to enable logical deletion
     logical.delete.column	string	logical_is_deleted	N/A	Name of the logical deletion column. The default value is logical_is_deleted. You can customize the value.

7. Refresh and check the mapping between the source and destination tables. In addition, you can modify table attributes, add additional fields, and use the automatic table creation capability to create tables in the destination GaussDB(DWS) database.
   Figure 10 Mapping between source and destination tables
   
   • Edit additional fields: Click Additional Field in the Operation column to add custom fields to the destination GaussDB(DWS) table. For a new table, you can add additional fields to the existing fields in the source table. You can customize the field name, select the field type, and enter the field value.
     • Field Name: name of the new field in the destination GaussDB(DWS) table
     • Field Type: type of the new field in the destination GaussDB(DWS) table
     • (Optional) Field Type Length: length of the new field type in the destination GaussDB(DWS) table
     • Field Value: Value source of the new field in the destination GaussDB(DWS) table

       Table 9 Additional field value obtaining mode

       Type	Example
       Constant	Digits, letters, and special characters are supported. Color emoticons may cause a job submission failure.
       Built-in variable	Source host IP address: source.host Source schema name: source.schema Source table name: source.table Destination schema name: target.schema Destination table name: target.table
       Source table field	Any field in the source table Do not change the name of the source table field when the job is running. Otherwise, the job may be abnormal.
       UDF	substring(#col, pos[, len]): obtains a substring of a specified length from the source column name. The substring range is [pos, pos+len). date_format(#col, time_format[, src_tz, dst_tz]): formats the source column name based on a specified time format. The time zone can be converted using src_tz and dst_tz. now([tz]): obtains the current time in a specified time zone. if(cond_exp, str1, str2): returns str1 if the condition expression cond_exp is met and returns str2 otherwise. concat(#col[, #str, ...]): concatenates multiple parameters, including source columns and strings. from_unixtime(#col[, time_format]): formats a Unix timestamp based on a specified time format. unix_timestamp(#col[, precision, time_format]): converts a time into a Unix timestamp of a specified time format and precision. time_format must be the same as that in the source data.

   • Automatic table creation: Click Auto Table Creation to automatically create tables in the destination database based on the configured mapping policy. After the tables are created, Existing table is displayed for them.
     Figure 11 Automatic table creation
     
     

     • DataArts Migration supports only automatic table creation. You need to manually create databases and schemas at the destination before using this function.
     • For details about the field type mapping for automatic table creation, see Field Type Mapping.
     • An automatically created Hudi table contains three audit fields: cdc_last_update_date, logical_is_deleted, and _hoodie_event_time. The _hoodie_event_time field is used as the pre-aggregation key of the Hudi table.

8. Configure DDL message processing rules.

   Real-time migration jobs can synchronize data manipulation language (DML) operations, such as adding, deleting, and modifying data, as well as some table structure changes using the data definition language (DDL). You can set the processing policy for a DDL operation to Normal processing, Ignore, or Error.

   • Normal processing: When a DDL operation on the source database or table is detected, the operation is automatically synchronized to the destination.
   • Ignore: When a DDL operation on the source database or table is detected, the operation is ignored and not synchronized to the destination.
   • Error: When a DDL operation on the source database or table is detected, the migration job throws an exception.
     Figure 12 DDL configuration
     

9. Configure task parameters.

   Table 10 Task parameters

   Parameter	Description	Default Value
   Execution Memory	Memory allocated for job execution, which automatically changes with the number of CPU cores	8GB
   CPU Cores	Value range: 2 to 32 For each CPU core added, 4 GB execution memory and one concurrency are automatically added.	2
   Maximum Concurrent Requests	Maximum number of jobs that can be concurrently executed. This parameter does not need to be configured and automatically changes with the number of CPU cores.	1
   Custom attributes	You can add custom attributes to modify some job parameters and enable some advanced functions. For details, see Job Performance Optimization.	-

10. Submit and run the job.

    After configuring the job, click Submit in the upper left corner to submit the job.

    Figure 13 Submitting the job
    

    After submitting the job, click Start on the job development page. In the displayed dialog box, set required parameters and click OK.

    Figure 14 Starting the job
    

    Table 11 Parameters for starting the job

    Parameter	Description
    Offset Parameter	Incremental synchronization: Incremental data synchronization starts from a specified time point. Full and incremental synchronization: All data is synchronized first, and then incremental data is synchronized in real time.
    Time	This parameter must be set for incremental synchronization, and it specifies the start time of incremental synchronization. NOTE: If you set a time that is earlier than the earliest binlog time, the latest log time is used.

11. Monitor the job.

    On the job development page, click Monitor to go to the Job Monitoring page. You can view the status and log of the job, and configure alarm rules for the job. For details, see Real-Time Migration Job O&M.

    Figure 15 Monitoring the job
    

Performance Optimization

If the synchronization speed is too slow, rectify the fault by referring to Job Performance Optimization.