Friday Jul 10, 2015

Backing up and restoring tables named with special characters


The names of databases and tables within MySQL are known as identifiers. In the simplest case these identifiers are just strings of certain ASCII characters (the basic Latin letters, the digits 0-9, the dollar sign and the underscore). However, if an identifier is placed in quotes, it can contain any character of the full Unicode Basic Multilingual Plane (except U+0000). We say that a character is a special character if it is permitted in a quoted identifier but not in an unquoted identifier.

MySQL Enterprise Backup (MEB) 3.12.1 introduces support for proper handling of table and database names with special characters. In MEB versions prior to 3.12.1 database and table names were represented as ASCII strings and the same string was used on the command line, internally within MEB and in filenames.  This caused MEB to fail some operations when database/table names contained characters other than those that can appear in unquoted MySQL identifiers.

Starting with version 3.12.1, MEB represents database and table names as strings of Unicode characters (or code points) and three distinct encodings are employed: the command-line character encoding, the internal UTF-8 encoding and the filename encoding used by the MySQL server. Thus, the same string of Unicode characters appears in three different forms depending on where the string is used.

The command-line character encoding is defined by the operating system. On Linux and other Unix-line systems this is done by the command shell and on Windows by the Command Prompt.  In order for the special character support to work, MEB must know what character encoding is used on the command line. The user can tell MEB the character encoding used on the command line by specifying the name of the encoding with the --default-character-set option. If this option is not given, then MEB tries to detect the command-line character encoding by examining the command program from which it was invoked. 

In the following example the database db contains three tables with names that contain special characters: àbc,  à123 and déf. We backup the tables àbc and à123 using the Transportable Tablespaces feature (TTS) of the MySQL Server as follows:

$ mysqlbackup --include-tables='`db`\.`à.*`' --use-tts 
              --backup-dir=/full-backup  backup
MySQL Enterprise Backup version 3.12.1 Linux-3.2.0-80-generic-i686 [2015/06/22]
Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved.

The tables àbc and à123 are included in this partial backup by specifying with the --include-tables option a regular expression that matches the fully-qualified table names `db`.`àbc` and `db`.`à123`.

In the example above, we have specified both the database and table name quoted with backticks. However, it is not necessary to provide these backticks because MEB forms four strings from every fully-qualified table name: DB.TABLE, `DB`.TABLE, DB.`TABLE` and `DB`.`TABLE`, and a regular expression is defined to match a table if it matches any of these four strings generated from the table name.

Special characters can be specified with these four MEB options:

--rename="OLD-NAME to NEW-NAME"

Regular expressions can contain any Unicode character in the Unicode Basic Multilingual Plane and they are matched against strings of Unicode characters.  This means that special characters can be used in regular expressions in exactly the same way as the characters that are not special.

Useful tips

Each command program (or a shell, in the Unix terminology) treats certain characters in a special way. For example, the bash program (which is popular on Linux and other Unix-like systems) uses single and double quotes as quoting characters and assigns a very special meaning to backticks: anything enclosed in backticks is treated as a command line and evaluated. This can be problematic when the user invokes MEB from the bash program. Suppose that the user wishes to restore the table db.à123 from a backup and rename it to db.new_à123. The command-line

$ mysqlbackup ... --include-tables="db\.à123"  
                  --rename="`à123` TO `new_à123`"   copy-back

seems to do just that, but unfortunately when invoked from the bash shell it fails with the following error:

à123: command not found

The problem is that the new table à123 contains a special character and the table name must be therefore quoted. However, the string `à123` is interpreted by the bash shell as a request to run the command à123 which, of course, fails.

There are two ways to remedy this situation. In the first fix, we place the parameter of the --rename option in single quotes:

$ mysqlbackup ... --include-tables="db\.a123" 
                  --rename='`à123` TO `new_à123`'  copy-back

This works because the bash shell treats backticks as normal characters when they appear in a string placed in single quotes.

Another solution is provided by MEB. In addition to the backtick quoting, MEB allows identifiers to be quoted with single quotes and double quotes. Therefore, the following command line (where we replaced backticks with single quotes) works

$ mysqlbackup ... --include-tables="db\.a123"   
                  --rename="'à123' TO 'new_à123'"  copy-back

MEB converts any identifiers on the command line that are quoted with single or double quotes to a standard form where the quoting is done with backticks. This is a very handy feature when MEB is invoked from a command program, such as the bash shell, that assigns special meaning to backticks.

