Binary Log Backups

Version 7 by David Wartell
on Dec 01, 2008 13:58.

<< Changes from 6 to 7

compared with

Current by David Wartell
on Dec 01, 2008 13:59.

Key

This line was removed.

This word was removed. This word was added.

This line was added.

Changes (4)

View Page History

{page-title}{excerpt}

{excerpt}

How Do MySQL Binary Log Backups Work?{excerpt}

{excerpt}

MySQL [Binary Logs|http://dev.mysql.com/doc/refman/5.0/en/binary-log.html] contain a record of each SQL query executed against the database that changes data since the logs where last flushed (new log file started).  MySQL Binary Logs must be enabled by passing the \--log-binoption to mysqld. The log contains queries like UPDATE and DELETE.  The log does _not_ record SELECT or SHOW queries for example because they do not change the database.MySQL Binary Logs are some times confused with the InnoDB binary transaction log.  InnoDB uses a binary log to journal changes to the InnoDB table space file(s) as a protection from crashes to protect table space data integrity.  When \--log-bin is enabled even InnoDB transactions are written to the binary transaction log used for replication or restores.

...

h5. How Do MySQL Binary Log Backups Work?

If you have binary logging enabled then your MySQL binary log files might look like:
{code}
localhost-bin.000001
localhost-bin.000002
localhost-bin.000003
localhost-bin.index
{code}
For each database and table the backup program executes:
# [LOCK TABLES|http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html]
# [FLUSH LOGS|http://dev.mysql.com/doc/refman/5.0/en/flush.html]
# [SHOW CREATE TABLE|http://dev.mysql.com/doc/refman/5.0/en/show-create-table.html] _table name_
# [SELECT * INTO OUTFILE|http://dev.mysql.com/doc/refman/5.0/en/select.html] _temporary file _
# Write the contents of the temporary file to the end of the dump file
# [UNLOCK TABLES|http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html]

Note: see [http://dev.mysql.com/doc/refman/5.0/en/backup-policy.html]

*How the FLUSH LOGS Query Works*

When the [FLUSH LOGS|http://dev.mysql.com/doc/refman/5.0/en/flush.html] query is executed MySQL starts a new binary log file to record queries in.  So now the binary log files look like:
{code}
localhost-bin.000001
localhost-bin.000002
localhost-bin.000003
localhost-bin.000004
localhost-bin.index
{code}
Incremental queries made AFTER the backup operation appear in the localhost-bin.000004 binary log file.

*Restoring with Binary Logs*

To restore from a point in time using binary logs you would:
# Restore the database from the last SQL Dump completed before the desired recovery point. 
# Use [mysqlbinlog|http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html] to restore to the desired point in timewhere N is the log entry number you want to restore up to.

{code}
mysqlbinlog --stop-position=Nlocalhost-bin.000004 | mysql
{code}

h5. Advantages

# Allows point-in-time restore right down to individual queries.

h5. Disadvantages

# Requires a Full backup be done periodically  There is no way to do an incremental backup with a SQL Dump.  This means a backup can be very time consuming especially on larger databases.
 
# Holds a global read lock on all tables blocking writes from other connections for the duration of the full backup.  Locking can be optional.  If locking is not performed there is no consistency in the backup.
 
# Restoring only desired databases or tables requiring editing the SQL Dump file before restoring form it.
 
# Restoring to point in time with the mysqlbinlog utility can be complicated.

h3.

Search Current Space

Binary Log Backups

Changes (4)