This topic describes how to create an external store and associate Log Service with a MySQL database.

Prerequisites

  • Log data is collected and stored in Log Service. For more information, see Data collection overview.
  • Data is stored in the MySQL database.

Background information

The external storage feature of Log Service allows you to associate Log Service with databases created on ApsaraDB for RDS MySQL instances, self-managed MySQL databases hosted on Elastic Compute Service (ECS) instances, and self-managed MySQL databases created in other scenarios. The external storage feature also allows you to write query and analysis results to the MySQL databases for further processing. For more information about how to create a MySQL external store, see Perform an association query and analysis on the data of a Logstore and a MySQL database.

Procedure

  1. Configure a whitelist for your MySQL database.
    • If you use a MySQL database created on an RDS instance, add the following CIDR blocks to the whitelist: 100.104.0.0/16, 11.194.0.0/16, and 11.201.0.0/16. For more information, see Configure an IP address whitelist for an ApsaraDB RDS for MySQL instance.
    • If you use a self-managed MySQL database hosted on a VPC-type ECS instance and the ECS instance is added to a security group, configure security group rules to allow access from the following CIDR blocks: 100.104.0.0/16, 11.194.0.0/16, and 11.201.0.0/16. For more information, see Add security group rules.
    • If you use a self-managed MySQL database created in other scenarios, add the following CIDR blocks to the whitelist: 100.104.0.0/16, 11.194.0.0/16, and 11.201.0.0/16.
  2. Create an external store.
    1. Install the Log Service CLI. For more information, see Overview.
    2. Create a configuration file /root/config.json.
    3. Add the following script to the /root/config.json file. Replace the parameter values based on your business requirements.
      {
      "externalStoreName":"storename",
      "storeType":"rds-vpc",
      "parameter":
         {
         "region":"cn-qingdao",
         "vpc-id":"vpc-m5eq4irc1pucp*******",
         "instance-id":"i-m5eeo2whsn*******",
         "host":"localhost",
         "port":"3306",
         "username":"root",
         "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 self-managed MySQL database hosted on a VPC-type ECS instance, set region to the region where the ECS instance resides.
      • If you use a self-managed MySQL database created in other scenarios, set region to an empty string. Format: "region": "".
      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.
      • If you use a self-managed MySQL database hosted on a VPC-type ECS instance, set vpc-id to the ID of the VPC.
      • If you use a MySQL database created on a classic network-type RDS instance or if you use a self-managed MySQL database created in other scenarios, set vpc-id to an empty string. Format: "vpc-id": "".
      instance-id The ID of the instance.
      • If you use a MySQL database created on an RDS instance, set instance-id to the ID of the RDS instance.
      • If you use a self-managed MySQL database hosted on a VPC-type ECS instance, set instance-id to the ID of the ECS instance.
      • If you use a self-managed MySQL database created in other scenarios, set instance-id to an empty string. Format: "instance-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 of the RDS 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 you use a self-managed MySQL database created in other scenarios, set host to a host address of the database. Make sure that the host address is accessible.
      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 self-managed MySQL database hosted on a VPC-type ECS instance, set port to the MySQL service port of the ECS instance.
      • If you use a self-managed MySQL database created in other scenarios, set port to the MySQL service port.
      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.
    4. Create an external store.
      Replace the value of project_name with the name of the project that you want to use.
      aliyunlog log create_external_store --project_name="log-rds-demo" --config="file:///root/config.json" 

What to do next

  • Update the MySQL external store.
    aliyunlog log update_external_store --project_name="log-rds-demo" --config="file:///root/config.json" 
  • Delete the MySQL external store.
    aliyunlog log delete_external_store --project_name="log-rds-demo" --store_name=abc

What to do next

Join queries on a Logstore and a MySQL database