MariaDB database from nas to Raspberry

Configuration
1

Hi,

I have a HomeAssistant running on a raspberry Pi 4.
The data is written to a MariaDB database, which is running on a Synology nas, so this database is backed-up through the nas backup.

This is working fine, but I am planning to re-install the nas.

Is it possible to move the MariaDB from the nas to th Raspberry (where the Home Assistant resides), or to switch Home Assistant to the default SQLLite and import the MariaDB database?
If it can than I can shut down the nas and rinstall it, without interrupting or loosing the Home Assistant history.

If it is possible, does someone has a plan with steps how to do this. I know several things about computers, home assistant, nas and so on, but not so much about (sql) databases.

I have found this:
Migrate back from MariaDB

But if I am right, this assumes that the MariaDB is also on the same hardware as Home Assistant?

On the Synology, I havefound an option to export the MariaDB database, but there are a lot of settings, which I do not know how to set and after that, I do not know how to migrate/move this to the Home Assistant SQL database.

Can someone help me do this?

2

I did similar operation (migrating from MariDB package to MariaDB in docker on Synology using HeidiSQL. In your case it would mean:

  • add MariaDB add on to installation on RPi
  • stop HA (this might be tricky, I’m not 100% sure if you can stop just core, without stopping add on)
  • copy DB (all tables and settings) from NAS to RPi using HeidiSQL
  • change configuration of HA to point recorder to add-on instead of NAS
  • start HA

After reinstallation of NAS you can run this process in reverse order to copy data back to NAS or leave it as is on RPi, if it works for you.

3

Hi Mirek,

Thank you, that’s a good starting point. One of the tutorials I have found migrated from MariaDB to SQL, but moving seems easier/safer?

Where do I run the HeidiSQL, on the NAS, the Raspberry (with home assistant) or on a PC?
I am also able to login in the NAS and the Raspberry with Putty SSH, can thaat help?

I have to find the database location on the NAS first.

EDIT: I will read the HeidiSQL page first, before asking you more about it!
:slight_smile:

4

No.
The mysql2sqlite takes the hostname of your mysql as a parameter.

5

You need to run HeidiSQL from 3rd computer, I think it should be Windows machine. It might take some time, as I recall my ~6GB database took around ~45 minutes to be copied. You do not need to know exact location of the database. HeidiSQL uses SQL to read and write data, so all you need to know is IP of the machine where the DB is installed on, port and credentials to access particvular DB - just like for recorder configuration in HA.

6

I have succeeded in opening the MariaDB database (on the Synology) with HeidiSQL,
and I see the Home Assitant database(s).

but I do not know how to proceed. I have installed MariaDB addon in Home Assistant, but at the moment I didn’t point the recorder in the Config yaml.

Do I have to export or can I do a copy-paste?

And how can I connect to the Raspberry with home assistant to move/copy the database to it?

7

Okay, maybe I succeeded…

I first did an “export database to sql” and ticket the “create database and tables”:

1
1785×396 21 KB

After that I connected in another winrod to the raspberry with home assistant, where there were also the home assistant databases, but first I deleted those (were empty, newly generated databases).

After that I did a “run sql file” and chose the exported file I just created.
In the configuration the recorder points to the local database on the Raspberry.

After 10 minutes, the databases seem to be copied. I restarted Home Assistant and all seems working!
I have to inspect everything if there are some problems or not working sensors, but it looks fine for now!

8

Okay, I have turned off the nas and still, Home Assistant is working, so it really is using the database on the Raspberry instead of the nas! :slight_smile:

Now I can rebuild the nas and will decide later if I move the database back to the nas again.

Thank you both for the help!