Reliable MariaDB-addon backup ideas

Hello All HA lovers and haters,

We have got already quite a number of somehow related topics, but I could not find a proper solution to a reliable backup of MariaDB running on hassio as an add-on.
I’ve found at least two tickets (130146 and 188334) pointing out, that the snapshot/backup service on HA does not backup required mariaDB files in a proper way, which often ends up in a corrupted restore.

I am interested in the historical values and I do not want to lose the data by reinstalling the add-on after unsuccessful restore, as in many cases recommended and proposed as a final solution!

My idea is to dump the complete database via mysqldump to the /config/db-backup folder and let it be processed by a subsequent HA nightly snapshot.

And here comes my lack of knowhow and kind request for your assistance:
I could successfully call mysqldump

  1. from the addon_core_mariadb docker (e.g. via Portainer):
    mysqldump --user=homeassistant --password=mypasswd --lock-tables --all-databases > /var/tmp/db.sql
  2. from hassio OS calling the docker:
    docker exec -it addon_core_mariadb mysqldump --user=homeassistant --password=mypassword --lock-tables --all-databases" > /var/tmp/db.sql

As a next step I wanted to create a shell_command which would run the command mysqldump in an automation preceding the snapshot.

Unfortunately neither mysqldump nor docker is available in the homeassistant docker shell.
MariaDB add-on does not provide ssh, so I cannot login there to run the 1) option.
Not available docker prohibits me from running the 2) option.

Do you know how to call mysqldump on a MariaDB-addon from the shell in an automation?
or
Do you have any better idea how to backup the MariaDB in an automation?
Moving the DB to NAS is not a solution for me.

Any kind of your suggestion is very much appreciated
Best, Peter

2 Likes

Hi Peter,

did you find a solution. I hear here, that it now a days, if is backuped in the right way out of the box. BUT I don’t find any additional information.

BR,
Tristan

Hi Tristan,

I gave up setup on MariaDB and divided it into two parts. One is back on SQLite for sensors with values I need on a short term basis and “long terms sensors values”, which I save in InfluxDB.

The backup is now on google cloud GitHub - sabeechen/hassio-google-drive-backup: Automatically create and sync Home Assistant backups into Google Drive
I created own Google API credentials. Now I’m happy with good database performance and stable backup.

Hope it helps in your setup.
Cheers
Peter