Sunday, 25 February 2018

Creating and Restoring Database Backups With MySQL Enterprise Backup

MySQL Enterprise Backup is a backup utility for MySQL servers. It is a multi-platform, high-performance tool, offering rich features like “hot” (online) backup, incremental and differential backup, selective backup and restore, support for direct cloud storage backup, backup encryption and compression, and many other valuable features.

All MySQL Enterprise Backup functions are executed with the mysqlbackup client. It is used for performing different types of backup and restore operations, as well as other related tasks like backup compression, decompression, validation, and so on.

A hot backup is a backup performed while the database is running. This type of backups does not block normal database operations. It captures even changes that occur while the backup is happening. Comparing to the other backup types, it causes the least disruption to the database server, and it is a desirable backup option when you want to avoid taking your application, web site, or web service offline. 

A warm backup is a backup performed with the database put under a read-only state. This type of backups blocks any write operations to the tables during the backup process, but still allow tables to be read.

When connected to a running MySQL server, MySQL Enterprise Backup backs up all MyISAM and other non-InnoDB tables using the warm backup technique after all InnoDB tables have already been backed up with the hot backup method. 

Kinds of backups :
  • A full backup includes the complete data from the database 
  • A differential backup includes all changes to the data since the last full backup. It is faster than a full backup, saves storage space on the database server, and saves on network traffic when the backup is being transferred to a different server. 
  • An Incremental backup includes all changes to the data since the last backup. It offers similar advantages over a full backup as a differential backup does, and often to a even greater extent by further decreasing the backup size. But it might also require more preparations on a longer series of backups, before a restore can be performed.
Features:
  • High Performance – Save time with faster backup and recovery
  • Incremental Backup – Backup only data that has changed since the last backup
  • Partial Backup – Target particular tables or tablespaces
  • Compression – Cut costs by reducing storage requirements up to 90%
  • Backup to Tape – Stream backup to tape or other media management solutions
  • Fast Recovery – Get servers back online and create replicated servers
  • Point-in-Time Recovery (PITR) – Recover to a specific transaction
  • Partial restore – Recover targeted tables or tablespaces
  • Restore to a separate location – Rapidly create clones for fast replication setup
Types of files contained in a backup :


Files backed-up for innodb

The InnoDB-related data files that are backed up include the ibdata* files (which represent the system tablespace and possibly the data for some user tables), any .ibd files (which contains data from user tables created with the file-per-table setting enabled), and the data extracted from the ib_logfile* files (the redo log information representing changes that occur while the backup is running), which is stored in a new backup file ibbackup_logfile.

If you use the compressed backup feature, the .ibd files are renamed in their compressed form to .ibz files.

The files, as they are originally copied, form a raw backup that requires further processing before it is ready to be restored. You then run the apply step (either as part of a copy-back-and-apply-log command or a backup-and-apply-log command, or as a separate apply-log command), which updates the backup files based on the changes recorded in the ibbackup_logfile file, producing a prepared backup. 

Files Backed up for Data Stored with MyISAM 

mysqlbackup also backs up the .MYD files, .MYI files, and the .frm files associated with the MyISAM tables. 

MyISAM tables and these other types of files cannot be backed up in the same non-blocking way as InnoDB tables can be. They can be backed up using the warm backup technique

Files Generated by mysqlbackup :

  • backup-my.cnf: Records the crucial configuration parameters that apply to the backup. These parameter values are used during a restore operation, so that the original values are used regardless of changes to your server's my.cnf file since the backup.
  • meta/backup_create.xml: Lists the command line arguments and environment in which the backup was created.
  • meta/backup_content.xml: Essential metadata for the files and database definitions of the backup data.
  • server-my.cnf: Contains values of the backed-up server's global variables that are set to non-default values.
  • server-all.cnf: Contains values of all the global variables of the backed-up server.
  • *.bkt: Transfer file created for an encrypted InnoDB table during backup. It contains the reencrypted tablespace key and other information related to the encryption. See Chapter 6, Working with Encrypted InnoDB Tables for detail.
MySQL Enterprise Backup now supports optimistic incremental backup, in which mysqlbackup scans only those InnoDB data files that have been modified since the last backup for changed pages and then saves them into the incremental backup. It potentially makes incremental backups faster. 

Apply-log operations can now be performed with multiple worker threads in parallel, which can improve performance for the operations. The number of threads to be used can be specified with the --process-threads.

Information Needed to Back Up a Database
  • Path to MySQL configuration file : Default system locations, hardcoded application default locations, or from the --defaults-file option in the mysqld startup script.
  • MySQL port : MySQL configuration file or mysqld startup script.
  • Path to MySQL data directory : MySQL configuration file or mysqld startup script.
  • ID and password of privileged MySQL user
  • Size of InnoDB redo log files :Calculated from the values of the innodb_log_file_size and innodb_log_files_in_group configuration variables. Use the technique explained for the --incremental-with-redo-log-only option.
