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 of the IoT Platform console, choose Link IoT Edge > 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, choose Link IoT Edge > Edge Instances.
  2. Find the created edge instance and click View.
  3. On the Instance Details page, click the Edge Applications tab. On this tab, click Allocate 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 dialog box that appears, click OK to assign resources such as sub-devices and Function Compute-based edge applications to the edge instance.
    You can click Deployment Details to view the deployment progress and result.

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 Alibaba Cloud Function Compute console.
    If you have not activated the Function Compute service, read the terms, select I have read and agree, and click Activate Now.
  3. Optional. In the left-side navigation pane, select Service-Function. From the drop-down list of Create Function, select Create Service. On the Create Service page, configure parameters and click Create.
    The Service Name parameter is required. In this example, you must specify EdgeFC for the Service Name parameter. You can specify other parameters based on your needs.
    Note If the EdgeFC service has been created for other scenarios or applications, you do not need to create a new one.
  4. After creating the service, you must create a function. On the Service-Function page, click Create Function. On the Create Function page, select Event Function and click Next.
  5. Configure the following primary parameters of the on-premises database stored function.
    Parameter Description
    Service Name Select EdgeFC.
    Function Name Enter saveMysqlDB.
    Runtime The running environment of the function. Select python3.
    Function Handler Use the default value index.handler.
    Memory Select 512 MB.
    Timeout Enter 10. Unit: seconds.
    Single Instance Concurrency Use the default value.

    You can configure other parameters based on your needs or leave them empty. For more information about how to configure parameters, see 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 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 .

    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, choose Link IoT Edge > Edge Instances.
  3. Find the created edge instance and click View.
  4. On the Instance Details page, click the Edge Applications tab. On this tab, click Allocate 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 dialog box that appears, click OK to assign resources such as sub-devices and Function Compute-based edge applications to the edge instance.
    You can click Deployment Details to view the deployment progress and result.
  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