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
- from the addon_core_mariadb docker (e.g. via Portainer):
mysqldump --user=homeassistant --password=mypasswd --lock-tables --all-databases > /var/tmp/db.sql - 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