MySQL Enterprise Backup User's Guide (Version 3.11.1)
In this example, we specify all required options on the command
line for illustration purposes. After testing and standardizing
the backup procedure, we could move some options to the MySQL
configuration file. The options specify connection information
for the database and the location to store the backup data. The
final option backup
specifies the
type of operation, because mysqlbackup can
perform several kinds of backup, restore, and pack/unpack
operations.
For this example, we specify the final option as
backup-and-apply-log
. This option
performs an extra stage after the initial backup, to bring all
InnoDB tables up-to-date with any changes that occurred during
the backup operation, so that the backup is immediately ready to
be restored. For backups of huge or busy databases, you might
split up these stages to minimize load on the database server.
That is, run mysqlbackup first with the
backup
option, transfer the backup to another
server, then run mysqlbackup with the
apply-log
option to perform the
final processing.
The output echoes all the parameters used by the backup operation, including several that are retrieved automatically using the database connection. The unique ID for this backup job is recorded in special tables that mysqlbackup creates inside the instance, allowing you to monitor long-running backups and view the results of previous backups. The final output section repeats the location of the backup data and provides the LSN values that you might use when you perform an incremental backup next time over the full backup that is just made.
$ ./mysqlbackup --user=root -p --backup-dir=/home/admin/backups backup-and-apply-log
MySQL Enterprise Backup version 3.11.1 Linux-2.6.18-274.el5-i686 [2014/09/04]
Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.
mysqlbackup: INFO: Starting with following command line ...
./mysqlbackup --user=root -p --backup-dir=/home/admin/backups
backup-and-apply-log
mysqlbackup: INFO:
Enter password:
mysqlbackup: INFO: MySQL server version is '5.6.17-log'.
mysqlbackup: INFO: Got some server configuration information from running server.
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'backup-and-apply-log' run mysqlbackup
prints "mysqlbackup completed OK!".
140904 12:34:54 mysqlbackup: INFO: MEB logfile created at /home/admin/backups/meta/MEB_2014-09-04.12-34-54_backup_apply_log.log
--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /var/lib/mysql/
innodb_data_home_dir =
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_page_size = 16384
innodb_checksum_algorithm = innodb
innodb_undo_directory = /var/lib/mysql/
innodb_undo_tablespaces = 0
innodb_undo_logs = 128
--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /home/admin/backups/datadir
innodb_data_home_dir = /home/admin/backups/datadir
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /home/admin/backups/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 50331648
innodb_page_size = 16384
innodb_checksum_algorithm = innodb
innodb_undo_directory = /home/admin/backups/datadir
innodb_undo_tablespaces = 0
innodb_undo_logs = 128
mysqlbackup: INFO: Unique generated backup id for this is 14098484948398421
mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
140904 12:34:57 mysqlbackup: INFO: Full Backup operation starts with following threads
1 read-threads 6 process-threads 1 write-threads
140904 12:34:57 mysqlbackup: INFO: System tablespace file format is Antelope.
140904 12:34:57 mysqlbackup: INFO: Starting to copy all innodb files...
140904 12:34:57 mysqlbackup: INFO: Found checkpoint at lsn 1611237.
140904 12:34:57 mysqlbackup: INFO: Starting log scan from lsn 1610752.
140904 12:34:57 mysqlbackup: INFO: Copying log...
140904 12:34:57 mysqlbackup: INFO: Log copied, lsn 1611237.
140904 12:34:57 mysqlbackup: INFO: Copying /var/lib/mysql/ibdata1 (Antelope file format).
140904 12:34:59 mysqlbackup: INFO: Completing the copy of innodb files.
140904 12:34:59 mysqlbackup: INFO: Starting to copy Binlog files...
140904 12:34:59 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
140904 12:34:59 mysqlbackup: INFO: Starting to lock all the tables...
140904 12:35:00 mysqlbackup: INFO: All tables are locked and flushed to disk
140904 12:35:00 mysqlbackup: INFO: Opening backup source directory '/var/lib/mysql/'
140904 12:35:00 mysqlbackup: INFO: Starting to backup all non-innodb files in
subdirectories of '/var/lib/mysql/'
140904 12:35:00 mysqlbackup: INFO: Copying the database directory 'performance_schema'
140904 12:35:01 mysqlbackup: INFO: Completing the copy of all non-innodb files.
140904 12:35:01 mysqlbackup: INFO: Completed the copy of binlog files...
140904 12:35:02 mysqlbackup: INFO: A copied database page was modified at 1611237.
(This is the highest lsn found on page)
Scanned log up to lsn 1611237.
Was able to parse the log up to lsn 1611237.
Maximum page number for a log record 0
140904 12:35:02 mysqlbackup: INFO: All tables unlocked
140904 12:35:02 mysqlbackup: INFO: All MySQL tables were locked for 2.530 seconds.
140904 12:35:03 mysqlbackup: INFO: Reading all global variables from the server.
140904 12:35:03 mysqlbackup: INFO: Completed reading of all global variables from the server.
140904 12:35:03 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /home/admin/backups
140904 12:35:03 mysqlbackup: INFO: Full Backup operation completed successfully.
140904 12:35:03 mysqlbackup: INFO: Backup created in directory '/home/admin/backups'
140904 12:35:03 mysqlbackup: INFO: MySQL binlog position: filename mysqld-bin.000001, position 120
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 1610752
End LSN : 1611237
-------------------------------------------------------------
mysqlbackup: INFO: Creating 14 buffers each of size 65536.
140904 12:35:03 mysqlbackup: INFO: Apply-log operation starts with following threads
1 read-threads 1 process-threads
mysqlbackup: INFO: Using up to 100 MB of memory.
140904 12:35:03 mysqlbackup: INFO: ibbackup_logfile's creation parameters:
start lsn 1610752, end lsn 1611237,
start checkpoint 1611237.
mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
mysqlbackup: INFO: InnoDB: Setting log file size to 50331648
mysqlbackup: INFO: InnoDB: Setting log file size to 50331648
140904 12:35:14 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to
lsn 1611237.
mysqlbackup: INFO: Last MySQL binlog file position 0 120, file name mysqld-bin.000001:120
140904 12:35:14 mysqlbackup: INFO: The first data file is '/home/admin/backups/datadir/ibdata1'
and the new created log files are at '/home/admin/backups/datadir'
140904 12:35:14 mysqlbackup: INFO: Apply-log operation completed successfully.
140904 12:35:14 mysqlbackup: INFO: Full backup prepared for recovery successfully.
mysqlbackup completed OK!
Now the backup subdirectory is created under the
backup-dir
we specified. The directory name for
each new backup is formed from the date and the clock time when
the backup run was started, in the local time zone. The backup
directory contains the backed-up ibdata
files
and ibbackup_logfile
. Each subdirectory
corresponds to a MySQL database, and contains copies of
.frm
, .MYD
,
.MYI
, and similar files. For an example of
the layout of such a backup directory, see
Section C.1, “Sample Directory Structure for Full Backup”.