Thursday Apr 03, 2014

Data Encryption with MySQL Enterprise Backup 3.10

Introduction

MySQL Enterprise Backup (MEB) 3.10 introduces support for encrypted backups by allowing backup images, or single-file backups, to be encrypted. However, backups stored in multiple files in a backup directory can not be encrypted.

Any MEB command that produces a backup image can be optionally requested to encrypt it. The encrypted backup image can be stored in a file or tape in the same way as an unencrypted backup image. Similarly, any MEB command that reads data from a backup image accepts also an encrypted backup image. This means that encrypted backups can be used in all the same situations as unencrypted backup images.

MEB encrypts data with Advanced Encryption Standard (AES) algorithm in CBC mode with 256-bit keys. AES is a symmetric block cipher which means that the same key is used both for encryption and decryption. The AES cipher has been adopted by the U.S. government and it is now used worldwide.

A new format for the encrypted backup image is introduced. This is a proprietary format developed by Oracle and it allows efficient encryption and decryption in parallel.

Encryption keys

Encryption keys are strings of 256 bits (or 32 bytes) that are represented by strings of 64 hexadecimal digits. The simplest way to create an encryption key for MEB is to type 64 randomly chosen hexadecimal digits and save them in a file. Another method is to use some shell tool to generate a string of random bytes and encode it as hexadecimal digits. For example, one could use the OpenSSL shell command to generate a key as follows:

$ openssl rand 32 -hex
8f3ca9b850ec6366f4a54feba99f2dc42fa79577158911fe8cd641ffff1e63d6

This command uses random data generated on the host for creating the key. Whichever method is used for the creation of the key, the essential point is that the resulting key consists of random bits.

The security of MEB encryption is based on two rules that apply not only to MEB but to all encryption schemes using symmetric block ciphers:

Rule 1: The encryption keys must be random.

Rule 2: The encryption keys must remain secret at all times.

When these rules are followed, it is very difficult for unauthorized persons to get access to the secure data.

Encryption keys can be specified either on the command-line with the

--key=KEY 
option where KEY is a string of 64 hexadecimal digits, or in a file with the
--key-file=FILENAME

option where FILENAME is the name of the file that contains a string of 64 hexadecimal digits.

It is important to notice that specifying the key on the command-line with the --key option is generally not secure because the command-line is usually visible to other users on the system and it may even be saved in system log files that may be accessible by unauthorized persons. Therefore, the --key-file option should be preferred over the --key option in all production environments, and the use of the --key option should be limited to testing and software development environments.

Using encryption

Encryption is very simple to use. Any MEB command that produces a backup image can be requested to encrypt it by specifying the --encrypt option with either --key or --key-file option. The following example shows how to make a compressed backup and store it as an encrypted backup image.


$ mysqlbackup --encrypt --key-file=/backups/key --compress --backup-dir=/full-backup  --backup-image=/backups/image.enc  backup-to-image

MySQL Enterprise Backup version 3.10.0 Linux-3.2.0-58-generic-i686 [2014/03/04]

Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...

 /home/pekka/bzr/meb-3.10/src/build/mysqlbackup --encrypt

        --key-file=/backups/key --compress --backup-dir=/full-backup

        --backup-image=/backups/image.enc backup-to-image

 mysqlbackup: INFO:

IMPORTANT: Please check that mysqlbackup run completes successfully.

           At the end of a successful 'backup-to-image' run mysqlbackup

           prints "mysqlbackup completed OK!".

140306 21:40:33 mysqlbackup: INFO: MEB logfile created at /full-backup/meta/MEB_2014-03-06.21-40-33_compress_img_backup.log

 mysqlbackup: WARNING: innodb_checksum_algorithm could not be obtained from config or server variable and so mysqlbackup uses the default checksum algorithm 'innodb'.

--------------------------------------------------------------------

                       Server Repository Options:

--------------------------------------------------------------------

...

...

...

Backup Image Path = /backups/image.enc

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

 mysqlbackup: INFO: Uses LZ4 r109 for data compression.

 mysqlbackup: INFO: Creating 18 buffers each of size 16794070.

140306 21:40:36 mysqlbackup: INFO: Compress Image Backup operation starts with following threads

        1 read-threads    6 process-threads    1 write-threads

140306 21:40:36 mysqlbackup: INFO: System tablespace file format is Barracuda.

