Home Assistant Container Part 3: MariaDB and InfluxDB

Intro ¶

We’re continuing where we left off after Part 2: Home Assistant Container.

In this Part 3, we’ll set up the MariaDB and InfluxDB containers which we’ll use to store the data from the Home Assistant recorder and history.

Install MariaDB and InfluxDB containers ¶

Continuing in the same trend as the previous two posts, we start of with the docker-compose.yaml config for our new containers.

The configuration for these containers looks as follows:

version: '3.0'

services:
  [...]
  homeassistant:
    [...]
  
  mariadb:
    container_name: mariadb
    image: mariadb
    restart: unless-stopped
    ports:
      - "3306:3306/tcp"
    environment:
      - TZ=Europe/Brussels
      - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
      - MYSQL_DATABASE=${MYSQL_HA_DATABASE}
      - MYSQL_USER=${MYSQL_HA_USER}
      - MYSQL_PASSWORD=${MYSQL_HA_PASSWORD}
    volumes:
      - /opt/mariadb/data:/var/lib/mysql
      - /opt/mariadb/config/:/etc/mysql/conf.d
  
  influxdb:
    container_name: influxdb
    image: influxdb
    restart: unless-stopped
    ports:
      - "8086:8086/tcp"   # So we can access the WebUI
    environment:
      - TZ=Europe/Brussels
      - DOCKER_INFLUXDB_INIT_MODE=setup
      - DOCKER_INFLUXDB_INIT_USERNAME=${INFLUXDB_USER}
      - DOCKER_INFLUXDB_INIT_PASSWORD=${INFLUXDB_PASSWORD}
      - DOCKER_INFLUXDB_INIT_ORG=${INFLUXDB_ORG}
      - DOCKER_INFLUXDB_INIT_BUCKET=${INFLUXDB_BUCKET}
    volumes:
      - /opt/influxdb/data:/var/lib/influxdb2
      - /opt/influxdb/config/:/etc/influxdb2
    ulimits:
      nofile:
        soft: 32768
        hard: 32768

Environment variables ¶

You’ll notice we added some environment variables in the configuration, such as ${MYSQL_ROOT_PASSWORD}.

These placeholders ensure we don’t put sensitive data in our docker-compose.yaml. This is especially useful for when we want to back-up and/or distribute our Docker config in a Version Control System (VCS) like GitHub.

To provide a value for these environment variable, create a .env file in the same directory as the docker-compose.yaml file, i.e. /opt.

sudo nano .env
# Add secrets, see below

# Also grant our non-admin account to this file
#  so we don't need sudo to edit in the future
sudo chown root:docker .env
sudo chmod g+w .env

The .env file would look like this. Make any modifications (like passwords!) here.

MYSQL_ROOT_PASSWORD=mariadbrootpassword
MYSQL_HA_DATABASE=ha_db
MYSQL_HA_USER=homeassistant
MYSQL_HA_PASSWORD=mariadbhapassword

INFLUXDB_USER=homeassistant
INFLUXDB_PASSWORD=influxhapassword
INFLUXDB_ORG=sequr
INFLUXDB_BUCKET=homeassistant

If you run the config check before creating the .env file, you’ll also be warned about this.

docker-compose -f docker-compose.yaml config

WARN[0000] The "MYSQL_HA_DATABASE" variable is not set. Defaulting to a blank string.
WARN[0000] The "MYSQL_HA_USER" variable is not set. Defaulting to a blank string.
WARN[0000] The "MYSQL_HA_PASSWORD" variable is not set. Defaulting to a blank string.
WARN[0000] The "INFLUXDB_USER" variable is not set. Defaulting to a blank string.
WARN[0000] The "INFLUXDB_PASSWORD" variable is not set. Defaulting to a blank string.
WARN[0000] The "INFLUXDB_ORG" variable is not set. Defaulting to a blank string.
WARN[0000] The "INFLUXDB_BUCKET" variable is not set. Defaulting to a blank string.

Dependencies ¶

In case we reboot our Docker system, we also want the Home Assistant container to wait for the MariaDB and InfluxDB container to be online before starting. This is to ensure the connection from HA to these systems doesn’t fail and no data is lost.

So add the following to the config entry to the homeassistant container:

[...]
  homeassistant:
    [...]
    depends_on:
      - mariadb
      - influxdb

Initial run ¶

You can now run docker-compose up -d to install the MariaDB and InfluxDB containers.

You’ll also notice the Home Assistant container is recreated as its config has changed.

InfluxDB Token ¶