Sometimes the Windows Command Prompt can be a little bit tricky to set up correctly to handle special characters. For example, single quotes do not work with the Windows Command Prompt. Therefore, some users might want to consider using the free Cygwin command program when working with special characters on Windows. Cygwin software provides a command line similar to the one on the Unix-like systems. Using Cygwin should allow easy manipulation of the command-line character encoding.


Sometimes MEB has the wrong idea of the character encoding used on the command line. Here we show two examples of what can happen if the command-line character encoding is different from what MEB thinks it is.

$ mysqlbackup --default-character-set="latin1" --backup-dir=/full-backup 
              --include-tables='db\.a123'  --rename="a123 TO 'new_à123'" copy-back
MySQL Enterprise Backup version 3.12.1 Linux-3.2.0-80-generic-i686 [2015/07/07] 
Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved.
150707 17:01:30 mysqlbackup: INFO: Importing table: db.a123 and 
                                   renaming it to `db`.`new_à123`.
150707 17:01:31 mysqlbackup: INFO: Analyzing table: `db`.`new_à123`.
150707 17:01:31 mysqlbackup: INFO: Copy-back operation completed successfully.
150707 17:01:31 mysqlbackup: INFO: Finished copying backup files to '/sqldata/tts-5.6'
mysqlbackup completed OK!

Everything looks good in the example shown above: it seems that we have successfully restored the table db.a123 and renamed it to db.new_à123, as we intended. However, when we look at the list of tables at the server we see that the new table name is not db.new_à123, but db.new_Ã 123:

$ echo "use db; show tables;" | mysql
new_Ã 123

This problem was caused by specifying an incorrect value for the command-line character encoding with the --default-character-set="latin1" option on the command line invoking the copy-back operation.

In the next example we show another possible outcome when MEB is given a wrong command-line character encoding. We invoke the following MEB restore operation from a command shell that uses UTF-8 character encoding.

$ mysqlbackup --default-character-set="hebrew" --backup-dir=/full-backup 
              --include-tables='db\.a123'  --rename="a123 TO 'new_à123'" 
              --force  copy-back
MySQL Enterprise Backup version 3.12.1 Linux-3.2.0-80-generic-i686 [2015/06/22] 
Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved.
 mysqlbackup: ERROR: Failed to convert "hebrew" characters to "utf8"
 characters in string "a123 TO 'new_à123'". 
 mysqlbackup: ERROR: Unicode character conversion failed.

The conversion from "hebrew" characters to "utf8" characters fails because the character encoding used on the command line is not "hebrew", but "utf8". This conversion fails in the middle of the string because  some of the UTF-8 encoded characters are not valid "hebrew" encoded characters. The error message "Unicode character conversion failed" means always that MEB has the wrong idea of the character encoding used on the command line. To recover from this failure, the user must find out the character encoding used on the command line and specify it to MEB with the --default-character-set option.

Monday Mar 23, 2015

Renaming tables with MySQL Enterprise Backup 3.12.0


MySQL Enterprise Backup 3.12.0 (MEB) introduces a new feature for restoring an InnoDB table from a backup. Now it is possible to rename the table during restore. This is useful when the user wants to restore a table from a backup without overwriting the existing version of the table in the database.

The following example illustrates how the renaming feature could be used.  Suppose that the DBA has deleted three rows from a table T1 by mistake and he now wishes to get them back from a backup. He wants to leave the database online and to restore the 3 deleted rows from a TTS backup (a backup created with the --use-tts option) that contains the table T1.  He can do this with this feature in three steps:

  1. He restores with MEB the table T1 from a TTS backup renaming it to T2.

  2. He uses MySQL client to issue SQL statements to copy the 3 mistakenly deleted rows from the table T2 to the table T1.

  3. He drops the table T2.

Now the accidentally dropped rows have been restored and the restore took place when the MySQL server was online and the restore did not disturb the normal operation of the server in any way.

User Interface

The command-line interface for restore is extended with the --rename option that specifies a mapping of from the old name to the new name. The --rename option has the following syntax:

--rename="OLD-NAME  to  NEW-NAME

The OLD-NAME and NEW-NAME are either fully-qualified tablenames of the form DB.TABLE, or tablenames without the database part. The OLD-NAME must match the name of the table selected for restore.  

Example 1:

A sample command-line for restoring the table  to  test.abc_new:
$ mysqlbackup --include-tables="test\.abc"
              --rename="abc TO abc_new" 
