Documentation Home
MySQL Enterprise Backup 8.0 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.3Mb
PDF (A4) - 1.3Mb
HTML Download (TGZ) - 194.7Kb
HTML Download (Zip) - 226.3Kb


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 mysqlbackup 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=mysqlbackup --password --host=127.0.0.1 --backup-image=/home/mysqlbackup/backups/my.mbi \
  --backup-dir=/home/mysqlbackup/backup-tmp  backup-to-image
MySQL Enterprise Backup  Ver 8.0.16-commercial for linux-glibc2.12 on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

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

190402 12:56:04 MAIN    INFO: Starting to log actions.
Enter password: 
190402 12:56:09 MAIN    INFO: No SSL options specified.
190402 12:56:09 MAIN    INFO: MySQL server version is '8.0.16-commercial'
190402 12:56:09 MAIN    INFO: MySQL server compile os version is 'linux-glibc2.12'
190402 12:56:09 MAIN    INFO: Got some server configuration information from running server.

190402 12:56:09 MAIN    INFO: Backup directory exists: '/home/mysqlbackup/backup-tmp'
190402 12:56:09 MAIN    INFO: MySQL server version_comment is 'MySQL Enterprise Server - Commercial'
190402 12:56:09 MAIN    INFO: Server is not a community server.
190402 12:56:09 MAIN    INFO: KEF target path:'/home/mysqlbackup/backup-tmp/meta/keyring_kef'
190402 12:56:09 MAIN    INFO: TDE Keyring service initialized.
190402 12:56:09 MAIN    INFO: MEB logfile created at /home/mysqlbackup/backup-tmp/meta/MEB_2019-04-02.12-56-09_image_backup.log

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir                        = /home/admin/bin/mysql-commercial-8.0.16/datadir/
  innodb_data_home_dir           = 
  innodb_data_file_path          = ibdata1:12M:autoextend
  innodb_log_group_home_dir      = /home/admin/bin/mysql-commercial-8.0.16/datadir/
  innodb_log_files_in_group      = 2
  innodb_log_file_size           = 50331648
  innodb_undo_directory          = /home/admin/bin/mysql-commercial-8.0.16/datadir/
  innodb_undo_tablespaces        = 2
  innodb_buffer_pool_filename    = ib_buffer_pool
  innodb_page_size               = 16384
  innodb_checksum_algorithm      = crc32

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

Backup Image Path = /home/mysqlbackup/backups/my.mbi
190402 12:56:09 MAIN    INFO: Unique generated backup id for this is 15542241696070511

190402 12:56:09 MAIN    INFO: Creating 14 buffers each of size 16777216.
190402 12:56:09 MAIN    INFO: Full Image Backup operation starts with following threads
		1 read-threads    6 process-threads    1 write-threads
