DocsICP FilingConsole
Documentation
Home Page Link IoT Edge Support Link IoT Edge User Guide Edge applications Scenarios Store device data in a local MySQL database

Store device data in a local MySQL database

Updated at:
Copy as MD
My Favorites

This topic describes how to store data from a LightSensor device in a local MySQL database using an official sample driver.

Prerequisites

  • This example applies only to Link IoT Edge Pro Edition. Ensure that you have created an edge instance. For more information, see Set up the environment for the Pro Edition.
  • Create a light sensor product and a LightSensor device. Then, assign the device to the edge instance. For more information, see Sample driver.

Background information

In production environments, you often need to analyze on-site conditions at the edge by combining historical sensor data with industry-specific algorithm models. To do this, you must first store the data that is reported by devices in a local database.

Function Compute for edge provides a local database storage helper. This helper separates device data into different tables based on the ProductKey_DeviceName format and stores the data in a local MySQL database. Other functions, such as algorithm logic in Function Compute, can then query this data. Because storage space on edge devices is limited, the local database provides a storage limit for each single table and a data rollback mechanism. The default storage limit is 10,000 records. The rollback mechanism is triggered when the storage limit is reached.

Step 1: Create a MySQL container image application

The MySQL container image application functions as a MySQL database server. Clients, such as functions, can access this server to create MySQL databases.

  1. Log on to the IoT Edge console.
  2. In the navigation pane on the left, click Application Management.
  3. Create an edge application of the container image type. For more information, see Container image application.

    The following tables describe some of the parameters. You can use the default values for the other parameters or leave them unconfigured.

    Table 1. Application information parameters
    ParameterDescription
    Application nameSet a name for your application, such as publicMySql.
    Application typeSelect Container image.
    Repository typeSelect Public repository.
    Registry AddressSet Registry Address to mysql:latest.
    Application versionSet the version for the application. The version number must be unique for this application. You cannot set the same version number for two different versions of the same application.
    Environment variableClick Add Environment Variable and add the environment variable shown in the Environment variable configuration table below.
    Table 2. Environment variable configuration
    NameValue
    MYSQL_ROOT_PASSWORDThe password for the root account of the MySQL database. For example, set it to 123abc.
    Table 3. Container configuration parameters
    ParameterDescription
    Use host network modeSelect No.
    Network port mappingSet the network port mapping for your application.
    • Host port: Set to 3306.
    • Container port: Set to 3306.
    • Type: Select TCP.
    Enable privileged modeSelect Yes.
    Volume mappingSet your volume mapping.
    • Source path:
      • For Linux, set it to /var/mysql/data.
      • For Windows, set it to \Drive_letter_for_MySQL_files:\mysql\data , such as \D:\mysql\data.
    • Destination path: Set to /var/lib/mysql.
    • Read/write permissions: Select Read/Write.
  4. Click Confirm to create the MySQL container image application.

Step 2: Assign the MySQL container image application to the edge instance

  1. In the navigation pane on the left, click Edge Instances.
  2. Find the edge instance that you created in the "Prerequisites" section and click View.
  3. On the Instance Details page, go to the Edge Applications tab and click Assign Application.
  4. In the Assign Application panel, find the publicMySql application that you created, click Assign in the Actions column, and then click Close.
  5. On the Instance Details page, click Deploy in the upper-right corner. In the dialog box that appears, click OK to deploy resources, such as sub-devices and functions, to the edge.

