PiTR restore fails if xtrabackup user password is changed in binary log files
Description
Environment
AFFECTED CS IDs
Activity
Pavel Tankov February 13, 2024 at 8:22 PM
bash-4.4$ cat /etc/mysql/init-file/init.sql
SET SESSION wsrep_on=OFF;
SET SESSION sql_log_bin=0;
ALTER USER 'xtrabackup'@'%' IDENTIFIED BY 'bahur';
ALTER USER 'xtrabackup'@'%' IDENTIFIED BY 'LbKQ7B48gegJYvprYMvB%!';ege.gunes January 12, 2024 at 8:04 AM
@dmitriy.kostiuk 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:
NAME CLUSTER STORAGE DESTINATION STATUS COMPLETED AGE
backup1 cluster1 fs-pvc pvc/xb-backup1 Succeeded 23m 24m <--passwords are different
backup2 cluster1 fs-pvc pvc/xb-backup2 Succeeded 18m 19m <--passwords are different
backup3 cluster1 fs-pvc pvc/xb-backup3 Succeeded 13m 14m
backup3 cluster1 fs-pvc pvc/xb-backup4 Succeeded 8m53s 9m29s
backup4 cluster1 fs-pvc pvc/xb-backup5 Succeeded 3m11s 4m29sIn 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:
$ cat <<EOF | base64 --wrap=0
ALTER USER 'root'@'%' IDENTIFIED BY '<latestPass>';
ALTER USER 'root'@'localhost' IDENTIFIED BY '<latestPass>';
ALTER USER 'operator'@'%' IDENTIFIED BY '<latestPass>';
ALTER USER 'monitor'@'%' IDENTIFIED BY '<latestPass>';
ALTER USER 'clustercheck'@'localhost' IDENTIFIED BY '<latestPass>';
ALTER USER 'xtrabackup'@'%' IDENTIFIED BY '<latestPass>';
ALTER USER 'xtrabackup'@'localhost' IDENTIFIED BY '<latestPass>';
ALTER USER 'replication'@'%' IDENTIFIED BY '<latestPass>';
EOF
$ kubectl apply -f - <<EOF
apiVersion: v1
kind: Secret
type: Opaque
metadata:
name: cluster1-mysql-init
data:
init.sql: <base64encodedstring>
EOF
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.
for i, binlog := range r.binlogs { remaining := len(r.binlogs) - i log.Printf("working with %s, %d out of %d remaining\n", binlog, remaining, len(r.binlogs)) ... err = os.Setenv("MYSQL_PWD", os.Getenv("PXC_PASS")) if err != nil { return errors.Wrap(err, "set mysql pwd env var") } cmdString := "mysqlbinlog --disable-log-bin" + r.recoverFlag + " - | mysql -h" + r.db.GetHost() + " -u" + r.pxcUser cmd := exec.CommandContext(ctx, "sh", "-c", cmdString)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