140306 21:40:36 mysqlbackup: INFO: Starting to copy all innodb files...

 mysqlbackup: INFO: Copying meta file /full-backup/backup-my.cnf.

 mysqlbackup: INFO: Copying meta file /full-backup/meta/backup_create.xml.

140306 21:40:36 mysqlbackup: INFO: Copying /sqldata/simple-5.6/ibdata1 (Barracuda file format).

140306 21:40:36 mysqlbackup: INFO: Found checkpoint at lsn 188642964.

...

...

...

140306 21:40:51 mysqlbackup: INFO: Compress Image Backup operation completed successfully.

 mysqlbackup: INFO: Image Path = /backups/image.enc

-------------------------------------------------------------

   Parameters Summary         

-------------------------------------------------------------

   Start LSN                  : 188642816

   End LSN                    : 188642964

-------------------------------------------------------------

mysqlbackup completed OK! with 2 warnings



This resulting encrypted backup image (file "image.enc") can be used with all commands that accept a backup image in the same way as an unencrypted backup image. For example, one could restore the server from the encrypted backup as follows:


$ mysqlbackup --decrypt --key-file=/backups/key --uncompress --backup-image=/backups/image.enc --backup-dir=/full-backup copy-back-and-apply-log

MySQL Enterprise Backup version 3.10.0 Linux-3.2.0-58-generic-i686 [2014/03/04]

Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...

 /home/pekka/bzr/meb-3.10/src/build/mysqlbackup --decrypt

        --key-file=/backups/key --uncompress --backup-image=/backups/image.enc

        --backup-dir=/full-backup copy-back-and-apply-log

 mysqlbackup: 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!".

 mysqlbackup: INFO: Backup Image MEB version string: 3.10.0 [2014/03/04]

 mysqlbackup: INFO: The input backup image contains compressed backup.

140310 12:51:54 mysqlbackup: INFO: MEB logfile created at /full-backup/meta/MEB_2014-03-10.12-51-54_copy_back_cmprs_img_to_datadir.log

...

...

140310 12:52:14 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to

          lsn 188642964.

140310 12:52:14 mysqlbackup: INFO: The first data file is '/home/pekka/sqldata/copyback-simple-5.6/ibdata1'

          and the new created log files are at '/home/pekka/sqldata/copyback-simple-5.6'

140310 12:52:14 mysqlbackup: INFO: Apply-log operation completed successfully.

140310 12:52:14 mysqlbackup: INFO: Full Backup has been restored successfully.

mysqlbackup completed OK!



In these examples we have used the --key-file option for specifying the encryption key because it is more secure than giving the key on the command-line with the --key option.

Tips

This section describes two tips that may be useful when working with encrypted backups.

The "Wrong key" error

Encryption and decryption use the same key. If decryption is attempted with a key different from the encryption key, a wrong key error occurs. When this happens, MEB prints an error message like the one shown below.


MySQL Enterprise Backup version 3.10.0 Linux-3.2.0-58-generic-i686 [2014/03/04]

Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...

        mysqlbackup --backup-image=/backups/image.enc --decrypt

        --key-file=/key-file2 list-image

 mysqlbackup: INFO:

IMPORTANT: Please check that mysqlbackup run completes successfully.

           At the end of a successful 'list-image' run mysqlbackup

           prints "mysqlbackup completed OK!".

 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.

 mysqlbackup: ERROR: Failed to decrypt encrypted data in file /backups/image.enc : the file may be corrupted or a wrong encryption key was specified.



For the user, this can be problematic because two possible reasons for the failure are offered in the error message: either the backup is corrupted or a wrong key was supplied. This is not a bug or feature of MySQL Enterprise Backup but, instead, it is a theoretical limitation imposed by the encryption scheme. It is not possible even in theory to distinguish with absolute certainty between these two explanations when decryption fails.

However, these two explanations are not always equally likely. If decryption fails at the very start without decrypting any data, then it is more likely that a wrong key was supplied. On the other hand, if the decryption fails later after some data was successfully decrypted, then it is very likely that the correct key was given but the encrypted backup is broken. Using these two rules it is possible to determine with high probability the cases where decryption fails because of a wrong key.

Recognizing encrypted backups

