All Products
Search
Document Center

Link IoT Edge:Store device data to on-premises MySQL databases

Last Updated:Aug 16, 2023

This topic describes how to store device data to an on-premises MySQL database. In this example, a LightSensor device is specified.

Prerequisites

  • A Link IoT Edge Pro Edition edge instance is created. For more information about how to create an edge instance, see Build environments for Link IoT Edge Pro Edition.

  • A LightSensor device is created and assigned to the edge instance. For more information about how to create the LightSensor product and device, see Sample drivers.

Background information

In production scenarios, you need to combine historical data collected by the LightSensor device and an algorithmic business model to analyze and determine on-site conditions at the edge side. This requires you to store device data to an on-premises database.

Link IoT Edge provides an on-premises database storage assistant that uses the ProductKey_DeviceName format to separate device data into different tables and store them to the on-premises MySQL database. You can use functions that are provided by the Function Compute service to query data. Due to insufficient storage space of the edge device, the on-premises database allows you to set a size limit for a single table and use the rollback mechanism when the limit is exceeded. By default, a single table can store 10,000 entries.

1. Create a MySQL edge application based on a container image

A MySQL edge application serves as a MySQL database server. You can use functions as clients to access the server and create MySQL databases.

  1. In the left-side navigation pane, click Applications.
  2. For more information about how to create a container image-based edge application, see Use container images to create edge applications.

    The following three tables describe parameters to be specified. You do not need to specify the parameters that are excluded in the tables or you can use the default parameter values.

    Table 1. Application parameters

    Parameter

    Description

    Application Name

    The name of the application. Example: publicMySql.

    Application Type

    Select Container Image.

    Repository Type

    Select Public Repository.

    Image Address

    Enter mysql:latest.

    Application Version

    The version of the application. You must specify a unique version number for a single application.

    Environmental Variables

    Click + Add Environment Variable and refer to the following table to add an environment variable.

    Table 2. Environment variables

    Variable

    Value

    MYSQL_ROOT_PASSWORD

    The root password of the MySQL database. Example: 123abc.

    Table 3. Container parameters

    Parameter

    Description

    Host Mode

    Select No.

    Network Port Mapping

    Configure network port mapping for your application.

    • Host Port: Enter 3306.

    • Container Port: Enter 3306.

    • Type: Select TCP.

    Privilege Mode

    Select Yes.

    Volume Mapping

    Configure volume mapping.

    • Source Path: Enter /var/mysql/data.

    • Destination Path: Enter /var/lib/mysql.

    • Read/Write Permissions: Select Read/Write.

  3. Click Confirm to create a MySQL edge application.

2. Assign the MySQL edge application to the edge instance

  1. In the left-side navigation pane, click Edge Instances.
  2. Find the created edge instance and click View in the Actions column.
  3. On the Instance Details page, click the Edge Applications tab. On the Edge Applications tab, click Assign Application.
  4. Assign the created MySQL edge application named publicMySql to the edge instance, and click Close.

  5. On the Instance Details page, click Deploy in the upper right corner. In the message that appears, click OK to assign resources such as sub-devices and Function Compute-based edge applications to the edge instance.

3. Create an on-premises database stored function

  1. Download the following package of the on-premises database stored function.

  2. Log on to the Function Compute console.
    If you have not activated Function Compute, read the terms, select I have read and agree, and then click Activate Now.
  3. Optional. In the left-side navigation pane, click Services and Functions. On the Services and Functions page, click Create Service. On the Create Service page, set the parameters as required and click Submit.
    The Service Name parameter is required. In this example, the Service Name parameter is set to EdgeFC. You can set other parameters based on your needs.
    Note If the EdgeFC service has been created for other scenarios or applications, you do not need to recreate the service.
  4. After you create the service, you must create a function in the service. On the Services and Functions page, click Create Function. On the Create Function page, click Configure and Deploy in the Event Function section.
  5. Configure the following primary parameters of the on-premises database stored function.

    Parameter

    Description

    Service Name
    The service where the function resides. Select EdgeFC.
    Function Name

    Enter saveMysqlDB.

    Runtime

    The running environment of the function. Select python3.

    Function Handler
    The handler of the function. Use the default value index.handler.
    Memory
    The size of memory that is required to execute the function. Select 512MB.
    Timeout
    The timeout period of the function. Enter 10. Unit: seconds.
    Single Instance Concurrency
    The number of concurrent requests that can be processed by an instance. Use the default value.

    You can set other parameters based on your needs or leave them unspecified. For more information, see What is Function Compute?

    Confirm the function information and click Create.

  6. After the function is created, the function details page appears. On the Code tab, select Upload Zip File and click Select File to upload the saveMysqlDB-code.zip package that you downloaded in step 1. Then, click Save.

    After the package is uploaded, you can view the source code on the In-line Edit tab.saveMysqlDB

  7. Optional. Configure database parameters.

    The saveMysqlDB function provides the sample code that is used to store device data to the on-premises MySQL database. You can modify the sample code based on your needs. The following parameters are configurable:

    • table_max_entries: the maximum number of entries that can be stored in a single table of the database. The default value is 10000.

    • table_clean_count: the number of the earliest entries to be deleted when the size limit of the database is reached. The default value is 4000.

