Do I have to stop mariadb addon when i use this
Home Assistant Supervisor: Create a partial backup.
?
@tom_l I just came across https://github.com/home-assistant/addons/issues/1353#issuecomment-864385570 , which seems to indicate you don’t have to stop mariadb anymore when doing a snapshot. Can anyone confirm this is the case, and that a full backup/restore cycle will work properly? If so, this is another great reason to swap to mariadb!
2 Likes
e-raser
December 20, 2021, 1:01pm
9
How do you run logical automated MySQL backups?
No need to stop the Addon during Snapshot/Backup is nice, but it will create a physical backup I think. Which is always not so handy compared to a simple MySQL dump (logical backup).
I have no idea on that one. In what way would a logical backup be preferable? I’m just interested in getting the whole HA instance back to how it was at a point in time, and if the physical backup accomplishes that I’m happy. But if there’s a better way…
e-raser
December 20, 2021, 8:24pm
11
tom_l
December 20, 2021, 9:29pm
12
The framework for fixing this in the SQLite DB is being worked on,
home-assistant:dev ← agners:ha-core-backup-notification
opened 09:58PM - 02 Dec 21 UTC
<!--
You are amazing! Thanks for contributing to our project!
Please, DO N… OT DELETE ANY TEXT from this template! (unless instructed).
-->
## Breaking change
<!--
If your PR contains a breaking change for existing users, it is important
to tell them what breaks, how to make it work again and why we did this.
This piece of text is published with the release notes, so it helps if you
write it towards our users, not us.
Note: Remove this section if this PR is NOT a breaking change.
-->
## Proposed change
<!--
Describe the big picture of your changes here to communicate to the
maintainers why we should accept this pull request. If it fixes a bug
or resolves a feature request, be sure to link to that issue in the
additional information section.
-->
This implements two new Websocket API endpoints `backup/start` and `backup/end`. Those are meant to notify the Core when the Supervisor is creating a backup of the Core. Since backups are done while the Core is running, this allows the Core to make sure all files in the `config` directory are in a safe state to read. Until now, the recorder continued to write to the database file while the backup is being made. This can lead to corrupted database files in the backup. With this implementation, the recorder locks the database using `BEGIN IMMEDIATE`, which essentially prevents writes from any Database connection while keeping the database available for reads. The implementation also halts the recorders queue so that no database inserts are even attempted.
This method is described as "historical" method in the SQLite documentation. Developers also confirmed that this method is still save to use and also works with WAL enabled.
https://www.sqlite.org/backup.html
https://sqlite.org/forum/forumpost/1307e369863b7b24?t=h
Note: Currently the `backup/start` and `backup/end` endpoints are implemented in the recorder directly. In case other components need to know when a backup is created, the Websocket API endpoint should be implemented as part of the `hassio` component or similar.
## Type of change
<!--
What type of change does your PR introduce to Home Assistant?
NOTE: Please, check only 1! box!
If your PR requires multiple boxes to be checked, you'll most likely need to
split it into multiple PRs. This makes things easier and faster to code review.
-->
- [ ] Dependency upgrade
- [ ] Bugfix (non-breaking change which fixes an issue)
- [ ] New integration (thank you!)
- [x] New feature (which adds functionality to an existing integration)
- [ ] Breaking change (fix/feature causing existing functionality to break)
- [ ] Code quality improvements to existing code or addition of tests
## Additional information
<!--
Details are important, and help maintainers processing your PR.
Please be sure to fill out additional details, if applicable.
-->
- This PR fixes or closes issue: fixes #
- This PR is related to issue:
- Link to documentation pull request:
## Checklist
<!--
Put an `x` in the boxes that apply. You can also fill these out after
creating the PR. If you're unsure about any of them, don't hesitate to ask.
We're here to help! This is simply a reminder of what we are going to look
for before merging your code.
-->
- [x] The code change is tested and works locally.
- [x] Local tests pass. **Your PR cannot be merged unless tests pass**
- [ ] There is no commented out code in this PR.
- [ ] I have followed the [development checklist][dev-checklist]
- [ ] The code has been formatted using Black (`black --fast homeassistant tests`)
- [ ] Tests have been added to verify that the new code works.
If user exposed functionality or configuration variables are added/changed:
- [ ] Documentation added/updated for [www.home-assistant.io][docs-repository]
If the code communicates with devices, web services, or third-party tools:
- [ ] The [manifest file][manifest-docs] has all fields filled out correctly.
Updated and included derived files by running: `python3 -m script.hassfest`.
- [ ] New or updated dependencies have been added to `requirements_all.txt`.
Updated by running `python3 -m script.gen_requirements_all`.
- [ ] For the updated dependencies - a link to the changelog, or at minimum a diff between library versions is added to the PR description.
- [ ] Untested files have been added to `.coveragerc`.
The integration reached or maintains the following [Integration Quality Scale][quality-scale]:
<!--
The Integration Quality Scale scores an integration on the code quality
and user experience. Each level of the quality scale consists of a list
of requirements. We highly recommend getting your integration scored!
-->
- [ ] No score or internal
- [ ] 🥈 Silver
- [ ] 🥇 Gold
- [ ] 🏆 Platinum
<!--
This project is very active and we have a high turnover of pull requests.
Unfortunately, the number of incoming pull requests is higher than what our
reviewers can review and merge so there is a long backlog of pull requests
waiting for review. You can help here!
By reviewing another pull request, you will help raise the code quality of
that pull request and the final review will be faster. This way the general
pace of pull request reviews will go up and your wait time will go down.
When picking a pull request to review, try to choose one that hasn't yet
been reviewed.
Thanks for helping out!
-->
To help with the load of incoming pull requests:
- [ ] I have reviewed two other [open pull requests][prs] in this repository.
[prs]: https://github.com/home-assistant/core/pulls?q=is%3Aopen+is%3Apr+-author%3A%40me+-draft%3Atrue+-label%3Awaiting-for-upstream+sort%3Acreated-desc+review%3Anone
<!--
Thank you for contributing <3
Below, some useful links you could explore:
-->
[dev-checklist]: https://developers.home-assistant.io/docs/en/development_checklist.html
[manifest-docs]: https://developers.home-assistant.io/docs/en/creating_integration_manifest.html
[quality-scale]: https://developers.home-assistant.io/docs/en/next/integration_quality_scale_index.html
[docs-repository]: https://github.com/home-assistant/home-assistant.io
1 Like
e-raser
December 21, 2021, 10:04am
13
Oh wow. Never imagined this will happen. That’s great, fabulous news! One reason less to migrate to MySQL
e-raser
February 4, 2022, 12:33am
14
Any estimations when this will make it into a productive release?
Had a little incident (Fix corrupted / malformed SQLite database (home-assistant_v2.db) - #10 by e-raser ) and that unreliable database backup thing is still a major issue in my opinion.
tom_l
February 4, 2022, 12:46am
15
No, from what little I understand it is not a trivial fix. It will take some time.
rossk
June 4, 2022, 9:17pm
16
@tom_l Do you know if there is an event I can listen for as a trigger for completion of a backup so as to trigger a restart of the mariadb addon?
TIA
@rossk if you’re worried about corruption, see my post Database corruption after full restore - is this “normal” still? - #7 by Sddawson above. As far as I know, it is all handled by mariadb.
rossk
June 5, 2022, 6:59am
18
Well I am worried about it as I had to use restore from a backup I took 2 days ago and on restart was littered with mariadb / recorded errors, can’t start etc.
I ended up loosing my database completely.
I have spend the last 18 months gathering data and making daily backups and this is the first time I have ever had to restore from one and to say I am pissed would be a massive understatement.
I also discovered that my backups have grown from an average 350 mb to over 3gb, as for some reason following a recent home assistant update my automated partial backups have decided to select the media folder all by itself (suspect when they changed the media folder in a recent update).
I lost the whole day yesterday messing with home assistant.
So I am now looking for a way to know when a backup has completed but it seems this is not even possible and the whole backup thing is much neglected.
So having read the above, my own personal experience suggests it’s still an issue.
1 Like
That’s not good to hear! When your backups are taken, do you see the Mariadb messages in the log that are mentioned in the post I linked to? Did you try to restore from a previous backup, other than the latest one?
rossk
June 5, 2022, 9:15am
20
To be honest I never even looked for these as I never knew this was an issue until I was afflicted by it.
Yes tried a couple of partials and one full all with the same results.
It troubles me somewhat that I have been sitting pretty, thinking look at my well organised and managed backups - all the time never giving any thought as to their usefulness.
Having lost my data now, I am going to investigate the best ways of backing up and clearly simply relying on HA backup function is not sufficient at present.
Thanks for the input
rossk
June 5, 2022, 9:35am
21
OK, just checked last nights backup and can see the following on the mariadb logs:
[00:30:03] INFO: Lock tables using mariadb client…
So at this stage have no reason to think that this was not happening previously!
Yes, I’ve been “sitting pretty” for ages now too (or perhaps not). Could be worth posting in the thread I linked, or some other Mariadb area.
Answer to topic question: “No, it’s likely not normal (anymore)”. Because of a restore experience:
Seems this is working meanwhile. Had to restore a HA backup and database looks fine so far. Details @ Fix corrupted / malformed SQLite database (home-assistant_v2.db) - #23 by e-raser .
e-raser
October 1, 2022, 12:34pm
24