The minimum privileges for the MySQL user with which mysqlbackup connects to the server are:
  • RELOAD on all databases and tables.
  • CREATE, INSERT, DROP, and UPDATE on the tables mysql.backup_progress and mysql.backup_history, and also SELECT on mysql.backup_history.
  • SUPER, to enable and disable logging, and to optimize locking in order to minimize disruption to database processing.
  • REPLICATION CLIENT, to retrieve the binary log position, which is stored with the backup.
  • PROCESS, to process DDL statements with the ALGORITHM = INPLACE clause.
  • ALTER privilege for adding new columns to the backup history table
CREATE USER 'mysqlbackup'@'localhost' IDENTIFIED BY 'new-password';
GRANT RELOAD ON *.* TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'mysqlbackup'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'mysqlbackup'@'localhost';
GRANT SUPER ON *.* TO 'mysqlbackup'@'localhost';
GRANT PROCESS ON *.* TO 'mysqlbackup'@'localhost';
GRANT ALTER ON mysql.backup_history TO 'mysqlbackup'@'localhost';

For working on transportable table space use :

GRANT LOCK TABLES, SELECT, CREATE, DROP, FILE ON *.* TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_sbt_history TO 'mysqlbackup'@'localhost';

In order to be able to use MySQL Enterprise Backup to perform a backup of a MySQL Server instance, the following MySQL options must be specified in the configuration file of the server instance, under the [mysqld] section 
 
datadir=...
innodb_data_home_dir=...
innodb_data_file_path=...
innodb_log_group_home_dir=...
innodb_log_files_in_group=...
innodb_log_file_size=...

Creating a Full backup :

A full backup can be created using following command 

$ /usr/local/meb/bin/mysqlbackup --user=root --password --backup-dir=/mnt/backup/hotbackups backup-and-apply-log

Output :

MySQL Enterprise Backup version 3.12.0 [2014/11/12] 
Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...
 /usr/local/meb/bin/mysqlbackup --user=root --password 
        --backup-dir=/mnt/backup/hotbackups backup-and-apply-log 

Enter password: 
 mysqlbackup: INFO: MySQL server version is '5.6.9-rc-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!".

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir = /usr/local/mysql/data/
  innodb_data_home_dir = /usr/local/mysql/data
  innodb_data_file_path = ibdata1:40M:autoextend
  innodb_log_group_home_dir = /usr/local/mysql/data
  innodb_log_files_in_group = 2
  innodb_log_file_size = 5242880
  innodb_page_size = 16384
  innodb_checksum_algorithm = innodb
  innodb_undo_directory = /usr/local/mysql/data/
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir = /mnt/backup/hotbackups/datadir
  innodb_data_home_dir = /mnt/backup/hotbackups/datadir
  innodb_data_file_path = ibdata1:40M:autoextend
  innodb_log_group_home_dir = /mnt/backup/hotbackups/datadir
  innodb_log_files_in_group = 2
  innodb_log_file_size = 5242880
  innodb_page_size = 16384
  innodb_checksum_algorithm = innodb
  innodb_undo_directory = /mnt/backup/hotbackups/datadir
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

 mysqlbackup: INFO: Unique generated backup id for this is 13742482113579320

 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
130719 11:36:53 mysqlbackup: INFO: Full Backup operation starts with following threads
  1 read-threads    6 process-threads    1 write-threads
130719 11:36:53 mysqlbackup: INFO: System tablespace file format is Antelope.
130719 11:36:53 mysqlbackup: INFO: Starting to copy all innodb files...
130719 11:36:53 mysqlbackup: INFO: Copying /usr/local/mysql/data/ibdata1 (Antelope file format).
130719 11:36:53 mysqlbackup: INFO: Found checkpoint at lsn 135380756.
130719 11:36:53 mysqlbackup: INFO: Starting log scan from lsn 135380480.
130719 11:36:53 mysqlbackup: INFO: Copying log...
130719 11:36:54 mysqlbackup: INFO: Log copied, lsn 135380756.

<span style="color: blue;"><i>(I have truncated some of the database and table output to save space)</i></span>
.....
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/innodb_index_stats.ibd (Antelope file format).
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/innodb_table_stats.ibd (Antelope file format).
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_master_info.ibd (Antelope file format).
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_relay_log_info.ibd (Antelope file format).
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_worker_info.ibd (Antelope file format).
.....
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/testcert/t1.ibd (Antelope file format).
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/testcert/t3.ibd (Antelope file format).
.....
130719 11:36:57 mysqlbackup: INFO: Copying /usr/local/mysql/data/watchdb/watches.ibd (Antelope file format).
.....
130719 11:36:57 mysqlbackup: INFO: Completing the copy of innodb files.
130719 11:36:58 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
130719 11:36:58 mysqlbackup: INFO: Starting to lock all the tables...
130719 11:36:58 mysqlbackup: INFO: All tables are locked and flushed to disk
130719 11:36:58 mysqlbackup: INFO: Opening backup source directory '/usr/local/mysql/data/'
130719 11:36:58 mysqlbackup: INFO: Starting to backup all non-innodb files in 
 subdirectories of '/usr/local/mysql/data/'