On Unix-like operating systems "magic numbers" may be used for identifying the type of a file. Magic numbers are patterns in files that allow recognizing the type of a file by examining the first bytes in the file. Both the unencrypted backup images and encrypted backup images have magic numbers that can be used by shell tools to detect the file type. For example, by putting these lines to the /etc/magic file

0   string  MBackuP\n   MySQL Enterprise Backup backup image
0   string  MebEncR\n   MySQL Enterprise Backup encrypted backup


the file command detects the backups images as follows:

$ file /backups/image1 /backups/image2
/backups/image1: MySQL Enterprise Backup backup image
/backups/image2: MySQL Enterprise Backup encrypted backup


Thursday Sep 19, 2013

Backing up selective innodb tables using MEB.

MySQL 5.6 introduced the TTS(transportable table spaces) feature which enables moving a table from one server to another. This feature coupled with MEB 3.9 enables backing up a set of tables matching (regex specified with) the –include option.

The backup of selective tables using transportable tablespaces feature of innodb is referred as tts/selective backup in the remainder of the section.

The difference between a regular partial backup and with using tts is that the regular partial backups are stand alone and cannot be plugged into a another server where as the tts backups in contrast enables the tables to be plugged into another server instance


The Selective Backup Operation

To specify a set of tables to be backed up use the --use-tts option along with the --include=[regex] option for the backup operation. --use-tts option supports two values with-minimum-locking and with-full-locking.

with-minimum-locking -  This is the default option. The tables being backed up are hot copied in parallel along with the redo log. After the data file copy, the tables are locked in read only mode, the delta of the log is copied into the backup, and the locks are released. The advantage of this option is that the tables are available for modifications during most part of the back up process and are available in read only mode for a short duration.

with-full-locking - With this options the tables are locked in read only mode during the entire duration of the backup. As there cannot be any modifications while the backup is happening, the tables are consistent and the redo log is not backed up. This saves space and makes the apply log step faster as it just involves some book keeping operations.

Eg:
mysqlbackup --port=3306 --protocol=tcp --user=root –backup-dir=backupdir –include=Sales.Sales_*  --use-tts backup-and-apply-log


The Restore(copy-back) Operation

Restoring from a tts backup requires the server to be running in case of a tts backup unlike the other types of backup. The connection options of the server where the set of tables have to be restored need to be provided for the copyback of a tts backup.

Eg:
mysqlbackup --defaults-file=/backup-my.cnf --port=3406 --protocol=tcp --user=root --backup-dir=backupdir --datadir=<target_server_datadir> copy-back


Advantages of using MEB for tts Backup

Have a backup strategy for backing a subset of tables(for eg: backing up only important/most used tables).
Take advantage of compressed and image backup options supported by MEB.
This feature can effectively be used to copy a set of innodb tables from one server instance to another.  

This feature handles only tables having their own tablespaces(innodb_file_per_table on) and does not support partitioned tables.

Thursday Oct 04, 2012

Optimistic non-locking copy of InnoDB .frm files


MySQL Enterprise Backup(MEB) does hot backup of innodb data and log files. Till MEB 3.6.1, the user backs up the only innodb tables in a 3 step process:

STEP 1. Take backup using --only-innodb option

STEP 2. Temporarily make the table read only by executing “FLUSH TABLES WITH READ LOCK”

STEP 3. Manually copy the .frm files of innodb tables to the destination directory where backup is stored.

 


MEB 3.7.0 has an enhancement to innodb file copying. The .frm files gets copied along with the hot backup done for innodb files. I would like to make the blog a little interactive by explaining the feature as answers:


1. What are these .frm files?


The files containing the metadata, such as the table definition, of a MySQL table.

For backups, the full set of .frm files are always required along with the backup data, to be able to restore tables that are altered or dropped after the backup.


2. Can the .frm files not be copied by MEB itself?


--only-innodb-with-frm is the new option introduced in MEB 3.7.1 to do a copy of .frm files without locking the tables during backup operation itself. This is to reduce the pain of manually copying the .frm files. The option is intended for backups where you can ensure that no ALTER TABLE, CREATE TABLE, DROP TABLE, or other DDL statements modify the .frm files for InnoDB tables during the backup operation.


3. How is data consistency ensured?


MEB does validation of the .frm files after copying by comparing with the server directory to see if the timestamps of any of the .frm files is greater than the saved system time (check .frm time).  This change in timestamp of the .frm files will show if a table is altered during the process of backup.


