my new Pi zero with Wi-Fi.

With this board, there is no excuse to not use MQTT for my projects.

So, today, I build a cheapo MQTT broker for under 20 dollars.

Look at the length of this video and you see, how easy it is to build one.

And you will be astonished, what this little bugger is able to do in addition to broker

MQTT messages!

So, let’s start: The secret behind the whole thing is Peter

Scargill.

He is a wizard in small devices and I recommend to check out his blog.

Pete produced a Christmas gift for the community: A script, which automatically installs a lot

of things on Linux systems, including the Raspberry Pi.

You chose, which packets you want and wait for a few minutes or hours.

After that, you get your system more or less completely configured.

Really great stuff!

I wanted to use this script for my new Pi zero, when I read, that Peter was not happy

with Raspian on the Pi Zero.

It was way too slow for him.

Fortunately, he found a better solution: Diet Pi, which can also be used for our project.

So, I started with installing dietPi on an SD card, enabled it for my Wi-Fi, and copied

Peter’s script to my device.

After that, I created two new users, Pi and admin, and started the script.

Then, I had to wait for a few hours.

Eventually, the script finished, and I had to reboot my Zero.

You find the link in the description, if you want to use Peters script for your purposes.

To check my newly created system out, I started the Mosquitto broker.

It worked.

Next, I started Node-Red using the admin user.

And it worked, too.

Maybe not as fast as I runs on my pi 3, but usable without any problems.

When I looked at the available nodes, I discovered, that Peter did completed staff work.

He already added many nodes like his famous big-timer, johnny5, pushbullet, some google

stuff, raspberry stuff and so on, and so on.

This alone is worth many hours of work!

And he added also the Node-Red dashboard.

So, you get a more-or less complete Node-Red installation.

In addition, he added the SQlite database system and phpadmin light for its administration.

Absolutely great!

There is only one thing I can add here: I made a copy of my SD card and make it available

to you for download.

This saves you all the hassles of downloading and installing dietpi, and wait for the completion

of Peters Script.

If you want, you can download and copy this file to a SD card using Win32 Disk Imager,

insert the card into your Raspby Pi Zero, connect your Zero to a display and a keyboard

and enter your Wi-Fi credentials into the file /etc/network/interfaces.

That’s all.

And the whole thing did take for sure less than half an hour.

If you kept the assigned fixed IP address, you can Putty into a fully configured MQTT

broker by connecting to 192.168.0.202.

Just for your information, you find these three users on your system:

Username Password pi password

admin admin For Node-Red root dietpi

Of course, you can change the passwords, or add new users, if you wish.

Because we saved so many hours, we still have some time left.

So, I will build a small data logger for my Sonoff switch using MQTT, Node-Red, and the

SQlite data base.

From now on, I assume, that you used my SD card image for your Pi Zero.

This image contains all material needed.

So, first, we test the Mosquitto broker.

We could do this by using the built-in functions like mosquitto_sub and mosquitto_pub in the

terminal window.

But, because we already have Node-Red available, we use this tool to do that.

So, we can also check the connection between the two packages.

We use the “MQTT test flow” in Node-Red to do that.

First we have to adjust the IP address of the MQTT broker to the address of your Pi

zero, if you changed it.

This has to be done in these two nodes.

In Node-Red, you have to “deploy” after every change to activate the changes.

Now, Node-Red should connect to Mosquitto and you should see the green points here.

If we now press this button, a text is published to Mosquitto.

And because we subscribed to the all topics, the message comes back from Mosquitto to Node-Red

and into our debug window.

So, our “Hello world” scenario is live.

Node-Red and Mosquitto are working together.

Cool.

Next, we go to the “MQTT data Logger Flow”.

This scenario needs three main nodes to perform the task.

I added two debug nodes to show you, what happens.

Let’s start with the MQTT node.

This node is subscribed to the “Relay topic of our Sonoff device from last video.

The next node inserts the topic, the payload, and a timestamp to SQlite.

The last node creates the connection to the database.

So, Let’s first look at the setup of the database.

To do that, we have to connect to phpadminlite.

Here, we can manage our database.

If you used my image file, you will already find a database called IOT with a few tables.

We will only use table Sonoff for this logger.

This table has three fields: The topic, a timestamp, and the relay state.

If we browse this database, we see, that it is empty.

Now, we go back to Node-Red and to the function which creates the SQL statement which inserts

a record into the database.

I have to confess, I have no idea of Java script and I just used an example and adapted

it till it did, what I wanted.

So, for sure, there are better ways of doing it.

But still, it might be useful for you.

The first part creates this line in Node-Red.

It shows us, when the last update took place.

The next part creates a string called SQL which is filled with an SQL statement.

At the end, this string is pushed to the database.

If we look at the creation of this string, we see “Insert into Sonoff, which is the

table we want to fill.

Then, we find all three fields we want to populate.

Next, we find the Values, also separated by commas.

The string values have to be framed by quotes.

If we have a closer look, we see, that we created an ordinary SQL statement.

If we go back to our SWL administration, we can execute this statement.

And we see the result in the database.

The last node does exactly that: It transfers the SQL statement to the database.

That’s all.

If we now switch the Sonoff of- and on again, we see two additional SQL statements in Node-Red

and the two entries in the database.

So, our MQTT logger works!

Summarized: - We can use our Pi Zero W as a MQTT broker.

- If we use dietpi and Peter Scargill’s script, we easily can create such a device.

- If you use my pre-fabricated SD card image, you are even faster and you get your broker

in less than half an hour.

- This broker does not only work as a MQTT broker.

It includes also many other pre-installed packets

- Because viewers asked me, how to log MQTT data, I used this newly created device to

build a simple MQTT messages broker with SQlite - All-in all, it is astonishing what we can

get for less than 20 dollars and half an hour of work.

It does what we wanted and we even can extend its usage .

I hope, this video was useful, or at least interesting for you.

If true, then like.

Bye