In this example we assume that the backup contains several tables. Therefore, we have to specify a single table ( with the --include-tables option.

Example 2:

A sample command-line for restoring the table  to

$ mysqlbackup --include-tables="db\.abc"
              --rename="abc to" 

Note that if the database db2 does not exist, the restore will create it.

Example 3: 

Below is an excerpt of the printouts MEB produces when a table is renamed during restore using the command-line from Example 1: 

$ mysqlbackup --backup-dir=/full-backup --include-tables="test\.abc" --rename="abc TO new_abc" copy-back
MySQL Enterprise Backup version 3.12.0 Linux-3.2.0-69-generic-i686 [2015/01/22] 
Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.
 mysqlbackup: INFO: Starting with following command line ...
        --backup-dir=/full-backup --include-tables=test\.abc 
        --rename=abc TO new_abc copy-back 
 mysqlbackup: INFO: 
IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'copy-back' run mysqlbackup
           prints "mysqlbackup completed OK!".
150312 11:17:53 mysqlbackup: INFO: MEB logfile created at /full-backup/meta/MEB_2015-03-12.11-17-53_copy_back.log
 mysqlbackup: INFO: MySQL server version is '5.6.11'.
 mysqlbackup: INFO: Got some server configuration information from running server.
 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
150312 11:17:53 mysqlbackup: INFO: Copy-back operation starts with following threads
		1 read-threads    1 write-threads
150312 11:17:53 mysqlbackup: INFO: Creating table:
150312 11:17:53 mysqlbackup: INFO: Copying /full-backup/datadir/test/abc.ibd.
150312 11:17:53 mysqlbackup: INFO: Completing the copy of all non-innodb files.
150312 11:17:54 mysqlbackup: INFO: Importing table: and renaming it to test.new_abc.
150312 11:17:55 mysqlbackup: INFO: Analyzing table: test.new_abc.
150312 11:17:55 mysqlbackup: INFO: Copy-back operation completed successfully.
150312 11:17:55 mysqlbackup: INFO: Finished copying backup files to '/sqldata/tts-5.6'
mysqlbackup completed OK! 


Renaming works only when restoring a single table from a TTS backup (a backup created with the --use-tts option). If the backup contains multiple tables, then a single table should be specified for restore with the --include-tables and --exclude-tables options. 

Friday Sep 05, 2014

Selective Restore of InnoDB Tables with MySQL Enterprise Backup 3.11


Sometimes the best way to repair data issues and problems within a MySQL database is to restore only some of the tables from a backup. For example, suppose that some data was accidentally deleted from one table due to a software error, then the easiest way to recover the lost data might be to restore only one table from a backup. Previously this kind of partial restore was not supported by MySQL Enterprise Backup (MEB). However, MEB 3.11 introduces support for selective restore from backups created with the --use-tts option (or TTS backups).

TTS backups are backups that are created with the transportable tablespaces feature of InnoDB. These backups consist of InnoDB tables that have their own tablespaces (.ibd files). A TTS backup can be restored only to a running server, because MEB needs the server to execute SQL statements during the restore.

In selective restore the user specifies the names of the tables to be restored from a TTS backup using the --include-tables=REGEX1 or the --exclude-tables=REGEX2 option (or both), where REGEX1 and REGEX2 denote regular expressions. A table is restored from a backup if the fully-qualified name of the table (that is, a name of the form DATABASE.TABLE) matches REGEX1 and does not match REGEX2.

Selective restore does not overwrite any existing tables at the server. So, the user should first delete (or rename) all those tables at the server that are going to be restored from a backup.


The following examples demonstrate how selective restore can be used. Suppose there is a database called "color" with five InnoDB tables that have their own tablespaces:


We have created a TTS backup of the "color" database with the following mysqlbackup command

$ mysqlbackup --include-tables="^color\." --use-tts --backup-dir=/full-backup 

and prepared the backup in the "/full-backup" directory with

$ mysqlbackup --backup-dir=/full-backup apply-log

Let's now assume that the table "red" is now broken and we wish to restore it from the backup. We must first delete the table by issuing a DROP TABLE statement with the MySQL client. After removing the table we can restore it from a TTS backup as follows

$ mysqlbackup --backup-dir=/full-backup --include-tables="^color\.red$" 

Here we specified a single table with the --include-tables option.

In the next example we work with the same "color" database as above, but we make a backup in a single file

$ mysqlbackup --include-tables="^color\." --use-tts --backup-dir=/full-backup 
              --backup-image=/backups/image backup-to-image

The created backup image contains all five InnoDB tables of the "color" database.

Now we wish to restore the tables "blue2", "dark_blue" and "light_blue" from the backup image. But before we can do this, we have to drop these tables at the server. After that the following mysqlbackup command restores these three tables.

$ mysqlbackup --backup-image=/backups/image --backup-dir=/full-backup 
              --include-tables="^color\..*blue.*$" --exclude-tables="blue1" 

We have specified the tables to be restored with the --include-tables and --exclude-tables options illustrating the power of regular expressions in specifying the set of tables to be restored.

If only a few tables are to be restored, then usually the easiest way to do this is to specify these tables with the --include-tables option. The mysqlbackup command shown below restores the tables "blue2" and "dark_blue" from a single-file backup.

$ mysqlbackup --backup-image=/backups/image --backup-dir=/full-backup 

A user more familiar with regular expressions might want to replace the value for the --include-tables option in the example above with


which is an equivalent but shorter regular expression.

Working with multiple InnoDB datafile formats

There are currently two InnoDB datafile formats - the older Antelope format and the newer Barracuda format that offers features not available with the Antelope format. If either the server or the backup contain InnoDB tables in different formats, then this might cause problems for selective restore. Fortunately, these problems can be solved by invoking the restore command with suitable options.

To understand this problem we have to look at how InnoDB database engine creates new tables.  When an InnoDB table that has its own datafile (.ibd file) is created at the server, the format of the InnoDB datafile is determined by the CREATE TABLE statement and the value of the innodb_file_format system variable. If the CREATE TABLE statement specifies features that require the Barracuda format and the value of the innodb_file_format system variable is also "Barracuda", then the datafile is created in Barracuda format. But if the value of innodb_file_format is "Antelope", then the format of the datafile will be "Antelope" even if the CREATE TABLE statement actually specified features requiring the Barracuda file format.

During selective restore MEB issues first a CREATE TABLE statement (obtained from the backup) to create an empty table at the server. After this, the original (empty) datafile is replaced with the datafile copied from the backup. But this step fails, if the original datafile and the datafile in the backup are not in the same format. This happens, for example, if the datafile in the backup is in Barracuda format, but the value of the innodb_file_format system variable is "Antelope" when selective restore is attempted.

This problem can be solved by specifying the --force option for the mysqlbackup command-line invoking selective restore. With the --force option, mysqlbackup changes temporarily the value of the innodb_file_format system variable for each table that is restored. This quarantees that selective restore succeeds regardless of the format of InnoDB tables in the backup and the value of innodb_file_format, provided, of course, that other users do not change the value of innodb_file_format during the restore.


The integrity of the database is not enforced by MEB. Instead, it is the user's responsibility to keep track of the foreign-key constraints on the restored data.


Selective restore is not possible from TTS backups created in the full-locking mode (specified with --use-tts=with-full-locking). Selective restore works only with TTS backups created in the minimum-locking mode (--use-tts=with-minimum-locking), which is the default mode for TTS backups.

Thursday Apr 03, 2014

Data Encryption with MySQL Enterprise Backup 3.10


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

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

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

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.


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

Wednesday Apr 02, 2014

Offline checksum validation for directory and Image backup using MySQL Enterprise Backup

Data integrity:
Data integrity refers to maintaining and assuring the accuracy and consistency of data over its entire life-cycle. Every organization whether it's small or large want to make sure their data is consistent and error free. Data might move to other media/ different storage system for performance, speed, scalability or any other business reasons. So we want to make sure data is not corrupt while migration/movement. Data integrity is a policy which enterprise can enforce to be confident about their own data.

The overall intent of any data integrity technique is to ensure data is recorded exactly as  intended and upon retrieval later, ensure data is the same as it was when originally recorded.

User should be able to verify data integrity of Innodb data files of a taken backup. Because during backup MEB performs integrity check to ensure consistency of data which MEB copies  from server data_dir. This feature gives flexibility to the user to run integrity check on his/her data at any time after backup. Thus this feature allows to check data integrity of backup directory/Image off-line.

Checksum mismatch/s will cause InnoDB to deliberately shut down a running server. It is preferable to use this command/operation rather than waiting for a server in production usage to encounter the damaged data pages.

This feature will be useful when user has taken a backup and is skeptical that the data might be corrupt before restore. It allows user to verify correctness of their backed data before restore.

 MEB's parallel architecture supports integrity check in parallel. So multiple threads in parallel operating on different chunks of the IBD data at the same time. Performance of data integrity is truly great compared to innochecksum offline utility which is single threaded.

Command-line options:

Existing "validate" command will be used to validate backup directory content. In option field, 

"--backup-dir=back_dir" we have to specify with validate.

e.g. ./mysqlbackup --backup-dir=back_dir validate

To validate compressed backup dir following command line should be used

e.g. ./mysqlbackup --backup-dir=comp_back_dir validate

To validate image

e.g. ./mysqlbackup --backup-image=back_image validate

The error message expected from Validate operation over a corrupt data file is:

 "mysqlbackup: ERROR: <filename> is corrupt and has : N corrupt pages"

In order to validate each pages of an Innodb data file. We need an algorithm name which was being used by server while we took backup.

In backup_dir/backup-my.cnf has parameter named "innodb_checksum_algorithm" along with other parameters. We use this parameter from "backup-my.cnf" file and initialize server checksum algorithm for validate of backup directory.

We have several algorithms like none, which stores magic value on each page, crc32, innodb as well as strict mode. Strict algorithm mode will try to validate checksum on given algorithm only. If checksum of a page is calculated with some other algorithm then it'll fail to validate. But, if algorithm given is not in strict mode it will try to validate page by trying all algorithm.

Validate operation involves no write sub-operation and hence no write threads required.

PAGE_CORRUPT_THRESHOLD is a constant, which specifies threshold/upper limit of corrupt pages per .ibd file. To avoid scanning through all the pages in ibd file we have an internal "PAGE_CORRUPT_THRESHOLD" for each .ibd file. When "validate" reaches this threshold it skips current .ibd file and moves to the next .ibd file.

Due to the limitations of checksum algorithms in principle, a 100% safe detection of each and every corruption is not guaranteed. But if MEB does not find a corruption, the server won't either since MEB uses the same algorithm. However, the algorithms used by server was theoretically proven solid in terms of detecting corruption.

MEB "validate" feature validates files of Innodb storage engine like .ibd, .par (Partitioned Innodb table file) etc. MEB can't validate Non-Innodb files as server don't have support of checksum for these files.

Reason for above problem:
For Non-Innodb files like .frm, .MYD, .MYI etc. no checksum is added by the server. InnoDB adds checksums before it writes data to the disk. So the data is protected for its whole life time: write to disk by server, stay on disk, read from disk by backup, write to disk by backup, stay on disk, read from disk by validate and even the copy-back cycle.

Thursday Sep 19, 2013

How to restore directly on a remote machine from the backup stream

MySQL Enterprise Backup has been improved to support single step restore from the latest release 3.9.0. It enables you to restore the backup image to remote machine in single step. However, first you would have to create the backup image in local disk, copy the backup image to remote machine, and then restore in remote machine by running copy-back-and-apply-log command.

This approach has two overheads:

    Serial execution: You have to wait for each step to finish before beginning the next (e.g. You must have to wait for backup-to-image operation to finish before beginning copy).
    Disk consumption: You might not have enough space on the source disk to store that backup-image in the first place.

By means of restoring directly on a remote machine via piping backup stream over SSH, you could overcome both these problems.
That means, 
You don't have to store the backup contents anywhere,
Pipe backup stream directly to remote machine,
Optionally, perform compression and decompression on the fly and
Perform restore operation simultaneously.

How to do it:

    Use SSH and pipes to transfer data between backup and restore operations, and
    Perform the backup to stream and restore in remote machine simultaneously.


    a) perform image backup and stream the data to stdout --backup-image=- --backup-to-image
    b) pipe the stdout to remote server using ssh and restore data using copy-back-and-apply-log.
