A MySQL data source allows you to read data from MySQL by using MySQL Reader and write data to MySQL by using MySQL Writer. You can configure synchronization nodes for MySQL by using the codeless user interface (UI) or code editor. This topic describes the network environment and permissions that you must prepare before you configure a data source. It also describes how to add a MySQL data source to DataWorks.

Prerequisites

Before you configure a data source, make sure that the following operations are performed:
  • Prepare the data source: A MySQL data source is created.
  • Plan and prepare resources: An exclusive resource group for data integration is purchased and configured. For more information, see Plan and configure resources.
  • Evaluate and plan the network environment: Before you add the data source to DataWorks, connect the data source to an exclusive resource group for data integration based on your business requirements. After the data source and exclusive resource group for data integration are connected, configure access settings such as a vSwitch and a whitelist.
    • If data sources and exclusive resource groups for data integration reside in the same region and virtual private cloud (VPC), they are automatically connected.
    • If data sources and exclusive resource groups for data integration reside in different network environments, you must connect data sources and resource groups by using methods such as a VPN gateway.
    For more information about how to configure a whitelist, see Configure whitelists for data sources.
  • Prepare an account and authorize the account:

    You must create an account that can be used to access data sources, read data from the source data source, and write data to the destination data source in the data integration process.

    For more information, see Create and authorize an account.
  • Enable the MySQL binary logging feature. This feature is required only for real-time data synchronization. For more information about real-time data synchronization, see Overview of real-time synchronization.

    If the source data source is a MySQL database, you must enable the binary logging feature. Binary logs record changes to all table schemas and modifications on table data. You can execute statements such as CREATE and ALTER to perform operations on table schemas. You can execute statements such as INSERT, UPDATE, and DELETE to perform operations on table data. You can use binary logs to view the change history of the database, back up incremental data and restore data in the database, and replicate data from the primary database to secondary databases.

    Formats of binary logs:
    • Statement: SQL statement-based replication. Binary logs in this format record the SQL statements that are executed to modify data entries.
    • Row: row-based replication. Binary logs in this format record only modification details about data entries in rows.
    • Mixed: replication in mixed mode. This mode combines the statement and row formats. In most cases, binary logs in the statement format are used to record the SQL statements that are executed to modify data entries, such as functions. If data replication from the primary database to secondary databases cannot be implemented by using binary logs in this format, switch to the row format. MySQL determines which format to use based on each SQL statement that is executed.
    For more information about how to enable the binary logging feature, see Enable the binary logging feature.

Background information

Workspaces in standard mode allow you to isolate data sources. You can add data sources for the development and production environments separately to isolate the data sources. This ensures data security. For more information, see Isolate connections between the development and production environments.

Add a MySQL data source

  1. Go to the Data Source page.
    1. Log on to the DataWorks console.
    2. In the left-side navigation pane, click Workspaces.
    3. In the top navigation bar, select the region where your workspace resides. Find your workspace and click Data Integration in the Actions column.
    4. On the page that appears, click Connection in the left-side navigation pane. The Data Source page appears.
  2. On the Data Source page, click New data source in the upper-right corner.
  3. In the Add data source dialog box, click MySQL in the Relational Database section.
  4. In the Add MySQL data source dialog box, configure the parameters.
    You can set the Data source type parameter to Alibaba Cloud instance mode or Connection string mode for a MySQL data source.
    • The following table describes the parameters that are displayed after you set the Data source type parameter to Alibaba Cloud instance mode.Alibaba Cloud instance mode
      Parameter Description
      Data source type The type of the data source. Set this parameter to Alibaba Cloud instance mode.
      Data Source Name The name of the data source. The name must contain letters, digits, and underscores (_), and must start with a letter.
      Data source description The description of the data source. The description can be a maximum of 80 characters in length.
      Environment Valid values: Development and Production.
      Note This parameter is displayed only when the workspace is in standard mode.
      Region The region where the ApsaraDB RDS for MySQL instance resides.
      RDS instance ID The ID of the ApsaraDB RDS for MySQL instance. You can view the ID in the ApsaraDB RDS console.
      RDS instance account ID The ID of the Alibaba Cloud account that is used to purchase the ApsaraDB RDS for MySQL instance. Use the Alibaba Cloud account to log on to the DataWorks console. Move the pointer over the profile picture in the upper-right corner and select Security Settings. Then, view the ID of the Alibaba Cloud account.
      Database name The name of the ApsaraDB RDS for MySQL database.
      User name The username that is used to connect to the database.
      Password The password that is used to connect to the database.
      Note You must add the IP addresses or Classless Inter-Domain Routing (CIDR) blocks that you use to connect to the ApsaraDB RDS for MySQL instance to a whitelist of the instance. For more information, see Configure whitelists.
    • The following table describes the parameters that are displayed after you set the Data source type parameter to Connection string mode.JDBC
      Parameter Description
      Data source type The type of the data source. Set this parameter to Connection string mode.
      Data Source Name The name of the data source. The name must contain letters, digits, and underscores (_), and must start with a letter.
      Data source description The description of the data source. The description can be a maximum of 80 characters in length.
      Network connection type Valid values:
      • Aliyun Vpc
      • IDC
      • public
      • classic
      Environment Valid values: Development and Production.
      Note This parameter is displayed only when the workspace is in standard mode.
      JDBC URL The Java Database Connectivity (JDBC) URL of the database, in the format of jdbc:mysql://ServerIP:Port/Database.
      User name The username that is used to connect to the database.
      Password The password that is used to connect to the database.
  5. On the Data Integration tab, click Test connectivity in the Operation column of each resource group.
    A sync node uses only one resource group. To ensure that your sync nodes can be properly run, you must test the connectivity of all the resource groups for Data Integration on which your sync nodes will be run. If you need to test the connectivity of multiple resource groups for Data Integration at a time, select the resource groups and click Batch test connectivity. For more information, see Test data store connectivity.
  6. After the connection passes the connectivity test, click Complete.

What to do next

You have learned how to configure a MySQL data source. You can proceed to subsequent tutorials. The subsequent tutorials describe how to configure MySQL Reader or MySQL Writer. For more information, see Configure MySQL Reader and MySQL Writer.