4.2.2 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.1.0 Linux-2.6.39-400.215.10.el5uek-x86_64 [2017/02/13] 
Copyright (c) 2003, 2017, Oracle and/or its affiliates. All Rights Reserved.

170214 17:38:59 MAIN    INFO: A thread created with Id '140286817412928' 
170214 17:38:59 MAIN    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 

170214 17:38:59 MAIN    INFO: 
Enter password: 
170214 17:39:07 MAIN    INFO: MySQL server version is '5.7.17'
170214 17:39:07 MAIN    INFO: MySQL server compile os version is 'Linux'
170214 17:39:07 MAIN    INFO: Got some server configuration information from running server.

170214 17:39:07 MAIN    INFO: Server system variable 'old_alter_table' was set to '0'. Setting it to '1'.
IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'backup-to-image' run mysqlbackup
           prints "mysqlbackup completed OK!".

170214 17:39:07 MAIN    INFO: MEB logfile created at /home/admin/backup-tmp/meta/MEB_2017-02-14.17-39-07_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_undo_directory          = /var/lib/mysql/
  innodb_undo_tablespaces        = 0
  innodb_undo_logs               = 128
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32

--------------------------------------------------------------------
                       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_undo_directory          = /home/admin/backup-tmp/datadir
  innodb_undo_tablespaces        = 0
  innodb_undo_logs               = 128
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32

Backup Image Path = /home/admin/backups/my.mbi
170214 17:39:07 MAIN    INFO: Unique generated backup id for this is 14871119474450201

170214 17:39:07 MAIN    INFO: Creating 14 buffers each of size 16777216.
170214 17:39:07 MAIN    INFO: Full Image Backup operation starts with following threads
		1 read-threads    6 process-threads    1 write-threads
170214 17:39:07 MAIN    INFO: Found checkpoint at lsn 2543123.
170214 17:39:07 MAIN    INFO: Starting log scan from lsn = 2543104 at offset = 2536448 and checkpoint = 2543123 in file /var/lib/mysql/ib_logfile0.
170214 17:39:07 RDR1    INFO: A thread created with Id '140286535427840' 
170214 17:39:07 RDR1    INFO: Copying meta file /home/admin/backup-tmp/backup-my.cnf.
170214 17:39:07 PCR1    INFO: A thread created with Id '140286527035136' 
170214 17:39:07 PCR2    INFO: A thread created with Id '140286518642432' 
170214 17:39:07 RDR1    INFO: Copying meta file /home/admin/backup-tmp/meta/backup_create.xml.
170214 17:39:07 RDR1    INFO: System tablespace file format is Antelope.
170214 17:39:07 RDR1    INFO: Starting to copy all innodb files...
170214 17:39:07 PCR3    INFO: A thread created with Id '140286510249728' 
170214 17:39:07 PCR4    INFO: A thread created with Id '140286382569216' 
170214 17:39:07 PCR5    INFO: A thread created with Id '140286501857024' 
170214 17:39:07 PCR6    INFO: A thread created with Id '140286493464320' 
170214 17:39:07 WTR1    INFO: A thread created with Id '140286485071616' 
170214 17:39:07 RLR1    INFO: A thread created with Id '140286476678912' 
170214 17:39:07 RLW1    INFO: A thread created with Id '140286468286208' 
170214 17:39:07 RLP1    INFO: A thread created with Id '140286459893504' 
170214 17:39:07 RDR1    INFO: Copying /var/lib/mysql/ibdata1 (Barracuda file format).
170214 17:39:08 RDR1    INFO: Copying /var/lib/mysql/mysql/engine_cost.ibd (Barracuda file format).
170214 17:39:08 RDR1    INFO: Copying /var/lib/mysql/mysql/gtid_executed.ibd (Barracuda file format).
170214 17:39:08 RDR1    INFO: Copying /var/lib/mysql/mysql/help_category.ibd (Barracuda file format).
170214 17:39:08 RDR1    INFO: Copying /var/lib/mysql/mysql/help_keyword.ibd (Barracuda file format).
170214 17:39:08 RDR1    INFO: Copying /var/lib/mysql/mysql/help_relation.ibd (Barracuda file format).
170214 17:39:08 RDR1    INFO: Copying /var/lib/mysql/mysql/help_topic.ibd (Barracuda file format).
170214 17:39:09 RDR1    INFO: Copying /var/lib/mysql/mysql/innodb_index_stats.ibd (Barracuda file format).
170214 17:39:10 RDR1    INFO: Copying /var/lib/mysql/mysql/innodb_table_stats.ibd (Barracuda file format).
170214 17:39:10 RDR1    INFO: Copying /var/lib/mysql/mysql/plugin.ibd (Barracuda file format).
170214 17:39:10 RDR1    INFO: Copying /var/lib/mysql/mysql/server_cost.ibd (Barracuda file format).
170214 17:39:10 RDR1    INFO: Copying /var/lib/mysql/mysql/servers.ibd (Barracuda file format).
170214 17:39:10 RDR1    INFO: Copying /var/lib/mysql/mysql/slave_master_info.ibd (Barracuda file format).
170214 17:39:10 RDR1    INFO: Copying /var/lib/mysql/mysql/slave_relay_log_info.ibd (Barracuda file format).
170214 17:39:10 RDR1    INFO: Copying /var/lib/mysql/mysql/slave_worker_info.ibd (Barracuda file format).
170214 17:39:10 RDR1    INFO: Copying /var/lib/mysql/mysql/time_zone.ibd (Barracuda file format).
170214 17:39:10 RDR1    INFO: Copying /var/lib/mysql/mysql/time_zone_leap_second.ibd (Barracuda file format).
170214 17:39:10 RDR1    INFO: Copying /var/lib/mysql/mysql/time_zone_name.ibd (Barracuda file format).
170214 17:39:10 RDR1    INFO: Copying /var/lib/mysql/mysql/time_zone_transition.ibd (Barracuda file format).
170214 17:39:10 RDR1    INFO: Copying /var/lib/mysql/mysql/time_zone_transition_type.ibd (Barracuda file format).
170214 17:39:10 RDR1    INFO: Copying /var/lib/mysql/pets/cats.ibd (Barracuda file format).
170214 17:39:10 RDR1    INFO: Copying /var/lib/mysql/sys/sys_config.ibd (Barracuda file format).
170214 17:39:10 RDR1    INFO: Completing the copy of innodb files.
170214 17:39:10 RDR1    INFO: Requesting a dump of the InnoDB buffer pool
170214 17:39:10 RDR1    INFO: Waiting for the dump of the InnoDB buffer pool to complete
170214 17:39:11 RDR1    INFO: The dump of the InnoDB buffer pool completed
170214 17:39:11 RDR1    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.
170214 17:39:11 RDR1    INFO: Preparing to lock tables: Connected to mysqld server.
170214 17:39:11 RDR1    INFO: Starting to lock all the tables...
170214 17:39:11 RDR1    INFO: Flushing the default MyISAM key cache.
170214 17:39:11 RDR1    INFO: All tables are locked and flushed to disk
170214 17:39:11 RDR1    INFO: Completed the copy of binlog files...
170214 17:39:11 RDR1    INFO: Opening backup source directory '/var/lib/mysql'
170214 17:39:11 RDR1    INFO: Starting to backup all non-innodb files in 
	subdirectories of '/var/lib/mysql'
