Xtrabackup2.4.5 gtid and binlog position different between backup and prepare

[louis hust]

Hi,

I am using xtrabackup 2.4.5 to backup mysql5.7.16.

When backup, i got the following message :

170313 15:03:00 Backup created in directory '/data/received_files/percona-xtrabackup-2.4.5-Linux-x86_64/bin/xtrabackup_backupfiles/'
MySQL binlog position: filename 'mysql-bin.000011', position '134796', GTID of the last change 'fbcdf0a7-f280-11e6-8a0f-005056a03e27:1-4805'
170313 15:03:00 [00] Streaming backup-my.cnf
170313 15:03:00 [00] ...done
170313 15:03:00 [00] Streaming xtrabackup_info
170313 15:03:00 [00] ...done
xtrabackup: Transaction log of lsn (3254817596) to (3254817605) was copied.
170313 15:03:00 completed OK!

As you see, the binlog position is mysql-bin.000011 @134796, with GTID 1-4805,
But when i prepare the backup,get the following output:

InnoDB: Doing recovery: scanned up to log sequence number 3254817605 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 3254817605 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 133205, file name mysql-bin.000011
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

So why the binlog file postion is different between backup and prepare?

After prepare, I check the backup database's gtid_executed:

mysql> SELECT @@global.gtid_executed;
+---------------------------------------------+
| @@global.gtid_executed |
+---------------------------------------------+
| fbcdf0a7-f280-11e6-8a0f-005056a03e27:1-4788 |
+---------------------------------------------+

But in the :xtrabackup_binlog_info

mysql-bin.000011 134796 fbcdf0a7-f280-11e6-8a0f-005056a03e27:1-4805

So loss some transactions?

[Jericho Rivera (jericho-rivera)]

Please share the full xtrabackup command used for backup and prepare stages. You can remove user/password we just need the full options used at each stage.

[louis hust (hdchild)]

netcat -l -p 7016 | tar -xf - -C /data/received_files/mysql_install
./xtrabackup \
--defaults-file=/usr/local/mysql/my.cnf \
--user=root --password=mysql \
--ftwrl-wait-timeout=60 \
--ftwrl-wait-query-type=all \
--ftwrl-wait-threshold=5 \
--kill-long-queries-timeout=0 \
--kill-long-query-type=all \
--safe-slave-backup --slave-info \
--stream=tar \
--backup 2>/usr/local/mysql/backup.log | netcat 122.17.189.56 7016

./xtrabackup \
--defaults-file=/usr/local/mysql/my.cnf \
--use-memory=4G \
--target-dir=/data/received_files/mysql_install \
--prepare &> /usr/local/mysql/prepare.log

./xtrabackup \
--defaults-file=/usr/local/mysql/my.cnf \
--target-dir=/data/received_files/mysql_install \
--cppy-back

[Shahriyar Rzayev (rzayev-sehriyar)]

Quick dummy test:

With Xtrabackup 2.4.6 and 5.7.17 PS:

# Backup

/home/shahriyar.rzaev/Percona_Xtrabackups/xb_2.4/usr/local/xtrabackup/bin/xtrabackup --defaults-file=/home/shahriyar.rzaev/sandboxes/rsandbox_Percona-Server-5_7_17/master/my.sandbox.cnf --user=jeffrey --password='msandbox' --target-dir=/home/shahriyar.rzaev/backup_dirs/ps_5.7_master//full/2017-03-20_08-38-19 --backup --host=localhost --port=20192 --binlog-info=ON

170320 08:38:53 Backup created in directory '/home/shahriyar.rzaev/backup_dirs/ps_5.7_master/full/2017-03-20_08-38-19/'
MySQL binlog position: filename 'mysql-bin.000035', position '194', GTID of the last change '00020192-1111-1111-1111-111111111111:1-68'

# Prepare
/home/shahriyar.rzaev/Percona_Xtrabackups/xb_2.4/usr/local/xtrabackup/bin/xtrabackup --defaults-file=/home/shahriyar.rzaev/sandboxes/rsandbox_Percona-Server-5_7_17/master/my.sandbox.cnf --user=jeffrey --password='msandbox' --target-dir=/home/shahriyar.rzaev/backup_dirs/ps_5.7_master//full/2017-03-20_08-38-19 --prepare --host=localhost --port=20192 --binlog-info=ON

InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 194, file name mysql-bin.000035

# from log:

$ cat xtrabackup_binlog_info
mysql-bin.000035 194 00020192-1111-1111-1111-111111111111:1-68

Result:
Could not spot the difference between backup/prepare

[Launchpad Janitor (janitor)]

[Expired for Percona XtraBackup because there has been no activity for 60 days.]

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-1432