.....
130719 11:36:58 mysqlbackup: INFO: Copying the database directory 'comicbookdb'
.....
130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'mysql'
130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'performance_schema'
.....
130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'test'
.....
130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'watchdb'
130719 11:36:59 mysqlbackup: INFO: Completing the copy of all non-innodb files.
130719 11:37:00 mysqlbackup: INFO: A copied database page was modified at 135380756.
          (This is the highest lsn found on page)
          Scanned log up to lsn 135384397.
          Was able to parse the log up to lsn 135384397.
          Maximum page number for a log record 375
130719 11:37:00 mysqlbackup: INFO: All tables unlocked
130719 11:37:00 mysqlbackup: INFO: All MySQL tables were locked for 1.589 seconds.
130719 11:37:00 mysqlbackup: INFO: Full Backup operation completed successfully.
130719 11:37:00 mysqlbackup: INFO: Backup created in directory '/mnt/backup/hotbackups'
130719 11:37:00 mysqlbackup: INFO: MySQL binlog position: filename mysql-bin.000013, position 85573

-------------------------------------------------------------
   Parameters Summary         
-------------------------------------------------------------
   Start LSN                  : 135380480
   End LSN                    : 135384397
-------------------------------------------------------------

 mysqlbackup: INFO: Creating 14 buffers each of size 65536.
130719 11:37:00 mysqlbackup: INFO: Apply-log operation starts with following threads
  1 read-threads    1 process-threads
130719 11:37:00 mysqlbackup: INFO: ibbackup_logfile's creation parameters:
          start lsn 135380480, end lsn 135384397,
          start checkpoint 135380756.
 mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 .... 99 Setting log file size to 5242880
Setting log file size to 5242880
130719 11:37:00 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to
          lsn 135384397.
 mysqlbackup: INFO: Last MySQL binlog file position 0 85573, file name mysql-bin.000013
130719 11:37:00 mysqlbackup: INFO: The first data file is '/mnt/backup/hotbackups/datadir/ibdata1'
          and the new created log files are at '/mnt/backup/hotbackups/datadir'
130719 11:37:01 mysqlbackup: INFO: Apply-log operation completed successfully.
130719 11:37:01 mysqlbackup: INFO: Full backup prepared for recovery successfully.

mysqlbackup completed OK!

Now,  we can take a look at the backup file that was created:

root@prodserver011: $ pwd
/mnt/backup/hotbackups
root@prodserver011: $ ls -l
total 8
-rw-r--r--   1 root  staff  351 Jul 19 11:36 backup-my.cnf
drwx------  21 root  staff  714 Jul 19 11:37 datadir
drwx------   6 root  staff  204 Jul 19 11:37 meta
$ ls -l datadir
total 102416
drwx------   5 root  staff       170 Jul 19 11:36 comicbookdb
-rw-r-----   1 root  staff   5242880 Jul 19 11:37 ib_logfile0
-rw-r-----   1 root  staff   5242880 Jul 19 11:37 ib_logfile1
-rw-r--r--   1 root  staff      4608 Jul 19 11:37 ibbackup_logfile
-rw-r--r--   1 root  staff  41943040 Jul 19 11:37 ibdata1
drwx------  88 root  staff      2992 Jul 19 11:36 mysql
drwx------  55 root  staff      1870 Jul 19 11:36 performance_schema
drwx------   3 root  staff       102 Jul 19 11:36 test
drwx------  30 root  staff      1020 Jul 19 11:36 testcert
drwx------  19 root  staff       646 Jul 19 11:36 watchdb
 
root@prodserver011: $ ls -l meta
total 216
-rw-r--r--  1 root  staff  90786 Jul 19 11:37 backup_content.xml
-rw-r--r--  1 root  staff   5746 Jul 19 11:36 backup_create.xml
-rw-r--r--  1 root  staff    265 Jul 19 11:37 backup_gtid_executed.sql
-rw-r--r--  1 root  staff    321 Jul 19 11:37 backup_variables.txt

As you can see, the backup was created in /mnt/backup/hotbackups. If I wanted to have a unique folder for this backup, I can use the –with-timestamp.

The –with-timestamp option places the backup in a subdirectory created under the directory you specified above. The name of the backup subdirectory is formed from the date and the clock time of the backup run.

We can run the same backup command again, but with the –with-timestamp option:

$ /usr/local/meb/bin/mysqlbackup --user=root --password --backup-dir=/mnt/backup/hotbackups backup-and-apply-log --with-timestamp

$ pwd
/mnt/backup/hotbackups
root@prodserver011: $ ls -l
total 0
drwx------  5 root  staff  170 Jul 19 11:49 2015-05-19_11-49-48
$ ls -l 2015-05-19_11-49-48
total 8
-rw-r--r--   1 root  staff  371 Jul 19 11:49 backup-my.cnf
drwx------  21 root  staff  714 Jul 19 11:49 datadir
drwx------   6 root  staff  204 Jul 19 11:49 meta