The total number of frm files in the server directory is also verified against the copied contents. If the number of .frm files is less compared to server directory, it shows that table/tables have been dropped during the process of backup. If the number of .frm files is more compared to server directory, it shows that new table/tables have been created during backup operation.


4. How does MEB handle data inconsistency?


MEB copies the .frm files through several iterations,  does the validation and throws a WARNING if there is any inconsistency found in .frm files at the end of backup operation. This means the user is warned of some DDL operations that had occurred during backup operation, and has to manually copy the .frm files or do a backup again.


5. What is the option and explain its usage?


The option introduced is --only-innodb-with-frm which does optimistic copy of .frm files without locking. This can be used when the user wants to backup only innodb tables along with .frm files.

The option can take one of the 2 values: all | related.


--only-innodb-with-frm=all does copy of all .frm files of all innodb tables.

--only-innodb-with-frm=related works in conjunction with --include option.This is to allow partial backup of .frm files corresponding to the tables specified in --include.


Let me show the usage with example output:


./mysqlbackup -uroot --backup-dir=/logs/backupWithFrmAll --only-innodb-with-frm=all backup

MySQL Enterprise Backup version 3.7.1 [2012/06/05]

Copyright (c) 2003, 2012, Oracle and/or its affiliates. All Rights Reserved.


INFO: Starting with following command line ...

./mysqlbackup -uroot --backup-dir=/logs/backupWithFrmAll

       --only-innodb-with-frm=all backup


INFO: Got some server configuration information from running server.


IMPORTANT: Please check that mysqlbackup run completes successfully.

           At the end of a successful 'backup' run mysqlbackup

           prints "mysqlbackup completed OK!".


--------------------------------------------------------------------

                      Server Repository Options:

--------------------------------------------------------------------

 datadir                          =  /mysql/trydb/

 innodb_data_home_dir             =  

 innodb_data_file_path            =  ibdata1:10M:autoextend

 innodb_log_group_home_dir        =  /mysql/trydb/

 innodb_log_files_in_group        =  2

 innodb_log_file_size             =  5242880


--------------------------------------------------------------------

                      Backup Config Options:

--------------------------------------------------------------------

 datadir                          =  /logs/backupWithFrmAll/datadir

 innodb_data_home_dir             =  /logs/backupWithFrmAll/datadir

 innodb_data_file_path            =  ibdata1:10M:autoextend

 innodb_log_group_home_dir        =  /logs/backupWithFrmAll/datadir

 innodb_log_files_in_group        =  2

 innodb_log_file_size             =  5242880


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

mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.

mysqlbackup: INFO: System tablespace file format is Antelope.

mysqlbackup: INFO: Found checkpoint at lsn 1656792.

mysqlbackup: INFO: Starting log scan from lsn 1656320.

120817 15:36:22 mysqlbackup: INFO: Copying log...

120817 15:36:22 mysqlbackup: INFO: Log copied, lsn 1656792.

         We wait 1 second before starting copying the data files...

120817 15:36:23 mysqlbackup: INFO: Copying /mysql/trydb/ibdata1 (Antelope file format).

120817 15:36:23 mysqlbackup: INFO: Copying /mysql/trydb/innodb1/table2.ibd (Antelope file format).

120817 15:36:23 mysqlbackup: INFO: Copying /mysql/trydb/innodb1/table3.ibd (Antelope file format).

120817 15:36:23 mysqlbackup: INFO: Copying /mysql/trydb/innodb1/table1.ibd (Antelope file format).

mysqlbackup: INFO: Opening backup source directory '/mysql/trydb/'

120817 15:36:23 mysqlbackup: INFO: Starting to backup .frm files in the subdirectories of /mysql/trydb/

mysqlbackup: INFO: Copying innodb data and logs during final stage ...

mysqlbackup: INFO: A copied database page was modified at 1656792.

         (This is the highest lsn found on page)

         Scanned log up to lsn 1656792.

         Was able to parse the log up to lsn 1656792.

         Maximum page number for a log record 0

mysqlbackup: INFO: Copying non-innodb files took 2.000 seconds

120817 15:36:25 mysqlbackup: INFO: Full backup completed!

mysqlbackup: INFO: Backup created in directory '/logs/backupWithFrmAll'


-------------------------------------------------------------

  Parameters Summary         

