Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 30.1Mb
PDF (A4) - 30.3Mb
PDF (RPM) - 30.2Mb
EPUB - 7.8Mb
HTML Download (TGZ) - 7.6Mb
HTML Download (Zip) - 7.6Mb
HTML Download (RPM) - 6.6Mb
Eclipse Doc Plugin (TGZ) - 8.3Mb
Eclipse Doc Plugin (Zip) - 10.2Mb
Man Pages (TGZ) - 200.1Kb
Man Pages (Zip) - 311.5Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  Establishing a Backup Policy

7.3.1 Establishing a Backup Policy

To be useful, backups must be scheduled regularly. A full backup (a snapshot of the data at a point in time) can be done in MySQL with several tools. For example, MySQL Enterprise Backup can perform a physical backup of an entire instance, with optimizations to minimize overhead and avoid disruption when backing up InnoDB data files; mysqldump provides online logical backup. This discussion uses mysqldump.

Assume that we make a full backup of all our InnoDB tables in all databases using the following command on Sunday at 1 p.m., when load is low:

shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql

The resulting .sql file produced by mysqldump contains a set of SQL INSERT statements that can be used to reload the dumped tables at a later time.

This backup operation acquires a global read lock on all tables at the beginning of the dump (using FLUSH TABLES WITH READ LOCK). As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH statement is issued, the backup operation may stall until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables.

It was assumed earlier that the tables to back up are InnoDB tables, so --single-transaction uses a consistent read and guarantees that data seen by mysqldump does not change. (Changes made by other clients to InnoDB tables are not seen by the mysqldump process.) If the backup operation includes nontransactional tables, consistency requires that they do not change during the backup. For example, for the MyISAM tables in the mysql database, there must be no administrative changes to MySQL accounts during the backup.

Full backups are necessary, but it is not always convenient to create them. They produce large backup files and take time to generate. They are not optimal in the sense that each successive full backup includes all data, even that part that has not changed since the previous full backup. It is more efficient to make an initial full backup, and then to make incremental backups. The incremental backups are smaller and take less time to produce. The tradeoff is that, at recovery time, you cannot restore your data just by reloading the full backup. You must also process the incremental backups to recover the incremental changes.

To make incremental backups, we need to save the incremental changes. In MySQL, these changes are represented in the binary log, so the MySQL server should always be started with the --log-bin option to enable that log. With binary logging enabled, the server writes each data change into a file while it updates data. Looking at the data directory of a MySQL server that was started with the --log-bin option and that has been running for some days, we find these MySQL binary log files:

-rw-rw---- 1 guilhem  guilhem   1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem  guilhem         4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem  guilhem        79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem  guilhem       508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem  guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem  guilhem    998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem  guilhem       361 Nov 14 10:07 gbichot2-bin.index

Each time it restarts, the MySQL server creates a new binary log file using the next number in the sequence. While the server is running, you can also tell it to close the current binary log file and begin a new one manually by issuing a FLUSH LOGS SQL statement or with a mysqladmin flush-logs command. mysqldump also has an option to flush the logs. The .index file in the data directory contains the list of all MySQL binary logs in the directory.

The MySQL binary logs are important for recovery because they form the set of incremental backups. If you make sure to flush the logs when you make your full backup, the binary log files created afterward contain all the data changes made since the backup. Let's modify the previous mysqldump command a bit so that it flushes the MySQL binary logs at the moment of the full backup, and so that the dump file contains the name of the new current binary log:

shell> mysqldump --single-transaction --flush-logs --master-data=2 \
         --all-databases > backup_sunday_1_PM.sql

After executing this command, the data directory contains a new binary log file, gbichot2-bin.000007, because the --flush-logs option causes the server to flush its logs. The --master-data option causes mysqldump to write binary log information to its output, so the resulting .sql dump file includes these lines:

-- Position to start replication or point-in-time recovery from

Because the mysqldump command made a full backup, those lines mean two things:

  • The dump file contains all changes made before any changes written to the gbichot2-bin.000007 binary log file or newer.

  • All data changes logged after the backup are not present in the dump file, but are present in the gbichot2-bin.000007 binary log file or newer.

On Monday at 1 p.m., we can create an incremental backup by flushing the logs to begin a new binary log file. For example, executing a mysqladmin flush-logs command creates gbichot2-bin.000008. All changes between the Sunday 1 p.m. full backup and Monday 1 p.m. will be in the gbichot2-bin.000007 file. This incremental backup is important, so it is a good idea to copy it to a safe place. (For example, back it up on tape or DVD, or copy it to another machine.) On Tuesday at 1 p.m., execute another mysqladmin flush-logs command. All changes between Monday 1 p.m. and Tuesday 1 p.m. will be in the gbichot2-bin.000008 file (which also should be copied somewhere safe).

The MySQL binary logs take up disk space. To free up space, purge them from time to time. One way to do this is by deleting the binary logs that are no longer needed, such as when we make a full backup:

