Hass IO and Maria DB

Hello, I wish to run HASS IO on a Raspberry PI, and have the Maria DB installed on another Linux Server. I have installed Maria DB on Ubuntu Server 16.04 and the Hass IO on the PI with no problems, howver , I can’t seem to figure out the settings to get the recorder to use this Maria DB. Am I correct in thinking that on first use, the database is created? My settings for Maria DB in the Add On section is

{
“databases”: [
“homeassistant”
],
“logins”: [
{
“username”: “My User”,
“host”: “%”,
“password”: “My Password”
}
],
“rights”: [
{
“username”: “hass”,
“host”: “%”,
“database”: “homeassistant”,
“grant”: “ALL PRIVILEGES ON”
}
]
}

My settings in the YAML file are

recorder:
purge_interval: 1
purge_keep_days: 30
db_url: mysql://hass:[email protected]:3306/homeassistant?charset=utf8

but the database is on another server, any ideas welcome, I keep getting the following error in the log file

2018-04-03 00:08:20 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (_mysql_exceptions.OperationalError) (2003, ‘Can’t connect to MySQL server on ‘192.168.1.29’ (111 “Connection refused”)’) (Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)

I have checked and the Maria DB is up, and nor firewall is turned on

Any thoughts welcome

Thanks

Did you add permissions like this?

GRANT ALL PRIVILEGES ON *.* TO 'hass'@'%' IDENTIFIED BY 'my-new-password' WITH GRANT OPTION;

Also, have you tried using telnet from your HA host (or any host on your local LAN) to port 3306 can be reached? (Only confirms port is listening)

Hi, yes I have granted the privs as above, but can’t use telnet when SSH’d into the Hass IO server as it says command not found

Hass.io probably doesn’t have telnet (I run HA as manual install). On the database server if you do

netstat -anp | grep 3306

Does it show the MariaDB server listening?

Hi, yes it does

What version of MariaDB are you running? What “flavor” of Linux is running on the database server box?

Hey, I just set this up on my network as a test. I couldnt get it to work until I did this.

GRANT ALL PRIVILEGES ON *.* TO hass@'192.168.1.185' IDENTIFIED BY 'pass1234' WITH GRANT OPTION;

I explicitly allowed my HA host by its IP. The wildcard % did NOT work. Try this on your database server and see if it works.

@parkyerbike did you set up the MariaDB addon on hassio? That one will start a MariaDB instance on the RPi, I think it’s not what you are trying to do.
To use MariaDB on an external server you only need to change the configuration file, don’t need to install.

Moreover, if I remember the procedure you have to create yourself the empty database, HA will create the tables in it.

I use HA not Hass.io, but I just did this as a test and you do indeed need to create the empty database on the MariaDB server

Did you solve this issue?

I did, only just though. I was thinking about it the wrong way, I don’t need to install mariadb on the hassio instance, I just added the recorder settings and created the database manually and all worked like a dream

1 Like

Could you be so kind to advice how?