Import data from a Mariadb/phpmyadmin database on a different raspberry pi

Third party integrations
1

Home Assistant : Supervised latest version on Raspberry Pi 4 with SSD

I have a water butt monitoring project on another Raspberry Pi 4 that is collecting regular data of water levels in 2 water butts. This is working quite nicely and writes a row to a database recording HRCS04 sonic distance readings to the water surface.

What I have been thinking about is also using this raw data in Home Assistant. I can access a web page on the 2nd Raspberry Pi which is quite nice but recording this data in Home Assistant sensors could be great for integration.

Given that I use mqtt a lot, I wondered if it was possible to trigger mqtt messages on the external database when a new row is inserted. Then these could be stored in an MQTT sensor (not sure how the sensor would be defined yet but I assume this is doable).

I guess the other option is to somehow access this database from home assistant but have not been able to find a way of doing that.

Anyone done anything similar and if so, was it a success?

2

I am now considering the following option:

My water butt project is on an ESP32 and is coded with Micropython. I believe I can double up on sending the data in this code, once via the Phpmyadmin data insert call and, directly after, create an mqtt message with umqttsimple installed on the esp32. Assuming this creates no great hits on memory usage, that should mean mqtt is created for every reading.

I will need to look at how I set a sensor up to read in these mqtt values in HA. I assume that is simple? There seems to be an mqtt sensor yaml integration in documentation

3

OK went with mqtt code on the esp to send the data via my mosquitto server. The numerous readings I required were in a json topic.

Created a sensor in mqtt to pull out one of the json values as an entity state and also setting all the json readings as attributes of this.

Works fine.