Updated on 2025-05-08 GMT+08:00

MySQL

The MySQL connector allows you to connect applications with the MySQL database, and manage data transmission.

  • MySQL is a popular, open-source relational database widely used as the backend of web applications. It supports multiple operating systems, such as Windows, Linux, Unix, and macOS X. MySQL features high performance, reliability, and flexibility, and is popular among enterprises and developers.
  • The default MySQL port number is 3306. The administrator can change the port number as required. MySQL supports multiple storage engines, including InnoDB, MyISAM, and MEMORY. Each storage engine has its own pros and cons and can be configured flexibly.

Creating a MySQL Connection

  1. Log in to the new ROMA Connect console.
  2. In the navigation pane on the left, choose Connector. On the page displayed, click New Connection.
  3. Select the MySQL connector.
  4. In the dialog box displayed, configure the connector and click Test connection. After the connection is successful, click OK.

    Parameter

    Description

    Name

    Enter the connector instance name.

    Region

    Select a region.

    Project

    Select a project.

    Instance

    Select an instance for subsequent connectivity verification.

    Connection and Security

    Select the connection mode for the database. Currently, the Default mode is supported.

    IP Address

    Enter the IP address of the database.

    Port

    Enter the port number to which the database is connected.

    Database Name

    Enter the name of the database to be connected.

    Username

    Enter the username used to connect to the database.

    Password

    Enter the password used to connect to the database.

    Description

    Enter the description of the connector to identify it.

Action

  • Obtain Record: Retrieve data from a MySQL table by specifying the table name, WHERE condition, Order By field, Limit, and Offset.
  • Incrementally Obtain Record: Periodically fetch new or updated records from a MySQL table since a specified start time. This action must be used together with a timer.
  • Add Record: Insert a new row into a specified MySQL table.
  • Update Record: Update one or more rows in a specified MySQL table by applying filter criteria and providing new values.
  • Replicate Record: Replicate data to a specified MySQL data table. This action must be used together with a timer and CDC must be enabled
  • Delete Record: Delete one or more rows from a specified MySQL table based on filter criteria.
  • Custom SQL: Execute native SQL statements for operations on the MySQL database.

