Persistence Non local MYSQL database

Tags: #<Tag:0x00007f38684d4318> #<Tag:0x00007f38684d41d8> #<Tag:0x00007f38684dbf78>

Hi, I have a non local MYSQL server

IP ending with .160 is my openhab box
IP ending with .180 is mySQL server

openhab.log says:

 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://xxx.xxx.xxx.180:3306/OpenHAB, user=openhab
java.sql.SQLException: Access denied for user 'openhab'@'xxx.xxx.xxx.160' (using password: YES)

mysql.cfg

url=jdbc:mysql://xxx.xxx.xxx.180:3306/OpenHAB


# the database user
user=openhab

# the database password
password=xxxxxxxxxxxxxxxxxxxxxxxxxx

I added in my cfg file user=openhab @xxx.xxx.xxx.180
that is not working.

Any idee?

for me it looks like you didn’t gain access to the user openhab on the server. Do you have phpMyAdmin setup? There you can configure access rights easily.

Mysql/MariaDB has a privilege system, that allows to restrict/gain access to each database for each user and even link it to dedicated hosts.

If you want to setup the permissions manually in the SQL console you might want to look at https://www.shellhacks.com/mysql-show-users-privileges-passwords/ or https://www.shellhacks.com/create-mysql-database-command-line/

Last but not least keep in mind IPv6, sometimes it tricks you if the sql server is reachable via IPv4 AND v6. - In this case I suspect it is not an issue because you give the server address in v4 (and not the host name.)

Thanks for the advise.
The user openhab (on the database server) has rights on the openhab database.

But the log says "Access denied for user ‘openhab’@‘xxx.xxx.xxx.160’ "
This refers to the user openhab on the openhab server , not the DB user openhab on the database server if i’m correct.

any idea what i’m doing wrong?

This is a MySQL problem, not an openHAB problem.

Scroll down to “Grant remote access to specific IPs or networks”.

Yes, something like

mysql> CREATE USER 'openhab'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'openhab'@'%' WITH GRANT OPTION;

Mysql permissions are host based. So you can allow a mysql user to login from some host but deny it from others. Allow from any IP as @elite recommends may be an option, but if your OH server has a static IP it would be more secure to restrict incoming database connections from all others.

Thanks for all the information!
Now it is still a bit of a puzzle to find out what i have to change in my setup
But I’m a lot wiser now, thanks