Documentation Home
MySQL Enterprise Backup 4.0 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.1Mb
PDF (A4) - 1.2Mb
EPUB - 232.3Kb
HTML Download (TGZ) - 179.9Kb
HTML Download (Zip) - 211.2Kb


4.2.1 Backing Up an Entire MySQL Instance

In the following example, we back up an entire MySQL instance to a single file using the backup-to-image command, which appears at the end of the sample command. We specify some of the connection information for the database using the --user and --host options (and, with the --password option, tell the MySQL server to prompt for a user password). The location and filename for the single-file backup is specified using the --backup-image option, and the location for an empty folder to store temporary files is supplied with the --backup-dir option.

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 MySQL instance, allowing you to monitor long-running backups and view information on 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 has just been made.

$ ./mysqlbackup --user=root --password --host=127.0.0.1 --backup-image=/home/admin/backups/my.mbi \
    --backup-dir=/home/admin/backup-tmp backup-to-image

MySQL Enterprise Backup version 4.0.0 Linux-3.2.0-25-generic-pae-i686 [2015/09/29] 
Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...
 ./mysqlbackup --user=root --password --host=127.0.0.1 
        --backup-image=/home/admin/backups/my.mbi 
        --backup-dir=/home/admin/backup-tmp backup-to-image 

 mysqlbackup: INFO: 
Enter password: 
 mysqlbackup: INFO: MySQL server version is '5.7.9'.
 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-to-image' run mysqlbackup
           prints "mysqlbackup completed OK!".

150930 19:19:26 mysqlbackup: INFO: MEB logfile created at /home/admin/backup-tmp/meta/MEB_2015-09-30.19-19-26_image_backup.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 = crc32
  innodb_undo_directory = /var/lib/mysql/
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir = /home/admin/backup-tmp/datadir
  innodb_data_home_dir = /home/admin/backup-tmp/datadir
  innodb_data_file_path = ibdata1:12M:autoextend
  innodb_log_group_home_dir = /home/admin/backup-tmp/datadir
  innodb_log_files_in_group = 2
  innodb_log_file_size = 50331648
  innodb_page_size = 16384
  innodb_checksum_algorithm = crc32
  innodb_undo_directory = /home/admin/backup-tmp/datadir
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

Backup Image Path = /home/admin/backups/my.mbi
 mysqlbackup: INFO: Unique generated backup id for this is 14436551662849660

 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
150930 19:19:28 mysqlbackup: INFO: Full Image Backup operation starts with following threads
		1 read-threads    6 process-threads    1 write-threads
150930 19:19:28 mysqlbackup: INFO: System tablespace file format is Barracuda.
150930 19:19:28 mysqlbackup: INFO: Starting to copy all innodb files...
 mysqlbackup: INFO: Could not find binlog index file. binlogs will not be copied for this backup.
 Point-In-Time-Recovery will not be possible.
 If this is online backup then server may not have started with --log-bin.
 You may specify its location with --log-bin-index option.
 mysqlbackup: INFO: Copying meta file /home/admin/backup-tmp/backup-my.cnf.
 mysqlbackup: INFO: Copying meta file /home/admin/backup-tmp/meta/backup_create.xml.
150930 19:19:28 mysqlbackup: INFO: Found checkpoint at lsn 2478740.
150930 19:19:28 mysqlbackup: INFO: Starting log scan from lsn 2478592.
150930 19:19:28 mysqlbackup: INFO: Copying log...
150930 19:19:28 mysqlbackup: INFO: Log copied, lsn 2478749.
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/ibdata1 (Barracuda file format).
 mysqlbackup: INFO: Processing General table spaces in /var/lib/mysql/ 
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mydb/mytable.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/engine_cost.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/gtid_executed.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/help_category.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/help_keyword.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/help_relation.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/help_topic.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/innodb_index_stats.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/innodb_table_stats.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/plugin.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/server_cost.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/servers.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/slave_master_info.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/slave_relay_log_info.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/slave_worker_info.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/time_zone.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/time_zone_leap_second.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/time_zone_name.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/time_zone_transition.ibd (Barracuda file format).
150930 19:19:28 mysqlbackup: INFO: Copying /var/lib/mysql/mysql/time_zone_transition_type.ibd (Barracuda file format).
150930 19:19:29 mysqlbackup: INFO: Copying /var/lib/mysql/sys/sys_config.ibd (Barracuda file format).
150930 19:19:29 mysqlbackup: INFO: Completing the copy of innodb files.
 mysqlbackup: INFO: Processing General table spaces in /var/lib/mysql/ 
 mysqlbackup: INFO: Now complete gen ts
150930 19:19:30 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
150930 19:19:30 mysqlbackup: INFO: Starting to lock all the tables...
150930 19:19:30 mysqlbackup: INFO: All tables are locked and flushed to disk
150930 19:19:30 mysqlbackup: INFO: Opening backup source directory '/var/lib/mysql/'
150930 19:19:30 mysqlbackup: INFO: Starting to backup all non-innodb files in 
	subdirectories of '/var/lib/mysql/'
150930 19:19:30 mysqlbackup: INFO: Adding database directory: datadir/mydb
150930 19:19:30 mysqlbackup: INFO: Adding database directory: datadir/mysql
150930 19:19:30 mysqlbackup: INFO: Adding database directory: datadir/performance_schema
150930 19:19:30 mysqlbackup: INFO: Adding database directory: datadir/sys
150930 19:19:30 mysqlbackup: INFO: Completing the copy of all non-innodb files.
 mysqlbackup: INFO: Processing General table spaces in /var/lib/mysql/ 
 mysqlbackup: INFO: Now complete gen ts
 mysqlbackup: INFO: Processing General table spaces in /home/admin/backup-tmp/datadir 
 mysqlbackup: INFO: Processing General table spaces in /home/admin/backup-tmp/datadir 
 mysqlbackup: INFO: Now complete gen ts
150930 19:19:32 mysqlbackup: INFO: A copied database page was modified at 2478707.
          (This is the highest lsn found on page)
          Scanned log up to lsn 2478749.
          Was able to parse the log up to lsn 2478749.
150930 19:19:32 mysqlbackup: INFO: All tables unlocked
150930 19:19:32 mysqlbackup: INFO: All MySQL tables were locked for 2.089 seconds.
150930 19:19:32 mysqlbackup: INFO: Reading all global variables from the server.
150930 19:19:32 mysqlbackup: INFO: Completed reading of all global variables from the server.
150930 19:19:32 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /home/admin/backup-tmp
 mysqlbackup: INFO: Copying meta file /home/admin/backup-tmp/meta/backup_variables.txt.
 mysqlbackup: INFO: Copying meta file /home/admin/backup-tmp/datadir/ibbackup_logfile.
 mysqlbackup: INFO: Copying meta file /home/admin/backup-tmp/server-all.cnf.
 mysqlbackup: INFO: Copying meta file /home/admin/backup-tmp/server-my.cnf.
 mysqlbackup: INFO: Copying meta file /home/admin/backup-tmp/meta/backup_content.xml.
 mysqlbackup: INFO: Copying meta file /home/admin/backup-tmp/meta/image_files.xml.
150930 19:19:35 mysqlbackup: INFO: Full Image Backup operation completed successfully.
150930 19:19:35 mysqlbackup: INFO: Backup image created successfully.
 mysqlbackup: INFO: Image Path = /home/admin/backups/my.mbi

-------------------------------------------------------------
   Parameters Summary         
-------------------------------------------------------------
   Start LSN                  : 2478592
   End LSN                    : 2478749
-------------------------------------------------------------

mysqlbackup completed OK!

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