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.
- In the left-side navigation pane, click Applications.
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.
Click Confirm to create a MySQL edge application.
2. Assign the MySQL edge application to the edge instance
- In the left-side navigation pane, click Edge Instances.
- Find the created edge instance and click View in the Actions column.
- On the Instance Details page, click the Edge Applications tab. On the Edge Applications tab, click Assign Application.
Assign the created MySQL edge application named publicMySql to the edge instance, and click Close.
- 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
Download the following package of the on-premises database stored function.
- 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.
- 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.
- 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.
Configure the following primary parameters of the on-premises database stored function.
Parameter
Description
Service NameThe service where the function resides. Select EdgeFC.Function NameEnter saveMysqlDB.
RuntimeThe running environment of the function. Select python3.
Function HandlerThe handler of the function. Use the default valueindex.handler
.MemoryThe size of memory that is required to execute the function. Select 512MB.TimeoutThe timeout period of the function. Enter 10. Unit: seconds.Single Instance ConcurrencyThe 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.
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.
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
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.
- In the left-side navigation pane, click Edge Instances.
- Find the created edge instance and click View in the Actions column.
- On the Instance Details page, click the Edge Applications tab. On the Edge Applications tab, click Assign Application.
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.
On the Instance Details page, select Message Routing.
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
.Message Topic Filter
Select All.
Message Destination
Select Edge Applications. Then, select appsaveMysqlDB.
6. Redeploy the edge instance
- 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.
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.
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.
NoteReplace 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.