Friday Jul 10, 2015

Backing up and restoring tables named with special characters

Introduction

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:

--include=REGEXP
--include-tables=REGEXP
--exclude-tables=REGEXP
--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.

Trouble-shooting

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
à123
àbc
a123
déf
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

Introduction

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 test.abc  to  test.abc_new:
$ mysqlbackup --include-tables="test\.abc"
              --rename="abc TO abc_new" 
              ...
              copy-back
In this example we assume that the backup contains several tables. Therefore, we have to specify a single table (test.abc) with the --include-tables option.

Example 2:

A sample command-line for restoring the table db.abc  to  db2.abc:

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

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 ...
 /home/pekka/bzr/meb-trunk/src/build/mysqlbackup 
        --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: test.abc.
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: test.abc 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! 

Limitations

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. 

Thursday Mar 19, 2015

Distributing innodb tables made simpler!

With the support for cloud backups in MEB, distributing innodb tables across multiple MySQL instances has become easier.

1. Backup - take a cloud(Amazon S3) backup of the tables to be shared using the --use-tts=with-full-locking option.

./mysqlbackup \
--host=localhost --user=mysqluser --protocol=TCP --port=3306 \
--cloud-service=s3 --cloud-aws-region=us-east-1 \
--cloud-bucket=mebbackup –cloud-object-key=items.img \
--cloud-access-key-id=<access-key> --cloud-secret-access-key=<secret-key> \
--include-tables=^mycompany\.items.* --use-tts=with-full-locking \
--backup-dir=/tmp/bkups/backupdir --compress --backup-image=- \
backup-to-image


(here the regex used will backup the tables whose name match “items*” in the database “mycompany”.)

2. Change Permissions - from the Amazon S3 (web) console, change the permissions of the backup file.

For a restore by users without S3 accounts, the file “Open/Download” permissions should be granted to “Everyone”. To restrict restore only to users with valid S3 accounts grant “Open/Download” permissions for “Authenticated Users”.

3. Restore - any number users(/MySQL applications) from anywhere (with/without S3 accounts) can use MEB to restore the new tables into a running MySQL instance.

./mysqlbackup \
--host=localhost --user=mysqluser --protocol=TCP --port=3306 \
--cloud-service=s3 --cloud-aws-region=us-east-1 \
--cloud-bucket=mebbackup –cloud-object-key=items.img \
--backup-dir=/tmp/bkups/cpbackdir --uncompress \
--datadir=/home/mysql-5.6/datadir --backup-image=- \
copy-back-and-apply-log


(options --cloud-access-key-id=<access-key> --cloud-secret-access-key=<secret-key> needed if “Open/Download” permissions are set for “Authenticated Users”)

Make sure the tables being imported do not exist in the target database. This operation is same as importing tables from a backup taken with --use-tts option.

4. Done!

Tuesday Mar 17, 2015

MySQL Enterprise Backup 3.12.0 has been released

MySQL Enterprise Backup team is excited to announce the new release of MySQL Enterprise Backup (MEB) 3.12.0.

 MEB 3.12.0 focuses on enhancing the capabilities of the product that would be very useful to the database administrators.

New functionality added

Support for Open Stack Object Storage

In the last release of MEB, we added cloud support of Amazon S3 to do backups and restore from MEB. Now in MySQL Enterprise Backup 3.12.0, we have added cloud backup and restore using OpenStack Object Storage ("Swift") 1.0.
Authentication can be handled either through Swift's own TempAuth authentication system or the OpenStack Identity Service (Keystone) 2.0.

 A number of new command options have been introduced to support the OpenStack Object Storage. Details of Cloud Storage Options are available here


Table rename while selective restore

MySQL Enterprise Backup now supports renaming of a table while restoring a table to an online server.

This feature will be very useful for DBAs while restoring any table from a backup to recover specific data while keeping the original one intact.

Bugs Fixed

In MEB 3.12.0 we have addressed quite a number of bug fixes. I have listed a few of them below. For more information please refer documentation.

