This topic describes how to configure Logtail in the Log Service console to collect MySQL query results.

Prerequisites

Logtail is installed on the server that you use to collect MySQL query results. For more information, see Install Logtail on a Linux server or Install Logtail on a Windows server.
Note Servers that run Linux support Logtail 0.16.0 or later . Servers that run Windows support Logtail 1.0.0.8 or later.

Implementation

Logtail executes the specified SELECT statement at a regular interval based on the Logtail configurations, and then uploads the query results to Log Service.

When Logtail obtains a query result, Logtail saves the value of the CheckPoint field in the local server. The next time Logtail executes the SELECT statement, it adds the saved value of the CheckPoint field to the SELECT statement. In this way, Logtail collects the incremental data of MySQL databases.

Implementation

Features

  • Supports MySQL databases.
  • Allows you to paginate query results.
  • Allows you to set time zones.
  • Allows you to set timeout periods.
  • Allows you to record synchronization statuses by using the checkpoint mechanism.
  • Supports SSL.
  • Allows you to set the maximum size of data that can be collected at a time.

Scenarios

  • Collect incremental data based on specific marks such as an auto-increment ID or a point in time.
  • Customize data synchronization based on specified filtering conditions.

Procedure

  1. Log on to the Log Service console.
  2. In the Import Data section, select 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 Using 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 Execute Now.

        For more information, see Install Logtail on ECS instances.

        Note If you want to collect logs from self-managed clusters or servers from third-party cloud service providers, you must manually install Logtail. For more information, see Install Logtail on a Linux server or Install Logtail on a Windows server.
      2. After Logtail is installed, click Complete Installation.
      3. In the Create Machine Group step, configure Name and click Next.

        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 ID-based machine group.

  5. Select the newly created machine group and move it from the Source Server Groups section to the Applied Server Groups section. Then, click Next.
    Notice If you apply a machine group immediately after it is created, the heartbeat status of the machine group may be FAIL. This issue occurs because the machine group is not connected to Log Service. In this case, 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, set the Config Name and Plug-in Config parameters.
    • inputs: Required. The Logtail configurations for log collection.
      Note You can configure only one type of data source in the inputs field.
    • processors: Optional. The Logtail configurations for data processing. You can configure one or more processing methods in the processors field. For more information, see Overview.
    {
      "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 Yes The type of the data source. Set the value to service_canal.
    Address String No The endpoint that is used to access the MySQL database. Default value: 127.0.0.1:3306.
    User String No The username that is used to log on to the database. Default value: root.
    Password String No The password that is used to log on to the database. Default value: null.
    If you require a high level of security, we recommend that you set the password to xxx. After your configurations are synchronized to the local server, find the Password parameter in the /usr/local/ilogtail/user_log_config.json file and set the value to your password. For more information, see Modify the configurations on the server where Logtail is installed.
    Note If you modify this parameter in the console, the local configuration will be overwritten after the modification is synchronized to the local server.
    DialTimeOutMs Int No The timeout period for connections to the database. Unit: milliseconds. Default value: 5000.
    ReadTimeOutMs Int No The timeout period for data reads. Unit: milliseconds. Default value: 5000.
    StateMent String No The SQL statement.

    If you set CheckPoint to true, you must include the CheckPointColumn parameter in the WHERE clause of the SQL statement. In addition, you must set the CheckPointColumn parameter to ?. For example, if you set CheckPointColumn to id, you must set the StateMent parameter in the format of SELECT * from ... where id > ?.

    Limit Boolean No Specifies whether to paginate query results. Default value: false. This value indicates that query results are not paginated.

    We recommend that you set Limit to true. If you set Limit to true, the LIMIT clause is automatically appended to the SQL StateMent when you run a query.

    PageSize Int No The number of log entries to return on each page. You must specify this parameter if you set the Limit parameter to true.
    MaxSyncSize Int No The maximum number of log entries that are synchronized at a time. Default value: 0. This value indicates that no limit is placed on the size of data that can be synchronized at a time.
    CheckPoint Boolean No Specifies whether to use checkpoints during data collection. Default value: false. This value indicates that checkpoints are not used during data collection.
    CheckPointColumn String No The name of the checkpoint column.

    You must specify this parameter if you set the CheckPoint parameter to true.

    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 stored as 64-bit integers. If you set this parameter to time, the data type of the values in checkpoint column is MySQL DATE, TIME, or DATETIME.

    You must specify this parameter if you set the CheckPoint parameter to true.

    CheckPointStart String No The initial value of the checkpoint.

    You must specify this parameter if you set the CheckPoint parameter to true.

    CheckPointSavePerPage Boolean No If you set this parameter to true, a checkpoint is saved after each pagination. If you set this parameter to false, a checkpoint is saved after each synchronization.
    IntervalMs Int Yes The synchronization interval. Unit: milliseconds.

Modify the configurations on the server where Logtail is installed

If you did not configure real information for the Host, User, Password, and other parameters under the inputs field, you can modify the parameters after the configurations are synchronized to the server where Logtail is installed.

  1. Log on to the server where Logtail is installed.
  2. Find the service_canal keyword in the /usr/local/ilogtail/user_log_config.json file, and set the Host, User, and Password parameters.
  3. Run the following command to restart Logtail:
    sudo /etc/init.d/ilogtaild stop; sudo /etc/init.d/ilogtaild start

What to do next

After Logtail sends MySQL query results to Log Service, you can view the results in the Log Service console. This section shows the schema and data that are collected by Logtail.
  • Schema
    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 entry
    "count":  "4"  
    "id:  "721097"  
    "os:  "Windows"  
    "time:  "2017-08-25 13:00:00"  
    "version":  "1.3.0"