VM Setup

• Create an Ubuntu 18.04 Virtual Machine and login as a user with root privileges.

• Clone the project:

$ git clone https://github.com/thanospan/EESTech-Challenge-2018-2019.git

• Install Docker:

$ sudo apt update
$ sudo apt install apt-transport-https ca-certificates curl software-properties-common
$ curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
$ sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu bionic stable"
$ sudo apt update
$ apt-cache policy docker-ce
$ sudo apt install docker-ce
$ sudo systemctl status docker

• Add the user to the docker group to be able to execute the docker command without sudo:

$ sudo usermod -aG docker ${USER}
$ su - ${USER}
$ id -nG

• Pull the required Docker images:

$ docker pull nodered/node-red-docker
$ docker pull eclipse-mosquitto
$ docker pull mysql

• Run the required Docker containers:

$ docker run -itd -p 1880:1880 --name nodered --restart unless-stopped nodered/node-red-docker
$ docker run -itd -p 1883:1883 -p 9001:9001 --name mosquitto --restart=unless-stopped eclipse-mosquitto
$ docker run -d --name mysql --restart unless-stopped -e MYSQL_ROOT_PASSWORD='root-pw' mysql

... where root-pw is the password to be set for the MySQL root user.

• Find the IP addresses of the containers:

$ docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' nodered
$ docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' mosquitto
$ docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' mysql

• Access MySQL inside of the container:

$ docker exec -it mysql mysql -uroot -p
Enter password:

• Create a user for accessing the MySQL database from Node-RED:

mysql> CREATE USER 'username'@'nodered-addr' IDENTIFIED BY 'user-pw';

... where username, user-pw are the username, password to be set for the MySQL user and nodered-addr is the IP address of the nodered container.

• Set the privileges for the new user:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'nodered-addr';
mysql> ALTER USER 'username'@'nodered-addr' IDENTIFIED WITH mysql_native_password BY 'user-pw';
mysql> FLUSH PRIVILEGES;

• Create a new database and select it:

mysql> CREATE DATABASE iot;
mysql> USE iot;

• Create the following tables:

mysql> CREATE TABLE env1(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
thing_id VARCHAR(10) NOT NULL,
timestamp VARCHAR(20) NOT NULL,
tmp FLOAT NOT NULL,
hmd FLOAT NOT NULL,
prs FLOAT NOT NULL);

mysql> CREATE TABLE env2(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
thing_id VARCHAR(10) NOT NULL,
timestamp VARCHAR(20) NOT NULL,
prx ENUM('On', 'Off') NOT NULL,
amb MEDIUMINT UNSIGNED NOT NULL);

mysql> CREATE TABLE btn(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
thing_id VARCHAR(10) NOT NULL,
timestamp VARCHAR(20) NOT NULL,
btn ENUM('Pressed', 'Released') NOT NULL);

• Check the tables and exit:

mysql> DESCRIBE env1;
mysql> DESCRIBE env2;
mysql> DESCRIBE btn;
mysql> exit;

• Open a browser and visit http://{vm-ip-address}:1880 to access the Node-RED editor:

• Install the required nodes:

Search for node-red-node-mysql and node-red-dashboard.

• Once installed, click the Close button.

• Import the flows from EESTech-Challenge-2018-2019/Cloud/flows.json to Node-RED:

• Double click on one of the MQTT nodes.

• Set the topic. Topics used for this project: thingId/env1, thingId/env2, thingId/btn ... where thingId=1

• Click the button next to the select box to edit the MQTT broker node:

• Set the Server field to the IP address of the mosquitto container.

• Click Update and Done

• Double click on one of the MySQL nodes.

• Click the button next to the select box to edit the MySQL database node:

• Set the Host field to the IP address of the mysql container. Set the User, Password fields to the username and user-pw of the MySQL user created to access Node-RED. Set the Database field to the name of the MySQL database (e.g. iot).

• Click Update and Done.

• Click the Deploy button.

• Open a browser and visit http://{vm-ip-address}:1880/ui to access the Node-RED dashboard.