Binary Logs:
•The binary log file and relay log files (in the case of slave server) are now compressed when they are being included in a compressed backup and decompressed during a restore. (Bug #19149210)


•If a binary log file on the server got removed when backup was taking place, the backup failed, as mysqlbackup could not find the binary log file it wanted to copy into the backup. With the fix, mysqlbackup continues to finish the backup operation even if some binary log files have been deleted. Also, only the binary log files listed in the binary log index file are copied back to a server during a restore, so that purged binary log files, even backed up, are not restored. (Bug #19849326)

Backup:
•When mysqlbackup encountered a corrupted .frm file during a backup, it threw an error, tried to continue with the backup, and then hung eventually. With this fix, mysqlbackup just gives a warning message ("WARNING: An error occurred while adding manifest information for backup"), and then continues with the backup as usual. (Bug #19608231)


•The list-image operation on cloud backups failed. This was because the operation requires transfer of data without buffering, but mysqlbackup was transferring data in buffered mode by default. This fix makes mysqlbackup download data for the operation without buffering which is possible as long as the cloud proxy supports HTTP range hearers. (Bug #19162974)


•After a RESET SLAVE statement was executed on a slave server, a subsequent backup for the slave server failed with an error, as mysqlbackup could not copy the relay log file from the server. This was because mysqlbackup could not detect the location of the current relay log after the slave reset, and this fix makes sure mysqlbackup knows how to do that. (Bug #20180440, Bug #75074)

Restore:
•The –force option can now be used during the restore of a full backup to overwrite existing data in a non-empty target directory. (Bug #19266491)


•When trying to restore a non-TTS backup to a running server, mysqlbackup overwrote the data on the server without giving any warning. This fix makes mysqlbackup terminate the restore of a non-TTS full backup with an error whenever it finds the target data directory to be non-empty, and then issue a message that the –force option should be used if the user wants the original data to be overwritten. (Bug #19266491)


•When restoring an incremental backup using the copy-back-and-apply-log subcommand on Windows platforms, the operation failed when long file paths were used in the command-line options. This has been fixed in this release.(Bug #18448617)


•If an incremental image backup contained a large number of pages from a single table, the restore of the incremental backup using the copy-back-and-apply-log command might fail. This issue is fixed in MEB 3.12.0.(Bug #20492274)


MEB 3.12.0 is supported on the following platforms:

- Windows

- Debian 6.0, RHEL 6, RHEL 7, Oracle Linux, Suse 11,Ubuntu 12.04

- Solaris 10 Sparc/x86, Solaris 11 Sparc/x86

- Freebsd8

- MacOS 10.6

MEB 3.12.0 is now available in My Oracle Support and will soon be available in Oracle Cloud Delivery .


Monday Sep 15, 2014

MEB copies binary logs and relay logs to support PITR and cloning of master/slave

With MySQL Enterprise Backup(MEB) 3.9.0 we had introduced full instance backup feature for cloning the MySQL server. Now with MEB 3.11.0 we have enhanced the feature by copying all the master-slave setup files like MySQL server binary logs(will be referred as 'binlogs'), binary log index files, relay logs of slave, relay log index files, master info of slave, slave info files. As part of full instance backup, copying of binlog files is default behavior MEB-3.11.0 onwards. DBA should be aware of the fact that current full instance backup is bigger than the backups with old MEB's.

As every event on MySQL production database goes as a entry to binlog files in particular format, binlog files could be huge. Backing of huge binlog and/or relaylog files should not impact the performance of MySQL server. Hence, all the binlog files, except the current binlog used by server, are copied just like the innodb .ibd files without locking tables. Binlog files currently being used by server and added after backup started, are copied during read the lock which is acquired by MEB for copying meta files and redo logs.

DBA gets the following benefits:

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

1) Direct cloning of  master and slave possible from backup

Earlier DBA had to copy binlog files manually in order to setup  master/slave. Now, MEB 3.11 by default copies all the files including the global variables needed for setting up master-slave. Hence DBA can clone master or slave with the same state of backed-up server.

Now, DBA need not to use --slave-info option to copy the binlog info for setting up the slave after restore. By copying master and slave info files,  DBA can fetch the information of up to which master binlog position,  slave SQL thread has executed and IO threads has read etc. With this information along with relay logs, binlogs, DBA can easily setup slave from backed-up slave content

2) Backup of binary logs helps in Point In Time Recovery (PITR)

First let us understand what is PITR by above example. Consider DBA has taken full backup on Sunday(assume date as 14-09-2014), and incremental backups on Tuesday(date as 16-09-2014), Thursday(date as 18-09-2014). It means DBA can only restore database up to full backup or incremental backups in other words database can be restored either up to Sunday or up to Tuesday, Thursday,  but not in between let say Monday or Wednesday. Because backup is just a snapshot of data when it was taken. Hence backup taken once can't be restored in between without change log.  That's where binlog helps in restoring to a certain point of time, which is called Point-In-Time-Recovery(PITR). As binlogs captures all the events of a server with timestamps. Therefore to restore in between DBA need to have base data i.e. full backup and incremental binlogs.

Let's look at our example, below are the points to recover server to Wednesday 12 PM(assume date as 17-09-2014)
a) Restore the backup up to latest backup before PITR time(Here, restore Tuesday's incremental)
b) Get the SQL statements using below mysqlbinlog command up to PITR from the immediate next incremental binlogs(Here get SQL statements up to Wednesday from Thursday's incremental binlogs of binlog.000005, binlog.000006, binlog.000007)

mysqlbinlog --start-datetime=<latest backup time before PITR time> \
         --stop-datetime=<PITR point> \
         <incremental binlogs from immediate next backup>  > <SQL file>

For our above example, the command is
mysqlbinlog --start-datetime="2014-09-16 12:00:00" \
         --stop-datetime="2014-09-17 12:00:00" \
         binlog.000005 binlog.000006 binlog.000007  > mysql_PITR_restore.sql

Read Point-in-Time (Incremental) Recovery Using the Binary Log for more details about PITR using Binary logs.

c) Execute the SQL statements obtained on the restored server, server is restored to PITR point


3) Backing up relay-logs from slave server helps avoiding unnecessary pull of logs from master once it is restored

Let us understand this by an example

Slave has 1 relay log with master binlog positions from 1 to 100

SQL thread at slave reads from relaylog and apply events on slave. Now assume SQL thread currently executed statements 1 to 20 and 21 to 100 are yet to be executed.

If DBA takes backup without copying relay log, when he/she restores the backup as slave, it asks master from the binlog position 21. So restored slave need to pull the logs of binlog position 21 to 100 from master. More network I/O needed as usually slave is on different machine.

As MEB takes backup of relay log, slave can avoid pulling the logs for binlog positions 21 to 100. Now restored slave asks master from binlog positions 101 onwards. This way slave don't pull logs from master which are present in slave backup, there by reducing network I/O which is costly than disk I/O.

Unlike binary logs, relaylogs are mostly deleted automatically once applied by SQL thread, as a result few relay logs exist at any point of time. So all the relay logs are copied for all the backup types full, incremental, partial without major impact on backup size and time.

4) Copied binary logs remains consistent with the backup data

Earlier DBA had to copy binlog files manually in order to setup master/slave. Data files are copied by MEB and binlogs are copied by DBA at two different times, so there is a possibility of binlog files not consistent with the backed-up data.

Lets consider following example:
1. MEB takes backup of the server without binlogs at 1 PM
2. DBA has copied binlogs from the server at 1:30 PM
From 1 PM to 1:30, lets say 100 events logged in binlogs

Now to use these binlog files, DBA has to either execute 100 events on server or have to remove 100 events from binlog files.

Consider another example:

1. DBA has copied binlogs from the server at 1:30 PM
2. MEB takes backup of the server without binlogs at 2 PM
From 1:30 PM to 2 PM, lets say 100 events went into backup data

Now DBA has to copy the missing binlog files again from the running server.
With MEB 3.11.0 onwards, binlogs and the data are copied at the same time, so they are consistent with each other.

Options to avoid binlogs/relay logs:
--------------------------------------------------
If DBA is not concerned about backing up binlog files then he/she can use --skip-binlog and --skip-relaylog to skip relay log files in backup. It is advisable to use these options if he/she don't plan to clone server or want PITR.

For Master, to skip only binlogs:
./mysqlbackup --skip-binlog --backup-dir=back_dir --socket=server_sock backup

For Slave, to skip relay-logs
./mysqlbackup --skip-relaylog --backup_dir=back_dir --socket=server_sock backup

For Slave which is also a master, to skip both binlogs and relay logs
./mysqlbackup --skip-binlog --skip-relaylog backup_dir=back_dir --socket=server_sock
 backup


Options for offline backup:
------------------------------------
MEB also supports offline backup. In order to copy binlog and/or relaylog, MEB searches for default values of log-bin-index(default: host_name-bin.index), relay-log-index(default: host_name-relay-bin.index), relaylog-info-file(default: relay-log.info), master-info-file(default: master.info) at default location that is in server's 'datadir'. And if MEB finds those files then it successfully backs up those files. In case those files are configured with different values, DBA need to provide --log-bin-index = PATH, --relay-log-index = PATH, --relaylog-info-file = PATH, --master-info-file=PATH options to MEB in order to copy them.

Conclusion:
-----------------
To enrich the full instance backups that MySQL Enterprise Backup has been performing since release 3.9.0, all the replication setup files are included as part of all the backups in 3.11.0. With these files as well as all the global variables, plugin details, MEB now takes the responsibility of giving all the details to DBA for cloning any server. Read MEB 3.11.0 documentation for more details and many other great features.

Friday Sep 05, 2014

Selective Restore of InnoDB Tables with MySQL Enterprise Backup 3.11

Introduction

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.

Examples

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:

  blue1
  blue2
  dark_blue
  light_blue
  red

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 
              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$" 
              copy-back

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" 
              copy-back-and-apply-log

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 
              --include-tables="^color\.blue2$|^color\.dark_blue$" 
              copy-back-and-apply-log

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

--include-tables="^color\.(blue2|dark_blue)$"

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.

Warnings

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.

Limitations

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 Sep 04, 2014

Cloud storage for MySQL Enterprise Backup (MEB) users

Jothir wrote great blog post, introducing MEB's "Backup to Cloud" feature. And while this blog post is great source of information for Amazon users who consider using MEB it is not so clear how to setup the cloud storage itself for those MEB users who are only considering storing backups in the Cloud. I actually was one of them and decided to share my experience.

OK, so what do you need to start using MEB with Amazon S3 service?

  1. Amazon AWS account. You can register it for free at https://portal.aws.amazon.com/gp/aws/developer/registration/index.html
  2. A bucket where backup image will be stored. To create it, choose S3 service under "Services" menu, then click "Create Bucket" button and enter the name. You will pass this name to --cloud-bucket option.
  3. Access key and secret access key for a user which will connect to AWS. You can use root user for such purpose or create a special one. Latter option is the safest. To create a user choose "AIM" service, then "Manage Users" under "Create individual AIM users" tab, then click "Create new users" and enter user names. After you created a user you will have an option to see its security credentials. It will be somethiing like this:
    Access Key ID:
    AKIAJ2DK2OBDEGQSBGSQ
    Secret Access Key:
    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

    You will pass "Access Key ID" to --cloud-access-key-id option and "Secret Access Key" to --cloud-secret-access-key option.
  4. After you created a user you need to grant necessary permissions to it. There are many ways to manage AWS user permissions, but for our test we can choose simplest way: click on "Attach User Policy" button under "User Policies" part, then select "Amazon S3 Full Access". We are ready to try backup now.
  5. To summarize, following Cloud options are required:
    --cloud-service=s3 - it is always s3 and must be specified
    --cloud-aws-region='us-west-2' - here is the tricky part: you could specify different region when created the bucket, but default region should be used, so AWS is able to find the bucket
    --cloud-bucket=ourbucket - name of the bucket
    --cloud-access-key-id=AKIAJ2DK2OBDEGQSBGSQ - access key ID
    --cloud-secret-access-key=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 - Secret Access Key
    --cloud-object-key=test - any name: this will be name of the image in the bucket
  6. Add common MEB options, such as:
    --backup-dir=/path/to/backup/dir/to/store/temporary/files
    --backup-image=- - so we are storing in stdout
    backup-to-image - this is not an option, but the command =)
  7. Our final command is:
    $mysqlbackup --cloud-service=s3 --cloud-aws-region='us-west-2' \
    --cloud-bucket=ourbucket --cloud-access-key-id=AKIAJ2DK2OBDEGQSBGSQ \
    --cloud-secret-access-key=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 \
    --cloud-object-key=test --backup-dir=/home/sveta/BACKUPDIR/cloud_test \
    --backup-image=- backup-to-image


    Here is the cloud operation part of the MEB output:

    ...
    Uploading part %:10 20 30 40 50 60 70 80 90
    mysqlbackup: INFO: Finalizing data upload.
    mysqlbackup: INFO: Cloud operation complete.
    ...


You can see the file in your bucket using AWS console.

Nice thing is that you don't need to care about URLs, remote streaming and things like this: MEB will take care about everything itself.

Optimistic Backup

Introduction 

MySQL Enterprise Backup (MEB) is a highly efficient tool for taking backups of your MySQL databases. In the 3.11.0 release we are taking that one step further by introducing a new concept called "optimistic" backup. Optimistic backup leverages the patterns we saw frequently especially as related to very large databases.

For backups the goals are:
1 - Quality and Consistency - the backup and more importantly the restore just "works"
2 - Size, time, and overhead - like in the game of golf - low score wins - for backups and for
     restores.
3 - Flexibility – It’s not always one size fits all - whether how the backup is run, where it goes,
     how it is recovered.

With optimistic backup - we look at mostly the read aspects of your database to enable us to create a backup that is smaller, faster to backup, faster to restore, and has less overhead when running, but still remains consistent. As we said earlier “optimistic” is not for all databases. Those that benefit the most are databases where modifications(insert/updates/deletes) are focused on a minority of the tables, and store a great deal of data will benefit the most. In our tests (details below) mean time to restore reduced 18%, resulting from a 60% reduction in the size of the redo log that is applied on recovery. What follows is the deals on how this works. We also want to thank the DBAs who provided great feedback and details on their database characteristics during our development of this new feature.

Optimistic Backup

Whole idea is summarized in following two steps.

1.  Divide the InnoDB tables into two groups. In the first group are those tables that are unlikely to be modified during the backup, and in the second group are all the other tables. MEB is optimistic about the first set of tables as they are unlikely to be modified during backup operation hence these tables are termed as optimistic tables and backup operation is named after them as "optimistic backup". Tables in the second group are likely to be modified during backup operation hence are termed as optimistic busy tables.

2.  Split full backup operation into two phases. In the first phase of the backup, tables from the first group are copied to the backup without locking the MySQL server instance. In the second phase tables from second group are copied similar to normal backup i.e.  All InnoDB tables from second group and tables from the first group that have modified since the backup started are copied to backup, then MySQL server instance is locked to copy remaining files. First phase always excludes the undo/redo logs, system table space files and other meta files because these are most likely to be modified. Since optimistic tables are copied in the first phase hence it is called optimistic phase and second phase is called normal phase. 

How to trigger Optimistic backup

Following two new options have been introduced to trigger optimistic backup. Provide either of these two or both options to enable optimistic backup.

optimistic-time

This option specifies the cut-off date-time to identify tables to be skipped in first phase. In other words, tables modified before the specified date-time (i.e. optimistic tables), are copied in first phase and tables modified after specified date-time value (i.e. busy tables), are copied in second phase.  Following date-time formats are supported by this option- 

• <Number><unit> - A valid integer number according to its unit. Supported units are years, days, months, hours and minutes. 

For example: ‘--optimistic-time=1years’ means tables modified one year before the backup starts to be copied in first phase. In other words tables modified in last one year from the backup starts to be copied in second phase.

• Date-time - It can be specified in different formats as:  YYMMDD, YYYYMMDD, YYMMDDHHMMSS, YYYYMMDDHHMMSS, YY-MM-DD, YYYY-MM-DD, YY-MM-DD HH.MM.SS,   and YYYYMMDDTHHMMSS where T is a character. 

For example - 16th May 2014 can be specified as 140516 or 20140516. Therefore ‘--optimistic-time=20140516’ means tables modified before this time will be copied in first phase.

• now - This is the default value of this option. If no value is specified against this option then MEB will assign “now” as default value. Internally MEB keeps backup start time as optimistic time and use the same throughout the backup operation. With this option all InnoDB tables are be copied in the 1st step.

For example:   --optimistic-time=now   OR --optimistic-time

optimistic-busy-tables

This option specifies the InnoDB tables (i.e. busy tables) to be skipped in the first phase. It requires regular expression as specified in the POSIX 1003.2 standard, similar to – include/exclude-tables=REGEX option. This option expects fully qualified names of database objects, i.e. database_name[.table_name] in regular expression. Tables that match the REGEX criteria will be skipped in the first phase and copied in the second phase. This option can co-exist with 'optimistic-time', 'include-tables' and 'exclude-tables' options. These options will get precedence in ascending order as following 

Include/exclude-tables > optimistic-busy-tables > optimistic-time

Include/exclude-tables options gets higher precedence over optimistic-busy-tables option.

For instance - if same table is specified in the –exclude-tables and –optimistic-busy-tables then that table is not copied at all. 

optimistic-busy-tables gets higher precedence over optimistic-time option. 
For instance - if a table ‘user’ has modified time of ‎ “04/15/‎2014 ‏‎2:19:32”. If --optimistic-busy-tables=^test.user --optimistic-time=04142014 values are specified then file test/user.ibd is not be copied in first step because optimistic-busy-tables value matches the REGEX so test.user.ibd is not evaluated by optimistic-time option.

Using Optimistic backup

Following are samples of command line options need to be specified to perform different types of optimistic backups. 

1. Normal Backup with optimistic backup options

  >mysqlbackup.exe --backup-dir=D:\backup --optimistic-busy-tables="^db2\.t1+" backup

  >mysqlbackup.exe --backup-dir=D:\backup --optimistic-time backup

2. Compress Backup with optimistic backup options

 >mysqlbackup.exe --backup-dir=D:\backup --compress-method=lzma
   --optimistic-busy-tables="^db2\.t1+" backup 

 >mysqlbackup.exe --backup-dir=D:\backup --compress-method=lzma --optimistic-time=now
   backup 

3. Compress Image Backup with optimistic backup options

 >mysqlbackup.exe --backup-dir=D:\backup --compress-method=lzma
  --backup-image=D:\backup.img  --optimistic-busy-tables="^db2\.t1 +" backup-to-image

 >mysqlbackup.exe --backup-dir=D:\backup --compress-method=lzma
  --backup-image=D:\backup.img  --optimistic-time="2014-08-27 13.25.00" backup-to-image

4. Compress and encrypted Backup with optimistic backup options

 >mysqlbackup.exe --backup-dir=D:\backup --compress-method=lzma
   --backup-image=D:\backup.img --encrypt --key=xxxxxxxxx
   --optimistic-busy-tables="^db2\.t1 +" backup-to-image

 >mysqlbackup.exe    --backup-dir=D:\backup --compress-method=lzma
   --backup-image=D:\backup.img --encrypt --key=xxxxxxxxx
   --optimistic-time=20140827T132500 backup-to-image

5. Partial Backup with optimistic backup options

 >mysqlbackup.exe --backup-dir=D:\backup --optimistic-time=25days
   --include-tables="^db2+"  backup

 >mysqlbackup.exe --backup-dir=D:\backup --optimistic-busy-tables="^db2\.t1+"
   --include-tables="^db2+"  backup 

 >mysqlbackup.exe --backup-dir=D:\backup --optimistic-busy-tables="^db2\.t1+"
   --optimistic-time=10Months --include-tables="^db2+"  backup

Troubleshooting optimistic backup

If optimistic backup is triggered then MEB prints INFO messages in log in following order to know when each phase of backup started and finished. 

First phase of optimistic backup started 

…….............

…….............

First phase of optimistic backup completed successfully

Second phase of optimistic backup started

……….........

……….........

Second phase of optimistic backup completed successfully

Turn on --trace option for more diagnostic information in logs. Internally MEB converts optimistic-time value to an epoch time stamp and compares the same with the InnoDB files’ modification time. 

With increased trace levels we see optimistic-time, backup start time and InnoDB table modification time in form of the epoch time stamp. We can easily convert these epoch time stamps in human readable time either using SQL query or through some external tools, for instance epoch converter.

Choose values of Optimistic backup options wisely

It's imperative to choose the suitable values of above options in order to maximize the gain from optimistic backup. Optimistic backup targets large databases in which only a few tables are modified frequently. DBA can find out such tables or time since when most of those tables have modified, either by profiling database or directly from file system. If such tables are identified then those tables can be specified in optimistic-busy-tables option. If duration, since when only small percentage of tables are modified, is identified then that date-time can be specified in optimistic-time option.  

Imagine the value of optimistic-busy table option is not chosen carefully then chances are that some optimistic tables might be copied in second phase which ideally should have been copied in first phase only, as a result redo log size will increase and hence overall backup time.
Similarly if the value of the optimistic-time option is not chosen carefully then chances are that some busy tables might be copied in first phase which ideally should have been copied in second phase only. In second phase those stale tables are copied again, as a result overall backup time will increase. In such cases for image backup, busy tables will be copied in both phases hence disk space and backup time both will increase. 

If only a few tables less in size (e.g. ~2% of the total size of the the database) will be modified during backup time then default value of optimistic-time 'now' should serve the purpose. In this case only those a few tables will be copied twice. 

Performance tests

In our internal tests we created a 1.2 TB of DB which had 25 tables initially. We created 3 tables and updated them continuously until backup operation completed successfully. We observed ~60% reduction in redo log file (ibbackup_logfile) size and ~18%  reduction in overall backup (backup + apply log) time compared to normal backup operation. Of course these numbers highly depends on the hardware, CPU load and types of DDL being executed. Therefore a user might experience better or poor performance than the numbers shared here. Purpose to share the number is to show that if options are specified correctly then optimistic backup performs better compared to normal backup.

Conclusion

The optimistic backup isn't for all cases, but for many it will have significant advantages and benefits over a typical full backup. So we hope you'll give it a try and provide us feedback on how it works for your environment. We shall also follow-up with additional queries and scripts to help you easily determine how values to set for the optimistic backups, although we suspect that many DBAs likely already know their data so well they won't need these. Never forget, good backups, and more importantly successful and timely recovery is the top priority for a DBA. For more details and usage samples, please refer MEB Manual.

Tuesday Sep 02, 2014

MySQL Enterprise Backup 3.11.0 has been released!!


MySQL Enterprise Backup team is glad to announce the new release of MySQL Enterprise Backup (MEB) 3.11.0.

Overview

MEB 3.11.0 focuses on enhancing backup/recovery performance and adding new features that would help database administrators.

This release addresses some of the challenges customers face today while taking backups of very large databases(in terrabytes). This release also provides more complete backup with all the necessary logs required to clone a server. We have also added online restore of tables in 3.11.0 to help DBAs fix corrupted tables in the server from existing backups.

MEB 3.11.0 also brings in cloud support for backup and restore. To meet the growing trend of cloud storage, we released this feature in July '14 as part of  MEB 3.10.2 release.

Please refer to this blog on Cloud Storage


New features

  • Cloud Support
    • MySQL database backed up and restored in cloud
    • Amazon S3 supported
  • Optimistic Backup
    • Simplifies backup and restore of huge database with infrequently modified tables
    • Backup performance enhanced with smaller redo logs
  • Full Instance Backup
    • Complete backup of MySQL with binary logs and relay logs
    • Cloning of MySQL server (master/slave)
    • Binary logs consistent with backup, very useful to perform PITR(Point In Time Recovery)
  • Online selective restore
    • Online restore of single or multiple tables from TTS backups
    • Very useful for the DBAs to replace a corrupted table on an online server

Summary

MEB 3.11.0 release also contains quite a few important bug fixes for the customers.

Please refer 3.11.0 documentation for more details.

Very soon you will hear detailed blogs from the MEB Engineering team on all the new features introduced in 3.11.0.

MEB 3.11.0 is supported on the following platforms:

- Windows

- Debian 6.0, RHEL 6, RHEL 7, Oracle Linux, Suse 11,Ubuntu 12.04

- Solaris 10 Sparc/x86,  Solaris 11 Sparc/x86

- Freebsd8

- MacOS 10.6


MEB 3.11.0 is now available in My Oracle Support and will soon be available in Oracle Cloud Delivery.




Tuesday Jul 01, 2014

Streaming backups onto the Cloud Storage


MySQL Enterprise Backup(MEB) has been widely used as the most efficient tool to take backups of huge databases. The storage of backups can be done on-premises – viz, local hard disk, external hard disk drives, network mounted disk. MEB can also stream backups to tape by supporting different Media Management Software like Symantec NetBackup, EMC2 and Oracle Secure Backup. Streaming of backups can also be done using 'ssh' to another machine.

As data keeps growing, instead of expanding the storage, database users are choosing to rely on the growing trend which is the Cloud Storage.

A few facts about Cloud Storage:

1. Offers off-premises storage with AAA(Anytime, Anywhere Access)
2. Sharing of data to make a collaborative effort.
3. Highly fault tolerant, durable and scalable and secure.
4. Cost-effective

To the benefit of our MySQL database users, Mysql Enterprise Backup (MEB) has come up with the support for taking their backups and storing directly on the cloud.

MEB 3.10.2 provides this great feature of storing image backups on the cloud with its simplified command-line options. The backups can be restored from the cloud as well.

How does it work?


    MEB streaming backup from Remote Machine /  EC2 Instance to Amazon S3      

As illustrated in the picture, MEB can be hosted along with Server either on a remote machine or on the Amazon EC2 instance to take backup of MySQL databases/tables which will be stored in Amazon S3.

The list of cloud options introduced are:

  • --cloud-service : Cloud service for data backup or restoration. Currently, only the Amazon S3 service is supported, and “s3” is the only value mysqlbackup accepts for this option.
  • --cloud-bucket : The storage bucket on Amazon S3 for the backup image.
  • --cloud-object-key : The Amazon S3 object key for the backup image.
  • --cloud-access-key-id : AWS access key ID for logging onto Amazon S3.
  • --cloud-secret-access-key : AWS secret access key that goes with the AWS access key id used in --cloud-access-key-id.
  • --cloud-aws-region : Region for Amazon Web Services that mysqlbackup accesses for S3.
  • --cloud-trace : Print trace information for cloud operations. It works independently of --trace, which specifies the trace level for the non-cloud operations of mysqlbackup. Any non-zero value for the option enables the trace function. Default value is “0.”
  • --cloud-proxy=proxy-url:port : Proxy address and port number for overriding the environment's default proxy settings for accessing Amazon S3.


Lets dive into the MEB command-line with the options to be used for cloud storage.

For taking a simple image backup of the whole server:

./mysqlbackup --cloud-service=s3 --cloud-bucket=mebtest --cloud-object-key=cloud_image.bi --cloud-access-key-id=accessKeyId --cloud-secret-access-key=******** --cloud-aws-region=awsRegion --backup-dir=/backuptmp --backup-image=- backup-to-image  

For taking a image backup of selective tables:

This is very useful to allow access to specific table(s) to other users. As you are aware, the users can restore these tables onto their running server.

./mysqlbackup --cloud-service=s3 --cloud-bucket=mebtest --cloud-object-key=cloud_image.bi --cloud-access-key-id=accessKeyId --cloud-secret-access-key=******** --cloud-aws-region=awsRegion –include-tables=db1.tab1 --use-tts=with-full-locking --backup-dir=/backuptmp --backup-image=- backup-to-image  

MEB also supports compressed, encrypted and incremental image backups onto the cloud.

For example, to perform an encrypted image backup, the command-line is:

./mysqlbackup --cloud-service=s3 --cloud-bucket=mebtest --cloud-object-key=cloud_image.bi --cloud-access-key-id=accessKeyId --cloud-secret-access-key=******** --cloud-aws-region=awsRegion –encrypt –key-file=encryptKeyFile --backup-dir=/backuptmp --backup-image=- backup-to-image  

All operations supported for non-cloud image backups can be performed.
Here goes the list:

extract
image-to-backup-dir
validate
copy-back-and-apply-log

The image stored on the cloud can be directly restored onto a (running) server.

For restoring the selective image backup into a running server:

./mysqlbackup –defaults-file=/backuptmp/backup-my.cnf --cloud-service=s3 --cloud-bucket=mebtest --cloud-object-key=cloud_image.bi --cloud-access-key-id=accessKeyId --cloud-secret-access-key=******** --cloud-aws-region=awsRegion --backup-dir=/restoretmp --backup-image=- --datadir=/datadir copy-back-and-apply-log  
Note: This is the image backup taken with –use-tts option.

For more options and usage samples, please refer MEB Manual.

Thus MEB helps the user to take backups and stream it to cloud using simple command-line WITHOUT the need for any external tool or script.

Thursday May 22, 2014

MySQL Enterprise Backup Improved Compression Algorithm for 3.10

Background:

Prior to version 3.10, MySQL Enterprise Backup (MEB) used zlib compression for in-memory compression of datafiles. The compression worked by splitting the innodb datafiles into fixed size blocks and compressing each block independently.After searching on the web we found there are many compression algorithms available which can be used for compression. This triggered the idea of testing the performance of available compression algorithms. If the benchmark shows improved performance we can make backup and/or restore faster by adding the new compression algorithm to MEB.

Implementation :

The idea to implement the algorithms procceded as follows .

1. Select a "long list" of algorithms based on literature and what Google and other databases are using.
2. Create a prototype of MEB supporting the algorithms in the long list.
3. Run comparison tests of algorithms with the MEB prototype.
4. Select a "short list" of algorithms that will be added to MEB 3.10.

Criteria for Selecting the Algorithm:

The following criteria were used in comparing compression algorithms.

1. Compression speed
2. Decompression speed
3. Compression ratio
4. CPU-usage
5. Licensing model

These criteria have a differing importance. Compression speed, and compression ratio are probably more important to most users than decompression speed.

Performance Test:

We have now completed the performance tests of the new compression algorithms for MEB. See the below table for the list of compression algorithms were evaluated in the test.

Machine and OS Configurations:

OS : Oracle Linux 6 (x86_64)
Memory: 29 GB RAM
Cpu : 8 vCPUs (2 quad-core processors, no HT)
Read speed of the source dir(data directory) : 600 MB/s
Write speed of the destination dir(backup directory) : 300 MB/s

A backup of a 441 GB database was generated using TPC-H datagen tool taken when the mysqld process was not running .

Compression Algorithms Time [min] Compr. size [GB] Compr. / Orig. size Avg. CPU usage Avg. CPU Idle Reads [MB/s] Writes [MB/s] Source Disk busy
uncompressed/Normal Backup to Directory 31 N/A 100% 20% 65% 250 250 100%
Zlib (level=1) 34 165 37% 82% 15% 220 90 70%
Zlib (level=9) 720 120 27% - - - - -
LZF 27 222 50% 45% 50% 270 140 100%
LZO 27 224 51% 40% 55% 270 140 100%
Snappy 31 221 50% 55% 40% 260 130 80%
QuickLZ 26 203 46% 35% 55% 280 120 100%
LZ4 26 215 49% 35% 55% 280 130 100%
LZMA (level=1) 90 110 25% 78% 20% 80 22 25%
LZMA (level=9) 360 88 20% - - - - -

Few Important Notes:

•  Some columns are blanks because the test ran for longer duration of time so it was not feasible to collect monitoring stats.

• “Source Disk busy" is the number of I/O per second in percent of what the device can execute. It is not related to the device throughput (MB/s).

• MEB has an internal work queue to process data that is managed by separate read, process, and write threads. Read threads will place data in the process queue where processing threads then process it, and finally after the processing is complete, the data will be placed in the write queue where they will be written out to storage. Due to this design, if writes are slower than reads (which they often are), then the reads will effectively be throttled by the write speeds (write speeds typically being the limiting factor).

Analysis of the Compression Test's:

LZ4 and QuickLZ were the fastest algorithms, while ZLib (level=9) was by far the slowest. For compression ratios, LZMA (level=9) was only able to reach 20%, whereas QuickLZ reached 46%, and LZ4 49%. This illustrates the fact that there is a trade-off between backup speed and the reduction in data size. Nevertheless, we could say that algorithm A is better than algorithm B, if A is faster than B and produces a backup which is not larger than that of B, or if A produces a smaller backup than B and A is not slower than B. Using this criteria we can say that QuickLZ is a better compression algorithm than LZ4, Snappy, LZO, or LZF. Similarly, LZMA (level=1) is superior to Zlib (level=9).                                                                         The summary table shows two limiting factors for the backup speed. The IO speed of the of disk on which the database resides (the source disk) is thelimiting factor for uncompressed backup and compressed backups made with LZF, LZO,QuickLZ and LZ4. For Zlib (level=1), Snappy and LZMA (level=1) the limiting factor is the CPU. After removing the worst performing algorithms, we have four remaining that we can organize into a line where you get higher speeds as you move to the left, and better compression as you move to the right.

BEST SPEED --- QuickLZ --LZ4------ Zlib (lev.=1) ---- LZMA (lev.=1)---- LZMA(lev=9) --- BEST COMPRESSION

Restore Speed:

The restore speed was almost the same for all the algorithms. The restore of uncompressed backup and ZLib compressed backup took 28 minutes, and for all the other algorithms the restore time was 29 minutes.

Conclusion:

For licensing reasons QuickLZ cannot be used with MEB. Therefore it was replaced with LZ4. Thus, the new compression algorithms are LZ4 (for fast compression) and LZMA (for high compression ratio).


Tuesday Apr 29, 2014

Cloning a slave using Mysql Enterprise Backup on a GTID enabled server

MySQL 5.6 introduced a new feature called GTID (Global Transaction IDentifier) support in Replication. For every transaction that is committed on to the server, a GTID of the format :

server_uuid:transaction_id is written into the master's binary log.

This offers the following advantages:

  • Very helpful to set up a slave and create a replication setup.

  • User need not worry about fetching the master's binlog filename and position in the “CHANGE MASTER TO” command which is used to synchronise the slave with the master.

  • Applying GTIDs on slaves ensures consistency – since GTIDs are unique, it cannot be applied more than once on the server.

For a gtid enabled server, the following properties need to be set on both Master and Slave configuration files as shown below in Master.cnf and Slave.cnf

gtid-mode=on

enforce-gtid-consistency=true

log-bin

log-slave-updates=true

Here are the steps to achieve this:

Have a configuration file for master and slave with the below mentioned properties.

So the configuration file for a Master and Slave looks like:

Master.cnf

[mysqld]

port=3306

socket=/tmp/mysql3306.sock

binlog-format=MIXED

log-bin

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

binlog-checksum=CRC32

master-verify-checksum=1

server-id=1

report-host=localhost

datadir=/export/gtid-test/master-data

innodb-file-per-table=on 

Slave.cnf

[mysqld] 

port=3307

socket=/tmp/mysql3307.sock 

binlog-format=MIXED 

log-bin

log-slave-updates=true 

gtid-mode=on 

enforce-gtid-consistency=true 

master-info-repository=TABLE 

relay-log-info-repository=TABLE 

slave-parallel-workers=2

binlog-checksum=CRC32 

slave-sql-verify-checksum=1

server-id=2

binlog-rows-query-log_events=1

datadir=/export/gtid-test/slave-data 

report-host=localhost

On the Master:

Start the server with this configuration file:

./mysqld –defaults-file=/export/gtid-test/master.cnf &

Create a user and grant 'replication' privileges to that user:

mysql> create user 'replication'@'localhost' identified by 'reppwd';

mysql> grant super,replication slave on *.* to 'replication'@'localhost';

Now, you can use Mysql Enterprise Backup(MEB), which will simplify this task of setting up a new slave from an existing server (which will serve as “master” in this replication environment),

On MEB:

1. Use Mysql Enterprise Backup to take a backup of the master.

./mysqlbackup -uroot --socket=/tmp/mysql3306.sock –backup-dir=/export/gtid-test/slave-data-to-rest/ backup

Now the meta folder under the backup directory contains a new file named backup_gtid_executed.sql.

(In this case, /export/gtid-test/slave-data-to-rest/meta/backup_gtid_executed.sql)

Here are the contents of backup_gtid_executed.sql:

# On a new slave, issue the following command if GTIDs are enabled:

SET @@GLOBAL.GTID_PURGED='6efe48e8-b63c-11e3-a730-0021cc6bab7c:1-12';

# Use the following command if you want to use the GTID handshake protocol:

# CHANGE MASTER TO MASTER_AUTO_POSITION=1;

MEB will simplify the task of cloning a slave with the help of this file which contains the sql to set the GTID_PURGED on the slave (this is the GTID_EXECUTED value of the Master)

On the Slave:

2. Now to setup a new slave server, restore the backed up contents using “copy-back-and-apply-log” operation:

./mysqlbackup --defaults-file=/export/gtid-test/slave.cnf --backup-dir=/export/gtid-test/slave-data-to-rest/ copy-back-and-apply-log

slave.cnf contains the “datadir” where the backup contents will be restored. So no need to pass 'datadir' in the command line.

An image backup is much more efficient, as the image can be streamed onto the slave machine and restored directly. You can check out Restore directly on the remote Machine from backup stream for more information.

The new server is ready. Start the slave server.

./mysqld –defaults-file=/export/gtid-test/slave.cnf &

Observe that the restored directory is the datadirectory (as mentioned in slave.cnf) of the slave server

This can be synchronised with the master by doing the following simple steps:

mysql> show slave status;

This is an empty set as the slave is not synchronised still.

mysql> show global variables like '%gtid_executed%';

+---------------+------------------------------------------+

| Variable_name | Value |

+---------------+------------------------------------------+

| gtid_executed | 16446ee3-bad6-11e3-8530-0021cc6bab7c:1-2 |

+---------------+------------------------------------------+

1 row in set (0.00 sec)

3. Now to synchronise with Master – to set the gtid_purged, the gtid_executed on the slave need to be empty. Reset Master to do this.

mysql> reset master;

mysql> show global variables like '%gtid_executed%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| gtid_executed |     |

+---------------+-------+

4 a. Execute the backup_gtid_executed.sql to set the GTID_PURGED on the slave.

mysql> \. /export/gtid-test/slave-data-to-rest/meta/backup_gtid_executed.sql

4 b. Synchronise slave with Master;

mysql> change master to master_host='<hostname>', master_port=<port_number> master_auto_position=1;

Setting master_auto_position=1 will automatically replicate the changes.

5. Start the slave with the username and password to connect to Master.

mysql> start slave USER='replication' PASSWORD='reppwd';

mysql> show slave status \G

Look for:

Retrieved_Gtid_Set: 6efe48e8-b63c-11e3-a730-0021cc6bab7c:13

Executed_Gtid_Set: 6efe48e8-b63c-11e3-a730-0021cc6bab7c:1-13

which shows that the slave has all the transactions from 1-13.

This way, the backup taken with Mysql Enterprise Backup can be used to set any number of slaves for the master in a replication environment.

You can also refer : Using MEB with Replication

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


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.


Objective:
---------------
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.


Advantage:
----------------
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.


Limitations:
------------------
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.

Friday Mar 28, 2014

Faster and Space efficient Restore with MySQL Enterprise Backup

The new operation 'Copy-back-and-apply-log' introduced in MySQL Enterprise Backup 3.9.0 helps in faster restoration and helps in reducing the amount of space involved in restoring the backup contained in an image, because of elimination of the intermediate step of extraction of image contents. 

[Read More]
About

MySQL MEB Team Blog

Search

Categories
Archives
« July 2015
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
       
Today