This topic describes the data types and parameters that are supported by MySQL Writer and how to configure MySQL Writer by using the codeless user interface (UI) and code editor.

Prerequisites

Before you configure MySQL Writer, you must configure a MySQL data source. For more information, see Add a MySQL data source.

Background information

MySQL Writer writes data to tables stored in MySQL databases. MySQL Writer connects to a remote MySQL database by using Java Database Connectivity (JDBC), and executes an INSERT INTO or a REPLACE INTO statement to write data to the MySQL database. MySQL uses the InnoDB engine so that data is written to the database in batches.

MySQL Writer can also be used as a migration tool by users such as database administrators. MySQL Writer obtains data from a reader and writes the data to the destination database based on value of the writeMode parameter.
Note A synchronization node that uses MySQL Writer must have at least the permissions to execute the INSERT INTO or REPLACE INTO statement. Whether other permissions are required depends on the SQL statements that you specify in the preSql and postSql parameters when you configure the node.

Data types

MySQL Writer supports most MySQL data types. Make sure that the data types of your database are supported.

The following table lists the data types that are supported by MySQL Writer.
Category MySQL data type
Integer INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT, and YEAR
Floating point FLOAT, DOUBLE, and DECIMAL
String VARCHAR, CHAR, TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT
Date and time DATE, DATETIME, TIMESTAMP, and TIME
Boolean BOOL
Binary TINYBLOB, MEDIUMBLOB, BLOB, LONGBLOB, and VARBINARY

Parameters

Parameter Description Required Default value
datasource The name of the data source. It must be the same as the name of the added data source. You can add data sources by using the code editor. Yes No default value
table The name of the table to which you want to write data. Yes No default value
writeMode The write mode. Valid values: insert into, on duplicate key update, and replace into.
  • insert into: If a primary key conflict or unique index conflict occurs, data cannot be written to the conflicting rows, and the data that is not written to these rows is regarded as dirty data.

    If you create a synchronization node by using the code editor, set writeMode to insert.

  • on duplicate key update: If no primary key conflict or unique index conflict occurs, the data is processed in the same way as that when you set this parameter to insert into. If a conflict occurs, specified fields in the original rows are replaced by new rows, and data is written to MySQL.

    If you create a synchronization node by using the code editor, set writeMode to update.

  • replace into: If no primary key conflict or unique index conflict occurs, the data is processed in the same way as that when you set this parameter to insert into. If a conflict occurs, the original rows are deleted, and new rows are inserted. This indicates that all fields of the original rows are replaced.

    If you create a synchronization node by using the code editor, set writeMode to replace.

No insert into
column The names of the columns to which you want to write data. Separate the names with commas (,), such as "column": ["id", "name", "age"]. If you want to write data to all the columns in the destination table, set this parameter to an asterisk (*), such as "column": ["*"]. Yes No default value
preSql The SQL statement that you want to execute before the synchronization node is run. You can execute only one SQL statement on the codeless UI and multiple SQL statements in the code editor. For example, you can set this parameter to the TRUNCATE TABLE tablename statement to delete outdated data.
Note If you specify multiple SQL statements, the statements are not executed in the same transaction.
No No default value
postSql The SQL statement that you want to execute after the synchronization node is run. You can execute only one SQL statement on the codeless UI and multiple SQL statements in the code editor. For example, you can set this parameter to the ALTER TABLE tablename add colname timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP statement to add a timestamp.
Note If you specify multiple SQL statements, the statements are not executed in the same transaction.
No No default value
batchSize The number of data records to write at a time. Set this parameter to an appropriate value based on your business requirements. This greatly reduces the interactions between Data Integration and MySQL and increases throughput. If you set this parameter to an excessively large value, an out of memory (OOM) error may occur during data synchronization. No 1,024

Configure MySQL Writer by using the codeless UI

Create a synchronization node and configure the node. For more information, see Configure a sync node by using the codeless UI.

Perform the following steps on the configuration tab of the node:
  1. Configure data sources.
    Configure Source and Target for the synchronization node. Connections
    Parameter Description
    Connection The name of the data source to which you want to write data. This parameter is equivalent to the datasource parameter that is described in the preceding section.
    Table The table to which you want to write data. This parameter is equivalent to the table parameter that is described in the preceding section.
    Statement Run Before Writing The SQL statement that you want to execute before the synchronization node is run. This parameter is equivalent to the preSql parameter that is described in the preceding section.
    Statement Run After Writing The SQL statement that you want to execute after the synchronization node is run. This parameter is equivalent to the postSql parameter that is described in the preceding section.
    Solution to Primary Key Violation The write mode. This parameter is equivalent to the writeMode parameter that is described in the preceding section.
  2. Configure field mappings. This operation is equivalent to setting the column parameter that is described in the preceding section. Fields in the source on the left have a one-to-one mapping with fields in the destination on the right. Field mappings
    Operation Description
    Map Fields with the Same Name Click Map Fields with the Same Name to establish mappings between fields with the same name. The data types of the fields must match.
    Map Fields in the Same Line Click Map Fields in the Same Line to establish mappings between fields in the same row. The data types of the fields must match.
    Delete All Mappings Click Delete All Mappings to remove the mappings that are established.
    Auto Layout Click Auto Layout. Then, the system automatically sorts the fields based on specific rules.
  3. Configure channel control policies. Channel control
    Parameter Description
    Expected Maximum Concurrency The maximum number of parallel threads that the synchronization node uses to read data from the source or write data to the destination. You can configure the parallelism for the synchronization node on the codeless UI.
    Bandwidth Throttling Specifies whether to enable bandwidth throttling. You can enable bandwidth throttling and specify a maximum transmission rate to prevent heavy read workloads on the source. We recommend that you enable bandwidth throttling and set the maximum transmission rate to an appropriate value based on the configurations of the source.
    Dirty Data Records Allowed The maximum number of dirty data records allowed.

Configure MySQL Writer by using the code editor

In the following code, a synchronization node is configured to write data to a MySQL database by using the code editor. For more information, see Create a sync node by using the code editor.
{
    "type":"job",
    "version":"2.0",// The version number. 
    "steps":[ 
        {
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"mysql",// The writer type. 
            "parameter":{
                "postSql":[],// The SQL statement that you want to execute after the synchronization node is run. 
                "datasource":"",// The name of the data source. 
                "column":[// The names of the columns to which you want to write data. 
                    "id",
                    "value"
                ],
                "writeMode":"insert",// The write mode. Valid values: insert, replace, and update. 
                "batchSize":1024,// The number of data records to write at a time. 
                "table":"",// The name of the table to which you want to write data. 
                "preSql":[ 
                     "delete from XXX;" // The SQL statement that you want to execute before the synchronization node is run. 
                   ]
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{// The maximum number of dirty data records allowed. 
            "record":"0"
        },
        "speed":{
            "throttle":true,// Specifies whether to enable bandwidth throttling. The value false indicates that bandwidth throttling is disabled, and the value true indicates that bandwidth throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
            "concurrent":1, // The maximum number of parallel threads. 
            "mbps":"12"// The maximum transmission rate.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}