Setting up a MariaDB Container in Docker • Arnav Jain

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 docker-compose

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:

- MYSQL_ROOT_PASSWORD='secret_root_password'

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 mysql

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

Setup recorder

In the Home Assistant configuration file, add the following:

recorder:
  db_url: mysql://ha_user:secret_password@server_name:3306/ha?charset=utf8