Simple Log Service:Associate a MySQL data source

1. Configure a whitelist

2. Create an external store

1. Log on to the server where the CLI is installed. On the command line, run the touch command to create the configuration file /home/shell/config.json. Add the following script to the config.json file. You must replace the values for region, vpc-id, host, port, username, password, db, and table with your actual values.

   1. Parameters	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 external store. 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 is located. If you use an ApsaraDB RDS for MySQL database, set region to the region of the RDS instance. If you use an AnalyticDB for MySQL database, set region to the region of the AnalyticDB for MySQL instance. If you use a self-managed MySQL database on an ECS instance in a VPC, set region to the region of the ECS instance. Important The ApsaraDB RDS for MySQL instance, AnalyticDB for MySQL instance, or ECS instance must be in the same region as the SLS project.
      vpc-id The VPC ID. If you use an ApsaraDB RDS for MySQL instance in a VPC, set vpc-id to the ID of the VPC where the instance is located. If you use an AnalyticDB for MySQL instance in a VPC, set vpc-id to the ID of the VPC where the instance is located. If you use a self-managed MySQL database on an ECS instance in a VPC, set vpc-id to the ID of the VPC where the instance is located.
      host   If you use an internal endpoint for a database in a VPC, SLS resolves it to a private IP address and caches it when the external store is created. If this IP address later changes (for example, due to instance migration), access to the external store is disrupted because the cached IP is not automatically refreshed. To resolve this, you must update or re-create the external store. If you use an ApsaraDB RDS for MySQL database in a VPC, set host to the internal address of the RDS instance. The address can be an internal endpoint or a private IP address. If you use an AnalyticDB for MySQL database in a VPC, set host to the internal address of the AnalyticDB for MySQL instance. The address can be an internal endpoint or a private IP address. If you use a self-managed MySQL database on an ECS instance in a VPC, set host to the private IP address of the ECS instance.
      port The port number. If you use an ApsaraDB RDS for MySQL database, set port to the port number of the RDS instance. If you use an AnalyticDB for MySQL database, set port to the port number of the AnalyticDB for MySQL instance. If you use a self-managed MySQL database on an ECS instance, set port to the service port of MySQL on the ECS instance.
      username The username for the database account.
      password The password for 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 to create the external store. Replace project_name with the name of your project. This example uses log-rds-demo.

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

3. The create_external_store command returns no output on success. To verify the creation, run the aliyunlog log get_external_store --project_name="log-rds-demo" --store_name="sls_join_meta_store" --format-output=json command to view the details of the external store. The following response is an example:

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

3. Use the external store

* | insert into sls_join_meta_store  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 external store.
    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 external store.
    res = client.get_external_store(project,"rds_store")
    res.log_print()
    res = client.list_external_store(project,"")
    res.log_print()
    # Run 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()