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.
Best Answer
When it comes to an empty binary log, it is version specific:
120
in MySQL 5.6107
in MySQL 5.5106
in MySQL 5.198
in MySQL 4.1/5.0I 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 DBJun 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 outageFeb 04, 2011
: MySQL master binlog corruptionIn 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
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.