Log MQTT Gateway data to MySQL Database

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.