How database and web server interface with mosquitto?
Do you need to do below tasks or many 'common tasks' have ready-available software that you can configure and use.
Do you write your own program to subscribe to broker, get data, insert into (SQL) database. and then another software read data base and generate html file to feed the web server
It depends on your preference. I am a professional C++ and python developer.
There have been times I have written stuff using the python 'paho' mqtt client library. It's pretty simple if you are a developer but you have to start the service and keep it running, I used systemd units.
Over time I have just replaced all that with node-red modules. You can source and sink to and from databases with easy to install custom nodes.
I use node-red dashboard to make basic GUIs to make basic meter, do little graphs, do entry and make buttons. Pretty crappy but quite easy if you watch a youtube vid or so.
For fancy graphs, like my fermenting system, I run the data straight into influx with a node and use grafana to look at it.
For all my own producers, like my mmwave radars, sound generators, vapour controller, I use C++ on the esp to generate the mqtt.
I used to use the arduino pubsubclient. It's pretty simple, but it's a bit of a joke compared to the increased stability of code done in esp-idf on the esp32.
For a beginner, I reckon hit GPT up with some instructions on how to get node-red installed and make a few flows. It'll be fun and games,