:Import data from a MySQL database

Limits

• This feature is supported only in the following regions: China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Zhangjiakou), China (Hohhot), China (Ulanqab), China (Chengdu), China (Shenzhen), China (Heyuan), China (Guangzhou), China (Hong Kong), Singapore, Malaysia (Kuala Lumpur), Indonesia (Jakarta), Philippines (Manila), Thailand (Bangkok), Japan (Tokyo), South Korea (Seoul), UK (London), Germany (Frankfurt), US (Virginia), and US (Silicon Valley).

• If you import data from an AnalyticDB for MySQL source, you must convert JSON fields to the VARCHAR type before import. For example, if a field named detail is of the JSON data type, use cast(detail as VARCHAR) as detail to perform the conversion. For more information about the CAST function, see CAST function.

Import data

1. Log on to the Simple Log Service console.

2. In the Projects section, click the one you want.

   

3. In the Log Storage > Logstore pane, click > next to the target Logstore. Hover over Data Collection > Data Import and click +.

4. In the Quick Data Import wizard, search for and click MySQL - Data Import.

5. Configure the data import job.

   1. In the Configure Data Source step, configure the following parameters.

      Basic parameters

      Parameter	Description
      Task Name	The unique name of the data import job.
      Display Name	The display name of the job.
      Job Description	The description of the data import job.
      Use VPC Endpoint	RDS instance in VPC Select Use VPC Endpoint and ApsaraDB RDS Instance, and then configure the following parameters. VPC ID: The ID of the VPC where the ApsaraDB RDS for MySQL instance is located. RDS Instance ID: The ID of the ApsaraDB RDS for MySQL instance. You must set this parameter to the VpcCloudInstanceId of the instance. You can call the DescribeDBInstanceAttribute operation to obtain the VpcCloudInstanceId value. Self-managed on ECS Select Use VPC Endpoint and Self-managed Database on ECS, and then configure the following parameters. VPC ID: The ID of the VPC where the ECS instance is located. ECS Instance IP Address: The private IP address of the ECS instance. Public database Clear the Use VPC Endpoint check box and configure the following parameter. Public IP Address or Domain Name of Database: The public IP address or domain name of the MySQL database.
      MySQL Port	The service port of the MySQL database. Default value: 3306.
      Username	The username that is used to log on to the MySQL database. We recommend granting this account read-only permissions.
      Password	The password of the account.
      Database	The name of the MySQL database.
      Query	You can use a SELECT or SHOW statement. If you use a SHOW statement, you cannot set the Page Size parameter in the advanced settings. You can use a question mark (?) as a placeholder, which works with the CheckPoint Field. For example, if your query statement is SELECT * FROM employees WHERE id > ?, you can set CheckPoint Field to id and Starting Value of CheckPoint Field to 0. After each import job is complete, the system saves the ID of the last record as a checkpoint. The next time the job runs, the question mark (?) in the query statement is replaced with this checkpoint value.

      Advanced parameters

      Parameter	Description
      Maximum Number of Rows for Each Import	The maximum number of rows to import in each scheduled job. A value of 0 means no limit. Default: 0.
      Connection Timeout Settings	The timeout period for connecting to the MySQL database. Default value: 3,000 ms.
      Query Timeout Settings	The timeout period for querying the MySQL database. Default value: 5,000 ms.
      Page Size	A value of 0 disables pagination. If the value is greater than 0, the system appends limit pageSize offset {checkpoint} to the SELECT statement, allowing data to be imported in multiple pages within a single job.
      Time Field	The field in the MySQL data that represents time, such as create_time or update_time. Simple Log Service uses this field's value as the timestamp for the imported data.
      Time Field Format	The format of the time field. Java-based time formats are supported, such as yyyy-MM-dd HH:mm:ss. For more information, see Time formats.
      Time Zone	The time zone of the time field.
      CheckPoint Field	Select a checkpoint field to specify the starting point for the next data import. For example, if the query statement is select * from t where id > ? and id is an auto-incrementing field, you can set id as the checkpoint field. You can also use fields such as create_time or update_time. Note If you do not specify a time range in your SQL query, we recommend configuring a checkpoint field to import incremental data and prevent data duplication.
      Starting Value of CheckPoint Field	The initial value for the checkpoint field.
      Additional Fields	Add constant fields to the imported data. The format is map[string]string. For example, if your query statement is select a, b from t;, the system imports the fields a and b by default. You can add other fields to import into SLS, such as {"k1": "v1", "k2": "v2"}.

   2. Click Preview to check the import results. After you confirm the results, click Next Configuration.

   3. In the Scheduling Interval step, configure the following parameters.

      Parameter	Description
      Import Interval	The interval at which data is imported from the MySQL database to Simple Log Service.
      Execute Now	Select Execute Now to run the import job once.

   4. After the configuration is complete, click Next.

6. Create indexes and preview data. Then, click Next. By default, full-text indexing is enabled in Simple Log Service. You can also manually create field indexes for the collected logs or click Automatic Index Generation. Then, Simple Log Service generates field indexes. For more information, see Create indexes.

   Important

   If you want to query all fields in logs, we recommend that you use full-text indexes. If you want to query only specific fields, we recommend that you use field indexes. This helps reduce index traffic. If you want to analyze fields, you must create field indexes. You must include a SELECT statement in your query statement for analysis.

7. You must wait approximately 1 minute for the indexes to take effect. Then, you can view the collected logs on the Raw Logs tab. For more information about how to query and analyze logs, see Query and analysis quick start.

View the import job and statistical reports

After you create a data import job, you can view its configuration and statistical reports in the console.

1. In the Projects section, click the target project.

2. In the left-side navigation pane, choose Data Collection > Data Import, and then click the name of the target job.

3. On the Import Configuration Overview page, view the basic information and statistical reports for the import job.

Related operations

On the Import Configuration Overview page, you can also perform the following operations.

• Modify a job

  Click Edit Configurations to change the job configuration. For parameter details, see Import data.

• Delete a job

  Click Delete Configuration to delete the data import job.

  Warning

  This operation cannot be undone. Proceed with caution.