190402 12:56:09 MAIN    INFO: Found checkpoint at lsn 19840686.
190402 12:56:09 MAIN    INFO: Starting log scan from lsn = 19840512 at offset = 32256 and checkpoint = 19840686 in file /home/admin/bin/mysql-commercial-8.0.16/datadir/ib_logfile0.
190402 12:56:09 RDR1    INFO: Copying meta file /home/mysqlbackup/backup-tmp/backup-my.cnf.
190402 12:56:09 RDR1    INFO: Copying meta file /home/mysqlbackup/backup-tmp/meta/backup_create.xml.
190402 12:56:09 RDR1    INFO: Starting to copy all innodb files...
190402 12:56:09 RDR1    INFO: Copying /home/admin/bin/mysql-commercial-8.0.16/datadir/ibdata1.
190402 12:56:09 RDR1    INFO: Starting to copy all undo files...
190402 12:56:09 RDR1    INFO: Copying /home/admin/bin/mysql-commercial-8.0.16/datadir/undo_002.
190402 12:56:09 RDR1    INFO: Copying /home/admin/bin/mysql-commercial-8.0.16/datadir/undo_001.
190402 12:56:09 RDR1    INFO: Copying /home/admin/bin/mysql-commercial-8.0.16/datadir/sys/sys_config.ibd.
190402 12:56:09 RDR1    INFO: Copying /home/admin/bin/mysql-commercial-8.0.16/datadir/pets/cats.ibd.
190402 12:56:09 RDR1    INFO: Copying /home/admin/bin/mysql-commercial-8.0.16/datadir/mysql/backup_history.ibd.
190402 12:56:09 RDR1    INFO: Copying /home/admin/bin/mysql-commercial-8.0.16/datadir/mysql.ibd.
190402 12:56:10 RDR1    INFO: Completing the copy of innodb files.
190402 12:56:10 RDR1    INFO: Requesting a dump of the InnoDB buffer pool
190402 12:56:10 RDR1    INFO: Waiting for the dump of the InnoDB buffer pool to complete
190402 12:56:10 RDR1    INFO: The dump of the InnoDB buffer pool completed
190402 12:56:10 RDR1    INFO: Binary Log Basename: '/home/admin/bin/mysql-commercial-8.0.16/datadir/binlog'
190402 12:56:10 RDR1    INFO: Binary Log Index:    '/home/admin/bin/mysql-commercial-8.0.16/datadir/binlog.index'
190402 12:56:10 RDR1    INFO: Relay Channel:      'group_replication_applier'
190402 12:56:10 RDR1    INFO: Relay Log Basename: '/home/admin/bin/mysql-commercial-8.0.16/datadir/admin-XuBox-relay-bin-group_replication_applier'
190402 12:56:10 RDR1    INFO: Relay Log Index:    '/home/admin/bin/mysql-commercial-8.0.16/datadir/admin-XuBox-relay-bin-group_replication_applier.index'
190402 12:56:10 RDR1    INFO: Relay Channel:      'group_replication_recovery'
190402 12:56:10 RDR1    INFO: Relay Log Basename: '/home/admin/bin/mysql-commercial-8.0.16/datadir/admin-XuBox-relay-bin-group_replication_applier-group_replication_recovery'
190402 12:56:10 RDR1    INFO: Relay Log Index:    '/home/admin/bin/mysql-commercial-8.0.16/datadir/admin-XuBox-relay-bin-group_replication_recovery.index'
190402 12:56:10 RDR1    INFO: Starting to copy Binlog files...
190402 12:56:10 RDR1    INFO: Copying /home/admin/bin/mysql-commercial-8.0.16/datadir/binlog.000001.
190402 12:56:10 RDR1    INFO: Copying /home/admin/bin/mysql-commercial-8.0.16/datadir/binlog.000002.
190402 12:56:10 RDR1    INFO: Copying /home/admin/bin/mysql-commercial-8.0.16/datadir/binlog.000003.
190402 12:56:10 RDR1    INFO: Starting to lock instance for backup...
190402 12:56:10 RDR1    INFO: No SSL options specified.
190402 12:56:10 RDR1    INFO: The server instance is locked for backup.
190402 12:56:10 RDR1    INFO: Starting to read-lock tables...
190402 12:56:10 RDR1    INFO: No tables to read-lock.
190402 12:56:10 RDR1    INFO: Opening backup source directory '/home/admin/bin/mysql-commercial-8.0.16/datadir'
190402 12:56:10 RDR1    INFO: Starting to copy non-innodb files in subdirs of '/home/admin/bin/mysql-commercial-8.0.16/datadir'
190402 12:56:10 WTR1    INFO: Adding database directory: datadir/mysql
190402 12:56:10 WTR1    INFO: Adding database directory: datadir/performance_schema
190402 12:56:10 RDR1    INFO: Completing the copy of all non-innodb files.
190402 12:56:10 WTR1    INFO: Adding database directory: datadir/pets
190402 12:56:10 WTR1    INFO: Adding database directory: datadir/sys
190402 12:56:10 RDR1    INFO: Requesting consistency information...
190402 12:56:10 RDR1    INFO: Locked the consistency point for 3089 microseconds.
190402 12:56:10 RDR1    INFO: Consistency point server_uuid 'a01aa59f-5567-11e9-ad69-08002759ceb5'.
190402 12:56:10 RDR1    INFO: Consistency point gtid_executed ''.
190402 12:56:10 RDR1    INFO: Consistency point binary_log_file 'binlog.000004'.
190402 12:56:10 RDR1    INFO: Consistency point binary_log_position 379.
190402 12:56:10 RDR1    INFO: Consistency point InnoDB lsn 19840686.
190402 12:56:10 RDR1    INFO: Consistency point InnoDB lsn_checkpoint 19840686.
190402 12:56:10 RDR1    INFO: Requesting completion of redo log copy after LSN 19840686.
190402 12:56:10 RLR1    INFO: Redo log reader waited = 0.00 ms for logs to generate.
190402 12:56:10 RLW1    INFO: A copied database page was modified at 19840686. (This is the highest lsn found on a page)
190402 12:56:10 RLW1    INFO: Scanned log up to lsn 19840686.
190402 12:56:10 RDR1    INFO: Copying /home/admin/bin/mysql-commercial-8.0.16/datadir/binlog.000004.
190402 12:56:10 RDR1    INFO: Completed the copy of binlog files...
190402 12:56:10 RDR1    INFO: The server instance is unlocked after 0.085 seconds.
190402 12:56:10 RDR1    INFO: Reading all global variables from the server.
190402 12:56:10 RDR1    INFO: Completed reading of all 548 global variables from the server.
190402 12:56:10 RDR1    INFO: Writing server defaults files 'server-my.cnf' and 'server-all.cnf' for server '8.0.16-commercial' in '/home/mysqlbackup/backup-tmp'.
190402 12:56:10 RDR1    INFO: Copying meta file /home/mysqlbackup/backup-tmp/meta/backup_variables.txt.
190402 12:56:10 RDR1    INFO: Copying meta file /home/mysqlbackup/backup-tmp/datadir/ibbackup_logfile.
190402 12:56:10 RDR1    INFO: Copying meta file /home/mysqlbackup/backup-tmp/server-all.cnf.
190402 12:56:10 RDR1    INFO: Copying meta file /home/mysqlbackup/backup-tmp/server-my.cnf.
190402 12:56:10 RDR1    INFO: Copying meta file /home/mysqlbackup/backup-tmp/meta/backup_content.xml.
190402 12:56:10 RDR1    INFO: Copying meta file /home/mysqlbackup/backup-tmp/meta/image_files.xml.
190402 12:56:10 MAIN    INFO: Full Image Backup operation completed successfully.
190402 12:56:10 MAIN    INFO: Backup image created successfully.
190402 12:56:10 MAIN    INFO: Image Path = /home/mysqlbackup/backups/my.mbi
190402 12:56:10 MAIN    INFO: MySQL binlog position: filename binlog.000004, position 379

-------------------------------------------------------------
   Parameters Summary         
-------------------------------------------------------------
   Start LSN                  : 19840512
   End LSN                    : 19840686
-------------------------------------------------------------

mysqlbackup completed OK!