Meshtastic Message Persistence using Docker Containers Link to heading

Want to store messages received from your Meshtastic mesh and store it in a database for later analysis/use?

• Have them display on a dashboard on Home Assistant
• Store them in a database for later analysis
• Access GPS and node telemetry data. Pump it to Grafana for some sweet dashboards

This is the solution for you! Deploy made easy with Docker :)

I saw an opportunity to containerise and simplify the deployment process using Docker ❤️ this also makes the solution portable between AMD64 & ARM architectures. Handy if you prefer to run this on a Raspberry Pi.

Containers Link to heading

To quickly deploy this solution, clone the below repository and deploy the stack as described below

GitHub: https://github.com/kn0xyland/meshtastic-mqtt-mysql-docker

To build the container, simply update the ‘docker-compose.yml’ file with your own MQTT and MySQL server details, and then run docker-compose up -d to build and run the container. The Docker-Compose & Dockerfile are provided in the repository.

This will launch 3 containers, App, MySQL and MQTT.

The App container will run the PHP-CLI script, and will connect to the MySQL and MQTT containers.

All containers run within the same virtual network managed by Docker.This is so they can communicate with each other using the container names as hostnames. Eg. “DB” and MQTT" resolve to their respective containers. It also keeps the solution’s inter container communication secure and isolated from the host network.

Deployment Steps:

1. git clone https://github.com/kn0xyland/meshtastic-mqtt-mysql-docker.git
2. cd meshtastic-mqtt-mysql-docker
2. Update docker-compose.yml with your MQTT and MySQL server details
3. Run "docker-compose up -d" to build and run the containers
4. To view the logs of the App container, run "docker logs -f meshtastic-mqtt-mysql_app_1"
5. To stop the containers, run "docker-compose down" from within the same directory

Allow about a minute for the containers to stabilise, they are eventually consistent, so give it a moment to get everything up and running :)

Some useful diagnostic commands are:

To gain a shell into the running container, check logs or perform other tasks:

1. Run “docker ps” to get the container ID
2. Run “docker exec -it <container_id> /bin/bash” to gain a shell into the container
3. Run “docker logs -f <container_id>” to view the logs of the container
4. Run “docker restart <container_id> to restart a container
5. Run “docker kill <container_id>” to stop a container
6. Run “docker rm -f <container_id> to stop and remove the container from the host

Consider changing the passwords in the docker-compose.yml file to something more secure or use secrets to store the credentials. Also, consider securing the MQTT server with authentication as it is currently wide open for anyone to connect to from within your private LAN.

Configuration Link to heading

Exploring the docker-compose.yml file, you will see the environment variables for each of the containers, these are used to pass the necessary configuration to the containers so they start correctly.

Let’s look at them in more detail, here is a except from the App containers configuration:

    environment:
      DB_HOST: db
      DB_USER: meshtastic
      DB_PASSWORD: 123456acbde
      DB_NAME: meshtastic
      MQTT_HOST: mqtt
      MQTT_PORT: 1883
      MQTT_NODEID: "!daXXXXX"
      MQTT_TOPIC: "Meshtastic/2/json/LongFast/"

As you can see, authentication details are set here, as well as the MQTT topic to subscribe to. This is important because the PHP-CLI script will subscribe to this topic and capture the messages.

Notice how the MQTT_TOPIC is set to “Meshtastic/2/json/LongFast/”, this is the default topic that the Meshtastic devices publish to. If you have changed this on your Meshtastic devices, you will need to update this value in the docker-compose.yml file.

In my Meshtastic deployment, my PRIMARY channel, ch0, is set to a custom name instead of LongFast. I had to update this path with my channel name for the messages to be captured.

You will also need to set your Meshtastic device ID under MQTT_NODEID. This is the unique identifier for your Meshtastic device, and is used to filter out messages from other devices on the same channel.

Next up, the MySQL root password.. Definitely change this to something more secure, also consider storing the secret via docker secrets.

    environment:
      MYSQL_ROOT_PASSWORD: root

On the subject of MySQL, Let’s have a peak at the MySQL INIT script. This script is run when the MySQL container is first started, and is used to create the database and user, and grant the necessary permissions to the user.

CREATE DATABASE IF NOT EXISTS meshtastic;
CREATE USER IF NOT EXISTS 'meshtastic'@'%' IDENTIFIED BY '123456acbde';
GRANT ALL PRIVILEGES ON meshtastic.* TO 'meshtastic'@'%';
FLUSH PRIVILEGES;

You will need to update the Meshtastic user’s password, or I recommend you do at least. This will need to be matched in the App container’s environment variable for the user.

Configuration files explained: Link to heading

Dockerfile: Contains instructions for copying the PHP app into the container and installing the necessary dependencies to build the container image.

Docker-Compose.yml: Contains instructions for building and running the containers, as well as setting up the network. Also contains environment variables passed into the MySQL and MQTT containers directly.

mosquitto.conf: Contains the configuration for the Mosquitto MQTT server. This configuration file is mounted into the MQTT container in the location of where the Mosquitto server expects to find its config file. This file is configured to allow anonymous access to the server, you could however add authentication options here to secure it (Recommended)

init.sql: Contains the SQL commands to create the database and user, then grant the necessary permissions to the user. This file is mounted into the MySQL container and the schema is imported into the database on first boot via an INIT process.

meshtastic.sql: Contains the Meshtastic database schema for the MySQL database. This file is mounted into the MySQL container and the schema is imported into the database on first boot using the same process as the init.sql file.

entrypoint.sh - This script is run when the App container is started. It contains the commands to run the PHP-CLI script, and to keep it running in the background. It also contains the logic to check if the MySQL and MQTT servers are up before starting the PHP-CLI script.

monitor_mesh.php - This is the PHP-CLI script which subscribes to the MQTT server, captures the messages, and imports them into the MySQL database. This script is copied into the App container when it is built. Environment variables are used in this script to connect to the MySQL and MQTT servers.

Volume persistence has been configured through Docker Volumes. This means that the data in the MySQL database will persist even if the container is stopped or restarted. The data is stored in the ‘mysql-data’ volume, the same goes for the mqtt-data volume.

That is pretty much it! Messages sent to your PRIMARY channel should now end up in my MySQL DB via the App containers PHP-CLI script. A quick check of the MySQL table shows the messages being stored upon being received by the Meshtastic device.