---
title: Deploy and Use MySQL
slug: manufacturing-connect-edge-v2/deploy-and-use-mysql
docTags: 
createdAt: 2022-12-06T18:38:30.000Z
---

You can make the most of the massive amount of data collected by Manufacturing Connect Edge by storing the data in a database for further analysis. The Manufacturing Connect Edge default Marketplace includes a MySQL application.

# Step 1: Deploy the MySQL Application

Manufacturing Connect Edge provides a variety of applications in its default marketplace catalog. In the case of MySQL, a container-based MySQL database is provided, which is local to the Manufacturing Connect Edge device. Data collected at the edge can be stored in this local database. In addition, you can extract data from the local database for consumption by other databases in your enterprise.

:::hint{type="warning"}
**Caution**:

- Caution should be used when using this local database. If the size exceeds the hardware memory size, it crashes the Manufacturing Connect Edge system, causing data to be lost and requiring a fresh installation.
- To manage the size of the local database, we recommend weekly backups and management of local data by using scripts.
- As an alternative to the local database, collect data and send it to an external database that is on the same network as the Manufacturing Connect Edge device.
:::

## Before You Begin

Internet connectivity is required to deploy the Default Public Marketplace and to deploy applications. Once you create an application instance, you no longer need internet access to create additional instances of the same application.

**To deploy the MySQL application:**

1. In Manufacturing Connect Edge, navigate to **Applications **> **Marketplace**. 
2. Click the **MySQL **tile.
3. The MySQL Launch dialog box appears.
4. From the **Installation script version **drop-down list, select **latest**.
   The MySQL form appears.
5. Configure the following parameters. 
   - **Name**
   - **Description **(Optional)
   - **Port**: Enter a port. The default value is 3306. 
   - **Database**: Enter a database name.
   - **User**: Enter a user name.
   - **Password**: Enter a MySQL password.
   - **MySQL password**: Enter a MYSQL root password. 
   - **Restart**: Configure the restart setting. Enter **no**, **always**, or **on-failure**. 
6. Click **Launch**.
   The MySQL application appears as a tile in the *Overview *pane.

# Step 2: Create a MySQL Database Table

You will now need to define a database table for the data you're collecting. 

You can use a tool, such as MySQL Workbench, to connect to the Manufacturing Connect Edge local database to create a table. 

## Identify Database Table and Column Requirements

The columns and data types required for a database table depend on a device tag's configuration. For this exercise, a basic database table illustrates data that can be saved to the database. Using the [Flows Manager](docId\:eCK0tQfoy_d2ywgK4h1wq),  you can collect and parse the message payload to extract the device ID, device tag, and the register's value.

### Before You Begin

- You will need to configure the DataHub Subscribe node with a topic for a connected device. Navigate to **DeviceHub **> **Tags**, select a device, and copy the **RAW Topic** for the tag you want to use. 
- You need to know the message format. In the following example, you can see the field names and values that you may want to save in a database.

:::BlockQuote
\{"success": true,"datatype": "int", "timestamp": 1531914109501, "registerId": "D22AC344-B908-481D-AA7E-A45E0041AC46", "value": 433, "deviceID": "45F95B1C-F6F0-4696-A271-60FA1A5E79DD", "tagName": "AB-1"}
:::

**To identify database table and column requirements: **

1. In Manufacturing Connect Edge, navigate to **Flows Manager**. 
2. Click the **Go To Flow Definition** icon for a selected **Flows Manager**. 
   The* Flow canvas *opens in a new browser tab. 
   ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/MVI-HwGTIPdmiVe6eOtck_gotoflowdefinition.png" size="80" width="1080" height="547" caption="Go To Flow Definition icon" position="center" showCaption="true"}
3. From the node palette, drag the **DataHub Subscribe** node (DataConnector section) to the canvas.
   ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/j2Sz7jq3aibQ0V56l3Cy8_datahubsubscribenode.png" size="80" width="1055" height="690" caption="DataHub Subscribe node" position="center" showCaption="true"}
4. Drag the **Debug **node to the canvas and connect the two nodes.
5. Double click the **DataHub Subscribe** node. 
   The *Edit DataHub Subscribe Node *dialog box appears.
6. In the **Topic **field, paste the topic name you previously copied and click **Done**.
7. If needed, configure the *Datahub Subscribe* connection. See the "Step 3: Configure Connector Nodes" section in [Create a Flow](docId\:cvhYwrcRDrQPGReHBQNZ-) to learn more.
8. Click **Deploy**. 
9. Drag the **Sidebar **beneath the flow up and click the **Debug **icon. Verify that messages are displaying. 
   ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/gPJk6NsW3loIyEUdOJWuM_debugicon.png" size="80" width="885" height="365" caption="Debug node messages" position="center" showCaption="true"}

## Create a Database Table

While table creation can be accomplished by writing SQL statements in a flow, the preferred method uses a database tool, such as MySQL Workbench, to create the table and columns in the Manufacturing Connect Edge local database.

For the purpose of this exercise, connect to the Manufacturing Connect Edge database and create a database table with the following columns: **deviceID**, **tagName**, and **tagValue**.

## Create Flow to Populate a MySQL Database

You can create another flow to collect data and store it in a MySQL database. 

:::hint{type="info"}
**Note**: You can access the MySQL database located on the Manufacturing Connect Edge device or you can use an external database on the same network as the Manufacturing Connect Edge device.
:::

Refer to the flow below that you will create. 

