Major Factory Floor Upgrades Begin Monday May 4th, Some Delays are Expected through June 5th. Please Read More Here. Dismiss

You are using an unsupported browser!

Please upgrade your version of IE to at least 9, or use a modern browser such as Chrome or Firefox.

Introduction

In this article we will explore running an MySQL server on your computer.  We’ll use a Python script to subscribe to MQTT messages and then log sensor telemetry data received to the MySQL Database.  It is assumed that you have an MQTT Gateway and you have already installed and setup the Mosquitto MQTT Broker on your computer, if not please have a look at This Article.  The MQTT Gateway should be reporting sensor data to The MQTT Broker.  If you have all of that ready please read on.

Installing MySQL

MySQL can be installed on Linux, Mac, or Windows computers.  It is a database application that stores information in a common and easily retrievable format.  It is one of the most widely used DataBase formats.

Installation on Mac

MySQL can be installed on Mac using Brew with this command in the terminal:

brew install mysql

More information on installing mysql on Mac can be found here.

Installation on Linux

MySQL can be install on Linux using apt-get with this command in the terminal:

apt-get install mysql-server

Note that you may need to sudo this command depending on your system.  More information can be found here.

Installation on Windows

This is not as straight forward and would be best left to the documentation provided by MySQL here.

Initial setup of the MySQL Server

The Python app will do the majority of the heavy lifting logging your sensor data but there are a couple of things we need to configure on the MySQL server.  First we’ll want to create a DataBase.  MySQL supports multiple DataBases but for our use case we’ll only really use one.  We’ll also need to create one table inside that DataBase where information on the sensor will be located.  The Sensor’s Telemetry data on the other hand will be in a separate table that is automatically created by the Python Script.  Lastly we’ll create a user for MySQL that will be utilized by the Python Script to log data.

Start the MySQL Server

The first thing we need to do is actually start the server.  Execute the follow terminal command.

mysql.server start

Create the DataBase

First go ahead and start MySQL and log in as the root user by entering the following command into the terminal:

mysql -u root -p

You will be prompted to enter a password but MySQL does not password protect the root user by default so just press enter when prompted.

Now it’s time to think of a name for your Database.  I’m going to call mine Office since it will listen to sensors inside our office.  Use any database name you like but remember what it is because we will need it in a later step.  To create the database with the name you have chosen enter it in the terminal like this:

CREATE DATABASE office;

You should get a response like Query OK.  Now that the Database is created we need to let MySQL know that we want to work inside that database so enter the terminal command:

USE office;

If you named your Database something other than office be sure to substitute your database name here.

Create the Sensors table in the database

Our python script will update a sensors table any time it receives a transmission from the sensor.  This acts as sort of a table of contents for your sensors.  It does not actually contain readings from the sensor but rather information on them like their ID, the last time we heard from them, what type of sensor they are, etc.  Lets create a table for this information with the following terminal command:

CREATE TABLE sensors(device_id char(23) NOT NULL, transmission_count INT NOT NULL, battery_level FLOAT NOT NULL, type INT NOT NULL, node_id INT NOT NULL, rssi INT NOT NULL, last_heard TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

One thing to keep in mind with MySQL is quarries and commands can get really long.  This command created a table inside our database and defined all of the columns this table will have on every row.  Our Python script will follow this structure when inserting and updating rows in the database so be sure you do not change this command, if you do then you also need to update the Python Script.

Create a User for the Python script to use

For accountabilities sake it’s a good idea to create users for different tasks.  We’ll create a MySQL user for our python script that logs data to the Database.  To do this run the following terminal command:

CREATE USER 'python_logger'@'localhost' IDENTIFIED BY 'supersecure';

This creates a MySQL user with the username python_logger and the password supersecure.  It also notes that this user will be on the same computer as the MySQL server since our Python script will run on the same computer as the MySQL server and the Mosquitto MQTT Broker.  Now we need to give this python_logger permissions to create tables in the database and to insert and update rows in the tables.  To keep things simple and since this script is running locally on the computer we’ll just give it full access to our Database with this command:

GRANT ALL PRIVILEGES ON office.* to 'python_logger'@'localhost';

This gives the python_logger user all privileges to do pretty much anything it likes inside the office database(be sure to substitute office above if you named your database something different).  Now you are more than welcome to revise this a bit and only grant it the permissions it needs(Insert, Update, and Create Tables) but for the simplicity of this article lets just let it do anything inside the database.

This concludes initial setup for the MySQL Server, but KEEP THIS TERMINAL WINDOW OPEN for later use.

The Python Script

Our Python script will subscribe to the Mosquitto broker and when it receives telemetry messages from sensors through The MQTT Gateway it will publish that information to our MySQL data base.  Download the Python Script here.

At the top of this script you will find some variables.  Here you should enter information as needed.  The most important things are myGatewayID which should match the ID of your Gateway(you can get this by monitoring The MQTT Topic as explained in the Mosquitto Broker setup article, dbName should be what you named your database, and the mysqlUser and mysqlPassword variables should match the user you created.  You should not need to change anything else in the Python file other than at the top.

Once you have all the variables checked out go ahead and give it a go by running the python script on the same computer as the Mosquitto and MQTT Broker.  It should start logging data to the MySQL database.  Once you have the script running press the reset button on one of your sensors to force a transmission.  If you have the terminal window open where you started the Python Script you should see Transmission Received data Logged.  That means everything went as expected.

Now that we have a transmission sent from a sensor lets see if this all worked.  Back in your terminal window where you were entering mysql commands enter the terminal command:

SELECT * FROM sensors

This should show any sensors transmissions have been received from.  When a transmission is received from a sensor type that has never been seen before a table will be created for it named type_X where X is the sensor type so if you see a sensor listed in there that is type 8 then it’s sensor data will be logged to a table called type_8.  Lets see if any tables have been created by entering this command in the MySQL terminal:

SHOW TABLES;

Here you will see the sensors table and additional tables for every sensor type that has been heard from.  You can see the sensor data logged in a particular type table by entering the following in the MySQL terminal(Replace X with your sensor type):

SELECT * FROM type_X

Here you will see all data logged from that sensor.  If this works then pat yourself on the back, you’ve got the makings of a real cloud.

©2019 National Control Devices, LLC

ncd.io

Made in the USA
5 Year Warranty
Free Shipping on orders over $100
Free Shipping on orders over $100

To learn something new, take the path that you took yesterday. ~John Burroughs

*

Log in with your credentials

or    

Forgot your details?

Create Account