4. Assign the on-premises database stored function to the edge instance

  1. Create a Function Compute-based edge application from the saveMysqlDB function. For more information, see Use Function Compute to create edge applications.

    The following table describes the application parameters.

    Parameter

    Description

    Application Name

    The name of the application. Example: appsaveMysqlDB.

    Application Type

    Select Function Compute.

    Region

    Select the region where the service is deployed.

    Service

    Select EdgeFC.

    Function

    Select saveSqliteDB.

    Authorization

    Select AliyunIOTAccessingFCRole.

    Application Version

    The version of the application. You must specify a unique version number for a single application.

    The following table describes the function parameters.

    Parameter

    Description

    Running Mode

    Two running modes are available. In this example, select Continuous. The application runs immediately after being deployed.

    Memory Limit (MB)

    The maximum memory that is available for running the function. Unit: MB. Enter 512. If the memory that is used by the function exceeds the limit, the edge application is forced to restart.

    Timeout Limit (Seconds)

    The maximum processing period after the function receives an event. Use the default value 5. If the function does not return the result within the specified period, the edge application is forced to restart.

    Scheduled Execution

    Use the default value Close.

    Environmental Variables

    Click + Add Environment Variable and refer to the following table to add an environment variable.

    Table 4. Environment variables

    Variable

    Value

    DB_NAME

    The name of the MySQL database. You must specify a name based on naming conventions. Example: mysql_db.

    MYSQL_IP

    The endpoint of the MySQL service. You must use the name of the MySQL edge application to access the MySQL service. In this example, the name is publicMySql.

    You do not need to specify other parameters.

  2. In the left-side navigation pane, click Edge Instances.
  3. Find the created edge instance and click View in the Actions column.
  4. On the Instance Details page, click the Edge Applications tab. On the Edge Applications tab, click Assign Application.
  5. Assign the appsaveMysqlDB stored function to the edge instance, and click Close.

5. Configure message routing

For more information about how to add a message route and specify parameters, see Configure message routing.

  1. On the Instance Details page, select Message Routing.

  2. Click Assign Route to add a message route from the LightSenor device to Function Compute.

    Specify the following parameters based on prompted instructions, and click OK.

    Parameter

    Description

    Route Name

    The name of the message route.

    Message Source

    Select Device and choose LightSensor > LightSensor.

    Message Topic Filter

    Select All.

    Message Destination

    Select Edge Applications. Then, select appsaveMysqlDB.

6. Redeploy the edge instance

  1. On the Instance Details page, click Deploy in the upper right corner. In the message that appears, click OK to assign resources such as sub-devices and Function Compute-based edge applications to the edge instance.
  2. Log on to your gateway and run the tail -f /linkedge/run/logger/fc-base/appsaveMysqlDB/log.INFO command to view the logs and check the running status of the application.

  3. In the mysql_db on-premises MySQL database, run the select * from YourProductKey_YourDeviceName command to query the data that is transmitted by the appsaveMysqlDB stored function from the LightSensor device to the database.

    Note

    Replace the YourProductKey_YourDeviceName section with the information of the device certificate. In this example, you need to replace ProductKey with a1*****xPAf, replace DeviceName with LightSensor, and run the following command: select * from a1*****xPAf_LightSensor.

    Data Storage