Migrating from MySQL (mariaDB) back to sqlite

Configuration
51

There is also a command line tool to export a MariaDB/MySQL database to a new SQLite3 database.
I’ve successfully used it today to migrate back to SQLite3, all data were migrated so short and long term statistics.
I use Home Assistant Core installation.

Steps should be as follow for Home Assistant OS:

  1. Using Terminal & SSH addon, connect and install Python, Pip and MariaDB-to-SQLite3
apk add python3
apk add py3-pip
pip install mysql-to-sqlite3
  1. Stop Home Assistant
  2. Migrate the database:
    mysql2sqlite -f /config/home-assistant_v2.db -d <database_name> -u <mariadb_user> -p -V --use-buffered-cursors
    Replace <database_name> and <mariadb_user> by the database name and user used by Home Assistant
  3. Comment the db_url in your YAML configuration
  4. Restart Home Assistant core.

Really check what I wrote before executing I.E. for path as I had not the opportunity to test with HA OS but main ideas are here.
All tables are migrated using this command so if you use the same database for Home Assistant statistics and something else, these other data will be migrated.
See the tool documentation to see how to make a more precise migration if needed.

19 Likes