When you are using mysqldump, you can get the binary logs coordinates of the backup at the point-in-time of the the mysqldump's start.
Just add the --master-data option
mysqldump --master-data=2 --flush-logs --single-transaction...
Using --master-data=2
will record the binary log filename and position in the form of a CHANGE MASTER TO
command. It is recorded as a comment.
Using --master-data=1
will also record the binary log filename and position in the form of a CHANGE MASTER TO
command. It is not recorded as a comment, but as a command that will be executed when you reload the mysqldump.
In both cases, you can always find that CHANGE MASTER TO
command on line 23 of the dump. I mentioned this before in my old post MySQL Exec_Master_Log_Pos value greater than Read_Master_Log_PosMySQL Exec_Master_Log_Pos value greater than Read_Master_Log_Pos under the heading "BUT WAIT..."
To retrieve that line from a mysqldump file named myfullbackup.sql
, simply run
head -23 myfullbackup.sql | tail -1 > MasterLogFilePos.sql
and the CHANGE MASTER TO
command will be in it
If you run the mysqldump with
mysqldump --master-data=2 --flush-logs --single-transaction...
the Master will continue allowing reads and writes to all InnoDB tables.