Provision data with xtrabackup

[ebuildy]

Proposal

run a fresh MySQL with data from a xtrabackup

Use-Case

For QA , we run MySQL with provisionned data from xtrabackup

Is this a feature you are interested in implementing yourself?

Yes

Anything else?

As a workaround, we create the PVC from a volume snapshot containing the xtrabackup

[spron-in]

Hey @ebuildy ,

could you please tell us more about it?

I would see it as the following flow:

1. Create backup with xtrabackup
2. Create fresh cluster
3. Restore from (1) backup to this fresh (2) cluster

So it is a couple of steps, yes.
We have bootstrapping from backup in our roadmap though (assuming this is what you are looking for).

[ebuildy]

Yes bootstrap is exactly the right word here!

Currently I am using argo-workflow to retrieve the disk-snapshot on azure, then create a VolumeSnapshot and a PVC.

I did a small PR #1624 to configure PVC dataSource.

Here our argo-workflow:

This is deployed with argocd, pxc-size = 0 to prevent PVC creation, I patch the PXC object pxc-size = 1, wait for mysql ready, then run percona users creation SQL query.

[johnlaur]

We have bootstrapping from backup in our roadmap though (assuming this is what you are looking for).

+1 very interested!

We deploy a new cluster then create a restore job to deploy a selected backup image to the cluster. As the backups are made on a a foreign cluster, they lack the users/grants required by pxc-operator.

Please, if possible, consider an option to restore operator grants defined in the environment secrets to the database when bootstrapping a deployment from a backup or when performing a restore.

Quoted from https://docs.percona.com/percona-operator-for-mysql/pxc/backups-restore-to-new-cluster.html

When restoring to a new Kubernetes-based environment, make sure it has a Secrets object with the same user passwords as in the original cluster. More details about secrets can be found in System Users. The name of the required Secrets object can be found out from the spec.secretsName key in the deploy/cr.yaml (cluster1-secrets by default).

This seems conceptually 'backwards' from what one might expect -- the secrets in the environment should be the source of truth, perhaps even to the point where they should be tested and (re)set by pxc-operator when found to be missing on a deployed cluster.

[ebuildy]

Yeah I agree, the percona users setup is ... no sense

https://github.com/percona/percona-xtradb-cluster-operator/blob/main/build/pxc-entrypoint.sh#L398

In my opinion, the operator:

• must create all percona users it-self
• reconciliate root password

With my solution, we are able to create mysql DB with 500Go of data in a few seconds!

[johnlaur]

This is deployed with argocd, pxc-size = 0 to prevent PVC creation, I patch the PXC object pxc-size = 1, wait for mysql ready, then run percona users creation SQL query.

It seems we are both gravitating to something functionally equivalent to a 'post-restore' or 'pre-start' hook in order to fix the grants.

I would be most interested in hearing from Percona before exploring strategies to remedy this. If they agree that the operator should be responsible for reconciling the users/grants to those specified by the environment, that would imply a much different approach than attempting to reconcile error conditions through hooks or Jobs.

Regardless of how this discussion develops, I believe that a solution to easily restore pxc-operator grants onto an operator-managed cluster is an essential capability that is not currently provided by the pxc-operator. This is just my understanding, and maybe I have missed something explicit or implied in the documentation.

Based on feedback from @ebuildy I will probably try to extend my grant restore script generator into a k8s Job spec that can be run while the operator is configured for pxc-size=0. This job would start a single pxc node and restore the grants using MySQL's init file mechanism. Such a thing would be capable of restoring control of a deployed database to pxc-operator in case of trouble. I do not want to duplicate work if something like this already exists, and I just haven't dug deep enough to find it though.

[ebuildy]

https://github.com/percona/percona-xtradb-cluster-operator/blob/main/build/pxc-entrypoint.sh#L398

Well, there is already "reconcile" users feature here https://github.com/percona/percona-xtradb-cluster-operator/blob/main/pkg/controller/pxc/users.go#L47

Yes I use argo workflow to setup percona users.

[spron-in]

Hey @ebuildy @johnlaur - do you think we can jump into a call with our eng team and discuss this?
We don't have any particular community hours, but I can set smth up this or next week.
How about 1 PM UTC tomorrow (Tuesday 27 Feb)?