Sample command:

mysqlbackup --user=root --port=3306 --backup-dir=backup --socket=/tmp/mysql.sock  --backup-image=- backup-to-image | ssh <user name>@<remote host name> 'mysqlbackup --backup-dir=backup_img --datadir=/data/datadir --innodb_log_group_home_dir=. --innodb_log_files_in_group=8 --innodb_log_file_size=5242880 --innodb_data_file_path="ibdata1:12M:autoextend" --backup-image=- copy-back-and-apply-log'

In case of slower network, you could perform compressed backups to reduce the network traffic.  Compressed backups would require more cpu cycles, but provides faster data transfer.
Sample command with compression:

mysqlbackup --user=root --port=3306 --backup-dir=backup --socket=/tmp/mysql.sock  --backup-image=-  --compress backup-to-image | ssh <user name>@<remote host name> 'mysqlbackup --backup-dir=backup_img --datadir=/data/datadir --innodb_log_group_home_dir=. --innodb_log_files_in_group=8 --innodb_log_file_size=5242880 --innodb_data_file_path="ibdata1:12M:autoextend" --uncompress --backup-image=- copy-back-and-apply-log'

On successful completion of above command, your remote server is being restored and ready to use. This would also be useful to create a data snapshot for replication without any additional storage space.

MySQL MEB Team Blog


« July 2015