170214 17:39:11 WTR1    INFO: Adding database directory: datadir
170214 17:39:11 WTR1    INFO: Adding database directory: datadir
170214 17:39:11 WTR1    INFO: Adding database directory: datadir
170214 17:39:11 WTR1    INFO: Adding database directory: datadir
170214 17:39:11 RDR1    INFO: Completing the copy of all non-innodb files.
170214 17:39:11 RDR1    INFO: Requesting completion of redo log copy. Rapid: 0
170214 17:39:11 RLR1    INFO: Signaled '1' to complete log copy.
170214 17:39:11 RLR1    INFO: Signaled '1' to complete log copy.
170214 17:39:11 RLR1    INFO: Signaled '1' to complete log copy.
170214 17:39:11 RLR1    INFO: Redo log reader waited = 1270.00 ms for logs to generate.
170214 17:39:11 RLW1    INFO: A copied database page was modified at 2543123.
          (This is the highest lsn found on page)
          Scanned log up to lsn 2543132.
          Was able to parse the log up to lsn 2543132.
170214 17:39:13 RDR1    INFO: All tables unlocked
170214 17:39:13 RDR1    INFO: All MySQL tables were locked for 1.367 seconds.
170214 17:39:13 RDR1    INFO: Setting server system variable 'old_alter_table' back to '0'.
170214 17:39:13 RDR1    INFO: Reading all global variables from the server.
170214 17:39:13 RDR1    INFO: Completed reading of all global variables from the server.
170214 17:39:13 RDR1    INFO: Writing config file for server '5.7.17'.
170214 17:39:13 RDR1    INFO: Creating server config files server-my.cnf and server-all.cnf in /home/admin/backup-tmp
170214 17:39:13 RDR1    INFO: Copying meta file /home/admin/backup-tmp/meta/backup_variables.txt.
170214 17:39:13 RDR1    INFO: Copying meta file /home/admin/backup-tmp/datadir/ibbackup_logfile.
170214 17:39:13 RDR1    INFO: Copying meta file /home/admin/backup-tmp/server-all.cnf.
170214 17:39:13 RDR1    INFO: Copying meta file /home/admin/backup-tmp/server-my.cnf.
170214 17:39:13 RDR1    INFO: Copying meta file /home/admin/backup-tmp/meta/backup_content.xml.
170214 17:39:13 RDR1    INFO: Copying meta file /home/admin/backup-tmp/meta/image_files.xml.
170214 17:39:13 MAIN    INFO: Full Image Backup operation completed successfully.
170214 17:39:13 MAIN    INFO: Backup image created successfully.
170214 17:39:13 MAIN    INFO: Image Path = /home/admin/backups/my.mbi

-------------------------------------------------------------
   Parameters Summary         
-------------------------------------------------------------
   Start LSN                  : 2543104
   End LSN                    : 2543132
-------------------------------------------------------------

mysqlbackup completed OK!