MariaDB addon

Installation Home Assistant OS
1

Hi all!

First of all I want to say that I love Homeassistant and Hassio so thank you all for developing this great software.

I migrated from my old home assistant installation (raspberry pi 3) to hassio and I experienced a couple of problems.
I’m wondering where I can find the mariaDB database file. When I login trough SSH I couldn’t find it anywhere. Also, the mysql command returns an error so it’s unusable. I would like to see the mariaDB database to check it’s size since hassio is becoming slower and i think the database is getting too big.

The error i get when putting in the mysql command:
“➜ / mysql homeassistant
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/run/mysqld/mysqld.sock’ (2 “No such file or directory”)”

Also, my apple tv stopped responding. It says: “Establishing connection to apple tv”. Before updating to hassio it worked perfectly. Authenticating after scanning works perfectly but it doesn’t work in my frontend.

I’m working with home assistant 0.52.1 at the moment.

Thank you all for your feedback!
Kind regards,
Filip

2

Nobody with the same problems?

3

Hi @Filip, i’m running hass.io only for testing on a Virtualbox VM (ubuntu 16.04).
If i’m on the ubuntu host, i can find the database in:
/usr/share/hassio/addons/data/core_mariadb/databases/

When i connect to the maria_db container with:
docker exec -i -t addon_core_mariadb /bin/sh, they are in:
/data/databases/
Don’t know if it’s the same for Hass.io image for raspberry.
Hope that helps!

1 Like
4

Hi @VDRainer!

Thank you very much for your help!
Couldn’t find it in: /usr/share/hassio/addons/data/core_mariadb/databases/.

I’m connecting to my raspberry pi 3 through ssh on an external computer.
When i go to /usr/share/ i can’t find the hassio directory.

These are the files in /usr/share/ on my installation.

. git-core nano terminfo
… icons ncat vim
apk man nmap zoneinfo
applications misc perl5 zsh
ca-certificates mysql tabset

Again, thanks for your input. :wink:
Filip

5

HI @Filip

did you ever find your MariaDB? Cant find a clue, so hoped you had luck before me…

Cheers,
Marius

6

Did anyone ever find the location for mariadb database files in Hassio on an RPi3? I have also looked everywhere and have come up empty. Just a bunch of threads of other people looking for the same, but no answers! I can’t believe this information is not available.

This is no help. https://mariadb.com/kb/en/library/default-data-directory-for-mariadb/

7

Agreed! Where the hell is this thing? Helpppppppppppppppp

8

Another vote for finding this stupid thing. Need to purge it since the purge command from Hassio is not working for me.

9

I’m not totally sure about the hassio architecture so I cannot explain the details, but if what you want/need is just to connect to MariaDB you can use the following command:

mysql -u USER -pPASSWORD -h IP DATABASE

Default values are:
mysql -u hass -pPASSWORD -h 192.168.1.XX homeassistant

That is enough for me to connect to mariaDB in the RPI3.

Hope it helps.

2 Likes
10

Never found it I’m sorry.

11

I use PHPMyAdmin to maintain my MariaDB database, this will allow yo uto remove data.

2 Likes
12

I’m not practical to use this SQL tools.
Can you explain me how to do something like the purge leaving data only for the last X days?
Also which tables need to be considered.

Thanks

13

When I have had DB problems in the past, I usually use PHPMyAdmin to backup and completely remove the old HA database, and make a new database entirely, then update my HA config to use the new DB and this fix works for me.

I’m sure there is an SQL query that will remove values older than a certain date, Im’ not an SQL wiz either.

1 Like
14

Old thread, but just to solve this for anyone coming here on Google

Access your hassio command line, and use the docker tools to get access to the inside of the mariadb docker container

docker exec -i -t addon_core_mariadb bash

Once inside, this directory holds the files for the mysql dbs themselves.
/data/databases

However, that’s kinda useless for anything except backing them up :woman_shrugging:

SO you’ll need to use mysql in the example above: MariaDB addon to do anything meaningful with that data.

5 Likes
15

Thank you for the solution. I stopped using mariadb however because it was not stable enough for me.

16

I think you can access the database with the following command in the web terminal:

mysql -u hass -pPASSWORD -h YOUR_IP homeassistant

It work for me but the only thing that doesn’t work is that I’m unable to create other user. I want to install TRACCAR addon and I need to create a user for traccar to be able to save his data.
In my mariadb setup I have:

{
  "databases": [
    "homeassistant",
    "traccar"
  ],
  "logins": [
    {
      "username": "hass",
      "host": "%",
      "password": "password"
    },
    {
      "username": "tracuser",
      "host": "%",
      "password": "password2"
    }
  ],
  "rights": [
    {
      "username": "hass",
      "host": "%",
      "database": "['homeassistant','traccar']",
      "grant": "ALL PRIVILEGES ON"
    },
    {
      "username": "tracuser",
      "host": "%",
      "database": "['homeassistant','traccar']",
      "grant": "ALL PRIVILEGES ON"
    }
  ]

The log say that the traccar database is created but in fact it issn’t.
any idea ??

17

@claudegel, did you ever figure out the issue about Traccar database?

18

No still not able to connect to the mariadb from traccar. Mariabd is telling me that the database traccar is created and the user tracuser is updated but traccar cannot connect. I had to do some cleanup in my hassio and will try again tomorrow.

19

The problem is on mariadb side. The traccar database is never created so traccar cannot log into mariadb

20

Ok I’ve looked directly inside the sd card on the file system and the traccar database is created but there is no table inside. So It’s the traccar addon that cannot login to mariadb and is unable to create the needed mysql tables