The PiTR recoverer code assumes that xtrabackup user password is not changed between binlog files, but if the password is modified mysql cli is not accepting the old password.
The test case: 1. Create pxc cluster 2. generate some changes 3. modify xtrabackup user password 4. execute FLUSH BINARY LOGS to start a new binlog file 5. make more changes, flush binary logs and wait until pitr uploads the file 6. Try to restore to the latest data shapshot using pitr
Environment
None
AFFECTED CS IDs
CS0037568
Activity
Pavel Tankov February 13, 2024 at 8:22 PM
ege.gunes January 12, 2024 at 8:04 AM
it’d be nice if we document this init file feature
ege.gunes January 12, 2024 at 8:03 AM
As part of this fix we also implemented a new mechanism using MySQL’s init file feature.
Problem: If cluster is restored to a backup which has different passwords for users, operator fails to reconcile the cluster because it can't connect to database using the passwords in secrets.
Solution: MySQL allows us to run statements on startup by passing --init-file=init.sql. We're using this mechanism to apply latest passwords to database.
When user updates a password in the secret, operator creates a new secret called <clusterName>-mysql-init and puts the required ALTER USER statement in it. If this secret already exists, operator appends the statement.
When MySQL pods start, they mount this init secret if it exists and use it in --init-file. This way we ensure database has the latest passwords even if it's just restored to a backup with different passwords.
When a new backup is created and successfully finished, operator deletes init secret. Therefore if you have a situation like following, you'll need to do some manual work:
In this example passwords are changed after backup2 finished and then three new backups are created. Cluster doesn't have an init secret. If you want to restore to backup2, you need to create the init secret by your own with the latest passwords:
https://github.com/percona/percona-xtradb-cluster-operator/blob/v1.12.0/cmd/pitr/recoverer/recoverer.go#L342
The PiTR recoverer code assumes that xtrabackup user password is not changed between binlog files, but if the password is modified mysql cli is not accepting the old password.
The test case:
1. Create pxc cluster
2. generate some changes
3. modify xtrabackup user password
4. execute FLUSH BINARY LOGS to start a new binlog file
5. make more changes, flush binary logs and wait until pitr uploads the file
6. Try to restore to the latest data shapshot using pitr