This section discusses how to determine which binary log coordinates are associated with a backup image. These coordinates represent the validity point of the backup and indicate the point in the binary log at which database changes begin subsequent to the backup.
Reasons you might be interested in these coordinates:
To perform point-in-time-recovery on a server after restoring a backup image, you need to know where to begin restoring incremental backups from the binary log.
If you set up a replication slave by populating it with a backup image from the master, you need to know where to tell the slave to begin reading the master binary log.
To find the binary log coordinates associated with a backup image, you can read the image itself or check the row for the backup operation in the backup log:
Use the mysqlbackup program with the
--summary
option to
examine the backup image and check the Binlog
coordinates
line in the output:
shell> mysqlbackup --summary image.bak
...
Binlog coordinates: binlog.002795:196
The output indicates that the coordinates are log file
binlog.002795
, position 196.
On the host where the backup was performed, if you know the
backup_id
value for the the backup
operation that created the image file, you can use it to check
the corresponding row in the backup_history
log. Suppose that the backup operation produced this result:
mysql> BACKUP DATABASE * TO '/tmp/data.bak';
+-----------+
| backup_id |
+-----------+
| 5513 |
+-----------+
The backup ID is 5513. Use that value to find the binary log coordinates for the backup operation:
mysql>SELECT binlog_file, binlog_start_pos
->FROM mysql.backup_history WHERE backup_id=5513;
+---------------+------------------+ | binlog_file | binlog_start_pos | +---------------+------------------+ | binlog.002795 | 196 | +---------------+------------------+
This example assumes that
backup_history_log
is set to
ON
and that the value of
log_backup_output
includes
the TABLE
destination. If the value
includes only FILE
logging, you must search
the backup history log file manually.