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


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.


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

  • Exactly what I was looking for, thanks!
    – BjornW
    Commented May 7, 2023 at 8:56

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.