In order to determine whether a database backup is needed or not, I'm trying to see whether a mysql binlog file has any data in it. (During a backup, we make a fresh binlog file and record the filename. Then later, we can check the contents of that binlog to see if anything has happened since the last backup.)

Is there any way to know when a binlog file is actually empty? Perhaps using the mysqlbinlog command?

I would check the position from SHOW MASTER STATUS, but the position seems to vary with the mysql version... is it constant within a version? My version of mysql (5.6.11) starts a new binary log at position 120.

An alternative approach would be to record the time that the binlog was created at and check to see if there were any changes made after that, but it makes me nervous relying on the speed of the backup process like that.

  • Questions : Do you backup using mysqldump ??? Are your tables using InnoDB ??? Jun 11, 2015 at 3:39
  • How often do you take a backup?
    – Rick James
    Jun 12, 2015 at 3:51

1 Answer 1


When it comes to an empty binary log, it is version specific:

  • 120 in MySQL 5.6
  • 107 in MySQL 5.5
  • 106 in MySQL 5.1
  • 98 in MySQL 4.1/5.0

I have discussed this many times in the DBA StackExchange

In your case, you need to run just one command


This tells you the current binary log mysqld is writing to and the postition. It just so happens, the log position is also the filesize.

Here is an example how to check for an empty binlog before launching a backup

# Find out the MySQL Version
VERS=`mysql ${MYSQL_CONN} -ANe$"${SQL}"`
MYSQL_VERSION=`echo "${VERS}" | sed 's/\./ /g' | sed 's/ /./' | awk '{print $1}'`
case "${MYSQL_VERSION}" in
    "5.0") EMPTY_BINLOG_SIZE=98  ;;
    "5.1") EMPTY_BINLOG_SIZE=106 ;;
    "5.5") EMPTY_BINLOG_SIZE=107 ;;
    "5.6") EMPTY_BINLOG_SIZE=120 ;;
# Get the Current Master Log's Position
LOGPOS=`mysql ${MYSQL_CONN} -ANe$"${SQL}" | awk '{print $2}'`
# Compare log position to an empty log size
if [ ${LOGPOS} -eq ${EMPTY_BINLOG_SIZE} ]; then OK_TO_BACKUP=1 ; fi
if [ ${OK_TO_BACKUP} -eq 1 ]; then



If all your tables are using the InnoDB Storage Engine, you can dump the data at a point-in-time, capture the log file and position, and still allow INSERTs, UPDATEs, and DELETEs all at the same time (please do not do any DDL during the backup). When you use mysqldump with the options --single-transaction and --master-data=2, it will write the log file/position on line 23.

I just mentioned this 6 days ago : Master/Master/Slave Replication

That way, there is no need to wait for an empty binary log with no application downtime.

  • I was hoping there might be some mysql command to do it, but thank you for the list of versions and line numbers. I may end up doing that. (I'm using MyISAM by the way.) Jun 11, 2015 at 5:50

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.