shlomi-noach
commented
10 months ago I am unable to reproduce, but I think this issue description leaves a lot of implementation freedom.
To avoid waiting days for reproduction, I used this script:
#!/bin/bash
vtctldclient BackupShard commerce/0
for f in {1..3}; do
echo "new full backup cycle"
for i in {1..12}; do
echo "new incremental backup cycle"
sleep 30
mysql -e "insert into corder values(rand()*1000000,rand()*1000000,rand()*1000000,rand()*1000000);"
mysql -e "insert into corder values(rand()*1000000,rand()*1000000,rand()*1000000,rand()*1000000);"
sleep 30
vtctldclient BackupShard commerce/0 --incremental-from-pos=auto
done
vtctldclient BackupShard commerce/0
doneWhich:
- Runs a full backup (using
BackupShard) - Runs 12 incremental backups in ~ 1 min interval
- Runs a full backup
- loop: run 12 incremental backups again..
Essentially turns 12h->12m, 1h->1m, 30min->1min
The runtime totals at about 36 minutes (more due to overhead). On my test host, the runtime ranged Dec 17 12:31:34 to Dec 17 13:07:51, or in RFC3339 format these are 2023-12-17T12:31:34Z to 2023-12-17T13:07:51Z.
My tablets are:
$ mysql -e "show vitess_tablets"
+-------+----------+-------+------------+---------+------------------+------------+----------------------+
| Cell | Keyspace | Shard | TabletType | State | Alias | Hostname | PrimaryTermStartTime |
+-------+----------+-------+------------+---------+------------------+------------+----------------------+
| zone1 | commerce | 0 | PRIMARY | SERVING | zone1-0000000100 | <redacted> | 2023-12-17T12:24:32Z |
| zone1 | commerce | 0 | REPLICA | SERVING | zone1-0000000101 | <redacted> | |
| zone1 | commerce | 0 | RDONLY | SERVING | zone1-0000000102 | <redacted> | |
+-------+----------+-------+------------+---------+------------------+------------+----------------------+I ran the following restores:
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T12:31:35Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T12:32:00Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T12:35:00Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T12:42:00Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T12:43:00Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T12:44:00Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T12:45:00Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T12:46:00Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T12:47:00Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T12:48:00Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T12:49:00Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T12:50:00Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T12:51:00Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T12:52:00Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T12:53:00Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T12:54:00Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T12:55:00Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T13:04:00Z" zone1-0000000101
vtctldclient RestoreFromBackup --restore-to-timestamp "2023-12-17T13:07:00Z" zone1-0000000101and all went well and without error.
Let's try to focus on the mysqlbinlog error you got:
mysqlbinlog command failed: exit status 1\nfailed to apply binlog file /tmp/restore-incremental-3212119968/vt/vtdataroot/vt_2469782763/bin-logs/vt-2548885007-bin.000091
Can you find anything in the mysqld logs? Is it possible mysqlbinlog is not in your path? (Can you check that mysqlbinlog is on the same path as mysqld?)
GenLN
mattlord
Overview of the Issue
Doing incremental(builtin method)/full(xtrabackup) method. Failing PITR using timestamp or position. K8S deployment with operator.
Reproduction Steps
Run full backup manually for the first time and then run cron with every 12h full backup and incrementals every hour. Add cron to insert random data into tables every 30min which will ensure incrementals wouldn't fail (they fail if there is no change in db from the last backup)
Try to PITR from timestamp
vtctldclient RestoreFromBackup --restore-to-timestamp "timestamp" tablet-aliasView the error
Binary Version
Operating System and Environment details
Log Fragments
Here is the log output when running PITR from timestamp:
https://gist.github.com/GenLN/7b9ba323aee7778390a9269379b16c2d