Convert Home Assistant to use MariaDB as the database

by Posted byAndré Jacobs

Photo by Markus Winkler on Unsplash

Overview

Currently my Home Assistant is using the default SQLite database for recording sensor data but I would like to use MariaDB instead. This is part of my Day 14 of 100 Days of STEM.

NOTE: My Home Assistant setup is running on a Raspberry Pi 3 using a SSD drive, so using MariaDB will be fine in my case.

VERY IMPORTANT: You will lose all the previous recorded sensor data if you switch databases.

Objectives

My overall objectives to achieve over the next couple of days are:

  • [✅] Convert Home Assistant to be using MariaDB as the database
  • [ ] Backup database to Amazon S3
  • [ ] Backup database to local Linux server
  • [ ] Monitor and notify of errors

By default Home Assistant uses a SQLite database for storing the recorded sensor data. The database is located at /config/home-assistant_v2.db

Initial steps

  • First backup your Home Assistant installation.
  • Navigate to Snapshots. Supervisor → Snapshots.
  • Create a Full Snapshot and make sure you download a copy locally. You can do this by selecting the snapshot and choosing to download the snapshot.
  • You can view a copy of the SQLite database by extracting the snapshot file and then extracting the homeassistant.tar.gz file. The database file is named home-assistant_v2.db.
  • Apply available updates from the Dashboard. Supervisor → Dashboard. The order I chose was Home Assistant Core, Supervisor and then OS. (I did try starting with OS but it did some weird things).

  • NOTE: If you are having issues, then there is also the Observer that can give clues as to what is happening on Home Assistant. The port is 4357. Local link. The UI doesn’t always indicate what is going on behind the scenes and it is worth always waiting and doing a page refresh. Go grab that tea.

  • Now is a great time to rename any sensors. In my case my sensors were setup like T1, T2 .. TN (for Temperature etc.) but I want to include the location of the sensor in the name. The reason I chose this initial naming convention comes from the fact that I wanted to label the sensors and don’t have enough space to stick a big ol label on them. The approach I took was to open the Phoscon portal and rename the sensors there and then rebooted the Host system (i.e. Raspberry Pi).
  • After applying updates and making changes, it is a good idea to take another full snapshot.

Setting up MariaDB

  • Install the official MariaDB add-on. Navigate to Supervisor → Add-on Store and search for MariaDB.
  • Click on Install. Once it has finished then enable the Watch dog setting.
  • Change the password before starting the add-on. Navigate to the Configuration tab and set a generated password (you are using a password manager right?). Click save.
  • Start the MariaDB database. Navigate back to the Info tab and click start.
  • Verify everything is running and that the database has been installed. You can view the information on the Log tab.

Configure Home Assistant to use MariaDB for recording

  • You need to specify the database connection string for the recorder module.
  • Edit the file /config/configuration.yaml. For this I am using the File editor add-on, but you can also use SSH.
# Change the username,password and database name as required
recorder:
  db_url: mysql://homeassistant:password@core-mariadb/homeassistant?charset=utf8mb4
  • Save and check the configuration. Navigate to Configuration → Server Controls and click Check Configuration.

  • On the same page, click the Restart button under Server management.
  • Once everything is back up and running again you can verify by looking at the History section and will notice that all the sensor data has been reset and is only showing values from a few minutes ago.

Accessing MariaDB remotely

  • I want to be able to access the MariaDB from other computers on my network for the time being and have exposed the container port. NOTE: I recommend that you do not do this since you are exposing the database to anything and anyone on your network.
  • First I added a new read-only user to MariaDB.
databases:
  - homeassistant
logins:
  - username: homeassistant
    password: <<generated>>
  - username: ha-readonly
    password: <<generated>>
rights:
  - username: homeassistant
    database: homeassistant
  - username: ha-readonly
    database: homeassistant
    privileges:
      - SELECT
  • Then set the exposed port to be used.

  • I then used MySQLWorkbench to connect to the MariaDB database and verify that the sensor data is being stored as expected.

Wrapping up for today

My Home Assistant is now using MariaDB for storing all the sensor data and the next task would be to setup remote backing up to Amazon S3 and my local Linux server.

References