Synology NAS + MySQL

Configuration
1

I am trying to setup a mysql database that is hosted on my Synology NAS but I can’t get it to work.

I have installed everything on the NAS, webservice, MariaDB and PHP myAdmin, I have created a user called hass with password hass and a database called hass and granted ALL PRIVILEGES

nas

I have installed every thing I can find on the NAS, even the hassbian script for MariaDB

My NAS is running on internal IP 10.0.1.2 and my rPi on 10.0.1.4 (installed with hassbian)

I have added this to my config

recorder:
  purge_interval: 2
  purge_keep_days: 5
  db_url: mysql://hass:[email protected]/hass#?charset=utf8

But It will still not work for me, I get this error in

Error during connection setup: (_mysql_exceptions.OperationalError) (2003, 'Can\'t connect to MySQL server on \'10.0.1.2\' (111 "Connection refused")') (Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)

Can someone help me with what I am doing wrong?

2

Is firewall blocking access?
MySQL default port of access is 3306 so ensure this is open in DSM firewall.

Also did you configure MySQL for access from remote machines?

3

I haven’t taught of then firewall, I will check that.

I didn’t notice any options to acces the mysql from external access, where did you find that setting?

4

If you have a user account allowing any user from localhost to connect is present, it will prevent other users from connecting if the host part of their account allows a connection from any (%) host.

Try to set the hostname for user hass to 10.0.1.4 or 10.0.1.% .

5

@RogueCrow I checkad and I have DSM firewall inactivated, I forwarded port 3306 to the NAS ip in my touter without any success

@namadori tried to change the hostname, added both without any success.

nas1

I still get the error

Error during connection setup: (_mysql_exceptions.OperationalError) (2003, 'Can\'t connect to MySQL server on \'10.0.1.2\' (111 "Connection refused")') (Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)

6

It looks like my MariaDB are using port 3307. Is the standard port 3306 would that be a problem. But how do I change the port on MariaDB?

7

On my Synology MariaDB 10 installed on port 3307, they decide to leave 3306 for MariaDB 5. Can you check that?

image

EDIT: To connect to port 3307 just change the connection string in HA to mysql://hass:[email protected]:3307/hass

4 Likes
8

@namadori Just found that out to, but how do I then get HomeAssistant to look at port 3307?

9

It is up and running, changing the port and was the last pice that was missing! Thanks @namadori , I rely appreciate it!

10

Good to know you fixed it.

If your Synology supports Docker and can handle it (RAM) I suggest to install both there (MariaDB, HomeAssistant).

I am using those 2 containers and some others (have 8GB of RAM) and it has many advantages to running on RPi).

Also not sure what you did with port forwarding but please remove it if you do not need it (hopefully you didn’t expose it to Internet).

11

Yes I am super happy I works now, and off course I removed the port forwarding, I just tested if that did any different but It didn’t.

I am afraid my NAS I to old, its a DS412J from 2012 with a whopping 256mb of memory :see_no_evil: So docker on that one is a no go

12

Thanks a lot. That works for me, too. I was trying this without luck. Changing the port makes the trick

1 Like
13

I have made a a note in the instructions to add port 3307 if you use Maria DB10 and created a pull request.

2 Likes