Simple Log Service:Associate Simple Log Service with a MySQL database

Procedure

1. Configure a whitelist for your MySQL database

2. Create an external store

1. Install Simple Log Service CLI.

   Note

   Cloud Shell provided by Alibaba Cloud is integrated with Simple Log Service CLI. You can use Cloud Shell to manage Simple Log Service resources and download data from Simple Log Service without the need to deploy or configure Simple Log Service CLI. For more information, see Use Cloud Shell.

2. Run the touch command to create a configuration file named /home/shell/config.json. Add the following script to the /home/shell/config.json file. Replace the values of the following parameters based on your business requirements: region, vpc-id, host, port, username, password, db, and table.

   {
       "externalStoreName":"sls_join_meta_store",
       "storeType":"rds-vpc",
       "parameter":{
           "region":"cn-qingdao",
           "vpc-id":"vpc-m5eq4irc1pucp*******",
           "host":"localhost",
           "port":"3306",
           "username":"user",
           "password":"****",
           "db":"scmc",
           "table":"join_meta"
       }
   }

   Parameter	Description
   externalStoreName	The name of the external store. The name must be in lowercase.
   storeType	The type of the data source. Set the value to rds-vpc.
   region	The region. If you use a MySQL database created on an RDS instance, set region to the region where the RDS instance resides. If you use a MySQL database created on an AnalyticDB instance, set region to the region where the AnalyticDB instance resides. If you use a self-managed MySQL database hosted on a VPC-type ECS instance, set region to the region where the ECS instance resides. Important The RDS instance, AnalyticDB instance, or ECS instance that is used must reside in the same region as your Simple Log Service project.
   vpc-id	The ID of the VPC. If you use a MySQL database created on a VPC-type RDS instance, set vpc-id to the ID of the VPC to which the RDS instance belongs. If you use a MySQL database created on a VPC-type AnalyticDB instance, set vpc-id to the ID of the VPC to which the AnalyticDB instance belongs. If you use a self-managed MySQL database hosted on a VPC-type ECS instance, set vpc-id to the ID of the VPC to which the ECS instance belongs. If the host where your MySQL database resides can be accessed over the Internet, you do not need to configure vpc-id.
   host	The address of your MySQL database. If you use a MySQL database created on a VPC-type RDS instance, set host to an internal endpoint or the private IP address of the RDS instance. If you use a MySQL database created on a VPC-type AnalyticDB instance, set host to an internal endpoint or the private IP address of the AnalyticDB instance. If you use a self-managed MySQL database hosted on a VPC-type ECS instance, set host to the private IP address of the ECS instance. If your MySQL database can be accessed over the Internet, set host to a public endpoint or the public IP address of your instance.
   port	The port number. If you use a MySQL database created on an RDS instance, set port to the port of the RDS instance. If you use a MySQL database created on an AnalyticDB instance, set port to the port of the AnalyticDB instance. If you use a self-managed MySQL database hosted on a VPC-type ECS instance, set port to the MySQL service port of the ECS instance.
   username	The username of the account that you use to log on to your MySQL database.
   password	The password of the account that you use to log on to your MySQL database.
   db	The name of your MySQL database.
   table	The name of the table that you want to use in your MySQL database.

3. Run the following command to create an external store. Replace the value of project_name with the name of the project that you want to use. In this topic, log-rds-demo is used.

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

4. Run the following command to query the information about the external store. If the command is successfully run, no responses are 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 created external store. In the following example, the external store is a MySQL database created on an RDS instance. Output:

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

3. Query data from the associated MySQL database

After you associate Simple Log Service with your MySQL database by using the external store, you can click a Logstore in the log-rds-demo project and execute the * | select * from sls_join_meta_store query statement on the query and analysis page of the Logstore to query data from the associated MySQL database.

What to do next

• Update the MySQL external store.

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

• Delete the MySQL external store.

  aliyunlog log delete_external_store --project_name="log-rds-demo" --store_name=sls_join_meta_store