3.2.1 Backing Up an Entire MySQL Instance

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 LSN values that you might use when you graduate from doing full backups to incremental backups.

$ mysqlbackup --port=13000 --protocol=tcp --user=root --password \
  --backup-dir=/home/admin/backups backup-and-apply-log

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

INFO: Starting with following command line ...
 mysqlbackup --port=13000 --protocol=tcp --user=root --password
        --backup-dir=/home/admin/backups
        backup

...informational messages...

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir                          =  /home/mysql/data/
  innodb_data_home_dir             =  /home/mysql/data
  innodb_data_file_path            =  ibdata1:20M;ibdata2:20M:autoextend
  innodb_log_group_home_dir        =  /home/mysql/data
  innodb_log_files_in_group        =  4
  innodb_log_file_size             =  20971520

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir                          =  /home/admin/backups/datadir
  innodb_data_home_dir             =  /home/admin/backups/datadir
  innodb_data_file_path            =  ibdata1:20M;ibdata2:20M:autoextend
  innodb_log_group_home_dir        =  /home/admin/backups/datadir
  innodb_log_files_in_group        =  4
  innodb_log_file_size             =  20971520

 mysqlbackup: INFO: Unique generated backup id for this is 13071379168342780
...output showing backup progress...
110604  0:51:59 mysqlbackup: INFO: Full backup completed!
 mysqlbackup: INFO: Backup created in directory '/home/admin/backups'

-------------------------------------------------------------
   Parameters Summary
-------------------------------------------------------------
   Start LSN                  : 36864
   End LSN                    : 50335
-------------------------------------------------------------

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”.