[Device] OpcUa Extender Server upload MySQL database and Grafana

Questions and Answers

Moderator: OpcUa Support

Post Reply
OpcUa Support
Posts: 39
Joined: Thu Nov 17, 2022 7:15 am

[Device] OpcUa Extender Server upload MySQL database and Grafana

Post by OpcUa Support »

Description:
Utilize the OpcUa extender Server to retrieve power meter data and upload it to the MySQL database, and then employ the data visualization platform Grafana to present the data stored in the database.
In this case, OpcUa extender serves as a Opcua server, which retrieve power meter information and transfer to MySQL database.

System block:
Power meter_IoT Studio_OpcUa Extender Server_MySQL.png
Power meter_IoT Studio_OpcUa Extender Server_MySQL.png (61.86 KiB) Viewed 290 times
Environment:
  • Power meter: PM-3133-100P
  • RS485 to USB adapter
  • Working machine: NIFE-105
System architecture:
硬體配置圖.png
硬體配置圖.png (85.68 KiB) Viewed 290 times
Operating step:
Step1:
Hardware connection:
Install clip-on CT onto the power cable, and according to the user manual, connect the L and N terminals of the power meter in parallel with the corresponding terminals of the cable(1P2W-1CT).
power meter wiring.png
power meter wiring.png (22.5 KiB) Viewed 289 times
Also connect PM-3133 to working machine NIFE-105 with USB to RS485 adapter.


Step2:
OpcUa Extender Server retrievie power meter:
Look up the registers for voltage, current and power in the user manual, and configure the register addresses in the OpcUa Extender Server.

Modbus register of PM3133:
Register.png
Register.png (54.73 KiB) Viewed 289 times
Configuration of OpcUa Extender Server:

Code: Select all

Va	Va	Powermeter_Rtu	FLOAT		34353	1
Ia	Ia	Powermeter_Rtu	FLOAT		34355	1
kWa	kWa	Powermeter_Rtu	FLOAT		34357	1
Upload the configuration .csv file to OpcUa Extender Server:
OpcUa Ext add modbus RTU_config file.png
OpcUa Ext add modbus RTU_config file.png (47.2 KiB) Viewed 289 times
We will obtain the float format values for voltage, current, and power from addresses 34353, 34355, and 34357 respectively.


Step3:
OpcUa Extender Server historical setting:
The OpcUa extender uploads historical data for each individual node. Therefore, the configuration should be done for each node individually.

Shift to the "Historical" sheet and set the Historical configuration:
OpcUa Ext historical.png
OpcUa Ext historical.png (17.92 KiB) Viewed 288 times

Right click to add a new node:
OpcUa Ext historical editor.png
OpcUa Ext historical editor.png (11.4 KiB) Viewed 288 times

Configure every node to the historical editor:
OpcUa Ext historical editor2.png
OpcUa Ext historical editor2.png (64.68 KiB) Viewed 287 times

p.s. NodeID can be found in the UaExpert in the redbox of the picture below. Follow step2 and step3 in posting.php?mode=edit&p=142 to see how to get the node information on UaExpert
UaExpert get NodeID.png
UaExpert get NodeID.png (217.08 KiB) Viewed 287 times

Step4:
Configure MySQL database:
Before uploading data to the database, database should be setup first.
Setup a new connection to the database in workbench.
MySQL workbench setup new connection.png
MySQL workbench setup new connection.png (45.76 KiB) Viewed 285 times

Setup a password for the user uploaded data from the OpcUa extender server.
Goto "Server" > "User and Privileges"
MySQL user privileges.png
MySQL user privileges.png (182.16 KiB) Viewed 276 times

Select or add an user for OpcUa Extender Server and setup a password.
MySQL user privileges password.png
MySQL user privileges password.png (230.15 KiB) Viewed 276 times

Step5:
Connect OpcUa Extender Server to MySQL database:
Shift to the "Database" sheet, configure database setting.
Database: MySQL 8.x or MySQL 5.x
Hostname: (your database hostname)
Username: (your username)
Password: (your user password)
Default Schema: (the schema you want to upload)
Default Table: (the table in the schema you want to upload)
Click the "Test Connection" button to connect to MySQL.
Then click the "Init. Database" button to initialize the tables in MySQL.
p.s. You may go to viewtopic.php?t=108 for more detailed information to database connection of OpcUa extender server and IoT Studio.
OpcUa Ext database setting.png
OpcUa Ext database setting.png (72.03 KiB) Viewed 286 times
Now, MySQL database is ready to receive information from the power meter.


Step6:
Visualize electricity meter data from the MySQL database on a Grafana dashboard.

Connect MySQL database to Grafana.
Goto "Connection" on the left option bar > find "MySQL" > "Add new data source"
Grafana connect to MySQL.png
Grafana connect to MySQL.png (211.53 KiB) Viewed 276 times

Configure MySQL database connection.
Enter Host URL, Database name, Username and Password.
And click "Save & test". Message "Database Connection OK" will show if database connection succeed.
Grafana connect to MySQL detail.png
Grafana connect to MySQL detail.png (179.71 KiB) Viewed 276 times

Add a dashboard on the Grafana.
Goto "Dashboard" on the left option bar > "Create Dashboard" > "Add visualization" > select the data source we just added "mysql".
Grafana add dashboard.png
Grafana add dashboard.png (116.37 KiB) Viewed 275 times

Now, power meter data can now be visualized on a Grafana dashboard.
Best Regards,
OpcUa Support Team
Post Reply