bijavix blog

Home Assistant OS MySQL Database Migration

bijavix <[email protected]>

Migrate your Home Assistant OS Database from the default SQLite to a robust MySQL Database, speeding up your load time and keeping all your old history.

If you have a very complete home automation setup, with many entities sendind data into home assistant, you might want to consider migrating from the default SQLite into a robust database such as MySQL.

This guide will walk you through migrating Home Assistant OS from SQLite to a MySQL/MariaDB server with SSL/TLS, keeping your history.

(Reference HA documentation)[https://www.home-assistant.io/integrations/recorder/], check it if you have any doubts or want to make changes.

Data migration is not officially supported. This process is straight forward and has minimal risk. Keep your HAOS backup and home-assistant_v2.db safe until you’re sure the migration was successful.

1. Requirements

  • Home Assistant OS (Supervisor-managed).

  • MySQL 8+ or MariaDB 10.3+ server, preferably with SSL/TLS enforced.

  • Basic Linux and MySQL knowledge to understand what you are doing.

2. Prepare the remote database (TLS + schema)

If your DB already exists and is used only by Home Assistant, you may skip the database creation step. Ensure the schema is empty before migration.
Create the database (at DB server)
CREATE DATABASE homeassistantdb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
Create the user
CREATE USER 'homeassistant'@'HAOS_HOST' IDENTIFIED BY 'HAMYSQL_PASSWORD';
GRANT ALL PRIVILEGES ON homeassistantdb.* TO 'homeassistant'@'HAOS_HOST';
FLUSH PRIVILEGES;

If you use client certificates (mutual TLS), also provide a client cert/key to Home Assistant later. If you rely on a private CA, pinning ssl_ca should be enough.
Upload the CA certificate on Home Assistant through ssh

You should have a CA certificate for the DB server’s certificate. You’ll need place it under /ssl on HA.

# download the ca.pem from the db server and then upload it to the haos
scp db@DB_HOST:/etc/mysql/ssl/ca.pem ca.pem
scp ca.pem root@HAOS_HOST:/root/ca.pem

# On HAOS, move it into the shared /ssl mount (visible to HA Core)
mv /root/ca.pem /ssl/ca.pem

3. Back up Home Assistant and database extraction

  1. Create a full Backup in Settings > System > Backups, make sure to include History in the backup.

  2. Download the backup tar file and extract homeassistant.tar.gz from it, inside, at homeassistant/data you will find the home-assistant_v2.db database file we need.

4. Migrate SQLite to MySQL/MariaDB

Ensure the MySQL target database (homeassistantdb) is empty. If tables already exist from a previous attempt, drop them or use the truncate option (--mysql-truncate-tables) when running the tool.
Install the tool at your workstation
pip install sqlite3-to-mysql

The next step will from several minutes to a couple of hours, find something to do in the meanwhile and relax.
Run the migration
sqlite3mysql \
  --sqlite-file home-assistant_v2.db \
  --mysql-host db.example.com \
  --mysql-port 3306 \
  --mysql-database homeassistantdb \
  --mysql-user mysqluser \
  --prompt-mysql-password

5. Connecting Home Assistant to the remote DB

Edit /config/configuration.yaml and update the recorder: block to use the mysqlclient driver.

recorder:
  db_url: "mysql://homeassistant:[email protected]/homeassistantdb?charset=utf8mb4&ssl_ca=/ssl/ca.pem"
  db_max_retries: 16
  db_retry_wait: 20
  commit_interval: 15

The commit_interval is set to 15 seconds to reduce how often the db writes are commited, reducing disk I/O, at the cost of data loss in the even of power failure.

ssl_ca pins the CA used to validate the server certificate. If you want to use mutual TLS, also add &ssl_cert=/ssl/client.crt&ssl_key=/ssl/client.key.

6. Restart HA and verify

  1. Restart Home Assistant to make sure the changes are applied correctly.

  2. Watch Settings > System > Logs for recorder connection and schema checks.

  3. If you see warnings about DB Schema corrections and column modifications, then the connection was successful, but it will take several minutes to apply the changes. Data history should already be available.

7. Post-migration tuning (optional)

If the migration was successful and has been working with no issues, you can remove the home-assistant_v2.db from HAOS to free up disk space.

To keep the database lean and fast, we can config filters to purge old data that we hardly consult in history, modifying the configuration.yaml:

recorder:
  auto_purge: true
  purge_keep_days: 90
  exclude:
    entities:
      - sensor.last_boot
      - sensor.date
    entity_globs:
      - sensor.*_debug_*
      - sensor.weather_*

Here we are recording data for all entities, except the weather and debug. Events older than 90 days are deleted.

The purge should keep long-term statistics from sensors declaring their metadata correctly, indefinitely with hourly resolution.

8. Rollback plan

If anything went wrong, modify the HA configuration.yaml back to use SQLite or restore the backup. If Home Assistant failed to start due to an incorrect configuration, the config folder won’t be at /config, you will need to change directory into /mnt/data/supervisor/homeassistant/, modify there the config and then reboot.