Note: If you don’t use the backup-and-apply-log option you will need to read this: Immediately after the backup job completes, the backup files might not be in a consistent state, because data could be inserted, updated, or deleted while the backup is running. These initial backup files are known as the raw backup.

During the backup, mysqlbackup copies the accumulated InnoDB log to a file called ibbackup_logfile. This log file is used to “roll forward” the backed-up data files, so that every page in the data files corresponds to the same log sequence number of the InnoDB log. This phase also creates new ib_logfiles that correspond to the data files.

Backing Up an Entire MySQL Instance using backup-to-image

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

Verifying a Backup

You can check the integrity of your backup using the validate command.

$ ./mysqlbackup --backup-image=/mnt/backup/backups/my.mbi validate

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:46:09 MAIN    INFO: A thread created with Id '139996701841216' 
170214 17:46:09 MAIN    INFO: Starting with following command line ...
 ./mysqlbackup --backup-image=/mnt/backup/backups/my.mbi validate 

170214 17:46:09 MAIN    INFO: 
IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'validate' run mysqlbackup
           prints "mysqlbackup completed OK!".

170214 17:46:09 MAIN    INFO: Backup Image MEB version string: 4.1.0 [2017/02/13]
170214 17:46:09 MAIN    INFO: MySQL server version is '5.7.17'
170214 17:46:09 MAIN    INFO: Creating 14 buffers each of size 16777216.
170214 17:46:09 MAIN    INFO: Validate operation starts with following threads
  1 read-threads    6 process-threads
170214 17:46:09 MAIN    INFO: Validating image ... /mnt/backup/backups/my.mbi
170214 17:46:09 PCR1    INFO: A thread created with Id '139996431771392' 
170214 17:46:09 PCR2    INFO: A thread created with Id '139996423378688' 
170214 17:46:09 PCR3    INFO: A thread created with Id '139996414985984' 
170214 17:46:09 PCR4    INFO: A thread created with Id '139996406593280' 
170214 17:46:09 PCR5    INFO: A thread created with Id '139996263982848' 
170214 17:46:09 PCR6    INFO: A thread created with Id '139996398200576' 
170214 17:46:09 RDR1    INFO: A thread created with Id '139996440164096' 
170214 17:46:09 PCR1    INFO: Validate: [Dir]: meta
170214 17:46:10 PCR4    INFO: Validate: [Dir]: datadir/mysql
170214 17:46:10 PCR2    INFO: Validate: [Dir]: datadir/performance_schema
170214 17:46:10 PCR2    INFO: Validate: [Dir]: datadir/pets
.
.
.
170214 17:46:10 MAIN    INFO: datadir/mysql/time_zone_leap_second.ibd validated.
170214 17:46:10 MAIN    INFO: datadir/mysql/time_zone_name.ibd validated.
170214 17:46:10 MAIN    INFO: datadir/mysql/time_zone_transition.ibd validated.
170214 17:46:10 MAIN    INFO: datadir/mysql/time_zone_transition_type.ibd validated.
170214 17:46:10 MAIN    INFO: datadir/pets/cats.ibd validated.
170214 17:46:10 MAIN    INFO: datadir/sys/sys_config.ibd validated.
170214 17:46:10 MAIN    INFO: Validate operation completed successfully.
170214 17:46:10 MAIN    INFO: Backup Image validation successful.
170214 17:46:10 MAIN    INFO: Source Image Path = /mnt/backup/backups/my.mbi

mysqlbackup completed OK!

Restoring a Database

To restore a MySQL instance from a backup to a database server:
  • Shut down the database server.
  • Delete all files inside the server's data directory. If the data directory is not empty, you have to use the --force option to allow overwriting of the old data, or mysqlbackup will error out.
  • Use, for example, the copy-back-and-apply-log command, which converts the raw backup into a prepared backup by updating it to a consistent state, and then copies the tables, indexes, metadata, and any other required files onto a target server.
  • When the following InnoDB settings for the backup are different from those on the target server, it is important to supply the values for the backup to mysqlbackup during restore and to mysqld when you start the restored server (otherwise, the restore might fail, and you might have problem starting the restored server) :
          innodb_data_file_path
          innodb_log_file_size
          innodb_log_files_in_group
          innodb_page_size
          innodb_checksum_algorithm
          innodb_undo_tablespaces
          innodb_buffer_pool_filename

If you are not sure about those settings for your backup, they are stored in the backup-my.cnf file during the backup—you can find the file either in the temporary directory you specified with --backup-dir when you created the single-image backup, or in a backup directory you can create by unpacking the backup image using the extract command.

