How to determine if a mysql binary log file is empty?

Question

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.

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

• Nov 19, 2014 : Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB
• Jun 26, 2013 : Is it possible to convert mysql binlog from statement format to row format?
• Oct 20, 2011 : How to handle the My SQL DB Master to Master replication when there was a disk space outage
• Feb 04, 2011 : MySQL master binlog corruption
• All my posts where I discuss this

In your case, you need to run just one command

SHOW MASTER STATUS;

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

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Find out the MySQL Version
#
SQL="SELECT 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 ;;
esac
#
# Get the Current Master Log's Position
#
SQL="SHOW MASTER STATUS"
LOGPOS=`mysql ${MYSQL_CONN} -ANe$"${SQL}" | awk '{print $2}'`
#
# Compare log position to an empty log size
#
OK_TO_BACKUP=0
if [ ${LOGPOS} -eq ${EMPTY_BINLOG_SIZE} ]; then OK_TO_BACKUP=1 ; fi
if [ ${OK_TO_BACKUP} -eq 1 ]; then
   ...
fi

GIVE IT A TRY !!!

SUGGESTION

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.