back-up domoticz.db to a mysq db

[JoeneH]

Hi all,

I have been reading some of the posts on this and other forums, but i haven't figured it all out yet.

I am using Domoticz on a synology nas (Jadahl-package) and I am now looking to backup the sensor data to another database. I like to be able to make graphs on 5 min interval data for long periods, lets say one year.
For this I need to find some way to automatically put the data from domoticz into another database. I would also like to keep information on my nas and not send it to the cloud (after all I have a nas).

I have not been able to install influxdb on my NAS, so that solution is not an option anymore.

For now I have installed MariaDB10 and phpmyadmin to create and read mysql databases and now I need to find a simple way of adding data from the domoticz.db to a database in mariaDB.

And here my knowledge runs to an end since I have almost no experience with php, linux and other coding stuff.
Is it possible to import specific data from the domoticz.db into another mysql db?
I haven't been able to connect to and see the data in the domoticz.db file yet using phpmyadmin. Is this even possible? I don't know the commands yet for connecting to a database which is not part of the MariaDB10 server.

I have been able to copy the domoticz.db file to a shared folder on my nas and then view it with DB Browser (for Windows), so at least I understand the structure of the database (and now know which table, columns and DevicerowID I am looking for).

So any newbie help would be very welcome.

Edit:
Reading other posts it seems to suggest a two-step process:
1 Read the values from domoticz.db and 'show' them on a (temporary) website
2 Read the values from the website and add the to the mysql databse
Hence all the talk about web-servers and POST and other things I do not fully understand. Whys does this two-step process seems necessary?

Is it possible to do this in 1 single step:
retrieve (copy) the values I need from the domoticz.db and insert them into the mysql database? Or is the problem the fact that the domoticz.db file is not on the the same server as the mysql server?

[waaren]

Did you already find

this method ?

[JoeneH]

I am not sure I have seen that one, probably missed it, because it keeps things in sqlite3.
And I was just getting to know MariaDB en sql...

Besides, sqlite3 doesn't seem to work nicely on my nas.
Using Putty (ssh) to connect to the nas from a window machine the sqlite3 command does work, however creating even an empty database by typing sqlite3 test.db in a shared folder doesn't seem to work. After quiting sqlite3 no test.db file is found ...

That said, I am willing to try your idea, but I do not yet understand every step you specified.

Edit:
sqlite3 commands are working perfectly. For some reason created databases did not show up with ls, now they do...

[Egregius]

Have a look at my pass2php script. I also store temperature data in mysql.

[JoeneH]

I have been looking at pass2php, but that was a bit much to swallow in one time
There is a lot happening in that code and I am new to coding and stuff like this. And I have to copy files to a domoticz folder and I have no idea what those files do. I just like to understand what I am doing to my own nas, because it is safekeeping a nice archive of personal and important files.

Do you have some newbie explanation somewhere on this pass2php?

[Egregius]

There's no real explanation for pass2php. I prefer the users to search, trial and error to use it so they understand what's going on.

Depending on what data you want to store you could go different ways:
a) Use a hourly cron to grab the history of devices and put data in mysql.
b) Use a cron every 5 minutes to grab statuses of multiple devices and put data in mysql.
c) Use pass2php, store it whenever and however you'd like and you can also use it's advanced scripting possibilities.
Of course, I would go for c. If you also like to go that way I will be more than happy to help you get started in http://www.domoticz.com/forum/viewtopic ... 64&t=12343

[JoeneH]

Suppose I want to go for option a?

Is it possible to retrieve data directly from domoticz.db and insert it into a mariadb database?
Using something like
INSERT INTO temphistory (temperature, humidity, datetime)
SELECT Temperature, Humidity, Date
FROM domoticz.Temperature WHERE DeviceRowID = 2;

and then something to only select new entry's so no double Datetime.

I think this will not work because the domotizs database in not on the mariaDB server where the history db would be.
Is that correct?

[Egregius]

You can query the json api for the data.
I'll put something together tomorrow.

[gordonb3]

