I, like apparently a lot of users on here, have been trying to get the size of my snapshots down since they had gotten quite unwieldy. For anyone that uses the MariaDB there’s a very simple solution to this problem - skip the addon. Since it basically only holds data on your entities current state and history its not really a huge loss to me (and I think many others from reading posts) to simply lose it in the rare occasion you have to do a complete restore.
The problem though is that other addons have started to store their config in MariaDB. Most notably for me is the NGinx Proxy Manager addon. I have a heavy reliance on this addon and would prefer not to lose its configuration if I had to restore. But there’s no way to backup this config with snapshots unless you include the entire MariaDB add-on (states and history and all).
Fortunately I figured out a workaround that should apply to NPM as well as any other addons you might come across that want to store their config in MariaDB as well. As was pointed out to me in a recent Github issue I submitted, the SSH & Web Terminal addon has mysql installed OOTB. Therefore we can use mysqldump to take backups of specific databases. And we can SSH into this add-on from a shell_command which means we can automate this process to occur nightly, this way it gets included in my nightly snapshot.
The code for this is pretty simple but there is one out of HA setup step you have to do. In order to be able to SSH into the addon from a shell_command you’ll need to generate a key for HA to use. And you can’t use the normal folder of ~/.ssh because when running a shell_command, ~ is /root and nothing in /root is preserved through upgrades. I would suggest making a .ssh folder within /config and generating your key there, then you can refer to it in your ssh command with -i like I did below. Then add the public key to the list of authorized_keys in the SSH addon config.
Once you get that out of the way you can just bring in this shell command and automation, replace the placeholders in the shell command with your info and you’ll be all set, backups of your NGinxProxyManager add-on configuration nightly. If you have other addons that store config in MariaDB just make a shell_command for each and add them to the automation. Hope it helps!
shell_command:
# Note - Do not parameterize this command with templates or else you won't be able to pipe the output to a file
# Make a new one for each addon you need to back up and add it to the automation
backup_npm: 'ssh -o UserKnownHostsFile=/config/.ssh/known_hosts <Username for SSH addon>@<HA Local IP> -i /config/.ssh/id_ed25519 -p <port specified in SSH Addon> ''mysqldump --databases nginxproxymanager'' > /config/backups/nginxproxymanager.sql'
automation:
- id: '1588038346704'
alias: Backup addons nightly
description: 'Run shell command nightly to backup config of addons with config stored in MariaDB'
trigger:
- at: 04:00
platform: time
condition: []
action:
- data: {}
service: shell_command.backup_npm
NOTE: Run the ssh command in the host updates sensor once manually in the portainer add-on to get your known_hosts file updated, otherwise it won’t work. Or alternatively copy the necessary line into /config/.ssh/known_hosts file manually from some other machine so its set up. Otherwise the SSH command will fail behind the scenes.
Also for actually restoring from this file when you need to, I’d suggest using the phpMyAdmin addon to do it as it has an import option that takes a SQL file. There is definitely a command line way to do the restore but I’m not proficient enough with the mysql CLI to feel confident in my ability to provide that command without testing it. And I’m not going to test restore right now against my live HA instance. If someone else knows it, feel free to share a restore shell_command!
