Safely move Home Assistant Database hosted by MariaDB addon from old to new hardware

Configuration
1

I need to move my Home Assistant ecosystem from an old to new hardware.

Actually running on Raspberry Pi 4 booting from SSD with Debian 11 and HA Supervised.
Want to move to a VM under Proxmox with HassOS operating system.

Creating a snapshoot on old hardware and restoring on new seems fine except for MariaDB database that become corrupted.

Database is big and I want to preserve it.

I suppose I could stop MariaDB addon and take a snapshot to get a non-corrupted snapshot but creating a snapshoot is very time-consuming on Pi. I also need to repeat the database migration several times in these days while I test several things. So even doing several times a sql dump is not viable. I would like a faster method because I can’t stop addon for hours every time.

Best approach, I think, could be using mariabackup possibly with replication among old and new host, but mariabackup seems not installed in the addon.

I read about a file copy method of databases folder while engines are stopped, I’m was trying to do it but on HassOS data folder of addon_core_mariasb seems unaccessible (or I don’t know how to access because the /mnt/data/supervisor path seems not existing) but it’s not easy.

So what is a fast and safe way to move MariaDB database between old and new host?

There are already many topic on this issue I hope there is now a solution.

2

Discussed and showcased in this video, including MariaDB.

1 Like
3

I don’t like to watch videos. From what I seen, it seems just the idea of stop/stop/snapshot/restore/restart.

4

Not sure if it’s a supported way, but I moved the big databases (MariaDB and also InfluxDB) with the following steps:

  1. Prepare environment for direct SSH connection from source physical machine (Raspberry Pi with Debian and HA supervised) to destination Virtual Machine (Home Assistant OS under Proxmox). For HassOS the SSH connection has to be made to machine on 22222 port thanks to some steps hidden in docs as debugging features, and no by the regular so-called user feature of various SSH Addons.
  2. Stop both addons on both machines, source and destinations.
  3. Use SCP to copy mapped data folders between machines (for example from /user/share/hassio/... to /mnt/data/supervisor/...)
  4. Restart addons on destination virtual machine.

Seems working and reasonably fast (few minutes of downtime for moving few GB of data).

Still hoping in a simpler way in the future.