MQTT and raspberry charts in a web page step3

Use SQL

• Install phpmyadmin which will be handy

1 - Insert to mySQL

• python script save_to_db.py
• gets the last line of the mqttstats.csv
• uploads data

2 - run in the crontab every 5 minutes:

• */5 * * * * /home/pi/Desktop/tempmon.sh >> /home/pi/crontab.log 2>&1
• */5 * * * * /home/pi/Desktop/mqttmon.sh >> /home/pi/crontab.log 2>&1
• 1 0 * * * /home/pi/Desktop/trimcsv.sh
• */5 * * * * python3 /var/www/save_to_db.py >> /home/pi/crontab.log 2>&1

3 - clean up of the database

in mySQL:
SET GLOBAL event_scheduler = ON;
CREATE EVENT cleaning ON SCHEDULE EVERY 1 DAY ENABLE
DO
DELETE FROM sensor
WHERE date < CURRENT_TIMESTAMP - INTERVAL 5 DAY;

• portanto, deverei ter cerca de 60/5 * 24 *5 = 1440 linhas na base de dados até um máximo de 1728

Check data with:

mysql -u root -p MariaDB [(none)]> use mqtt

MariaDB [mqtt]> select * from sensor;