Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

date option for PITR doesn't restore up to selected time

Description

When I try to restore with PITR up to selected time it seems to restore all the transactions regardless of the time I have specified.

What I did:
1. insert data with sysbench
2. create backup1
3. do manually following in mysql:
create database tomislav;
use tomislav;
create table korisnik (a int primary key);
insert into korisnik values (1);
insert into korisnik values (2);
insert into korisnik values (3);
4. want to restore up to "create table korisnik..." so that when I restore I see that table is created but empty, but what I got is table with all 3 entries

Decoded binlog is attached to ticket.
This is the restore object that I used:

apiVersion: pxc.percona.com/v1 kind: PerconaXtraDBClusterRestore metadata: name: restore-pitr-aws-date spec: pxcCluster: cluster1 backupName: backup1 pitr: type: date date: "2021-01-05 16:27:43" backupSource: storageName: "s3-us-west-binlog"

This is database content:

mysql> show tables; +--------------------+ | Tables_in_tomislav | +--------------------+ | korisnik | +--------------------+ 1 row in set (0.00 sec) mysql> select * from korisnik; +---+ | a | +---+ | 1 | | 2 | | 3 | +---+ 3 rows in set (0.00 sec)

log of pitr restore object:

$ k logs pitr-job-restore-pitr-aws-date-cluster1-nstc4 2021/01/05 16:19:57 run recover 2021/01/05 16:20:28 working with binlog_1609858032_cad5efeff9d6252e1af167f508a03fc5 2021/01/05 16:20:49 working with binlog_1609858070_298d3118159ea70095b0787ce4d50964 2021/01/05 16:20:49 working with binlog_1609858131_0a58a0692eac1dac04569cab7e40faa3 2021/01/05 16:20:49 working with binlog_1609858197_244dc0b611c668f266887006f9c4b584 2021/01/05 16:20:49 working with binlog_1609858816_fe62c3c27925b32852fa66c6693fd425 2021/01/05 16:20:50 working with binlog_1609858877_3c594ebb5d06072c3c198f1d32bd779b 2021/01/05 16:21:04 working with binlog_1609858942_100fbf7c71f8e8683b9eb950bc459425 2021/01/05 16:21:13 working with binlog_1609859277_0d3a6a2b901c08d72fb31006a178c6de 2021/01/05 16:21:13 working with binlog_1609860322_b0e30e5b2b27414ce0e2513275efeadb 2021/01/05 16:21:14 working with binlog_1609860383_445bd3e308ccd47458406bc0d88589fc 2021/01/05 16:21:29 working with binlog_1609860448_7708854480b729527670e2ef29ffd4da

This is the environment of pitr restore object:

Environment: S3_BUCKET_URL: tomislav-backup/cluster1-2021-01-05-15:04:03-full ENDPOINT: https://s3.us-west-2.amazonaws.com DEFAULT_REGION: us-west-2 ACCESS_KEY_ID: <set to the key 'AWS_ACCESS_KEY_ID' in secret 'my-cluster-name-backup-s3'> Optional: false SECRET_ACCESS_KEY: <set to the key 'AWS_SECRET_ACCESS_KEY' in secret 'my-cluster-name-backup-s3'> Optional: false PXC_SERVICE: cluster1-pxc PXC_USER: xtrabackup PXC_PASS: <set to the key 'xtrabackup' in secret 'my-cluster-secrets'> Optional: false BINLOG_S3_ENDPOINT: https://s3.us-west-2.amazonaws.com BINLOG_S3_REGION: us-west-2 BINLOG_ACCESS_KEY_ID: <set to the key 'AWS_ACCESS_KEY_ID' in secret 'my-cluster-name-backup-s3'> Optional: false BINLOG_SECRET_ACCESS_KEY: <set to the key 'AWS_SECRET_ACCESS_KEY' in secret 'my-cluster-name-backup-s3'> Optional: false PITR_RECOVERY_TYPE: date BINLOG_S3_BUCKET_URL: tomislav-backup/binlogs PITR_GTID_SET: PITR_DATE: 2021-01-05 16:27:43 XB_USE_MEMORY: 750000000

Backup options in cr.yaml:

backup: image: perconalab/percona-xtradb-cluster-operator:master-pxc8.0-backup pitr: enabled: true storageName: s3-us-west-binlog timeBetweenUploads: 60 storages: s3-us-west: type: s3 s3: bucket: tomislav-backup credentialsSecret: my-cluster-name-backup-s3 region: us-west-2 endpointUrl: https://s3.us-west-2.amazonaws.com s3-us-west-binlog: type: s3 s3: bucket: tomislav-backup/binlogs credentialsSecret: my-cluster-name-backup-s3 region: us-west-2 endpointUrl: https://s3.us-west-2.amazonaws.com

If I manually use mysqlbinlog on the binlog like:

./mysqlbinlog binlog_1609860448_7708854480b729527670e2ef29ffd4da --base64-output=decode-rows --verbose --stop-datetime="2021-01-05 16:27:43"

I can see that it stops on "create database tomislav;" query.

Environment

None

Attachments

1
  • 05 Jan 2021, 05:02 PM

Smart Checklist

Activity

Tomislav Plavcic January 8, 2021 at 3:29 PM

Closed because I was not checking correctly.

Not a Bug

Details

Assignee

Reporter

Labels

Time tracking

1h logged

Priority

Smart Checklist

Created January 5, 2021 at 5:23 PM
Updated March 5, 2024 at 6:00 PM
Resolved January 8, 2021 at 3:28 PM

Flag notifications