1 | IoT MQTT System - Broker, DB and Python stations

MQTT IoT Service

The project consists in a set of files and scripts coded in order to built a cloud MQTT based service, for an IoT system, in a really short time. The online broker uses Mosquitto installed on an Amazon AWS server, the sensors are emulated through a python script, which allows you to run up to two stations, the MySQL database and the website run in localhost.

The final system is structured in this way:

Getting Started

First of all you will need an AWS account in order to build up the cloud service: this will be used later in order to create the cloud MQTT broker. Then, as all the scripts, the MySQL DB and the website run locally, you will need a PHP server with mysqli extension enabled and an instance of MySQL installed.

Let's take a quick look at PHP installing. The web server Apache will include all the packets we need, so you can install it on our linux machine:

$ sudo apt-get update
$ sudo apt-get install apache2

Now, you have to enable msqli extension. Edit the php.ini file:

$ sudo nano /etc/php/<YOUR_VERSION>/apache2/php.ini

And uncomment this line:

extension=php_mysqli.dll

Then install this extension

$ sudo apt-get install php-mysql

At this point the PHP configuration is complete.

Supposing you also have Python source code installed on your machine (See more here), you will need Paho Python and PyMySQL libraries:

$ pip install paho-mqtt
$ pip install PyMySQL

More info:

• Paho
• PyMySQL

Finally, as said before, the website uses PHP msqli functions to interact with the database, so you will need a local webserver. I recommend you to install Visual Studio Code and its PHP Server extension, through which you will be able to serve php file at http://localhost:3000.

MySQL database and table

Before start running the system, you will need to instal MySQL locally, start the service and create a new table inside. In order to do this you can run the following commands

$ sudo apt-get install mysql-server
$ sudo systemctl start mysql

Once the packet is installed and mysql is running, you should create a new user, so log for the first time with your root credentials

$ sudo mysql -u root -p

Enter a free password, add a new user and grant privileges using this queries

CREATE USER '<NEW-USER-NAME>'@'localhost' IDENTIFIED BY '<PASSWORD>';
GRANT ALL PRIVILEGES ON *.* TO '<NEW-USER-NAME>'@'localhost';

You need to remember username and password, beacause you will use them to configure the Python scripts. Conclude MySQL setting creating a database and a new table through the following queries:

CREATE DATABASE sensors;
USE sensors;
CREATE TABLE sensors ( id INT, data VARCHAR(100), time_id INT AUTO_INCREMENT PRIMARY KEY );

MQTT Broker

Now you will need to set up the virtual machine on Amazon AWS. This machine will host your Mosquitto broker. Let's see how to do this:

• Access the AWS console and create a new EC2 machine. To do this go through EC2 > Running Instances > Launch Instance
• Select the Ubuntu Server 18.04 LTS type, choose the correct IAM role and go ahead untill the security group configuration
• Here you will need to add two new rules for port 1883 and 8083 with source ip set to 0.0.0.0/0
• Launch the machine and wait untill it is fully running. Instructions are given clicking the "connect" button

• Now open a local terminal and connect to the machine, following the instructions
• Install Mosquitto MQTT broker service:

$ sudo apt-get update  
$ sudo apt-get install mosquitto mosquitto-clients

• Modify the configuration and add the two ports with the different protocols, then restart the service:

$ sudo nano /etc/mosquitto/conf.d/default.conf

Add this four lines and save the file:

listener 1883
protocol mqtt
listener 8083
protocol websockets

Then restart:

$ sudo systemctl restart mosquitto

Now your MQTT broker is up and running. Before leaving take note of the server IP address.

Start scripts

At this point you are ready to run the scripts. Download the all the files contained in this repository. You will obtain all the scripts and files needed to build up the service. Once you've done this open mqtt-broker directory and follow the steps:

• Enter in dbprocess folder, open config.py file and set up the credentials (You can also edit the database update frequency. Default is every 15 minutes)

• Run dbprocess.py. Now the DB will be connected to the broker. Keep the script running

• Return in the main folder and now enter stations one. Edit config file again and then run sensor.py
• Shell commands will be printed. You can create a new stations using start command.

• Once you've created two stations, the system will be up. Keep the shell running

Website

As was written before, you should have installed a program or a service which will allow you to run the index.php file.Before opening the webpage, edit the index file and go down to the javascript code, then edit HOST and PORT variables.

Now you can serve the file and the website will receive data from sensors in real time and retrive last four updates from the database.

Link to second part of the project: Part 2

Project developed as part my MSc degree in Engineering in Computer Science.

Useful links:

• Youtube tutorial
• LinkedIn profile
• Github Repository