shell> mysqldump --single-transaction --flush-logs --master-data=2 \
         --all-databases --delete-master-logs > backup_sunday_1_PM.sql

Deleting the MySQL binary logs with mysqldump --delete-master-logs can be dangerous if your server is a replication master server, because slave servers might not yet fully have processed the contents of the binary log. The description for the PURGE BINARY LOGS statement explains what should be verified before deleting the MySQL binary logs. See Section, “PURGE BINARY LOGS Syntax”.

Download this Manual
PDF (US Ltr) - 30.1Mb
PDF (A4) - 30.3Mb
PDF (RPM) - 30.2Mb
EPUB - 7.8Mb
HTML Download (TGZ) - 7.6Mb
HTML Download (Zip) - 7.6Mb
HTML Download (RPM) - 6.6Mb
Eclipse Doc Plugin (TGZ) - 8.3Mb
Eclipse Doc Plugin (Zip) - 10.2Mb
Man Pages (TGZ) - 200.1Kb
Man Pages (Zip) - 311.5Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
User Comments
  Posted by santm on May 12, 2006
We had one problem where we had too many bin files. As our database was heavily used the file size was quite big there were too many of them.

Please be sure to have a policy where you remove your old transactional log files as mentioned in the last example.

I could not found the disk pace was mentioned in Binary LOG Manual page.

  Posted by Laurent DENIS on June 20, 2006
Here's a scrip i wrote to backup my db, using a user that's not "mysql".

There's an entry in the sudoers file to allow the user to read the log files

The backup (mysql) user has "super,reload,all" privileges + its password in .my.cnf

The backup path is on a NAS filesystem.

What's missing is a function to remove old full backups if needed.



setRead() {
# change properties on binlog path
sudo chmod o+rx $BINLOGPATH
sudo chmod o+r $BINLOGPATH/*

resetRead() {
# restore properties on binlog path
sudo chmod o-r $BINLOGPATH/*
sudo chmod o-rx $BINLOGPATH

copyBinlogs() {
# copy binlogs to archive dir
echo "Copying binlogs"
if [ ! -f $ARCHIVEPATH/$SFILE.gz ]
echo "- binlog $SFILE"

saveBinlogs() {
# move latest binlogs to savedir
echo "Saving binlogs"
SAVEDIR=`date +%Y%m%d`

# --- Main ---
case "$MODE" in

# Weekly backup
# - copy all bin logs to backup directory
# - clean path of backup directory
echo "Weekly backup"
DATE=`date +%Y%m%d`
mysqldump -u $MYSQLUSER --single-transaction --flush-logs --master-data=2 \
--all-databases --delete-master-logs | gzip > $ARCHIVEPATH/$SAVEDIR/full-$DATE.sql.gz

# Daily backup
# - flush logs
# - copy all bin logs to backup directory if not already done
echo "Mysql daily backup"
mysqladmin -u $MYSQLUSER flush-logs

echo "Usage: mysql_backup [full|incremental]"


  Posted by Alexey Zilber on March 16, 2009
You should never use --delete-master-logs as the warning specifies. The above script is quite dangerous if you have many replication slaves.

A much better alternative is to do
  Posted by Fred Nurk on July 8, 2009
Regarding copyBinlogs() - it may be wise to preserve the creation and modification times, with 'cp -p', so it is easy to order them correctly when recovering.
  Posted by Fred Nurk on July 9, 2009
further to the comment on delete-master-logs - see

In other words, delete-master-logs is bad news for versions before the fix for that bug went in.
  Posted by Quan Tong Anh on August 25, 2010
@Laurent DENIS: Are you sure you want to backup all binlog files in mysql-bin.index? I think you must exclude the latest file which has been created by the mysqladmin with --flush-log option. It will be copy at the next backup.

  Posted by Jorge Torralba on May 27, 2014
Here is a simplified script to purge old binary log files. Seems to work




MYSQLCOMMAND=`which mysql`

# Get log file info from slave
masterlogfile=`$MYSQLCOMMAND -u $USER -h $SLAVE -p$PASSWD -Bse 'show slave status \G' | grep -w 'Master_Log_File' | cut -f2 -d ":" `

# Get log file info from master
lastbin=`$MYSQLCOMMAND -u $USER -h $MASTER -p$PASSWD -Bse 'show binary logs \G' | grep Log_name | tail -1 | cut -f2 -d ":" `

# Remove spaces
lastbin=`echo $lastbin | sed 's/^ //' `

# Only purge if both master and slave have same log files
if [ $masterlogfile = $lastbin ]; then
sql="PURGE BINARY LOGS TO '$lastbin' ;"
$MYSQLCOMMAND -v -u $USER -h $MASTER -p$PASSWD -Bse "$sql"

# Show list of all binary log files after purge
$MYSQLCOMMAND -u $USER -h $MASTER -p$PASSWD -Bse 'show binary logs \G'


Sign Up Login You must be logged in to post a comment.