All Products
Search
Document Center

:Collect MySQL query results

Last Updated:Dec 20, 2023

This topic describes how to create a Logtail configuration in the Simple Log Service console to collect MySQL query results.

Prerequisites

Principle

Logtail executes the SELECT statement that is specified in a Logtail configuration on a regular basis, and then uploads the query results to Simple Log Service.

After Logtail obtains query results, Logtail saves the value of the CheckPoint field in the results to the Logtail server. The next time Logtail executes the SELECT statement, Logtail adds the value of the CheckPoint field to the SELECT statement. This way, Logtail can collect incremental data.

Important

If you use checkpoints during data collection, you must sort the values of the CheckPoint field in the SELECT statement. Otherwise, data duplication or other issues may occur.

image

Features

  • You can collect query results from MySQL databases.

  • You can configure paged query settings.

  • You can specify time zones.

  • You can specify timeout periods.

  • You can save the values of the CheckPoint field.

  • SSL is supported.

  • You can specify the maximum number of logs that can be collected at a time.

Scenarios

  • Collect incremental data based on marks such as an auto-increment ID or a point in time.

  • Synchronize data based on filter conditions.

Procedure

  1. Log on to the Simple Log Service console.

  2. In the Import Data section, find and click MySQL Query Result - Plug-in.

  3. Select the project and Logstore. Then, click Next.

  4. Create a machine group.

    • If a machine group is available, click Use Existing Machine Groups.

    • If no machine groups are available, perform the following steps to create a machine group. In this example, an Elastic Compute Service (ECS) instance is used.

      1. On the ECS Instances tab, select Manually Select Instances. Then, select the ECS instance that you want to use and click Create.

        For more information, see Install Logtail on ECS instances.

        Important

        If your server is an ECS instance that belongs to a different Alibaba Cloud account from Simple Log Service, a server in a data center, or a server from a third-party cloud service provider, you must manually install Logtail on the server. For more information, see Install Logtail on a Linux server or Install Logtail on a Windows server. After you manually install Logtail, you must configure a user identifier for the server. For more information, see Configure a user identifier.

      2. After you confirm the settings, click OK.

      3. After Logtail is installed, click Complete Installation.

      4. In the Create Machine Group step, configure the Name parameter and click Next.

        Simple Log Service allows you to create IP address-based machine groups and custom identifier-based machine groups. For more information, see Create an IP address-based machine group and Create a custom identifier-based machine group.

  5. Confirm that the machine group is displayed in the Applied Server Groups section and click Next.

    Important

    If you apply a machine group immediately after you create the machine group, the heartbeat status of the machine group may be FAIL. This issue occurs because the machine group is not connected to Simple Log Service. To resolve this issue, you can click Automatic Retry. If the issue persists, see What do I do if no heartbeat connections are detected on Logtail?

  6. In the Specify Data Source step, configure the Config Name and Plug-in Configuration parameters. Then, click Next.

    You can specify a data source by using form configuration or editor configuration in JSON. For more information, see Details of the Logtail configuration.

  7. Preview data, configure indexes, and then click Next.
    By default, full-text indexing is enabled for Log Service. You can also configure field indexes based on collected logs in manual mode or automatic mode. To configure field indexes in automatic mode, click Automatic Index Generation. This way, Log Service automatically creates field indexes. For more information, see Create indexes.
    Important If you want to query and analyze logs, you must enable full-text indexing or field indexing. If you enable both full-text indexing and field indexing, the system uses only field indexes.
  8. Click Log Query. You are redirected to the query and analysis page of your Logstore.
    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, see Query and analyze logs.

Details of the Logtail configuration

You can specify a data source by using form configuration or editor configuration in JSON.

Form configuration

In the Specify Data Source step, configure the following parameters.

Parameter

Description

Config Name

The name of the Logtail configuration.

MySQLAddress

The address of the MySQL database. Example: rm-****.mysql.rds.aliyuncs.com.

Database Username

The username of the account that is used to log on to the MySQL database.

Database Password

The password of the account that is used to log on to the MySQL database.

If you have high requirements for data security, we recommend that you set the username and password to xxx. After your configurations are synchronized to the Logtail server, find the two parameters in the /usr/local/ilogtail/user_log_config.json file and change the values based on your business requirements. For more information, see Modify the Logtail configuration on the Logtail server.

Important

If you modify this parameter in the Simple Log Service console, the parameter setting in the Logtail configuration on the Logtail server is overwritten after the modification is synchronized to the server.

Database Name

The name of the MySQL database.

Database Connection Timeout Period