Step 3: Create a local database storage function

  1. Download the local database storage function saveMysqlDB-code.zip.
  2. Log on to the Function Compute console.
    If you have not activated this service, read the terms and select I have read and agree to the terms. Then, click Activate Now.
  3. (Optional) In the navigation pane on the left, click Services & Functions. On the Services & Functions page, in the Service List section, click Create Service to create a service.
    The Service Name parameter is required. Set this parameter to EdgeFC. You can configure the other parameters as needed or use their default settings.
    Note
    • If this is your first time creating a service in Function Compute, follow the configuration wizard.
    • If you have already created the EdgeFC service for other scenarios or miniprogram examples, you do not need to create it again.
  4. After the service is created, on the Services & Functions page, find the EdgeFC service and click Create Function.
  5. On the Create Function page, click Configure and Deploy in the Event Function section.
  6. Set the basic management configuration parameters for the local database storage function.
    ParameterDescription
    Service
    Select the EdgeFC service that you created.
    Function Name
    		Set to saveMysqlDB.
    Runtime
    		Set the runtime for the function. In this example, select Python 3.

    To the right of Upload Code, select Upload a ZIP file. Click Upload Code and upload the saveMysqlDB-code.zip package that you downloaded in Step 1.

    Handler
    Use the default value: index.handler.

    You can configure the other parameters as needed or use their default settings. For more information, see Function Compute.

    After you confirm the function information, click Create.

  7. After the function is created, the function details page appears. On the Code tab, in the Code Execution Management section, select Edit Online to view the source code.
    saveMysqlDB
  8. (Optional) Configure database parameters.
    saveMysqlDB is a sample function for storing device data in a local MySQL database. You can modify the sample as needed. The following parameters can be adjusted:
    • table_max_entries: The maximum number of records in a single database table. The default value is 10,000.
    • table_clean_count: The number of the oldest records to delete when the database reaches its record limit. The default value is 4,000.

Step 4: Assign the local database storage function to the edge instance

  1. Use the saveMysqlDB function that you created to create an edge application of the Function Compute type. For more information, see Function Compute application.

    The following table describes the application information parameters:

    ParameterDescription
    Application nameSet a name for your application, such as appsaveMysqlDB.
    Application typeSelect Function Compute.
    RegionSelect the region where your service is located.
    ServiceSelect the EdgeFC service.
    FunctionSelect the saveMysqlDB function.
    AuthorizationSelect AliyunIOTAccessingFCRole.
    Application versionSet the version for the application. The version number must be unique for this application. You cannot set the same version number for two different versions of the same application.
    The following table describes the function configurations:
    ParameterDescription
    Enable default configurationsSelect No.
    Run modeThere are two run modes. Select Keep running. The program runs immediately after it is deployed.
    Timeout (seconds)The maximum processing time for a function after it receives an event. Use the default value of 5 seconds. If the function does not return a result within this time, the Function Compute program is forcibly restarted.
    Scheduled runUse the default configuration: Off.
    Environment variableClick Add Environment Variable and add the environment variables shown in the Environment variable configuration table below.
    Table 4. Environment variable configuration
    NameValue
    DB_NAMEThe name of the MySQL database. Set a custom name that follows the MySQL database naming convention. For example, set it to mysql_db.
    MYSQL_IPThe endpoint of the MySQL service. You must use the name of the MySQL container image application to access the service. In this example, the application name is publicMySql.

    No other parameters need to be configured.

  2. In the navigation pane on the left, click Edge Instances.
  3. Find the edge instance that you created in the "Prerequisites" section and click View.
  4. On the Instance Details page, go to the Edge Applications tab and click Assign Application.
  5. In the Assign Application panel, find the appsaveMysqlDB function that you created, click Assign in the Actions column, and then click Close.

Step 5: Configure message routing

This topic explains how to add messages and describes each parameter. For more information, see Set message routing.

  1. On the Instance Details page, click the Message Routing tab.
  2. Click Assign Route to add a message route that sends messages from the LightSensor device to Function Compute.
    Set the following parameters as prompted on the page, and then click OK.
    ParameterDescription
    Route NameSet a name for the message route.
    Message SourceSelect Device. Then, select Light Sensor > LightSensor.
    Topic FilterSelect All.
    Message DestinationSelect Edge Application and the appsaveMysqlDB function.

Step 6: Redeploy the edge instance

  1. On the Instance Details page, click Deploy in the upper-right corner. In the dialog box that appears, click OK to deploy resources, such as sub-devices and functions, to the edge.
  2. Log on to your gateway and run the tail -f /linkedge/run/logger/fc-base/appsaveMysqlDB/log.INFO command to view the application logs and monitor the runtime status of the application.
  3. In your local MySQL database, which is named mysql_db in this example, run the select * from YourProductKey_YourDeviceName; command. You can then query the LightSensor device data that is stored in the MySQL database by the appsaveMysqlDB application.
    Note Replace YourProductKey_YourDeviceName in the command with the device certificate information for your device. For example, if the ProductKey for a device is a1*****xPAf and the DeviceName is LightSensor, the command is select * from a1*****xPAf_LightSensor.
    Data storage
Previous：Integrate with InfluxDBNext：Machine learning edge inference