$ ./mysqlbackup  --defaults-file=/etc/mysql/my.cnf --datadir=/var/lib/mysql  \
   --backup-image=/mnt/backup/backups/my.mbi --backup-dir=/mnt/backup/restore-tmp copy-back-and-apply-log

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 22:11:38 MAIN    INFO: A thread created with Id '140027243398976' 
170214 22:11:38 MAIN    INFO: Starting with following command line ...
 ./mysqlbackup --defaults-file=/etc/mysql/my.cnf --datadir=/var/lib/mysql 
        --backup-image=/mnt/backup/backups/my.mbi 
        --backup-dir=/mnt/backup/restore-tmp copy-back-and-apply-log 

170214 22:11:38 MAIN    INFO: 
IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'copy-back-and-apply-log' run mysqlbackup
           prints "mysqlbackup completed OK!".

170214 22:11:38 MAIN    INFO: Backup Image MEB version string: 4.1.0 [2017/02/13]
170214 22:11:38 MAIN    INFO: MySQL server version is '5.7.17'
170214 22:11:38 MAIN WARNING: If you restore to a server of a different version, the innodb_data_file_path parameter might have a different default. In 
that case you need to add 'innodb_data_file_path=ibdata1:12M:autoextend' to the target server configuration.
170214 22:11:38 MAIN WARNING: If you restore to a server of a different version, the innodb_log_files_in_group parameter might have a different default. 
In that case you need to add 'innodb_log_files_in_group=2' to the target server configuration.
170214 22:11:38 MAIN WARNING: If you restore to a server of a different version, the innodb_log_file_size parameter might have a different default. In th
at case you need to add 'innodb_log_file_size=50331648' to the target server configuration.
170214 22:11:38 MAIN    INFO: Keyring service not initialized. MEB cannot validate the encryption keys.
170214 22:11:38 MAIN    INFO: Valid keyring to be specified on server start to access the encrypted tables.
170214 22:11:38 MAIN    INFO: MEB logfile created at /mnt/backup/restore-tmp/meta/MEB_2017-02-14.22-11-38_copy_back_img_to_datadir.log

.
.
.
170214 22:11:40 PCR5    INFO: Copying database directory: sys
170214 22:11:40 MAIN    INFO: Total files as specified in image: 297
170214 22:11:40 MAIN    INFO: MySQL server version is '5.7.17'
170214 22:11:40 MAIN    INFO: MySQL server compile os version is 'Linux'
170214 22:11:40 MAIN    INFO: Writing config file for server '5.7.17'.
170214 22:11:40 MAIN    INFO: Creating server config files server-my.cnf and server-all.cnf in /var/lib/mysql

[Some lines of output not shown]

170214 22:11:40 MAIN    INFO: Copy-back operation completed successfully.
170214 22:11:40 MAIN    INFO: Source Image Path = /mnt/backup/backups/my.mbi


170214 22:11:40 MAIN    INFO: MySQL server version is '5.7.17'
170214 22:11:40 MAIN    INFO: Restoring ...5.7.17 version
170214 22:11:40 MAIN    INFO: Creating 14 buffers each of size 65536.
170214 22:11:40 MAIN    INFO: Apply-log operation starts with following threads
  1 read-threads    1 process-threads    6 apply-threads
170214 22:11:40 MAIN    INFO: Using up to 100 MB of memory.
170214 22:11:40 MAIN    INFO: ibbackup_logfile's creation parameters:
          start lsn 2543104, end lsn 2543132,
          start checkpoint 2543123.
170214 22:11:40 ALW1    INFO: A thread created with Id '140026922972928' 
170214 22:11:40 ALW6    INFO: A thread created with Id '140027204466432' 
170214 22:11:40 ALW5    INFO: A thread created with Id '140027212859136' 
170214 22:11:40 ALW4    INFO: A thread created with Id '140026948151040' 
170214 22:11:40 ALW3    INFO: A thread created with Id '140026939758336' 
170214 22:11:40 ALW2    INFO: A thread created with Id '140026931365632' 
170214 22:11:40 RDR1    INFO: A thread created with Id '140027196073728' 
170214 22:11:40 PCR1    INFO: A thread created with Id '140027187681024' 
170214 22:11:40 PCR1    INFO: InnoDB: Doing recovery: scanned up to log sequence number 2543132.
170214 22:11:40 PCR1    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 
170214 22:11:40 PCR1    INFO: InnoDB: Setting log file size to 50331648.
170214 22:11:45 PCR1    INFO: InnoDB: Setting log file size to 50331648.
170214 22:11:50 PCR1    INFO: We were able to parse ibbackup_logfile up to
          lsn 2543132.
170214 22:11:50 PCR1    INFO: The first data file is '/var/lib/mysql/ibdata1'
                              and the new created log files are at '/var/lib/mysql'
170214 22:11:50 MAIN    INFO: MySQL server version is '5.7.17'
170214 22:11:50 MAIN    INFO: Restoring ...5.7.17 version
170214 22:11:50 MAIN    INFO: Apply-log operation completed successfully.
170214 22:11:50 MAIN    INFO: Full Backup has been restored successfully.

