Hass IO and Maria DB

Installation Home Assistant OS
1

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

2

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)

3

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

4

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?

5

Hi, yes it does

6

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

7

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.

8

@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.

9

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

10

Did you solve this issue?

11

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
12

Could you be so kind to advice how?