MySQL Enterprise Backup Part III


MySQL Enterprise Backup (MEB) provides an easy way to perform incremental backups. You do have to know the log sequence number or LSN1 of the previous backup. And you can find the LSN in the meta/backup_variables.txt file from the previous backup.

MEB saves all the changes from the specified previous backup, see the mysqlbackup: INFO: Backup contains changes from lsn 14652513 to lsn 14659161 line from the following:


$ ./mysqlbackup --incremental -u root -p --incremental-backup-dir=/home/dstokes/foo2 --with-timestamp --start-lsn=14652512 backup

MySQL Enterprise Backup version 3.6.0 [2011/07/01]
Copyright (c) 2003, 2011, Oracle and/or its affiliates. All Rights Reserved.

INFO: Starting with following command line ...
./mysqlbackup --incremental -u root -p
--incremental-backup-dir=/home/dstokes/foo2 --with-timestamp
--start-lsn=14652512 backup

Enter password:
INFO: Got some server configuration information from running server.

IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'backup' run mysqlbackup
prints "mysqlbackup completed OK!".

--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /usr/local/mysql/data/
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
innodb_log_files_in_group = 2
innodb_log_file_size = 67108864

--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /home/dstokes/foo2/2011-11-02_14-42-29/datadir
innodb_data_home_dir = /home/dstokes/foo2/2011-11-02_14-42-29/datadir
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /home/dstokes/foo2/2011-11-02_14-42-29/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 67108864

mysqlbackup: INFO: Unique generated backup id for this is 13202629492467990
mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
mysqlbackup: INFO: System tablespace file format is Antelope.
mysqlbackup: INFO: Found checkpoint at lsn 14655831.
mysqlbackup: INFO: Starting log scan from lsn 14655488.
111102 14:42:29 mysqlbackup: INFO: Copying log...
111102 14:42:29 mysqlbackup: INFO: Log copied, lsn 14655831.
We wait 1 second before starting copying the data files...
111102 14:42:30 mysqlbackup: INFO: Copying /usr/local/mysql/data/ibdata1 (Antelope file format).
mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
111102 14:42:30 mysqlbackup: INFO: Starting to lock all the tables....
111102 14:42:30 mysqlbackup: INFO: All tables are locked and flushed to disk
mysqlbackup: INFO: Opening backup source directory '/usr/local/mysql/data/'
111102 14:42:30 mysqlbackup: INFO: Starting to backup all files in subdirectories of '/usr/local/mysql/data/'
mysqlbackup: INFO: Backing up the database directory 'grimm'
mysqlbackup: INFO: Backing up the database directory 'mysql'
mysqlbackup: INFO: Backing up the database directory 'performance_schema'
mysqlbackup: INFO: Backing up the database directory 'test'
mysqlbackup: INFO: Backing up the database directory 'world'
mysqlbackup: INFO: Copying innodb data and logs during final stage ...
mysqlbackup: INFO: A copied database page was modified at 14655831.
(This is the highest lsn found on page)
Scanned log up to lsn 14659161.
Was able to parse the log up to lsn 14659161.
Maximum page number for a log record 605
111102 14:42:31 mysqlbackup: INFO: All tables unlocked
mysqlbackup: INFO: All MySQL tables were locked for 0.000 seconds
mysqlbackup: INFO: Backup contains changes from lsn 14652513 to lsn 14659161
111102 14:42:31 mysqlbackup: INFO: Incremental backup completed!
mysqlbackup: INFO: Backup created in directory '/home/dstokes/foo2/2011-11-02_14-42-29'

-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 14652513
End LSN : 14659161
-------------------------------------------------------------

mysqlbackup completed OK!

  1. The LSN is an arbitrary, ever-increasing value represents a point in time corresponding to operations recorded in the redo log. (This point in time is regardless of transaction boundaries; it can fall in the middle of one or more transactions.) It is used internally by InnoDB during crash recovery and for managing the buffer pool.
About these ads

4 Comments

Filed under MySQL Enterprise

4 responses to “MySQL Enterprise Backup Part III

  1. mahaveer

    how can i back up a single database (lets say “xyz” ) using mysql enterprise backup 3.7 . i mean what is the full command ? also the command to restore that one database to another server. i am new to mysql enetreprise backup. your help will be higly appreciated. i have to backup this database which is 25 GB in size and also to restore it, thats why i need proper working commands for both operations. ??????? waiting for the commands !!!!!

  2. Sanjay Manwani

    “You do have to know the log sequence number or LSN1 of the previous backup”
    You no longer have to track the LSN for sequential backups.
    From MEB 3.7.1, MEB can automatically get the LSN from the history table or from your last backup.
    See: https://blogs.oracle.com/MySQL/entry/meb

  3. Joyanta

    Hi,
    I was doing the same,but every time I did the incremental backup, the entire database is copied as taking Full backup .i identify those by viewing the size of full backup and incremental backup folder.And One of my database size is so large that is nearly 2 gb.Why is this happening??If I don’t add any data into my database,then the incremental process copies from the first. :(

  4. test

    Hi
    Please suggest mysql enterprise backup scheduling (automatic) in windows 2008 server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s