MariaDB + RaspberryPi 3 + LibreELEC

Configuration
1

Currently I am using HA in Docker on RPi 3 with a stock database.
Now I want to move to MariaDB installed on the same RPi.
I do not need to migrate old data to MariaDB, just want to start a new DB.

What I did is:

  1. Install MariaDB:
docker pull tobi312/rpi-mariadb

The command taken from here.
2. Run MariaDB:

docker run --name ha-mariadb -v /storage/mariadb/db:/var/lib/mysql -v /etc/localtime:/etc/localtime:ro -p 3306:3306 -e MYSQL_ROOT_PASSWORD=hassiopass -d tobi312/rpi-mariadb
  1. Setup database:
docker exec -it ha-mariadb mysql -u root -p

Password: hassiopass
Then type:

CREATE DATABASE home_assistant;
CREATE USER ‘SOME_USER’ IDENTIFIED BY ‘SOME_PASSWORD’;
GRANT ALL PRIVILEGES ON home_assistant.* TO ‘SOME_USER’;
FLUSH PRIVILEGES;
exit
  1. Add a line to “recorder.yaml”:
db_url: ‘mysql://SOME_USER:SOME_PASSWORD@localhost:3306/home_assistant?charset=utf8’

And I get this error:

Error during connection setup to mysql://SOME_USER:SOME_PASSWORD@localhost:3306/home_assistant?charset=utf8: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to local MySQL server through socket '/run/mysqld/mysqld.sock' (2)") (Background on this error at: http://sqlalche.me/e/13/e3q8) (retrying in 3 seconds)

The history is not available, this error is displayed:
image

I am noob in databases.
Probably I installed MariaDB in a wrong way.
Can anyone help me?

2

I’m not using Docker. I have a plain installation of HA core on my Raspberry 3 with Maria DB working fine. These are the steps I followed:

Install MariaDB and create a database for HA

CREATE DATABASE homeassistant;

GRANT ALL PRIVILEGES ON homeassistant.* TO 'homeassistant'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION;

sudo apt-get install libmariadbclient-dev
sudo apt-get install libmariadb-dev-compat

sudo -u homeassistant -H -s
cd /srv/homeassistant
source bin/activate

pip3 install mysqlclient

HA is connecting to the db using Unix Socket

recorder:
  db_url: mysql://@localhost/homeassistant?unix_socket=/var/run/mysqld/mysqld.sock&charset=utf8mb4

This is probably not working if you are using Docker or it probably needs to be adaped.

3

So this is another story.

This is not for Libreelec.

I tried to learn this info:

Have to admit - people who wrote this article really do know the subject - but cannot write about it in a clear manner.
I am for 25 years in SW development, but never worked with databases. And I understand what is a clear algorithm. Those “instructions” provided in the article - seem to be a pile of info, not an instruction at all.

4

Managed to install MariaDB & connect to HA.

Intro:
There is RPi 3 with LibreELEC (Kodi installed).
HA is installed in the Docker container.
MariaDB is to be installed in the Docker container too.

What I did is:
1 . Install MariaDB:

docker pull tobi312/rpi-mariadb

2 . Run MariaDB (invent & use your own SOME_ROOT_PASSWORD):

docker run --name ha-mariadb -v /storage/mariadb:/var/lib/mysql -v /etc/localtime:/etc/localtime:ro -p 3306:3306 -e MYSQL_ROOT_PASSWORD=SOME_ROOT_PASSWORD -d tobi312/rpi-mariadb

Note:
In my setup all user files are located in the "/storage" folder; the HA config also located there in the "/storage/HA" folder; the "/storage" folder was created by Kodi setup. The installed MariaDB has all db files in the "/storage/mariadb" folder.

3 . Setup database:

docker exec -it ha-mariadb mysql -u root -p

Password: SOME_ROOT_PASSWORD
Then type (invent & use your own SOME_USER & SOME_PASSWORD):

CREATE DATABASE home_assistant;
CREATE USER 'SOME_USER' IDENTIFIED BY 'SOME_PASSWORD';
GRANT ALL PRIVILEGES ON home_assistant.* TO 'SOME_USER';
FLUSH PRIVILEGES;
exit

4 . Add a line to “recorder.yaml” (use your own IP_ADDRESS, SOME_USER & SOME_PASSWORD):

db_url: mysql://SOME_USER:SOME_PASSWORD@IP_ADDRESS/home_assistant?unix_socket=/var/run/mysqld/mysqld.sock&charset=utf8mb4

I did NOT install any dependencies like “libmariadbclient-dev” (which was poorly described here).

1 Like