Migrate Recorder from MariaDB to SQLite

Posted on June 21, 2026June 21, 2026 by jtaubman

Home Assistant Configuration

Tested

This guide was live-tested on a real HA installation in June 2026 on x86 hardware running Home Assistant OS. All lessons learned are incorporated.

Background & Why This Is a Good Idea

SQLite is now the default and officially recommended database engine for Home Assistant.
HA has been heavily optimised for SQLite over recent years — it is now considered as fast as MariaDB for typical home use, and more stable.
Removing MariaDB simplifies your stack if you’re only using it for the recorder.
The HA dev team no longer recommends MariaDB for typical installations.

Decision: Migrate History or Start Fresh?

Choose before starting

If history doesn’t matter to you, Option B – Fresh Start (No History Preserved) is much simpler.

Option	Pros	Cons
A – Migrate with history	Keeps long-term statistics (Energy dashboard, etc.)	Requires SSH and command-line tools
B – Fresh start	Simple — 10 minutes total	Lose all history and long-term statistics

Option A – Full Migration (Preserve Long-Term Statistics)

Prerequisites

Terminal & SSH add-on installed and running
SSH client on your computer (PuTTY on Windows, built-in terminal on Mac/Linux)
Free disk space: at least 1.5× the size of your current MariaDB database
~30–60 minutes (x86 hardware is much faster than Raspberry Pi)

Phase 1: Take a Full Backup

Do not skip this

If anything goes wrong, this is your only safety net.

Go to Settings ? System ? Backups
Click Create backup ? Full backup
Wait for completion before continuing

Phase 2: Record Your MariaDB Credentials

Go to Settings ? Add-ons ? MariaDB ? Configuration tab
Note down:
- database name
- username
- password

Consider creating a dedicated migration user

The default MariaDB password is very long and hard to type. You can create a simpler user via phpMyAdmin. If you do, make sure to grant with 'username'@'%' (wildcard host) not 'username'@'localhost', or you will get ERROR 1045 Access denied. See Troubleshooting for details.

Phase 3: Expose MariaDB on a Network Port

Go to Settings ? Add-ons ? MariaDB ? Configuration ? Network
Enable Show disabled ports
Set the database engine port to 3306
Save and restart the add-on

Test the connection via SSH

Before proceeding, verify you can connect:

mysql -h <HA-IP-ADDRESS> -P 3306 -u <username> -p'yourpassword' <database>

Password flag syntax

Always use -p'yourpassword' with no space between -p and the password, and single quotes around it. If you type the password at the prompt, shell special characters can cause silent failures. If the password starts with a letter that matches a CLI flag (e.g. c), use --mysql-password 'yourpassword' instead — see Phase 9 Run the Migration.

If connected successfully you’ll see the MariaDB prompt. Run a quick size check:

SELECT table_schema AS "Database",
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS "Size (MB)"
FROM information_schema.tables
WHERE table_schema = 'homeassistant'
GROUP BY table_schema;

Type exit to leave.

Phase 4: Shrink the Database

Important

This removes short-term history but preserves long-term statistics. It’s essential — migrating a large database without purging first will fail with a Killed error.

Go to Developer Tools ? Actions
Search for recorder.purge (not recorder.purge_entities — that’s a different action requiring entity IDs)
Set:
- Keep days: 1
- Repack: ?
- Apply filter: ?
Click Perform Action

# YAML mode equivalent
action: recorder.purge
data:
  keep_days: 1
  repack: true
  apply_filter: true

Wait for completion

The action returns immediately but runs in the background. The repack is the slow part. Monitor progress by re-running the size query in phpMyAdmin every 20–30 minutes. Only proceed when the size has stopped dropping.

Check row count to gauge progress:

SELECT COUNT(*) FROM states;

Check free disk space in SSH:

df -h /config

You need at least 1.5× database size free. 14 GB free for a ~900 MB database is comfortable.

Phase 5: Prepare the SSH Add-on

Go to Settings ? Add-ons ? Terminal & SSH ? Configuration
Set a password under Options
Under Network, set SSH port to 22
Save and restart

Phase 6: Connect via SSH

ssh root@<HA-IP-ADDRESS> -p 22

Phase 7: Install the Migration Tool

Virtual environment required

Recent Alpine Linux versions used by HA OS enforce PEP 668 — pip install directly will fail with externally-managed-environment. Use a venv instead.

apk add python3
python3 -m venv /root/migrate-venv
source /root/migrate-venv/bin/activate
pip install mysql-to-sqlite3

Your prompt will change to (migrate-venv) confirming you’re inside the venv.

If you reconnect SSH later

Re-activate the venv before running the migration:

source /root/migrate-venv/bin/activate

Phase 8: Stop Home Assistant Core

ha core stop