Now that our InfluxDB container is running we’ll open the WebUI to grab a token. We’ll need this token later to grant Home Assistant access to our newly created bucket.

Open a browser and go to http://<ip.of.our.box>:8086 and login using the credentials you use in the .env file.

Go to the API Tokens menu and generate a new read/write token specifically for our Home Assistant bucket.

Click on the name of the token and a window will appear showing you the token. We’ll need this token in a later stage.

Home Assistant configuration ¶

With the containers up and running the first half of the job is done. We still need to tell Home Assistant about these new database.

For this, we will be adding our first Home Assistant configurations.

EDIT 2022-09-08
We’ll be editing our config via the terminal. If you prefer to do this in an online editor, have a look at Part 6 and then come back here ;)

cd /opt/homeassistant/config/
sudo nano configuration.yaml

You’ll see some config is already present.

# Loads default set of integrations. Do not remove.
default_config:

# Text to speech
tts:
  - platform: google_translate

automation: !include automations.yaml
script: !include scripts.yaml
scene: !include scenes.yaml

Add the following config below it:

recorder:
  db_url: !secret mariadb
  purge_keep_days: 10   # default

history:

influxdb:
  api_version: 2
  ssl: false
  host: !secret influxdb_host
  port: 8086
  token: !secret influxdb_token
  organization: !secret influx_org
  bucket: homeassistant
  tags:
    source: HomeAssistant
  tags_attributes:
    - friendly_name
  default_measurement: units
  ignore_attributes:
    - icon
  exclude:    # Customise to fit your needs
    entities:
      - zone.home
    domains:
      - persistent_notification
      - person

More secrets ¶

You’ll have noticed I made some secret references in the config above, like !secret mariadb.

Home Assistant also allows you to hide secrets from the configuration.yaml file so you can safely share it. Just like with the environment variables we used in our docker-compose.yaml, these are references to a value stored elsewhere. In this case, these are key-value pairs in a file named secrets.yaml located in the same folder as the configuration.yaml.

So let’s create this file and add our secrets to it.

cd /opt/homeassistant/config
sudo nano secrets.yaml

Notice this file already exists and contains an example entry:

# Use this file to store secrets like usernames and passwords.
# Learn more at https://www.home-assistant.io/docs/configuration/secrets/
some_password: welcome

Remove this example, and add your own secrets. Make sure you match the configuration of the .env file.

mariadb: "mysql://homeassistant:mariadbhapassword@<ip.of.our.box>:3306/ha_db?charset=utf8mb4"

influxdb_host: "<ip.of.our.box>"
influxdb_token: "influxdbtoken"
influx_org: "sequr"

Since our Home Assistant container uses the host network, it cannot find the MariaDB and InfluxDB containers through their hostname. That’s why we make use of the port forwards and refer to them via <ip.of.our.box>.

Restart ¶

After saving the files, open the Home Assistant web interface and go to Developer Tools > YAML. Click Check Configuration to make sure you didn’t make any typos are made a mistake in spacing.

If you get a green “Configuration valid!” you’re good and you can press the Restart button on the right to restart Home Assistant, which will apply all changes we made to the configuration.yaml file.

Manage Portainer from within Home Assistant ¶

One thing we’ll want to do fairly often once we start adding add-ons is having an option to quickly have a look at them without needing to open extra browser windows.

For this, we’ll make use of [iframe Panel][iframe]’s. This integration will add additional panels to the Home Assistant sidebar, which will open an application of our choosing within the HA dashboard.

To add an iframe for Portainer, add the following code to the configuration.yaml file:

panel_iframe:
  portainer:
    title: Portainer
    url: "https://192.168.10.106:9443/#!/2/docker/containers"
    icon: mdi:docker
    require_admin: true

Check your configuration again and restart Home Assistant. Once Home Assistant is back online, you’ll find the Docker logo in the sidebar and clicking it will open Portainer in the dashboard view.

And that wraps it up for part 3 of the Home Assistant Container series.

Next we’ll add some useful add-ons to Home Assistant, like the Mosquitto MQTT broker and Node-RED.

PS: the eagle-eyed may have noticed an additional container in my last screenshot. I also added adminer to my toolkit. Adminer, formerly known as phpMyAdmin, can be used to connect to and manage the MariaDB database.

If you want to copy my config, add the following service to docker-compose.yaml:

  adminer:
    container_name: adminer
    image: adminer
    restart: unless-stopped
    ports:
      - "8080:8080/tcp"
    depends_on:
      - mariadb

[iframe]: https://www.home-assistant.io/integrations/panel_iframe/ “iframe Panel integration