Collect MySQL query results

Log Service console

1. Log on to the Simple Log Service console.

2. On the right side of the page that appears, click the Quick Data Import card.

   

3. On the Import Data page, select MySQL query result - plug-in.

4. Select the project and logstore. Then, click Next.

5. On the Machine Group Configuration page, configure a machine group.

   1. Select a use case and an installation environment that match your requirements.

      Important

      Regardless of whether you have an existing machine group, you must select the correct use case and installation environment. This choice affects the subsequent configuration steps.

   2. Confirm that the target machine group is in the Applied Machine Group area, and then click Next.

      Existing machine groups

      From the Source Machine Group list, select the target machine group.

      

      No available machine groups

      Click Create Machine Group. In the Create Machine Group panel, set the parameters. You can identify a machine group by IP Address or Custom Identifier. For more information, see Create a machine group by using a user-defined identifier (recommended) or Create a machine group by using IP addresses.

      Important

      If you apply the machine group immediately after creation, its heartbeat status may be FAIL because the connection is not yet established. You can click Automatic Retry. If the issue persists, see A Logtail machine group has no heartbeats to troubleshoot the issue.

6. On the Configure Data Source tab, set Configuration Name and Plug-in Configuration, and then click Next.

   You can configure the data source by using the form or the editor (JSON configuration). For configuration details, see MySQL input plug-in.

   

7. Create indexes, preview the data, and then click Next. By default, Log Service enables a full-text index. You can also manually create field indexes based on the collected logs, or click Automatic Index Generation to automatically create field indexes. For more information, see Create indexes.

   Important

   Enable the full-text index if you need to query all log fields. If you need to query only specific fields, create a field index to reduce index traffic. To analyze fields by using SELECT statements, you must create a field index.

8. Click Query Log to go to the query and analysis page for the Logstore.

   Wait about one minute for the indexes to take effect. Then, you can view the collected logs on the Raw Logs tab. For more information, see Quick start for log query and analysis.

CRD-AliyunPipelineConfig

This section provides an example of how to create a collection configuration in an ACK cluster.

1. Log on to the ACK console. In the left navigation pane, click Clusters.

2. On the Clusters page, find the target cluster. In the Actions column, choose More > Manage cluster.

3. Create a file named example-k8s-file.yaml.

   • CRD parameter description

   • MySQL input plug-in parameter description

   Parameter description	Sample apiVersion: telemetry.alibabacloud.com/v1alpha1 # Create a CR from the ClusterAliyunPipelineConfig CRD. kind: ClusterAliyunPipelineConfig metadata: # The name of the resource. The name must be unique in the current Kubernetes cluster. The name is the same as the name of the Logtail configuration that is created name: example-crd-mysql spec: # Specify the project to which logs are collected. project: name: k8s-log-clusterid # Create a Logstore to store logs. logstores: - name: crd-mysql-test # Configure the parameters for the Logtail configuration. config: # Enter a sample log. You can leave this parameter empty. sample: '' inputs: - Type: service_mysql Address: "rm-*.mysql.rds.aliyuncs.com" CheckPoint: true CheckPointColumn: id CheckPointColumnType: int CheckPointSavePerPage: true CheckPointStart: "0" Database: **** IntervalMs: 1000 Limit: true MaxSyncSize: 100 PageSize: 100 User: **** Password: ******* StateMent: "select * from test where id > ? order by id" # Configure the output plug-in. flushers: # Use the flusher_sls plug-in to send logs to a specific Logstore. - Type: flusher_sls Logstore: crd-mysql-test Endpoint: cn-hangzhou.log.aliyuncs.com Region: cn-hangzhou TelemetryType: logs
   Type string (required) The type of the data source. Set the value to service_mysql.
   Address string (optional) The MySQL address, for example, rm-****.mysql.rds.aliyuncs.com. Default value: 127.0.0.1:3306. Address types: Internal (virtual private cloud (VPC)): used only for internal access from instances (such as ECS and ACK instances) in the same VPC. Public endpoint: used when your business is deployed in another VPC or on an on-premises machine and you need to access the instance over the Internet. You must manually apply for a public endpoint in advance. For more information, see Request or release a public endpoint. Note We recommend that you use the internal read/write splitting endpoint to connect to RDS instances because connections over the Internet are prone to fluctuations.
   User string (optional) The username of the account that is used to log on to the MySQL database. Default value: root.
   Password string (optional) The password of the account that is used to log on to the MySQL database. If you have high security requirements, we recommend that you set the username and password to xxx. After the collection configuration is synchronized to the local server, find the corresponding configuration in the /usr/local/ilogtail/user_log_config.json file on the local server and modify the configuration. For more information, see Collect MySQL query results. 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 (optional) The MySQL database name.
   DialTimeOutMs int (optional) The timeout period for connections to the MySQL database. Unit: milliseconds. Default value: 5000.
   ReadTimeOutMs int (optional) The timeout period for reads from the MySQL database. Unit: milliseconds. Default value: 5000.
   StateMent string (optional) The SELECT statement. If you set CheckPoint to true, the WHERE condition in the Statement must include the CheckPoint column (CheckPointColumn). 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 CheckPointColumn to id, set CheckPointStart to 0, and set StateMent to SELECT * from ... where id > ?. 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 (optional) Specifies whether to use a LIMIT clause to paginate query results. Valid values: true: uses a LIMIT clause. false (default): does not use a LIMIT clause. We recommend that you use a LIMIT clause to paginate query results. If you set Limit to true, the system automatically appends a LIMIT clause to the SELECT statement when the SQL query is performed.
   PageSize int (optional) The number of entries per page. This parameter is required if you set Limit to true.
   MaxSyncSize int (optional) The maximum number of logs that can be synchronized at a time. Default value: 0, which indicates that no limits are imposed.
   CheckPoint boolean (optional) Specifies whether to use checkpoints during data collection. Valid values: true: uses checkpoints. false (default): does not use checkpoints. A checkpoint can be used as the start point of the next data collection. This way, incremental data is collected.
   CheckPointColumn string (optional) The name of the column that stores checkpoints. This parameter is required if you set CheckPoint to true. Warning Values in the checkpoint column must be incremental. Otherwise, some data may not be collected. The maximum value in the result of a query operation is used as the input for the next query operation.
   CheckPointColumnType string (optional) This parameter is required if you set CheckPoint to true. The data 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 values in the checkpoint column can be of the date, time, or datetime type that is supported by MySQL.
   CheckPointStart string (optional) This parameter is required if you set CheckPoint to true. The initial value of the checkpoint column.
   CheckPointSavePerPage boolean (optional) This parameter is required if you set CheckPoint to true. Specifies whether to save checkpoints each time query results are paginated. true (default): saves checkpoints each time query results are paginated. false: saves checkpoints each time query results are synchronized.
   IntervalMs int (required) The synchronization interval. Unit: milliseconds. Default value: 60000.

4. Run kubectl apply -f example-k8s-file.yaml. Logtail starts to collect data from MySQL.

5. Log on to the Simple Log Service console. In the Projects section, click the one you want.

6. Click the icon to the right of the destination Logstore and select Query and Analysis to view the query logs.