Configuring Parameters

  • Obtaining records
    Table 1 Configuration parameters

    Parameter

    Description

    Table Name

    Select or enter the table name in the database to be queried.

    Field Name

    Select the field name of the data table to be queried. If this parameter is not specified, all fields will be returned.

    WHERE Condition

    Filter query results using conditions like column_name='value'. Supported operators: =, !=, >, <, >=, <=, like, in, not in, is null, and is not null.

    Order By Field

    Specify one or more columns to sort results, for example, id DESC.

    Limits

    Limit the number of returned rows, for example, 100.

    Offset

    Specify the starting row number for returned data, for example, 15.

    Data Schema

    Configure output fields. Fields are auto-generated when a table is selected but can be edited manually.

    Table 2 Output parameters

    Parameter

    Description

    Payload

    Response in JSON format, with keys as the database field names and values as their corresponding data. Entries on each line are comma-separated. For payload details, see Referencing Variables.

    Selected Count

    Number of rows in the query result. For details about the property, see Referencing Variables.

    After the execution is successful, the payload value is as follows:

    [
        {
            "Product_Id": "f47ac10b-58cc-4372-a567-0e02b2c3d479",
            "Created_Time": "2024-11-29T15:06:09Z",
            "Product_Price": 1299,
            "Product_Name": "Product A",
            "Product_Desc": "Product A"
        },
        {
            "Product_Id": "c3d7b1b7-4cd1-4d72-b1b8-7d38e1a1fe5a",
            "Created_Time": "2024-11-29T15:06:38Z",
            "Product_Price": 1099,
            "Product_Name": "Product C",
            "Product_Desc": "Product C"
        }
    ]
  • Obtaining incremental records
    Table 3 Configuration parameters

    Parameter

    Description

    Table Name

    Select or enter the name of the table to obtain incremental records.

    Field Name

    Select the field name of the data table to be queried.

    WHERE Condition

    Filter query results using conditions like column_name='value'. Supported operators: =, !=, >, <, >=, <=, like, in, not in, is null, and is not null.

    Order By Field

    Specify one or more columns to sort results, for example, id DESC.

    Start Timezone for incremental database selection

    Start time zone for obtaining incremental data.

    Incremental Selection Timestamp Field Name

    Timestamp field for obtaining incremental data. The selected field must be of the time or timestamp type.

    Incremental Selection Timestamp Initial Value

    Initial timestamp for obtaining incremental data.

    Reset Initial Incremental Selection Time

    Whether to enable the reset of the initial time for obtaining incremental data. If this function is disabled, the system obtains incremental data from the current time.

    Incremental Selection Compensation Period (ms)

    Specify the extended time range (ms) to avoid missing records that may arrive late.

    Data Schema

    Configure output fields. Fields are auto-generated when a table is selected but can be edited manually.

    Table 4 Output parameters

    Parameter

    Description

    Payload

    Response in JSON format, with keys as the database field names and values as their corresponding data. Entries on each line are comma-separated. For payload details, see Referencing Variables.

    Example of obtaining incremental records:

    Timer (triggered every day) → MySQL (obtaining incremental records) → Log collection

  • Adding records
    Table 5 Configuration parameters

    Parameter

    Description

    Table Name

    Enter the name of the table where data will be inserted.

    Data to Insert

    Enter the value of the target field.

    Data Schema

    Configure output fields. Fields are auto-generated when a table is selected. This setting is not applicable for this action.

    Table 6 Output parameters

    Parameter

    Description

    Updated Count

    Row number of the inserted data. For details about the property, see Referencing Variables.

  • Updating records
    Table 7 Configuration parameters

    Parameter

    Description

    Table Name

    Select or enter the name of the table where data is to be updated.

    Data to Update

    Enter the value of the target field.

    WHERE Condition

    Select the target row using conditions like column_name='value'. Supported operators: =, !=, >, <, >=, <=, like, in, not in, is null, and is not null.

    Data Schema

    Configure output fields. Fields are auto-generated when a table is selected. This setting is not applicable for this action.

    Table 8 Output parameters

    Parameter

    Description

    Updated Count

    Row number of the updated data. For details about the property, see Referencing Variables.

  • Replicating records
    Table 9 Configuration parameters

    Parameter

    Description

    Table Name

    Select or enter the name of the table where data is to be synchronized.

    Clear Table

    Whether to clear the table each time data is replicated.

    Table 10 Output parameters

    Parameter

    Description

    Payload

    Response in JSON format, with keys as the database field names and values as their corresponding data. Entries on each line are comma-separated. For payload details, see Referencing Variables.

    This action supports the following scenarios:

    1. Stream data from one database to another with identical table names

      MySQL: Capture Database Changes → Replicate Record

      Timer (any action) → MySQL (Incrementally Obtain Record) → MySQL (Replicate Record)

    2. Stream data between databases with different table names (data mapping processor for table name mapping)

      MySQL (Capture Database Changes) → Data Mapper → MySQL (Replicate Record)

      Timer (any action) → MySQL (Incrementally Obtain Record) → Data Mapper → MySQL (Replicate Record)

  • Deleting records
    Table 11 Configuration parameters

    Parameter

    Description

    Table Name

    Select or enter the name of the table where data is to be deleted.

    WHERE Condition

    Select the target row using conditions like column_name='value'. Supported operators: =, !=, >, <, >=, <=, like, in, not in, is null, and is not null.

    Data Schema

    Configure output fields. Fields are auto-generated when a table is selected. This setting is not applicable for this action.

    Table 12 Output parameters

    Parameter

    Description

    Updated Count

    Row number of the deleted data. For details about the property, see Referencing Variables.

  • Custom SQL
    Table 13 Custom SQL

    Parameter

    Description

    Statement

    Statements display all your inputs as plain text. Do not include sensitive information. Only one select, update, insert, or delete statement can be executed. The select statement can return up to 500 records.

    If the table name contains a hyphen (-), use backquotes (`) to enclose the table name. For example, insert into `bfs-test`.source_01(id, name) values (01, 'testname01');. Otherwise, a syntax error is reported.

    Table 14 Output parameters

    Parameter

    Description

    Payload

    Response in JSON format, with keys as the database field names and values as their corresponding data. Entries on each line are comma-separated. For payload details, see Referencing Variables.

    Updated count

    Number of updated rows.

    Selected count

    Number of rows of data queried.

    Start time

    Start time of node execution.

    End time

    End time of node execution.

    Execution time

    Node execution duration, in milliseconds.