When exactly are the restore points for MariaDB PITR, and how do I find these?

Question

I will need 100% reliable point in time restore / PITR for medical examinations in Feb 2023. Critical data includes bitmap data of test-related notes, roughly 16GB over two days.

I have tested out a worst case scenario on an Azure MariaDB instance, in a mariadb database:

1. delete data
2. restore to a previous time

I performed a single deletion at
2022-11-11 16:18:37 (UTC)
of an entry for a user named "Bock".

Then via the Azure MariaDB Portal UI, I performed a Restore, requesting time
2022-11-11 04:54:17 (UTC)
as my restore time

Next I reviewed the data and saw that "Bock" had been deleted, and when I run the following query:
SELECT last_name,created_at,deleted_at FROM users WHERE id = 469;

the result is
| last_name | created_at | deleted_at |
+-----------+---------------------+---------------------+
| Bock | 2022-10-24 14:33:25 | 2022-11-11 16:18:37 |

The deletion time is after the requested restore point time. If the Restore service is "rounding up" to find the closest available restore point, I need to know that.
Can I get a list of the available restore points and their timestamps, maybe via a REST API?

Alongside that, is there any way to tweak or adjust when those Restore points occur?

Thanks

Answer 1

Ok, thanks to @GeethaThatipatri-MSFT for helping me get to the bottom of this. It turns out the PITR actually worked just fine. And I learned an important lesson for the future:

The azure hostname has to be changed in two places when cutting over to restored machine from a backup.

Example:
I started with a db on a VM called mariadb-abu-dev , intentionally deleted some data, then spun up a restored machine called mariadb-abu-dev-recover2.
The mistake I made was forgetting that Azure requires you to specify a connection user as user@server
So, when connecting to the "restored" machine, I connected using:

mysql \ -h mariadb-abu-dev-recover2.mariadb.database.azure.com \ -u abuadmin@mariadb-abu-dev \

Note that the -h host is correct, but the -u user has the wrong machine name.
In reality, I had created environment variables for all of this, so my actual connection command looked like this:

mysql -h $DB_HOST -u $DB_USERNAME -p

making it even harder to see what I had done wrong.
In future, the $DB_HOST needs to be changed (as I had done). And, the back-end of the user should have been changed -- (in this case, making it abuadmin@mariadb-abu-dev-recover2). The correct way to connect would have been:

mysql \ -h mariadb-abu-dev-recover2.mariadb.database.azure.com \ -u abuadmin@mariadb-abu-dev-recover2 \

Geetha and the team that looked into it said that:

...adding the @Testta to the end of my overall user, it is able to essentially 'override' what client thinks is the server provided in the user name...

I'm sure there are scenarios where this is helpful -- for me, it ended up being a footgun.

In sum, it's a straightforward fix, but it's yet another example of the danger of "multiple sources of truth", and something we'll have to be very careful of in production.

Thanks again to Geetha and the folks who helped sort this out!

Answer 2

Hi Peter,

Thanks for reaching out to Microsoft Q&A.

I couldn't find anything from Microsoft official docs or any other authors who have a way to find out the available azure restore points for maria db, the closest I could find something related to your question is the link below. That being said I believe there should be a way to get all these details and suggest you raise a support request to azure and if you got an answer request you to document here.

https://github.com/MicrosoftDocs/azure-docs/blob/main/articles/mariadb/concepts-backup.md

Please Upvote and Accept as answer if the reply was helpful, this will be helpful to other community members.

Answer 3

Thanks @Vinodh247-1375 , this is relevant information but it does not address my question.

There are two documents pages I've found that go somewhat toward an answer, but not all the way:
https://learn.microsoft.com/en-us/azure/mariadb/concepts-backup
(which you've pointed me to the original of in github's version control -- it appears that the published version on microsoft.com is up to date.)

Also:
https://learn.microsoft.com/en-us/azure/mariadb/howto-restore-server-portal

I've picked through both of these, and I have raised a support request with Azure. The folks at Azure tech support suggested that I post the question here.

My real business need is this -- there will be a two day period in late February 2023 during which my organization will be gathering critical data. We will need backups that can be restored quickly and reliably. Critical data includes bitmap data of test-related notes, collected during medical examinations, roughly 16GB over two days. During the actual examinations, the more frequently we can get snapshots and the more rapidly we could recover from a data loss, the better.

I'm exploring Azure / MariaDB as a solution, but I want to be sure it's fully reliable first.
According to your link, "A full database snapshot is performed daily."
Also, "Transaction log backups occur every five minutes."

The MariaDB web UI lets the user pick any arbitrary point in time when performing a "Restore", suggesting that it relies on transaction logs to seamlessly reconstruct the state of the database at any moment. But from the simple test I described in the original question, this did not work. The state of the data in the database on the newly-spun up "Restored" db server matched the current (corrupted) data from the pre-existing database in the original server.

Is Azure MariaDB cloud-hosted solution recommended for a situation like ours? That is, can we rely on the backup and point-in-time restore for very specific short intervals (minutes, ideally), or should we look for alternative ways to backup and restore?

Thanks