Migrating home assistant database from sqlite to mariadb.. ONLY if you are VERY familiar with Database administration

Share your Projects!
126

Solution

I took a simpler or lazier approach, and used a well supported library to quickly migrate home assistants sqlite database into MariaDB/MySQL with full history. There is no need to even transfer files - just a couple commands and done!

Prerequisites

  • install and activate MariaDB addon
  • Install and activate the Terminal & SSH addon

Maria DB Migration Steps

  1. Stop the recorder service from developer tools
    ( ‘Developer Tools’ > ‘Services’ > Search for Recorder: Disable > Press the ‘Call service’ )
  2. Start the Terminal & SSH addon (mariadb should be running as well)
  3. Run the commands below to install python (already exists on host, super fast)
  4. Run DB migration

Post-Migration (activation)

  • Configure HA to use new mariadb as recorder in configuration.yaml

Commands to migrate

# Install pre-req
apk add python3
apk add py3-pip
pip3 install sqlite3-to-mysql
# OR python3 -m pip install sqlite3-to-mysql

# run migration (CHANGE PASSWORD)
sqlite3mysql \
--sqlite-file /config/home-assistant_v2.db \
--mysql-database homeassistant \
--mysql-host core-mariadb \
--mysql-user homeassistant \
--mysql-password 'YOURPASSWORD'

(optional, include --chunk INTEGER to run the migration in chunks of records. This may help large datasets with lower resources.)

Arguments for script and more info → sqlite3-to-mysql · PyPI

FAQ

  • will it work with HAOS?
    Yes. The installed tools only need to exist as long as the commands take, and the Terminal & SSH add-on works great.

  • I got an error from mysql (invalid column, duplicate key, etc) !
    Backup mysql, purge all tables, and try again. MariaDB tables should not exist when we start migratration, will be fiully created from sqlite. ONly the database itself should exist.

    apk add mysql-client
mysqldump -h core-mariadb -u homeassistant homeassistant -p > sqlbackup_DATE.sql

  • Can I use if database is on another host?
    Yes, just change the ‘-h’ (hostname) argument to wherever it lives

  • Can I use with phpmysqladmin?
    The python tool wants direct access to the MySQL database, just like phpmysql. You need access to the HA instance with ability to run python (hence Terminal addon)

  • What about Energy Dashboard (and statistics generally)?
    Yep, all there. This migration should be everything in sqlite. (thanks @jusii, @malosaa)

  • My Energy costs are all messed up after migrating!
    Giving Home Assistant a day to work through should correct this, (thanks @malosaa)

  • Are there size limits?
    In theory any existing sqlite db can be migrated. only the resources of your machine limit that, but even little NUCs should get it done eventually.

And also…

Tremendous thanks to all the folks below who tested, questioned, and refined this method! We got a winner!

37 Likes