mysqlbackup completed OK! with 3 warnings

Depending on how you are going to start the restored server, you might need to adjust the ownership of the restored data directory. 

$ chown -R mysql:mysql /path/to/datadir

Making a Single-File Backup

This command creates a single backup image on the given absolute path. It still requires --backup-dir, which is used to hold temporary output, status, and metadata files.

$ mysqlbackup --backup-image=/backups/sales.mbi --backup-dir=/backup-tmp backup-to-image

Single-File Backup to Relative Path

$ mysqlbackup --backup-image=sales.mbi --backup-dir=/backups backup-to-image

Extract Existing Image to Backup Directory

$ mysqlbackup --backup-dir=/var/backup --backup-image=/backup/my.mbi image-to-backup-dir

List Single-File Backup Contents

$ mysqlbackup --backup-image=/backup/my.mbi list-image

Extract Single-File Backup into Current Directory

$ mysqlbackup --backup-image=/var/my.mbi extract

Extract Single-File Backup into a Backup Directory

$ mysqlbackup --backup-image=/var/my.mbi --backup-dir=/var/backup extract

Selective Extract of Single File

$ mysqlbackup --backup-image=/var/my.mbi --src-entry=meta/comments.txt extract

Selective Extract of Single Directory

$ mysqlbackup --backup-image=/backup/my.mbi --src-entry=meta extract

Streaming the Backup Data to Another Device or Server

The following command streams the backup as a single-file output to a remote host, where it may be saved directly to a tape device. --backup-dir=/tmp designates the directory for storing temporary files rather than the final output file.

$ mysqlbackup --backup-image=- --backup-dir=/tmp backup-to-image | \
ssh user@host command arg1 arg2...

The following command streams the backup as a single backup file to be restored on a remote MySQL server:

$ mysqlbackup  --backup-dir=backup --backup-image=-  --compress backup-to-image | \
ssh <user name>@<remote host name> 'mysqlbackup --backup-dir=backup_tmp --datadir=/data \
--innodb_log_group_home_dir=. \
--innodb_log_files_in_group=<innodb_log_files_in_group_of_backedup_server> \
 --innodb_log_file_size=<innodb_log_file_size_of_backedup_server> \ 
--innodb_data_file_path=<innodb_data_file_path_of_backedup_server> \ 
--uncompress --backup-image=- copy-back-and-apply-log'

Stream a Backup Directory to a Remote MySQL Server

$ mysqlbackup --backup-image=- --backup-dir=/path/to/my/backup backup-dir-to-image | \
ssh <user name>@<remote host name> \ 
'mysqlbackup --backup-dir=backup_tmp --datadir=/data --backup-image=- copy-back-and-apply-log'

Creating a Cloud Backup in an Oracle Cloud Storage Service Instance

$ mysqlbackup \
--include-tables=testdb.t1 --use-tts=with-full-locking \
--cloud-service=openstack  --cloud-container=<oracle storage cloud container> \
--cloud-user-id=<serviceInstanceName>-<identityDomainName>:<userName> --cloud-password=<password> \
--cloud-tempauth-url=https://<dataCenterCode>.storage.oraclecloud.com \
--cloud-trace=1 --cloud-object=image_900.mbi \
--backup-dir=/home/user/dba/orbackuptmpdir \
--backup-image=- \
backup-to-image

Creating a Cloud Backup on Amazon S3

$ mysqlbackup\
--cloud-service=s3 --cloud-aws-region=<aws region> \
--cloud-access-key-id=<aws access key id> --cloud-secret-access-key=< aws secret access key> \
--cloud-bucket=<s3 bucket name> --cloud-object-key=<aws object key> \
--backup-dir=/home/user/dba/s3backuptmpdir \
--backup-image=- \
backup-to-image

Extract an Existing Image from an Oracle Storage Cloud Service instance to a Backup Directory

$ mysqlbackup \
--cloud-service=openstack  --cloud-container=<oracle storage cloud container> \
--cloud-user-id=<serviceInstanceName>-<identityDomainName>:<userName> --cloud-password=<password> \
--cloud-tempauth-url=https://<dataCenterCode>.storage.oraclecloud.com \
--cloud-object=image_930.mbi \
--backup-dir=/home/user/dba/orbackupdir \
--backup-image=- \
image-to-backup-dir

Extract an Existing Image from Amazon S3 Cloud Storage to a Backup Directory

$ mysqlbackup\
--cloud-service=s3 --cloud-aws-region=<aws region> \
--cloud-access-key-id=<aws access key id> --cloud-secret-access-key=< aws secret access key> \
--cloud-bucket=<s3 bucket name> --cloud-object-key=<aws object key> \
--backup-dir=/home/user/dba/s3backupdir \
--backup-image=- \
image-to-backup-dir

Creating Incremental Backups Using Only the Redo Log

