Nov 1, 2018

How to Obtain MySQL Replication Master Binary Log Coordinates

You need the master's current coordinates within its binary log in order to configure the slave to start the replication process at the correct point.

If you have existing data on your master that you want to synchronize on your slaves before starting the replication process, you must stop processing statements on the master, and then obtain its current binary log coordinates and dump its data, before permitting the master to continue executing statements. If you do not stop the execution of statements, the data dump and the master status information that you use will not match and you will end up with inconsistent or corrupted databases on the slaves.

To obtain the master binary log coordinates, follow these steps:

Start a session on the master by connecting to it with the command-line client, and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:

mysql> FLUSH TABLES WITH READ LOCK;
For InnoDB tables, FLUSH TABLES WITH READ LOCK also blocks COMMIT operations.

Warning
Leave the client from which you issued the FLUSH TABLES statement running so that the read lock remains in effect. If you exit the client, the lock is released.

In a different session on the master, use the SHOW MASTER STATUS statement to determine the current binary log file name and position:

mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000855
Position: 456413426
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

If the master has been running previously without binary logging enabled, the log file name and position values displayed by SHOW MASTER STATUS or mysqldump --master-data will be empty. In that case, the values that you need to use later when specifying the slave's log file and position are the empty string ('') and 4.

If you use MySQL Enterprise Backup (MEB) to backup MySQL, the Binary Log Coordinates can be shown:
  • At the end of the backup phase:
    150813 07:25:12 mysqlbackup: INFO: MySQL binlog position: filename binlog.000033, position 18446744073709551615

    -------------------------------------------------------------
       Parameters Summary                                      
    -------------------------------------------------------------
       Start LSN                  : 295451136                  
       End LSN                    : 295451242                  
    -------------------------------------------------------------
     
  • In meta/backup_variables.txt:
    shell$ grep binlog_position meta/backup_variables.txt
    binlog_position=binlog.000033:-1
     
  • During the apply-log operation or the apply-log phase of backup-and-apply-log or copy-back-and-apply-log:
    mysqlbackup: INFO: Last MySQL binlog file position 0 18446744073709551615, file name binlog.000033:-1
     

When using backup-and-apply-log the apply-log phase may report what looks like a correct binary log position if the backup was made without a connection to MySQL, however this should not be used.

CAUSE

MySQL Enterprise Backup can only determine the position in the binary log that is consistent for the backup when it is able to lock all tables while executing SHOW MASTER STATUS. Without connecting or with locking disabled -1 one is used instead to indicate an unknown position.
While the binary log file is specified this may not be correct in the case where a rotation of the binary log happens after completing backing up the InnoDB tables and before completing backing up non-InnoDB files.

There are several options to be able to get the correct binary log position:
  1. Ensure that MySQL Enterprise Backup is able to lock all tables while getting the binary log file and position.
  2. If MySQL was shut down while creating the backup, the binary log position of the backup will be the last position in the binary log file mentioned.
  3. If no changes were made to non-InnoDB tables during the backup, the correct binary log position can be found in the error log after restoring the backup and starting the restored instance, for example:
    ...
    2015-08-13 07:34:29 6992 [Note] InnoDB: The log file was created by mysqlbackup --apply-log at ibbackup 150813  7:27:35. The following crash recovery is part of a normal restore.
    2015-08-13 07:34:29 6992 [Note] InnoDB: The log sequence numbers 295451242 and 295451242 in ibdata files do not match the log sequence number 295451660 in the ib_logfiles!
    2015-08-13 07:34:29 6992 [Note] InnoDB: Database was not shutdown normally!
    2015-08-13 07:34:29 6992 [Note] InnoDB: Starting crash recovery.
    2015-08-13 07:34:29 6992 [Note] InnoDB: Reading tablespace information from the .ibd files...
    2015-08-13 07:34:29 6992 [Note] InnoDB: Restoring possible half-written data pages
    2015-08-13 07:34:29 6992 [Note] InnoDB: from the doublewrite buffer...
    InnoDB: Last MySQL binlog file position 0 816726, file name binlog.000033
    ...

    See the highlighted message.
    This works because InnoDB internally keeps track of the binary log file and position of its last committed transaction. However all non-InnoDB changes are not tracked this way.