Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 2 down vote accepted

There is a cleaner approach

SMS=/tmp/show_master_status.txt
mysql -ANe "SHOW MASTER STATUS" > ${SMS}
CURRENT_LOG=`cat ${SMS} | awk '{print $1}'`
CURRENT_POS=`cat ${SMS} | awk '{print $2}'`
echo ${CURRENT_LOG} ${CURRENT_POS}

This logs into mysql only once.

share|improve this answer
    
Without a temp file: read CURRENT_LOG CURRENT_POS < <( mysql -BNe "SHOW MASTER STATUS"); echo CURRENT_LOG=$CURRENT_LOG CURRENT_POS=$CURRENT_POS –  Mircea Vutcovici Jun 10 at 18:03

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 :

mysql-bin.003751

to get master log position :

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

Result :

469262356

share|improve this answer
    
Absolutely correct.Can you tell me where are these files stored and how did you retrieve them –  Rudra Feb 18 '13 at 13:15
    
You can find these files in your mysql data folder. –  Kürşad DARA Feb 18 '13 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. –  Michael - sqlbot Feb 18 '13 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 –  Kürşad DARA Feb 19 '13 at 8:00

@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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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