Home Assistant - replace SQLite with MariaDB

Table of Contents

Before we begin, please ensure you are using either HAOS (Home Assistant Operation System) or HA Supervised, and that your account profile has Advanced Settings turned on. Otherwise, the installation process of MariaDB database will be different from the instructions below, although the configuration will remain the same.

Why replace default SQLite with MariaDB

As time goes on, the Home Assistant database file will get bigger and bigger, making it slower and slower to view logs and history. When there are lots of devices connected to the system, this can really affect your experience, like the history page taking a long time to open or even freezing. To fix this, we recommend replacing the database with one that has better performance. MariaDB, PostgreSQL and MYSQL are all good options.

It’s easy to install and configure with the official MariaDB Add-On, so we’ll use that as an example.

How to replace it with MariaDB

1. install MariaDB Add-On

Getting MariaDB up and running is easy! Just go to Settings in the sidebar, select Add-ons, then choose the Add-on Store. Search for “MariaDB”, install it and the process will be complete in no time. Before we run it, you’ll need to go to the MariaDB Add-ons page and configure it.

2. Configure and start up MariaDB

It’s easy to configure! Just go to the “Configuration” tab and enter your own password in the “password” field; a simple password will do since it’s only running locally. Then click the start button in the “Info” tab and check the “Log” tab to see if MariaDB is working properly. If you need help or more explanation, you can find configure instructions in the “Documentation” tab.

3. Add Recorder in configuration.yaml file

Once MariaDB is up and running, configure the db_url to make Home Assistant use MariaDB instead of SQLite. Go to the “File editor” in the sidebar and open the configuration.yaml file. At the end of the file, add either of the following lines:

recorder:
  db_url: 'mysql://homeassistant:ha@core-mariadb/homeassistant?charset=utf8mb4'

Or you can do it more properly, not reveal your password in configuration.yaml, add the “db_url” in secrets.yaml like:

mariadb_url: 'mysql://homeassistant:ha@core-mariadb/homeassistant?charset=utf8mb4'

and then add bellowing lines in configuration.yaml :

recorder:
  db_url: !secret mariadb_url

You can adjust how many days your history records should keep by adding more settings. You can also decide which entities and events should be included or excluded in your history. Bellowing is a full example of recorder settings. For more detailed information, you can also check out the recorder documentation.

# Example configuration.yaml entry with exclude
recorder:
  purge_keep_days: 90
  db_url: !secret mariadb_url
  exclude: # or you can use include
    domains:
      - automation
      - updater
    entity_globs:
      - sensor.weather_*
    entities:
      - sun.sun # Don't record sun data
      - sensor.last_boot # Comes from 'systemmonitor' sensor platform
      - sensor.date
    event_types:
      - call_service # Don't record service calls

4. Restart Home Assistant service

Before restarting the system, it was still using SQLite, so the last step is to restart the HA service. To do this, go to “Developer Tools” in the sidebar, click the “Check CONFIGURATION” button to make sure the configuration.yaml is in the correct format. If it passes, then click the “RESTART” button. The restart process will take a while. Once the system is back up, if no errors appear, then we have successfully replaced SQLite with MariaDB. You can also view the detailed info in the “Log” tab in the “MariaDB” Add-On page.

Tips

If you’re planning to use MySQL or PostgreSQL, you should read the requirements in the recorder documentation first. HA requires MySQL ≥ 8.0 and PostgreSQL ≥ 12. Of course, you can install them on separate servers if you’d like.

To improve performance when checking historical records, try to reduce the size of your records. Decide how many days of records you want to keep and what information you don’t need – this will keep your data size under control. If you don’t, even a new database will have performance issues when the data size gets too big.

Home Assistant – replace SQLite with MariaDB