The changes to InnoDB tables are determined based on the contents of the InnoDB redo log. Since the redo log files have a fixed size that you know in advance, it can require less I/O to read the changes from them than to scan the InnoDB tablespace files to locate the changed pages, depending on the size of your database, amount of DML activity, and size of the redo log files.

To calculate the size of the redo log, issue the command SHOW VARIABLES LIKE 'innodb_log_file%' and, based on the output, multiply the innodb_log_file_size setting by the value of innodb_log_files_in_group. To compute the redo log size at the physical level, look into the datadir directory of the MySQL instance and sum up the sizes of the files matching the pattern ib_logfile*.

The InnoDB LSN value corresponds to the number of bytes written to the redo log. To check the LSN at some point in time, issue the command SHOW ENGINE INNODB STATUS and look under the LOG heading. While planning your backup strategy, record the LSN values periodically and subtract the earlier value from the current one to calculate how much redo data is generated each hour, day, and so on.

The incremental backup feature is primarily intended for InnoDB tables, or non-InnoDB tables that are read-only or rarely updated. Incremental backups detect changes at the level of pages in the InnoDB data files, as opposed to table rows; each page that has changed is backed up.

You cannot perform incremental backups with the --compress option.

To create an incremental backup image with the --start-lsn option, use the following command, specifying with --incremental-backup-dir the backup directory, which, in this case, is a directory for storing the metadata for the backup and some temporary files:

$ mysqlbackup --defaults-file=/home/dbadmin/my.cnf --incremental \
  --start-lsn=2654255716 \
  --with-timestamp \
  --incremental-backup-dir=/incr-tmp \
 --backup-image=/incr-backup/incremental_image.bi
  backup-to-image

In the following example though, because --backup-image does not provide a full path to the image file to be created, the incremental backup image is created under the folder specified by --incremental-backup-dir:
  
$ mysqlbackup --defaults-file=/home/dbadmin/my.cnf --incremental \
  --start-lsn=2654255716 \
  --with-timestamp \
  --incremental-backup-dir=/incr-images \
  --backup-image=incremental_image1.bi
  backup-to-image

In the following example, the --incremental-base=history:last_backup option is used, given which mysqlbackup fetches the LSN of the last successful (non-TTS) full or partial backup from the mysql.backup_history table and performs an incremental backup basing on that.

$ mysqlbackup --defaults-file=/home/dbadmin/my.cnf \
  --incremental --incremental-base=history:last_backup \
  --backup-dir=/home/dbadmin/temp_dir \
  --backup-image=incremental_image1.bi 
   backup-to-image

Use the following commands to create incremental directory backups, using the --incremental-base

$ mysqlbackup --defaults-file=/home/dbadmin/my.cnf --incremental \
  --incremental-base=dir:/incr-backup/wednesday \
  --incremental-backup-dir=/incr-backup/thursday \
  backup

Making a Compressed Backup

You can only use the --compress option for full backups, not for incremental backups.
  
The backup compression feature works only for InnoDB tables. After the InnoDB tablespace files are compressed during backup, they receive the .ibz extension.
  
 The binary log and relay log files are compressed and saved with the .bz extension when being included in a compressed backup.

$ mysqlbackup --defaults-file=/etc/my.cnf --compress --compress-level=5 \
  --backup-image=backup.img backup-to-image

This is a sample command for making a compressed directory backup:

$ mysqlbackup --defaults-file=/etc/my.cnf --compress --compress-level=5 backup
$ mysqlbackup --defaults-file=/etc/my.cnf --compress --compress-level=5 backup-and-apply-log

Making a Partial Backup

Including or excluding specific tables by their names. This uses the --include-tables or --exclude-tables option.Including some or all InnoDB tables, but not other table types. This uses the --only-innodb option.
  
  Note : Typically, a partial backup is more difficult to restore than a full backup, because the backup data might not include the necessary interrelated pieces to constitute a complete MySQL instance. In particular, InnoDB tables have internal IDs and other data values that can only be restored to the same instance, not a different MySQL server. Always fully test the recovery procedure for any partial backups to understand the relevant procedures and restrictions.
  
 Because the InnoDB system tablespace holds metadata about InnoDB tables from all databases in an instance, restoring a partial backup on a server that includes other databases could cause the system to lose track of those InnoDB tables in the other databases. Always restore partial backups on a fresh MySQL server instance without any other InnoDB tables that you want to preserve.Including all tables with names starting with “emp” into the backup:

$ mysqlbackup \
 --host=localhost --user=mysqluser --protocol=TCP --port=3306 \
 --backup-dir=$MEB_TEMP_BACKUP_DIR --backup-image=$MEB_BACKUPS_DIR/my.mbi \
 --include-tables="\.emp" \
 backup-to-image

Taking a backup of all tables except tables from the “mysql” and “performance_schema” databases:

$ mysqlbackup \
 --host=localhost --user=mysqluser --protocol=TCP --port=3306 \
 --backup-dir=$MEB_TEMP_BACKUP_DIR --backup-image=$MEB_BACKUPS_DIR/my.mbi \
 --exclude-tables="^(mysql|performance_schema)\." \
 backup-to-image

