Menu
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.
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.
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.
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.
NCD offers a wide range of IoT sensors that are designed to meet the needs of various industries. Visit home to get overview of categories of services we offer.