The timeout period for connections to the MySQL database. Unit: milliseconds.

Query Timeout Period

The timeout period for reads from the MySQL database. Unit: milliseconds.

SELECT Statement

The SELECT statement.

If you select Use CheckPoint, you must include the column that is specified for the CheckPoint Column Name parameter in a WHERE clause of the SELECT statement that you specify for the SELECT Statement parameter. You can use question marks (?) to indicate replacement characters. If you use a question mark in the SELECT statement, you must also configure the CheckPoint Column Name parameter.

For example, if you set the CheckPoint Column Name parameter to id and set the Initial Value of CheckPoint Column parameter to 0, you must specify the value of the SELECT Statement parameter in the SELECT * from ... where id > ? format. Each time a query is performed, the system saves the ID of the last data record as the checkpoint. In the next query, the question mark (?) that is specified in the SELECT statement is replaced with the ID that corresponds to the saved checkpoint.

Use LIMIT Clause to Perform Paged Queries

Specifies whether to use a LIMIT clause to paginate query results.

We recommend that you select Use LIMIT Clause to Perform Paged Queries. If you select Use LIMIT Clause to Perform Paged Queries, a LIMIT clause is automatically added to the SELECT statement that you specify for the SELECT Statement parameter when Logtail executes the SQL statement.

Page Size

The maximum number of logs that can be returned on each page.

If you select Use LIMIT Clause to Perform Paged Queries, you must configure this parameter.

Maximum Records per Synchronization

The maximum number of logs that can be synchronized at a time. Default value: 0, which indicates that the number is unlimited.

Use CheckPoint

Specifies whether to use checkpoints during data collection. If you select Use CheckPoint, checkpoints are used during data collection. A checkpoint can be used as the start point of the next data collection. This way, incremental data is collected.

CheckPoint Column Name

The name of the column that stores checkpoints.

If you select Use CheckPoint, you must configure this parameter.

Warning

Values in the checkpoint column must be incremental. Otherwise, some data may not be collected. The maximum value in the results of a query operation is used as the input for the next query operation.

Data Type of CheckPoint Column

The type of the checkpoint column. Valid values: int and time. If you set this parameter to int, the values in the checkpoint column are of the int64 type. If you set this parameter to time, the values in the checkpoint column can be of the date, datetime, or time type that is supported by MySQL.

If you select Use CheckPoint, you must configure this parameter.

Initial Value of CheckPoint Column

The initial value of the checkpoint column.

If you select Use CheckPoint, you must configure this parameter.

Save CheckPoint per Pagination

If you select Save CheckPoint per Pagination, checkpoints are saved each time query results are paginated. If you do not select Save CheckPoint per Pagination, checkpoints are saved each time query results are synchronized.

Synchronized At

The synchronization interval. Unit: milliseconds.

Editor configuration in JSON

In the Plug-in Configuration field, specify information about your Logtail configuration.

  • inputs is required and is used to configure the data source settings for the Logtail configuration.

    Important

    You can specify only one type of data source in inputs.

  • processors is optional and is used to configure the data processing settings for the Logtail configuration to parse data. You can specify one or more processing methods.

    If your logs cannot be parsed based only on the setting of inputs, you can configure processors in the Plug-in Configuration field to add plug-ins for data processing. For example, you can extract fields, extract log time, mask data, and filter logs. For more information, see Use Logtail plug-ins to process data.

{
  "inputs": [
    {
      "type": "service_mysql",
      "detail": {
        "Address": "************.mysql.rds.aliyuncs.com",
        "User": "****",
        "Password": "*******",
        "DataBase": "****",
        "Limit": true,
        "PageSize": 100,
        "StateMent": "select * from db.VersionOs where time > ?",
        "CheckPoint": true,
        "CheckPointColumn": "time",
        "CheckPointStart": "2018-01-01 00:00:00",
        "CheckPointSavePerPage": true,
        "CheckPointColumnType": "time",
        "IntervalMs": 60000
      }
    }
  ]
}

Parameter

Type

Required

Description

type

string

No

The type of the data source. Set the value to service_mysql.

Address

string

No

The address of the MySQL database. Example: rm-****.mysql.rds.aliyuncs.com.

Default value: 127.0.0.1:3306.

User

string

No

The username of the account that is used to log on to the MySQL database. Default value: root.

Password

string

No

The password of the account that is used to log on to the MySQL database. This parameter is empty by default.

If you have high requirements for data security, we recommend that you set the username and password to xxx. After your configurations are synchronized to the Logtail server, find the two parameters in the /usr/local/ilogtail/user_log_config.json file and change the values based on your business requirements. For more information, see Modify the Logtail configuration on the Logtail server.

