How to deploy and access MySql using Azure IoT Edge

In my previous blog, I showed how regular Docker containers can be rolled out using Azure IoT Edge.

But what about databases, can these be deployed too?

Yes, I showed how to deploy and connect to SQL Server in the past and it works very well if you like SQL Server.

But what about MySql, can we connect to this database too?

Many of the world’s largest and fastest-growing organizations including Facebook, Google, Adobe, Alcatel Lucent, and Zappos rely on MySQL to save time and money powering their high-volume Web sites, business-critical systems, and packaged software.

https://www.mysql.com/why-mysql/

MySql is available as an official Docker container which is needed for this exercise:

So let’s give it a try.

We know which container Image URI we need to use:

In this example, I connect to version 5.7. Version 8 is also available but the security is more strict in that version. For now, I use the root account and give it a specific root password:

Note: Using a root password is used here for demonstration purposes. This is not the recommended way in production environments. As seen in the MySql documentation, a file with credentials is more secure:

Once the password is set, I need two other settings:

  1. Define a local folder which MySql can use to store persisted data outside the container
  2. Define access to the (inner) ports from outside the container

For demonstration purposes, I expose ports 3306 and 33060. I want to access it using apps and the workbench:

Configuration of ports and volumes is handled by the Container Create options:

{
    "HostConfig": {
        "Binds": [
            "/var/mysql:/var/lib/mysql"
        ],
        "PortBindings": {
            "3306/tcp": [
                {
                    "HostPort": "3306"
                }
            ],
            "33060/tcp": [
                {
                    "HostPort": "33060"
                }
            ]
        }
    }
}

This supports the access to the MySql server on both port 3306 and 33060 and folder /var/mysql.

It’s simple to change folder access on the local filesystem for our server to persist any MySQL data:

sudo mkdir /var/mysql
sudo chmod 777 /var/mysql

Once the container is deployed using Azure IoT Edge, we see the port settings:

And we see the usage of the folder bt MySql:

This proofs the container is deployed and MySql runs with the settings. But can we access it?

Test the connection with a client

Let’s test if we can access the MySql running is a container deployed from the cloud using Azure IoT Edge.

To test the access, I use the MySql workbench. I install it on a second device running Windows 10. It’s located in the same network as my MySql machine.

Download the Mysql workbench installer.

Note: I recommend to download Visual C++ first if you use Windows 10. Otherwise, the installation of the workbench could fail.

fill in the IP address of your MySql machine:

ip addr

Once the workbench is installed and running, fill in the IP address. Use the port 3306:

If you hit ‘Test Connection’ you are asked to fill in the root password. Fill it in and hit ‘ok’:

So we can access our MySql server. But it’s empty at this moment.

Note: our database is accessible within the network. In production, limit the access to your database as much as possible.

Try to add a database and a table:

SHOW DATABASES

CREATE DATABASE dockeredmysql

USE dockeredmysql

CREATE TABLE building (
  id INT UNSIGNED DEFAULT '0000' NOT NULL,
  name CHAR(20) DEFAULT '' NOT NULL,
  price DECIMAL(16,2) DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(id));

Once executed, we can insert and select records:

Update: MySql 8 on Jetson nano

Recently, I did a project with Azure IoT edge on a Jetson Nano. This is an ARM64 device.

MySQL 8 on Docker “mysql/mysql-server:latest” supports ARM64, so from there it’s an easy step towards a deployment:

Add these environment variables:

And, add these container create options:

The complete list:

{
    "HostConfig": {
        "Binds": [
            "/var/mysql:/var/lib/mysql"
        ],
        "PortBindings": {
            "3306/tcp": [
                {
                    "HostPort": "3306"
                }
            ],
            "33060/tcp": [
                {
                    "HostPort": "33060"
                }
            ],
            "33061/tcp": [
                {
                    "HostPort": "33061"
                }
            ]
        }
    }
}

This makes the ports available outside Docker and also connects to the folder on the file system.

We are able to connect:

Conclusion

We can deploy MySQL too within Azure IoT Edge. In this example, it’s even accessible from outside the machine running the server. So it should be accessible by apps running a container side by side with the MySql server container.

Keep in mind, the module is deployed zero-touch from the cloud, using the deployment mechanism provided by the Azure IoT Edge deployment manifest.

In the latest update of this post, we now support MySQL 8 too.

Een gedachte over “How to deploy and access MySql using Azure IoT Edge

Reacties zijn gesloten.