Migrate back from MariaDB to the default SQLite

Community Guides
,
62

Thank you. Working perfect.

Convert the database on external Linux VM is running only some minutes for 3GB SQLite database.

Back to SQLite after many years on MariaDB my HA is a little bit more responsive.

63

So I think I go back to MariaDB after one day on SQLite.

After restart I must wait 10 Minutes to see data from recoder like graph and some statistic sensor do not work anymore. So I think with some more values a database is better choice.

64

Home Assistant does periodic optimizations of the database so there is a good chance the issue will disappear on their own. I didn’t dive into the details since I trust Home Assistant to know best (which works for me).

A few gigabytes of database file should not be a problem on a raspberry pi 4.

If MariaDB works better for you, don’t let me hold you back from doing what works for you.

65

Nice instruction, but from what I understood: you can just turn off the mariaDB and configs that point to that DB when long term data and history does not mean anything to me, right?
So Basically the “easy” version without that much overhead would be just turn off and the system (re)creates the sqlite db, wont it?

66

Correct.
If you don’t want to migrate for example energy data, you can remove the MariaDB config and HA will start using the default database again.

1 Like
67

Thanks a lot for the tutorial! Spent a lot of time trying to get this to work because I can’t update home assistant anymore while using mariadb for some reason (waiting for recorder to start until it gives up, no error messages in 2024.11.4, no response in github issue). In the end it took ~60GB of memory to finish the conversion process but looks like it finally worked. :partying_face:
celebrated too early… home assistant starts, but history (also long term statistics) are gone :cry:

68

Did you keep the MariaDB add-on? If so, you can go back and forth until it works. There is link to another forum topic: https://community.home-assistant.io/t/migrating-from-mysql-mariadb-back-to-sqlite
Maybe you can find some tips on how to solve uour issue. Hope you figure it out.

69

I did, but the latest home assistant version doesn’t work with my MariaDB anymore (no error message besides “timeout waiting for recorder”) and other integrations don’t work on the previous version anymore so can’t easily go back.

70

Finally got it working again using this for conversion from mariadb to sqlite: GitHub - mysql2sqlite/mysql2sqlite: Converts MySQL dump to SQLite3 compatible dump
Not sure if this really made the difference or whether I made some mistake in the first export using the instructions above. I may have started the first export while home assistant was still using the database.