1

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 ??? – RolandoMySQLDBA Jun 11 '15 at 3:39
  • How often do you take a backup? – Rick James Jun 12 '15 at 3:51
2

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

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.

  • 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.) – AmadeusDrZaius Jun 11 '15 at 5:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.