-------------------------------------------------------------

  Start LSN                  : 1656320

  End LSN                    : 1656792

-------------------------------------------------------------


mysqlbackup completed OK!


bash$ ls /logs/backupWithFrmAll/datadir/innodb1/

table1.frm  table1.ibd  table2.frm  table2.ibd  table3.frm  table3.ibd


Here the backup directory contains all the .frm files of all the innodb tables.


./mysqlbackup -uroot --backup-dir=/logs/backupWithFrm --include="innodb1.table3.*" --only-innodb-with-frm=related backup


MySQL Enterprise Backup version 3.7.1 [2012/06/05]

Copyright (c) 2003, 2012, Oracle and/or its affiliates. All Rights Reserved.


INFO: Starting with following command line ...

./mysqlbackup -uroot --backup-dir=/logs/backup371frm

       --include=innodb1.table3.* --only-innodb-with-frm=related backup


INFO: Got some server configuration information from running server.


IMPORTANT: Please check that mysqlbackup run completes successfully.

           At the end of a successful 'backup' run mysqlbackup

           prints "mysqlbackup completed OK!".


--------------------------------------------------------------------

                      Server Repository Options:

--------------------------------------------------------------------

 datadir                          = /mysql/trydb/

 innodb_data_home_dir             =  

 innodb_data_file_path            =  ibdata1:10M:autoextend

 innodb_log_group_home_dir        =  /mysql/trydb

 innodb_log_files_in_group        =  2

 innodb_log_file_size             =  5242880


--------------------------------------------------------------------

                      Backup Config Options:

--------------------------------------------------------------------

 datadir                          =  /logs/backupWithFrm/datadir

 innodb_data_home_dir             =  /logs/backupWithFrm/datadir

 innodb_data_file_path            =  ibdata1:10M:autoextend

 innodb_log_group_home_dir        =  /logs/backupWithFrm/datadir

 innodb_log_files_in_group        =  2

 innodb_log_file_size             =  5242880


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

mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.

mysqlbackup: INFO: The --include option specified: innodb1.table3.*

mysqlbackup: INFO: System tablespace file format is Antelope.

mysqlbackup: INFO: Found checkpoint at lsn 1656792.

mysqlbackup: INFO: Starting log scan from lsn 1656320.

120817 15:25:47 mysqlbackup: INFO: Copying log...

120817 15:25:47 mysqlbackup: INFO: Log copied, lsn 1656792.

         We wait 1 second before starting copying the data files...

120817 15:25:48 mysqlbackup: INFO: Copying /mysql/trydbibdata1 (Antelope file format).

120817 15:25:49 mysqlbackup: INFO: Copying /mysql/trydbinnodb1/table3.ibd (Antelope file format).

mysqlbackup: INFO: Opening backup source directory '/mysql/trydb'

120817 15:25:49 mysqlbackup: INFO: Starting to backup .frm files in the subdirectories of /mysql/trydb

mysqlbackup: INFO: Copying innodb data and logs during final stage ...

mysqlbackup: INFO: A copied database page was modified at 1656792.

         (This is the highest lsn found on page)

         Scanned log up to lsn 1656792.

         Was able to parse the log up to lsn 1656792.

         Maximum page number for a log record 0

mysqlbackup: INFO: Copying non-innodb files took 2.000 seconds

120817 15:25:51 mysqlbackup: INFO: Full backup completed!

mysqlbackup: INFO: Backup created in directory '/logs/backupWithFrm'


-------------------------------------------------------------

  Parameters Summary         

-------------------------------------------------------------

  Start LSN                  : 1656320

  End LSN                    : 1656792

-------------------------------------------------------------


mysqlbackup completed OK!


bash$ ls /logs/backupWithFrm/datadir/innodb1/

table3.frm table3.ibd


Thus the backup directory contains only the .frm file matching the innodb table name specified in --include option.


In a nutshell, we present our great new option --only-innodb-with-frm which is a true hot InnoDB-only backup with .frm files, but with an additional check, if any DDL happened during the backup. If a DDL has happened, the DBA can decide if to repeat the backup, or to live with the potential inconsistency. This is the ideal solution for users that have all their "real" data in InnoDB and seldom change their schemas.


You may also like:

http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/backup-partial-options.html



About

MySQL MEB Team Blog

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
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
   
       
Today