How to retrieve master log position and master log file in shell script

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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