Copying history data from one database to another

I am about to replace a RPI3b with a brand new RPI4 for Home Assistant.
So far I have been using a MariaDB database on a Synology NAS due to the low performance and reliability of SQLite running on an SD Card.

With the new RPI4, which will be assembled on a Argon One M.2 case together with a 250GB SATA M.2 SSD, I want to run a local MariaDB that will keep all the history data.

My question is: what is the easiest way to copy data from the current DB to the new DB? Will a simple export/import via myphpadmin do the trick, or should I consider other options?

I know with MySQL you can just copy/paste the entire database directory and it works, I dont see why that wont work here

In this case that will be hard to achieve because by installing MariaDB as an addon, I will not have access to the actual files of the database.

You could use something like HeidiSQL or MySQL Workbench to do a direct table to table copy of the data. It’s hardly an elegant solution, but it should work.

Otherwise (and I’m not 100% certain this will work), but I think you can use the SSH add-on to run docker exec -it [mariadb docker name] bash and that should give you terminal access to the add-on from within hassos. From there, you can rsync the DB files from your old MariaDB install to your new one.

After spending some time on a more straightforward approach, I think I need to go for the SQL client way (dbeaver in my case).

My problem is that I am not able to reach the MariaDB database on the addon, as the database port does not seem to be mapped through the host.

How can I configure the addon so that I can reach MariaDB outside of the hassOS?

If I recall (it’s been a LONG time since I played with Supervisor), you should be able to go to the MariaDB settings and expose the port under “Network”.

That’s it, my bad, there is a dedicated setting for that port forward.

Working great now, thanks!

Did this work for you? If so, how did you copy the dub from your nas to the local mariadb?

I used dbeaver to connect to both databases and generated SQL scripts to create tables and do inserts on the target database.