installing 09 cMqtt2Mysql

It's optional step. But! if you want to have access to historical status of topics in mqtt layer for example to build grafana graphs or some different use. You will need it. It's written in C language. It's really efficient with resources. Data is stored in two tables topics and msgs. It's normalized data structure. So msgs have idTopic to bind with topic of origin. Structure of tables can by found in header of main.c in directory of program. You need to execute it in your data base. You need to have mysql or different sqllike data base running (mariadb). To make it from local machine where you have database service running execute commands. Assuming that you have settings same as this wiki is showing.

$ mysql -u 'ykpu' -p 'pimpimpampam' -D 'oiysh'

then past

CREATE TABLEtopics......;

to verify enter

mysql> show tables;

you will see topics and msgs table

to verify even more

mysql> desc msgs;

you will get structure of table. You can make it also to topics table.

I'm proposing to add it as it's to home directory. So in my case I have it in

/data/data/com.termux/files/home/cMqtt2Mysql

or

$PREFIX/cMqtt2Mysql

it's a directory with all stuff. You need to set up config variables in main.c file to your use. Open file and read header. You will find in it a create table query for sql. File by default is set up for my case of use. Your ip address can be different, ports, user or passwords... check it and change it.

building it is done by executing:

$ cd $PREFIX/cMqtt2Mysql

$ ./makeIt.sh

you can run it by command

testing cMqtt2Mysql

testing can by done by command

$ $PREFIX/cMqtt2Mysql/cMqtt2Mysql

for me I'm getting

`2022-02-09_14:52:50.26190 hello it's a c mqtt 2 mysql demon!

2022-02-09_14:52:50.26318 sql - connect to mysql

2022-02-09_14:52:50.26318 sql - connected :)

2022-02-09_14:52:50.26319 sql - get topics count

2022-02-09_14:52:50.26320 one line query one match [109]

2022-02-09_14:52:50.26320 one line query no match [0]

2022-02-09_14:52:50.26321 mqtt - init ...

2022-02-09_14:52:50.26321 iter...0

2022-02-09_14:52:50.26321 connect

2022-02-09_14:52:50.26322 subscribed .... ` NICE

Makeing it as a service. Wee need to add this to services. Log in to termux or use ssh to log in. Commands:

$ cd $PREFIX/var/service/

$ mkdir cMqtt2Mysql

$ mkdir -p ./cMqtt2Mysql/log

$ ln -sf $PREFIX/share/termux-services/svlogger $PREFIX/var/service/cMqtt2Mysql/log/run

$ nano ./cMqtt2Mysql/run

in file we need to add

`#!/data/data/com.termux/files/usr/bin/sh

exec /data/data/com.termux/files/home/cMqtt2Mysql/cMqtt2Mysql 2>&1 `

to save CTRL-x and y and enter to confirm. Commands

$ chmod +x ./cMqtt2Mysql/run

to make it executable :)

starting it as service

it should work out of the box we need to set it up to run by default on start. To do it enter command in termux terminal

_$ sv up cMqtt2Mysql

$ sv-enable cMqtt2Mysql

restar termux

DONE