Home Assistant Core on UnRaid and MariaDB configuration

1

After reading the information about setting up MariaDB I am still confused.
Currently I am running Home Assistant Core on UnRaid with the sqlite db.

I want to convert it to use the MariaDB I have setup on UnRaid.

I understand that I need to change the setup in recorder but am not sure exactly what needs to be done.

When I was using Home Assistant with the supervisor and had MariaDB setup I used the following.

mysql://hassio_admin:!mypassword!%40@core-mariadb/homeassistant

Where my confusion lies is in two parts. The first is do I need to create a database and tables in the UnRaid MariaDB. If so what specifically needs to be done.

The second part is what should the mysql statement look like if my MariaDB on UnRaid is at 192.168.1.90:3306

Thanks for any help.

2

I run HAOS on a linux vm and pass through my USB zwave/zigbee stick. It works flawlessly.

I realize I didn’t answer the question, just saying from one Unraid user to another, my setup has worked out REALLY well.

3

For 1), you create the DB (only the DB, the tables will be created by HA), and user / password directly on mariadb, than GRANT all rights on that DB to that user

For 2), the url is

mysql://<user>:<password>@<db_host>/<db_name>
1 Like
4

Thank you so much for this.
It seems easier then I thought, I will give it a go today and see what happens.
Appreciate it.

5

In the UnRaid MariaDB I created a database called hacore, with a username of homeassistant and a pasword of 123456789, as well as granting all privileges, just to see if I could make this work.

I then created in the recorder.yaml file the following line.

  db_url: mysql://homeassistant:[email protected]:3306/hacore

This does not seem to work. Do I need to remove the port# from the db host? or as listed here
https://www.home-assistant.io/integrations/recorder/
Do I need for the full line to look like this

mysql://user:password@SERVER_IP/DB_NAME?charset=utf8mb4

using the charset information.

Thank you

6

Please disregard my other posts, I seem to have it working.
At least it created 8 databases. I will have to monitor it.
I do not know what was wrong but after a dozen attempts, reboots, clearing of cache it started to work.
I then created a stronger db name and password and all seems well.
Thank you for the “push” on how to do it.

7

Hi @carltonb , did you migrate all your data across when you did this or start fresh? If you did migrate data, would love to know how you did (or if HA did it for you, particularly sensor history data).
Thanks!

8

Yes I did get it workingand it was easier then I thought.

First setup mariadb on unraid and made sure that was working.
I then went into my secrets file and created the username and password.

mariadb_url: "mysql://DBUSERNAME:[email protected]:3306/hacore"

mariadb_url: "mysql://carltonb:[email protected]:3306/hacore"

I then went into the recoder.yaml file and added the following.

recorder:
  # The number of days of history to keep in the recorder database
  purge_keep_days: 3

# How often (in days) the purge task runs
# UPDATE v0.109 - to be removed in future Home Assistant version - disabled!
#  purge_interval: 1

  db_url: !secret mariadb_url

I then restarted HA and lo and behoold after about 5 minutes mariadb was AUTOMATICALLY populated with my data.

After a few days I went back and removed the homeassistant db file in config.
Everything works well.

9

Thanks. I ended up migrating all my sqlite3 data across as I had sensor data from energy etc that I didn’t want to lose.

For anyone else looking, I used this:

Steps:

  • create a container / set up mariadb
  • create a new schema for ha
  • run the sqlite3-to-mysql to convert the sqlite3 db to mysql and set it to the mariadb db
  • follow above steps from @carltonb to update mariadb for recorder

G

1 Like
10

So I have a simmilar situation with my unraid install.
Do you have your HA container running in a custom docker network or is it host/bridge mode?

I have my unraid as 192.168.1.10
HA is 192.168.1.2
my Mariadb is 192.168.1.10:3306 (customer docker network)

No matter what I do, I can’t get HA to connect to the mariadb container.
I’m losing my mind as everything seems correct but it’s clearly struggling.

11

My unraid server is at 192.168.1.90.
I am using a custom networkfor both HA Core and Mariadb (192.168.1.90:3306)
My HA Core is at 192.168.1.90, though that info does not show up on the docker unraid dashboard.
In my circumstance it works fine.

12

Thanks for the feedback, out of curiousity, what MariaDB image are you using?
I couldn’t get it working using the linuxserver, but worked fine using the eoffical image.

In case it helps the next fool who refuses to use another distro and wants to to overly complicate their lives here is how I got the linuxserver container working :slight_smile:

I was able to get MariaDB (linuxserver) working on the docker network and HA working in bridge mode.

I had to go to settings - docker - stop docker - then enable

Allows direct communication between the host and containers using a custom macvlan network. By default this is disabled.

Enabling everything, HA was now able to connect to the container

MariaDB-Official didn’t need this change so it probably can be fixed another way

13

I am using the official linuxserver version.
I had no problems getting it up and running for my other software I use on unraid.
My original problem was I did not understand the Home Assistant instructions to get it working.
After the initial help in this thread it was “easy” for me.