Is it possible to gain the precise position of a live mysql database when performing full backup

Question

I have a production database that I usually run fullbackups on once every week. I have bin logs turned on for incremental backups.

The production server is on the west coast while business hours are on the east coast.

When I perform the fullback up I query the master for is log and position. I put these values in the filename of the zip that I compress the databases to.

I'm having difficulties using mysqlbinlog to create incremental sql file from a certain position that I know the backup has ended at.

Would running Show Master Status then then running mysqldump --flush-logs --single-transaction... suffice to get accurate information in order to restore.

I know the ideal setup is to backup a slave database since locking won't hurt the production environment but at the moment I haven't made the switch.

Am I not getting the desirable results because I'm not locking all the tables?

Thanks in advance

EDIT

I realized with the help of RolandoMySQLDBA that I wasn't pointing to the appropriate binlog file. I was instead pointing to the whole directory binlog/bin-log.[0-9]* > sql.txt. So with just the position I was hitting logs that started much earlier than desired. So I changed my script to pull the appropriate binlog file as well as the position and that worked like a charm.

Thanks

Answer 1

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