Collect MySQL query results

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.

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