I'm writing a shell script for point in time recovery for MySQL database.For the below statement I need to specify the start position and end position of a log file.

mysqlbinlog --start-position=368315 --stop-position=368316 /usr/local/mysql/data/mysql-bin.123456

How can I retrieve the log position and log file.

3 Answers 3


There is a cleaner approach

CURRENT_LOG=`cat ${SMS} | awk '{print $1}'`
CURRENT_POS=`cat ${SMS} | awk '{print $2}'`

This logs into mysql only once.

  • Without a temp file: read CURRENT_LOG CURRENT_POS < <( mysql -BNe "SHOW MASTER STATUS"); echo CURRENT_LOG=$CURRENT_LOG CURRENT_POS=$CURRENT_POS Commented Jun 10, 2014 at 18:03

@RolandoMySQLDBA has given an excellent and accurate answer to the question you asked, but you didn't give the full context of what you're trying to do.

Reading between the lines of your other recent questions, it seems that you will try to correlate this to a backup for point-in-time recovery. If that is true, then what you actually need to be doing is using the --master-data=2 option when you're running mysqldump.

There is no such thing as "close enough" when it comes to binary log coordinates. You need precision.

This option, in conjunction with --single-transaction causes mysqldump to issue FLUSH TABLES (allows long-running transactions to finish) followed by FLUSH TABLES WITH READ LOCK (blocks all table writes including by the replication thread), then, while the entire server is blocking on the lock you just acquired, it issues SHOW MASTER STATUS to capture the precise binlog coordinates, which are then added to the beginning of the backup file as a comment. Then it issues START TRANSACTION WITH CONSISTENT SNAPSHOT before finally issuing UNLOCK TABLES (allowing normal activity to resume on the server again) and then it begins to generate the dump file.

This timing, assuming you're using InnoDB tables, ensures that your backup represents the exact state of your server at the exact binlog position shown, so that you can playback a binary log to roll your database forward from there.

The behavior I've described is from the source to mysqldump.c "version 10.13" which is the version that ships with MySQL Server 5.5.30.

Of course, if you're not using mysqldump for your backup, then the solution will vary, such as using the xtrabackup_binlog_info file created by XtraBackup. This will be more accurate than anything you will get by trying to read log coordinates from the server yourself.


You can get information about master log file and position with commands below:

to get master log file name :

mysql -e "show master status" -s | tail -n 1 | awk {'print $1'}

Result :


to get master log position :

mysql -e "show master status" -s | tail -n 1 | awk {'print $2'}

Result :


  • Absolutely correct.Can you tell me where are these files stored and how did you retrieve them
    – Rudra
    Commented Feb 18, 2013 at 13:15
  • You can find these files in your mysql data folder. Commented Feb 18, 2013 at 13:16
  • This is not entirely correct, because the two parameters need to be retrieved in a single query to the database. Otherwise, there exists a narrow window of time, each time the binlog rotates, when you get an entirely invalid answer because the position returned by the 2nd query would be from the next file, not the filename you retrieved during the 1st query. Commented Feb 18, 2013 at 20:44
  • Yes you're right @Michael-sqlbot. I am just giving some clue about his question. He can improve the command for his script Commented Feb 19, 2013 at 8:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.