Recently I decided to transfer my Home Assistant database to MariaDB instead of the default SQLite db. Unfortunately, I spent way too much time trying to get the setup going, as I was running into errors all the time. This is a quick guide on how I got my setup up and running.
Setting up container
I run everything to do with my HA in docker, and will be doing the same with the database. For this I use
version: '2' services: mariadb: image: mariadb/server:10.5 environment: - PUID=1000 - PGID=1000 - TZ=Europe/Stockholm - MYSQL_ROOT_PASSWORD='secret_root_password' volumes: - ./mariadb/db:/var/lib/mysql # - ./mariadb/my.cnf:/etc/mysql/my.cnf:ro ports: - 3306:3306 logging: driver: "json-file" options: max-size: "200k" max-file: "10"
We start with the second volume mount commented out, as We will be pulling the default file at
/etc/mysql/my.cnf for minor modifications.
After the first start, we can remove the following row:
Enable connections form all IPs
Pull the file at
/etc/mysql/my.cnf and the following rows:
[mysqld] skip-networking=0 skip-bind-address
Save the file, and remove the comment from the second mount.
Enable access from all IPs for the root user
Execute shell into the container, and run the following commands.
Enter the local mysql shell using the command
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'super_secret_password' WITH GRANT OPTION; FLUSH PRIVILEGES; CREATE DATABASE ha; CREATE USER 'ha_user' IDENTIFIED BY 'secret_password'; GRANT ALL PRIVILEGES ON ha.* TO 'ha_user'@'localhost' IDENTIFIED BY 'secret_password'; FLUSH PRIVILEGES; exit
In the Home Assistant configuration file, add the following:
recorder: db_url: mysql://ha_user:secret_password@server_name:3306/ha?charset=utf8