#Migrate from MariaDB back to SQLite3

1 messages · Page 1 of 1 (latest)

worn skiff
#

Hi folks. I've been using MariaDB on an external NAS for a few years and would like to migrate back to SQLite3 to have everything contained within the HA host.

Is the best way to achieve this still this old MySQL to SQLite Python tool? Or is there a more up-to-date way to do it?

Home Assistant Community
Migrating from MySQL (mariaDB) back to sqlite

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: Using Terminal & SSH addon, connect a...

copper cargo
#

it seems that this guide is still up-to-date, make backup first, just in case.

lyric oyster
#

IIRC this can cause performance issues due to missing indexes or something.

worn skiff
lyric oyster
#

I do not have that information or know for sure. I just faintly remember having read this.

copper cargo
#

does it have something to do with the size of the database? Normally haos will sort most things out by itself.

lyric oyster
#

Let's just say that if it were trivial and fool proof there would be official docs or migration paths.

worn skiff
#

That's a pretty wild assumption. There's plenty of things in HA that are fairly trivial that aren't documented purely because they're pretty niche (like this use case).

lyric oyster
#

True but in your link there's an example where things broke from a migration: https://github.com/home-assistant/core/pull/89474
I can't easily find the other topic I thought I read. It's impossible to search for it. Just too much stuff. The devs talked about this on a live stream too but I can't easily find that either. I'd suggest to simply try it out. I'm aware I'm not very helpful here.

worn skiff
#

That issue you linked is going the opposite way though and makes sense if there was invalid datetime formats in the SQLite DB. It's normal for strict DBs to reject fuzzy types.

#

But thanks for taking a look anyway! I will give it a go and see if there's any noticable issues.

lyric oyster
#

By the way you can do this with the SSH addon too

apk add pipx && pipx install mysql-to-sqlite3 && pipx ensurepath && source .bashrc
mysql2sqlite ...

Or just

apk add pipx && pipx install mysql-to-sqlite3
.local/bin/mysql2sqlite ...
worn skiff
#

Thanks, but as mentioned in the original post it's on my NAS. Good to know for others that find this post though.

lyric oyster
#

That doesn't mean you have to run the tool on the NAS too.

worn skiff
#

Very true, it's just a python module that transposes from MySQL to SQLite.