Percona XtraBackup moved to https://jira.percona.com/projects/PXB

GTID position not recorded when --binlog-info=AUTO

Bug #1651505 reported by Jericho Rivera
14
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona XtraBackup moved to https://jira.percona.com/projects/PXB
Status tracked in 2.4
2.3
Fix Released
Wishlist
Sergei Glushchenko
Percona XtraBackup moved to https://jira.percona.com/projects/PXB 2.3.9
2.4
Fix Released
Wishlist
Sergei Glushchenko
Percona XtraBackup moved to https://jira.percona.com/projects/PXB 2.4.8

Bug Description

Since PXB 2.3.2 and 2.4.x, when taking a backup using xtrabackup binary (eg in v2.4.5) instead of innobackupex the GTID position is not recorded unless you specify --binlog-info=ON during backup phase.

# cat xtrabackup/xtrabackup_binlog_info
mysql-bin.000009 194

# cat xtrabackup/xtrabackup_info
uuid = 5b6db0a4-c6cb-11e6-807f-00163e7d7303
name =
tool_name = xtrabackup
tool_command = --user=root --password=... --backup --target-dir=/root/backups/xtrabackup/
tool_version = 2.4.5
ibbackup_version = 2.4.5
server_version = 5.7.16-10-log
start_time = 2016-12-20 15:45:37
end_time = 2016-12-20 15:45:39
lock_time = 0
binlog_pos =
innodb_from_lsn = 0
innodb_to_lsn = 252679027
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

PXB defaults to --binlog-info=AUTO based on the manual - https://www.percona.com/doc/percona-xtrabackup/2.4/advanced/lockless_bin-log.html.

During the prepare phase, xtrabackup creates the xtrabackup_binlog_info file but it does not have the GTID coordinates needed to setup replication on the slave.

How to repeat:
- Setup master 5.7.16 with GTID enabled
- execute `xtrabackup --backup --target-dir=/path`
- check `xtrabackup_info` and `binlog_pos=` line is empty
- run `xtrabackup --prepare --target-dir=path`, `xtrabackup_binlog_info` is created but no GTID coordinates

- do the same but use `innobackupex /path`, from the onset you'll see that `binlog_pos=` is populated in `xtrabackup_info` file and `xtrabackup_binlog_info` has proper GTID coordinates.

Not sure if this is a doc issue where we should notify users to use --binlog-info=ON when taking backups from GTID based servers or needs a code fix.

Tags: doc
Revision history for this message
Jericho Rivera (jericho-rivera) wrote :

Samples when innobackupex is used on the same server where xtrabackup was used to take a backup:

# cat innobackupex/2016-12-20_16-11-31/xtrabackup_binlog_info
mysql-bin.000009 194 d7922211-bf33-11e6-b1a8-00163e7d7303:1

# cat innobackupex/2016-12-20_16-11-31/xtrabackup_info
uuid = fa140bcf-c6ce-11e6-807f-00163e7d7303
name =
tool_name = innobackupex
tool_command = --user=root --password=... innobackupex/
tool_version = 2.4.5
ibbackup_version = 2.4.5
server_version = 5.7.16-10-log
start_time = 2016-12-20 16:11:31
end_time = 2016-12-20 16:11:34
lock_time = 0
binlog_pos = filename 'mysql-bin.000009', position '194', GTID of the last change 'd7922211-bf33-11e6-b1a8-00163e7d7303:1'
innodb_from_lsn = 0
innodb_to_lsn = 252679091
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

Revision history for this message
Jericho Rivera (jericho-rivera) wrote :

Marking as confirmed as this can be reproduced per - https://www.percona.com/forums/questions-discussions/percona-xtrabackup/46765-empty-binlog_pos-in-xtrabackup_info

Changed in percona-xtrabackup:
status: New → Confirmed
Revision history for this message
Sergei Glushchenko (sergei.glushchenko) wrote :

This page https://www.percona.com/doc/percona-xtrabackup/2.4/xtrabackup_bin/working_with_binary_logs.html clearly states that xtrabackup_binlog_info created during prepare.

Techincally, this page https://www.percona.com/doc/percona-xtrabackup/2.4/howtos/setting_up_replication.html is correct, but it needs to be updated to use xtrabackup, and then it should explain --binlog-info setting.

While we still can use file and position coordinates to setup GTID slave, it is not safe for MTS slaves.

tags: added: doc
Revision history for this message
Ovais Tariq (ovais-tariq) wrote :

@sergei, even if the xtrabackup_binlog_info file is created during prepare, yet it does not contain the GTID info. This is true even if xtrabackup chose to take a backup lock during the backup.

More info in https://bugs.launchpad.net/percona-xtrabackup/+bug/1684002

Revision history for this message
Sergei Glushchenko (sergei.glushchenko) wrote :

Hi Ovais,

With --binlog-info=AUTO when PS is being backed up xtrabackup_binlog_info created during prepare from information stored by InnoDB. It allows to avoid some locking. InnoDB does not store GTID info. Do you suggest to fallback to --binlog-info=ON when GTID used?

summary: - GTID position not recorded when --binlog-info=AUTO in PXB 2.4.5
+ GTID position not recorded when --binlog-info=AUTO
Revision history for this message
Ovais Tariq (ovais-tariq) wrote :

If GTID is used then the backup is not usable for the purpose of adding a new GTID slave. I would expect --binlog-info=ON to be used when user specifies the AUTO option and MySQL is configured with GTID. The user then can specifically request LOCKLESS by setting the --binlog-info option to LOCKLESS. innobackupex is doing a similar thing, it overrides the --binlog-info option that is passed down by the user.

Revision history for this message
Ovais Tariq (ovais-tariq) wrote :

Or the other option would be to store the GTID information in InnoDB similar to how the binlog coordinates are stored.

Revision history for this message
Sergei Glushchenko (sergei.glushchenko) wrote :

Ovais,

There is actually mysql.gtid_executed table in 5.7. Shouldn't server pick up correct GTID position from there?

Revision history for this message
Ovais Tariq (ovais-tariq) wrote :

Sergei,

I am running 5.6

Revision history for this message
Sergei Glushchenko (sergei.glushchenko) wrote :

  - https://github.com/percona/percona-xtrabackup/pull/370
  - https://github.com/percona/percona-xtrabackup/pull/371

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Duplicates of this bug

Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.