Name: #第126回 Raspberry Zero W / DietPi / MQTTメッセージロガーSQlite / PHPliteAdminの格安MQTTブローカー (#126 Cheap MQTT Broker on Raspberry Zero W / DietPi / MQTT Message Logger SQlite / PHPliteAdmin)
Uploaded: 2021-01-14T06:53:17.000Z
Duration: 10 min 20 s
Description: VoiceTubeの動画で発音を聞きながら英語表現を覚えよう！学べる英語：

In my last video, I started to use MQTT and a Raspberry Pi as a Broker This week, I got

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

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

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.

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

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

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

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.

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

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.

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.

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

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

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.

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

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

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

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

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

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

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

So, our “Hello world” scenario is live.

Node-Red and Mosquitto are working together.

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.

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.

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

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

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

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

#第126回 Raspberry Zero W / DietPi / MQTTメッセージロガーSQlite / PHPliteAdminの格安MQTTブローカー (#126 Cheap MQTT Broker on Raspberry Zero W / DietPi / MQTT Message Logger SQlite / PHPliteAdmin)