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 in Linux or Install Logtail in Windows.
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. In the Specify Logstore step, select the target project and Logstore, and click Next.
    You can also click Create Now to create a project and a Logstore. For more information, see Step 1: Create a project and a Logstore.
  4. In the Create Machine Group step, create a machine group.
    • If a machine group is available, click Using Existing Machine Groups.
    • This section uses ECS instances as an example to describe how to create a machine group. To create a machine group, perform the following steps:
      1. Install Logtail on ECS instances. For more information, see Install Logtail on ECS instances.

        If Logtail is installed on the ECS instances, click Complete Installation.

        Note If you need to collect logs from user-created clusters or servers of third-party cloud service providers, you must install Logtail on these servers. For more information, see Install Logtail in Linux or Install Logtail in Windows.
      2. After the installation is complete, click Complete Installation.
      3. On the page that appears, specify the parameters for the machine group. For more information, see Create an IP address-based machine group or Create a custom ID-based machine group.
  5. In the Machine Group Settings step, apply the configurations to the machine group.
    Select the created machine group and move the group from Source Server Groups to Applied Server Groups.
  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 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 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.
  7. In the Configure Query and Analysis step, configure the indexes.
    Indexes are configured by default. You can re-configure the indexes based on your business requirements. For more information, see Enable and configure the index feature for a Logstore.
    Note
    • You must configure Full Text Index or Field Search. If you configure both of them, the settings of Field Search are applied.
    • If the data type of index is long or double, the Case Sensitive and Delimiter settings are unavailable.

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"