Does anyone access the OpenHAB server MySQL database(s) from a Windows machine?
I have OpenHAB2 running on a Rpi3 and a MySQL database collecting readings from an Aeon Home energy monitor (HEM gen1). I would like to periodically run a query from my windows machine to use in Excel, but after numerous tries with various passwords, host names and other parameters using MySQL for Excel, I keep getting denied access. If someone could provide a working set-up with generic terms, it would be greatly appreciated.
I did brute force a solution by saving my database while on Putty to a directory that is open in Samba and then recreating it on my windows machine, but that is time consuming and would not have the latest data.
I’m not quite sure if you are talking about the MySQL DB OH uses for persistence or some other DB another program created.
Either way, assuming you do have a valid username and password your problem most likely results from the fact that when doing a standard install your user will only be allowed access from “localhost”, the machine the DB server is running on( true for all install Howtos concerning OH/mySQL i have seen here so far).
So first i would check that and grant the user the right to connect to the DB from remote machines.
HTH,
-OLI
It is the MySQL DB that OH uses for persistence.
Good suggestion about granting user remote access. I believe I have a standard installation, since I’m a beginner, I just followed the directions.
Just to close the loop I solved this. There are two general threads I found in my internet searches to do this. First is to alter (various options, like 0.0.0.0) (or comment out) the line “bind-address = 127.0.0.1” in the MySQL configuration file. I did this, it worked to get remote access from my windows machine, but busted the HAB persistence. I then found a better idea. Leave the MySQL configuration alone, but use PuTTY to create port forwarding for 3306 to the “localhost” on the RPi. The section is under connection/SSH/Tunnel “add new forwarded port”. I did this and everything works fine. You do need to keep the PuTTy session open to run queries.
Glad you got it working.
Of course there are many ways to skin a cat, but why didn’t you just add a user with access from your local subnet(taking 192.168.1.x and DB Name OpenHAB as example):
CREATE USER ‘exceluser’@‘192.168.1.%’ IDENTIFIED BY ‘yourpassword’;
GRANT SELECT ON OpenHAB.* TO ‘exceluser’@‘192.168.1.%’;
FLUSH PRIVILEGES;
Note this would give only read access to this user.
No need to open a putty session each time.
I did that, but it didn’t work. Maybe I did something wrong, but from what I read the “bind-address = 127.0.0.1” will prevent that from working. Anyway I’m good now.