The main problem with replicating data from one datasource to another is that you need to be constantly aware of possible changes to the source data format. As such it is usually better not to attempt to replicate the raw database but to resort to some higher level export routine for fetching changed data and then import that into the slave database. The method suggested by Egregius therefore seems best and I'm eager to see his code example.

[Egregius]

I posted the code on my site, I have syntax highlighting there

https://egregius.be/2018/store-temperat ... -analyses/

[JoeneH]

Thank you very much.

I am going to test this and then put my comments in this thread.
I understand some parts of it, and after a successful run hopefully more.

A few (newbie) questions at front:
I suppose I have to insert <?php into the first line?
There is no define for the sqlserver:port?
Line 61 and 79 contain the Dutch word 'aantal', is this correct?
where do I put this file?
what do I type to test/start this program?
And what do I type to stop this program?

[Egregius]

Yes, indeed forgot the php shebang, also updated the word 'aantal' to 'count' in the script.
If you have mysqli access in php-cli the place of the file doesn't mather, you can execute it with:

Code: Select all

php /path/to/temps2sql.php

If you don't have mysqli access in php-cli you have to run it thru a webserver. Then you can execute it with this:

Code: Select all

curl -s "http://127.0.0.1/temps2sql.php"

The script is a one time run, should finish in less than a second and will stop automatically.
Execute it by cron every hour, or whatever you like.

[JoeneH]

using the first option I got the following result

PHP Warning: Constants may only evaluate to scalar values in /volume1/blablabla/temphist2sql.php on line 11
Warning: Constants may only evaluate to scalar values in /volume1/blablabla/temphist2sql.php on line 11
PHP Fatal error: Class 'mysqli' not found in /volume1/blablabla/temphist2sql.php on line 58
Fatal error: Class 'mysqli' not found in /volume1/blablabla/temphist2sql.php on line 58

[Egregius]

Are you runnin php >=7?
And do you have the mysqli extension enabled?

[JoeneH]

I have installed the php7 package on my Synpology. And I am using Putty to access the NAS through my windows desktop, logging in as Admin.
How do I check if the mysqli extension is enabled?

The webserver option is something I am not familiar with. Webstation is automatically installed when installing phpmyadmin, but I haven't configured Webstation yet.

[Egregius]

I think you can set options in the webstation app.
Your php file should be in the webroot and open the file with the curl command.

[JoeneH]

In webstation the back-end server is nginx and php is php 7, in php-settings for php7 the extension mysqli is checked.
Did a reboot on the NAS.

Using the php command I am getting the same error message.

Using curl I am getting the following message:
Unable to connect to database [connection refused]

In the php file I had to add the port number to the IP address (sqlserver), and run the curl.

It works

Well, almost.
I now have two tables in my database: temp_day and temp_month
both tables have just one column: stamp, which is empty

the column is created with the following:
temp_day CREATE TABLE `temp_day` (
`stamp` datetime NOT NULL,
PRIMARY KEY (`stamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Maybe no connection to the domoticz database is made to fetch other column names and values?
There are no error messages after the curl command.

[Egregius]

Did you set the domoticz ip, port and idx's?
Or do you have authentication enabled in domoticz? If so, the url must be changed, or you must add the ip address to the exclusion list.

[JoeneH]

I did set the domoticz ip, port and idx's.
switching the port between 8080 and 8084 (which is the port number I see when connecting to Domoticz through a browser) doesn't seem to do anything.

Yes, I use authentication. However I suspect a glitch in Domoticz. I have added the ip addresses of my desktop and laptop to the exclusion list, but I still have to login sometimes. Even using a * for the last digits doesn't help.
What should be changed to which url if I use authentication?

Furthermore, for some reason the script is no longer creating tables. Yesterday I often removed the tables after test runs and it always created new tables (with just the stamp column).
Now, for some reason it doesn't do that any more.

[JoeneH]

It al works

I now used the curl command with the http://ip
I believe yesterday I used curl with /volume1/web so that was what I did this morning.

I have added the ip-range to the exclusion list in Domoticz, so that probably helped as well.

Now on to further testing:
see if new values are added
see if the authentication is domoticz really was a problem