HA itself stops, but the OS and add-ons (including MariaDB) remain running. This is correct.

Verify the SQLite file doesn’t already exist (important if retrying):

ls -lh /config/home-assistant_v2.db

If it exists from a previous failed attempt, delete it before re-running:

rm /config/home-assistant_v2.db

Phase 9: Run the Migration

Password flag gotcha

If your password starts with a letter that matches a mysql2sqlite flag (e.g. c, h, u, d), using -p'password' will cause a parsing error like:
Error: Invalid value for '-c' / '--chunk': '92' is not a valid integer.
Use --mysql-password 'yourpassword' instead to avoid this entirely.

mysql2sqlite \
  -f /config/home-assistant_v2.db \
  -d homeassistant \
  -u <username> \
  -h <HA-IP-ADDRESS> \
  -P 3306 \
  --mysql-password 'yourpassword' \
  -V \
  --use-buffered-cursors

Monitor progress in a second SSH session:

watch -n 5 'ls -lh /config/home-assistant_v2.db'

The file should appear and grow. On x86 hardware a ~900 MB database takes around 2 minutes. On Raspberry Pi expect 20–30 minutes.

Expected output on completion:

INFO     Vacuuming created SQLite database file.
This might take a while.
INFO     Done!

Troubleshooting

Error	Cause	Fix
`Killed` during `states` table	Database still too large	Run another purge (Phase 4) and wait longer
`table states has no column named domain`	Stale partial migration file	`rm /config/home-assistant_v2.db` and re-run
`Invalid value for '-c'`	Password starts with a flag letter	Use `--mysql-password 'yourpassword'` instead of `-p`
`ERROR 1045 Access denied`	User host mismatch	Ensure grant uses `'user'@'%'` not `'user'@'localhost'`
Out of memory	Insufficient RAM on HA hardware	Run migration on a PC/VM, copy `.db` to `/config/`, continue from Phase 10

Phase 10: Update configuration.yaml

nano /config/configuration.yaml

Comment out the MariaDB db_url line:

recorder:
  # db_url: mysql://user:password@core-mariadb/homeassistant?charset=utf8mb4

Save with Ctrl+O, exit with Ctrl+X.

Phase 11: Start Home Assistant Core

ha core start

Phase 12: Verify

Open HA in your browser
Settings ? System ? Logs — check for database errors
Energy Dashboard — scroll back several months, long-term statistics should be intact
History — will only go back ~1 day (expected, due to Phase 4 purge)

Post-startup log errors to expect and fix

SQL sensor errors after migration

If you had a sensor querying MariaDB’s information_schema.tables for database size, it will error immediately on SQLite:

ERROR (DbWorker_0) [homeassistant.components.sql.sensor] 
no such table: information_schema.tables

Replace the query in your configuration.yaml with the SQLite equivalent:

SELECT ROUND(page_count * page_size / 1024 / 1024, 1) AS size
FROM pragma_page_count(), pragma_page_size();

Use size as the column value. Restart HA after updating.

MariaDB stats error

You may also see:

Failed to call /addons/core_mariadb/stats - Add-on core_mariadb is not running

This is harmless and will stop once you uninstall MariaDB in Phase 14.

Phase 13: Rollback (if needed)

If anything isn’t right, reverting to MariaDB is instant:

Edit /config/configuration.yaml and uncomment the db_url line
Restart Home Assistant

Your MariaDB data is completely untouched.

Phase 14: Clean Up

Wait a few days before uninstalling

Leave MariaDB installed as a safety net while you confirm everything is working correctly — history loads, Energy dashboard is intact, no recurring errors.

When satisfied:

Settings ? Add-ons ? MariaDB ? Uninstall

This permanently deletes all MariaDB data. Only do this when you’re confident.

Option B – Fresh Start (No History Preserved)

Take a full backup — Settings ? System ? Backups ? Create backup
Open /config/configuration.yaml (File Editor add-on or SSH)
Comment out the db_url line:recorder: # db_url: mysql://...
Save
Settings ? Add-ons ? MariaDB ? Stop ? Uninstall
Settings ? System ? Restart

HA will create a fresh SQLite database at /config/home-assistant_v2.db automatically.

Post-Migration: Optional Recorder Tuning

recorder:
  # db_url omitted — SQLite is used by default
  purge_keep_days: 30       # Days of history to retain
  auto_purge: true          # Nightly purge at 04:12 local time
  auto_repack: true         # Repack every second Sunday
  commit_interval: 5        # Seconds between DB commits; increase to reduce SD card wear

Disk space requirement (official docs)

Always keep at least 1.5× your database size free on disk at all times. During schema upgrades or repacks HA may need a temporary full copy. If disk fills up, HA will move the database aside and start fresh as a last resort.