![](https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/YRrJTpX09TaDaBIAOiziz_image.png "Flow to populate a MySQL Database")

### Before You Begin

- You will need to configure the DataHub Subscribe node with a topic for a connected device. Navigate to **DeviceHub **> **Tags**, select a device, and copy the **RAW Topic** for the tag you want to use. 
- You must have experience working with flows and SQL queries. 

**To create a flow to populate a MySQL database:**

1. In Manufacturing Connect Edge, navigate to **Flows Manager**. 
2. Click the **Go To Flow Definition** icon for a selected **Flows Manager**. 
   The* Flow canvas *opens in a new browser tab. 
   ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/egD1Pny41TYSP9mPD7yE1_gotoflowdefinition.png" size="80" width="1080" height="547" caption="Go To Flow Definition icon" position="center" showCaption="true"}
3. Refer to the following tasks for nodes. 

### DataHub Subscribe and JSON Nodes

After configuring the DataHub Subscribe node with a topic from a connected device, it collects the message. The JSON node processes the message to ensure that it is in the proper JSON format required for further processing.

1. Drag the **DataHub Subscribe** and **JSON **nodes onto the canvas and connect both nodes. 
2. Double-click the DataHub Subscribe node. 
   The *Edit DataHub Subscribe Node *dialog box appears.
3. In the **Topic **field, paste the topic name you previously copied and click **Done**.
4. Click **Deploy**. 

### Function Node

The Function node parses the incoming message. 

1. Drag the **Function **node onto the canvas and connect it to the **JSON **node. 
2. Double-click the **Function **node and enter the JavaScript code below in the **On Message **tab. This parses the message payload. 
3. Click **Deploy**. 

```javascript
var obj = {};
obj.deviceID = msg.payload.deviceID;
obj.tagName = msg.payload.tagName;
obj.value = msg.payload.tagValue;
msg.payload = obj;
return msg;
```

### Template Node

Use the Template node to write SQL statements to insert data into the database columns.

1. Drag the **Template **node onto the canvas and connect it to the **Function **node. 
2. Double-click the **Template **node. 
3. In the **Property **field, select **msg.** and enter **topic**. 
4. Enter the SQL statement below in the **Template **window. 
5. Click **Deploy**. 

:::hint{type="info"}
**Note**: In the example statement below, **test **represents the name of the database table, which you need to create. The table doesn't exist by default. 
:::

```sql
INSERT INTO test (deviceID,tagName,tagValue) VALUES
('{{payload.deviceID}}','{{payload.tagName}}','{{payload.tagValue}}');
```

### MySQL Node

Configure this node with a port and credentials to connect to the local Manufacturing Connect Edge MySQL database when using the Marketplace application. When using an external MySQL database on the same network as the Manufacturing Connect Edge device, configure the MySQL** **node with the IP address, port, and credentials for that database server.

1. Drag the **MySQL **node onto the canvas and connect it to the **Template **node. 
2. Double-click the **MySQL **node. 
3. In the **Database **field, click the **Edit **icon. 
4. Configure the following parameters. 
   - **Host**: 127.0.0.1
   - **Port**: 3306
   - **User**: root
   - **Password**: Enter the MySQL database password
   - **Database**: Enter the name of the database

### Debug Node

Verify your flow is working as designed.

1. Drag the **Debug **node onto the canvas and connect it to the **MySQL **node. 
2. Drag the **Sidebar **beneath the flow up and click the **Debug **icon. Verify that messages are displaying. 
   ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/gPJk6NsW3loIyEUdOJWuM_debugicon.png" size="80" width="885" height="365" caption="Debug node messages" position="center" showCaption="true"}

## Validate Database Updates

To check that collected values are being inserted into the MySQL database, create a basic flow.

Refer to the updates to the flow created in the previous steps. 

![](https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/2IqrN3OqNyNMF4Siaoz9L_image.png "Updated flow to validate connectivity")

**To validate database updates: **

1. From the flow canvas you used to create the previous flow, copy and paste the **Template **node previously created and connect it to the **Function **node. 
2. Double-click the **Template **node. 
3. Enter the following SQL statement in the **Template **window and click **Done**. 
   `select * from test;`
4. Click **Deploy**. 
5. Copy and paste the **MySQL **node previously created and connect it to the newer **Template **node. 
6. Click **Deploy**. 
7. Drag a new **Debug **node onto the canvas and connect it to the newer **MySQL **node. 
8. Drag the **Sidebar **beneath the flow up and click the **Debug **icon. Verify that messages are displaying. 
   ::Image[]{src="https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/gPJk6NsW3loIyEUdOJWuM_debugicon.png" size="80" width="885" height="365" caption="Debug node messages" position="center" showCaption="true"}

# Step 3: Save Multiple Registers to MySQL

You will now update the original template node on the flow canvas to do the following:

- Collect raw data from various PLC registers.
- Aggregate the data at regular intervals.
- Save the aggregated data for use by other applications.

Use the Template node to format the SQL statement to update the database with values that have been collected from multiple registers.

**To save multiple registers to MySQL:**

1. From the same flow canvas you used to create and update flows, double-click the original **Template **node. 
2. Enter the following SQL statement in the **Template **window and click **Done**. 

```sql
INSERT INTO mytable(value1,value2,value3,value4) values 
({{output1}},{{output2}},"{{output3}}","{{output4}}");
```

:::hint{type="info"}
**Note**: Depending on the MYSQL column types, certain formatting may be required. For example, if a column is VARCHAR, the following notation must be used: **"\{\{}}"**. 
:::