Simple Log Service:Associate a MySQL data source

1. Configure a whitelist

2. Create an ExternalStore

1. Log on to the server where the CLI is installed. Run the touch command to create the /home/shell/config.json configuration file. Add the following script to the config.json file. Replace the values of the region, vpc-id, host, port, username, password, db, and table parameters as needed.

   1. Parameter description	Example { "externalStoreName":"sls_join_meta_store", "storeType":"rds-vpc", "parameter":{ "region":"cn-qingdao", "vpc-id":"vpc-m5eq4irc1pucp*******", "host":"rm-bp1******rm76.mysql.rds.aliyuncs.com", "port":"3306", "username":"user", "password":"****", "db":"scmc", "table":"join_meta" } }
      externalStoreName The name of the ExternalStore. The name must be in lowercase.
      storeType The type of the data source. Set this parameter to rds-vpc.
      region The region where the database instance resides. The details are as follows: If you use an ApsaraDB RDS for MySQL database, set the region parameter to the region of the RDS instance. If you use an AnalyticDB for MySQL database, set the region parameter to the region of the AnalyticDB for MySQL instance. If you use a self-managed MySQL database on an ECS instance, set the region parameter to the region of the ECS instance. Important The RDS instance, AnalyticDB for MySQL instance, or ECS instance must be in the same region as the Simple Log Service project.
      vpc-id The ID of the VPC where the database instance resides. The details are as follows: If your RDS PostgreSQL instance is in a virtual private cloud (VPC), set vpc-id to the ID of the VPC. For an ADB PostgreSQL database in a virtual private cloud (VPC), set vpc-id to the ID of that VPC. If the Alibaba Cloud Hologres database is in a virtual private cloud (VPC), set vpc-id to the ID of the VPC.
      host   The address of the database. The details are as follows: In a VPC, if the IP address of a database instance changes after you create an external table, access to the external table is affected. For example, this can happen if the database instance is migrated. This issue occurs even if you use an internal endpoint in the configuration. When the external table is created, the backend resolves the domain name to an IP address and saves the IP address to the backend configuration. The IP address that corresponds to the domain name is not automatically refreshed. In this case, you must update or re-create the external table. If you use an ApsaraDB RDS for MySQL database, set the host parameter to the internal address of the RDS instance. The internal address can be an internal endpoint or a private IP address. If you use an AnalyticDB for MySQL database, set the host parameter to the internal address of the AnalyticDB for MySQL instance. The internal address can be an internal endpoint or a private IP address. If you use a self-managed MySQL database on an ECS instance, set the host parameter to the private IP address of the ECS instance. If the database is accessible over the Internet, set this parameter to the public domain name or public IP address.
      port The port number. The details are as follows: If you use an ApsaraDB RDS for MySQL database, set the port parameter to the port number of the RDS instance. If you use an AnalyticDB for MySQL database, set the port parameter to the port number of the AnalyticDB for MySQL instance. If you use a self-managed MySQL database on an ECS instance, set the port parameter to the service port of MySQL on the ECS instance.
      username The username of the database account.
      password The password of the database account.
      db The name of the database.
      table The name of the database table. The following formats are supported: `table_name`, such as `test`. `schema_name.table_name`, such as `public.test`.

2. Run the following command on the command line to create the ExternalStore. project_name is the name of the Simple Log Service project. Replace it as needed. In this topic, log-rds-demo is used as an example.

   aliyunlog log create_external_store --project_name="log-rds-demo" --config="file:///home/shell/config.json" 

3. Query the ExternalStore information. If the command is successful, no response is returned. You can run the aliyunlog log get_external_store --project_name="log-rds-demo" --store_name="sls_join_meta_store" --format-output=json command to query the details of the ApsaraDB RDS for MySQL external data source. The following information is returned:

   { "externalStoreName":"sls_join_meta_store", "storeType":"rds-vpc", "parameter":{ "db": "scmc", "host": "rm-bp1******rm76.mysql.rds.aliyuncs.com", "port": "3306", "region": "cn-wulanchabu", "table": "test", "username": "user", "vpc-id": "vpc-m5eq4irc1pucp*******" } }

3. Use the external store

method:postlogstorelogs | insert into join_meta select cast(method as varchar(65535)),count(1) from log group by method

SDK operations

# encoding: utf-8 from __future__ import print_function from aliyun.log import * from aliyun.log.util import base64_encodestring from random import randint import time import os from datetime import datetime endpoint = os.environ.get('ALIYUN_LOG_SAMPLE_ENDPOINT', 'cn-chengdu.log.aliyuncs.com') accessKeyId = os.environ.get('ALIYUN_LOG_SAMPLE_ACCESSID', '') accessKey = os.environ.get('ALIYUN_LOG_SAMPLE_ACCESSKEY', '') logstore = os.environ.get('ALIYUN_LOG_SAMPLE_LOGSTORE', '') project = "ali-yunlei-chengdu" client = LogClient(endpoint, accessKeyId, accessKey, '') # Create an ExternalStore. res = client.create_external_store(project, ExternalStoreConfig(externalStoreName="rds_store", region="cn-chengdu", storeType="rds-vpc", vpcId="vpc-2vctx8reuqswmk********", host='rm-2vc6x67972iv********.mysql.cn-chengdu.rds.aliyuncs.com', port="3306", username="root", password="123456", database="test_database", table="test")) res.log_print() # Get the details of the ExternalStore. res = client.get_external_store(project,"rds_store") res.log_print() res = client.list_external_store(project,"") res.log_print() # Perform a JOIN query. req = GetLogsRequest(project,logstore,From,To,"","* | select count(1) from "+ logstore +" s join meta m on s.projectid = cast(m.ikey as varchar)") res = client.get_logs(req) res.log_print() # Write the query and analysis results to the MySQL database. req = GetLogsRequest(project,logstore,From,To,"","* | insert into rds_store select count(1) from "+ logstore ) res = client.get_logs(req) res.log_print()