Important

If you modify this parameter in the Simple Log Service console, the parameter setting in the Logtail configuration on the Logtail server is overwritten after the modification is synchronized to the server.

DataBase

string

No

The name of the MySQL database.

DialTimeOutMs

int

No

The timeout period for connections to the MySQL database. Unit: milliseconds. Default value: 5000.

ReadTimeOutMs

int

No

The timeout period for reads from the MySQL database. Unit: milliseconds. Default value: 5000.

StateMent

string

No

The SELECT statement.

If you set the CheckPoint parameter to true, you must include the column that is specified for the CheckPointColumn parameter in a WHERE clause of the SELECT statement that you specify for the StateMent parameter. You can use question marks (?) to indicate replacement characters. If you use a question mark in the SELECT statement, you must also configure the CheckPointColumn parameter.

For example, if you set the CheckPointColumn parameter to id and set the CheckPointStart parameter to 0, you must specify the value of the StateMent parameter in the SELECT * from ... where id > ? format. Each time a query is performed, the system saves the ID of the last data record as the checkpoint. In the next query, the question mark (?) that is specified in the SELECT statement is replaced with the ID that corresponds to the saved checkpoint.

Limit

boolean

No

Specifies whether to use a LIMIT clause to paginate query results. Valid values:

  • true

  • false (default)

We recommend that you set the Limit parameter to true. If you set the Limit parameter to true, a LIMIT clause is automatically added to the SELECT statement that you specify for the StateMent parameter when Logtail executes the SQL statement.

PageSize

int

No

The maximum number of logs that can be returned on each page. If you set the Limit parameter to true, you must configure this parameter.

MaxSyncSize

int

No

The maximum number of logs that can be synchronized at a time. Default value: 0, which indicates that the number is unlimited.

CheckPoint

boolean

No

Specifies whether to use checkpoints during data collection. Valid values:

  • true

  • false (default)

A checkpoint can be used as the start point of the next data collection. This way, incremental data is collected.

CheckPointColumn

string

No

The name of the column that stores checkpoints.

If you set the CheckPoint parameter to true, you must configure this parameter.

Warning

Values in the checkpoint column must be incremental. Otherwise, some data may not be collected. The maximum value in the results of a query operation is used as the input for the next query operation.

CheckPointColumnType

string

No

The type of the checkpoint column. Valid values: int and time. If you set this parameter to int, the values in the checkpoint column are of the int64 type. If you set this parameter to time, the values in the checkpoint column can be of the date, datetime, or time type that is supported by MySQL.

If you set the CheckPoint parameter to true, you must configure this parameter.

CheckPointStart

string

No

The initial value of the checkpoint column.

If you set the CheckPoint parameter to true, you must configure this parameter.

CheckPointSavePerPage

boolean

No

Specifies whether to save checkpoints each time query results are paginated. Valid values:

  • true (default): saves checkpoints each time query results are paginated.

  • false: saves checkpoints each time query results are synchronized.

IntervalMs

int

Yes

The synchronization interval. Unit: milliseconds. Default value: 60000.

Modify the Logtail configuration on the Logtail server

If you did not enter real information for parameters such as Address, User, and Password in Plug-in Config when you created the Logtail configuration, you can modify the parameters after the Logtail configuration is delivered to the Logtail server.

  1. Log on to the Logtail server.

  2. Find the service_mysql keyword in the /usr/local/ilogtail/user_log_config.json file and modify parameters such as Address, User, and Password.

  3. Run the following command to restart Logtail:

    sudo /etc/init.d/ilogtaild stop; sudo /etc/init.d/ilogtaild start

Troubleshooting

If no data is displayed on the preview page or query page after logs are collected by using Logtail, you can troubleshoot the errors based on the instructions that are provided in What do I do if errors occur when I use Logtail to collect logs?

Sample database table and sample log

This section provides a sample database table and a sample log that is collected by Logtail.

  • Sample database table

    CREATE TABLE `VersionOs` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
      `time` datetime NOT NULL,
      `version` varchar(10) NOT NULL DEFAULT '',
      `os` varchar(10) NOT NULL,
      `count` int(11) unsigned NOT NULL,
      PRIMARY KEY (`id`),
      KEY `timeindex` (`time`)
    )
  • Sample log

    "count":  "4"  
    "id:  "721097"  
    "os:  "Windows"  
    "time:  "2022-08-25 13:00:00"  
    "version":  "1.3.0"