Taking a backup of all tables in the “sales reps” database, but excludes the table with the name “euro-asia”

$ mysqlbackup \
 --host=localhost --user=mysqluser --protocol=TCP --port=3306 \
 --backup-dir=$MEB_TEMP_BACKUP_DIR --backup-image=$MEB_BACKUPS_DIR/my.mbi \
 --include-tables="^sales reps\." --exclude-tables="^sales reps\.euro-asia" \
 backup-to-image

Backing up all InnoDB tables, but not .frm files:

$ mysqlbackup --defaults-file=/home/dbadmin/my.cnf --only-innodb backup-to-image

The --only-innodb and --only-innodb-with-frm options back up InnoDB tables only, skipping those of other storage engines. You might also use them together with the --include option to make selective backup of InnoDB tables while excluding all other files created by other storage engines.

Optimistic Backup Using the Option optimistic-time=YYMMDDHHMMSS

In this example, tables that have been modified since the noon of May 16, 2011 are treated as busy tables and backed up in the normal phase of an optimistic backup, and all other tables are backed up in the optimistic phase:

$ mysqlbackup --defaults-file=/etc/my.cnf --optimistic-time=110516120000 backup-to-image

Optimistic Backup Using the Option optimistic-time=now

$ mysqlbackup --defaults-file=/etc/my.cnf --optimistic-time=now backup-to-image

Restoring a Database

$ mysqlbackup --defaults-file=<my.cnf> -uroot --backup-image=<image_name> \
  --backup-dir=<backupTmpDir> --datadir=<restoreDir> copy-back-and-apply-log

Restoring a Compressed Backup
$ mysqlbackup --defaults-file=<my.cnf> -uroot --backup-image=<image_name> \
  --backup-dir=<backupTmpDir> --datadir=<restoreDir> --uncompress copy-back-and-apply-log

Restoring a Compressed Directory Backup
$ mysqlbackup --defaults-file=<my.cnf> -uroot --backup-dir=<backupDir> --datadir=<restoreDir> \
  --uncompress copy-back-and-apply-log

Restoring an Incremental Backup Image
$ mysqlbackup --defaults-file=<my.cnf> -uroot --backup-image=<inc_image_name> \
  --incremental-backup-dir=<incBackupTmpDir> --datadir=<restoreDir> --incremental \
  copy-back-and-apply-log

Restoring an Incremental Backup Directory
$ mysqlbackup --backup-dir=/full-backup/2010-12-08_17-14-11 apply-log
..many lines of output...
101208 17:15:10  mysqlbackup: Full backup prepared for recovery successfully!

101208 17:15:10 mysqlbackup: mysqlbackup completed OK!
Then, we apply the changes from the incremental backup using the apply-incremental-backup command:


$ mysqlbackup --incremental-backup-dir=/incr-backup/2010-12-08_17-14-48 
  --backup-dir=/full-backup/2010-12-08_17-14-11 apply-incremental-backup
...many lines of output...
101208 17:15:12 mysqlbackup: mysqlbackup completed OK!


4 comments:

  1. Nice post.
    Indian Cyber Army’s most awaited internship is live now. Summer Internship 2018 on “ Ethical hacking” and book your seats before it runs out.Candidates have to get themselves registered to be a part of this Internship program. Here internship will give you on-the-job experience, help you learn whether you and Cyber security industry are a good match and can provide you with valuable connections and references. Here interns are usually exposed to a wide variety of tasks and responsibilities which allows the intern to showcase their strengths by working on projects for various managers that work on different parts of Indian Cyber Army. Becoming a high performing intern is a fantastic way to improve your employment prospects. This internship can be a great way to get your foot in the door of success with a prestigious or desirable Organization.As career in ethical hacking is most in demand.

    ReplyDelete
  2. Thanks for the information.It is really nice .Information security is the set of processes that maintain the confidentiality, integrity and availability of business data in its various forms.In this age of Technology advancement, computer and information technology have not only brought convenience to citizens in modern life but also for policemen & various Government officials of the nation to fight cybercrime through various modus operandi. Indian Cyber Army has been dedicated in fighting cyber crime, striving to maintain law and order in cyberspace so as to ensure that everyone remains digitally safe.Read more:- Information Security

    ReplyDelete
  3. thanks for sharing the more valuable information.
    Indian Cyber Army credibility in Ethical hacking training & Cybercrime investigation training is acknowledged across nation as we offer hands on practical knowledge and full assistance with basic as well as advanced level ethical hacking & cybercrime investigation courses. The training is conducted by subject specialist corporate professionals with wide experience in managing real-time ethical hacking/ cyber security projects. Indian Cyber Army implements a blend of academic learning and practical sessions to give the candidate optimum exposure.Ethical hacking training ,
    Ethical hacking course

    ReplyDelete