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. 

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.

Thursday Sep 04, 2014

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.

Thursday Apr 03, 2014

Data Encryption with MySQL Enterprise Backup 3.10

Introduction

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

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

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

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

Encryption keys

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

$ openssl rand 32 -hex
8f3ca9b850ec6366f4a54feba99f2dc42fa79577158911fe8cd641ffff1e63d6

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

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

Rule 1: The encryption keys must be random.

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

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

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

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

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

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

Using encryption

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


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

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

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

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

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

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

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

 mysqlbackup: INFO:

IMPORTANT: Please check that mysqlbackup run completes successfully.

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

           prints "mysqlbackup completed OK!".

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

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

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

                       Server Repository Options:

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

...

...

...

Backup Image Path = /backups/image.enc

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

 mysqlbackup: INFO: Uses LZ4 r109 for data compression.

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

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

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

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

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

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

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

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

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

...

...

...

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

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

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

   Parameters Summary         

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

   Start LSN                  : 188642816

   End LSN                    : 188642964

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

mysqlbackup completed OK! with 2 warnings



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


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

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

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

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

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

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

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

 mysqlbackup: INFO:

IMPORTANT: Please check that mysqlbackup run completes successfully.

           At the end of a successful 'copy-back-and-apply-log' run mysqlbackup

           prints "mysqlbackup completed OK!".

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

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

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

...

...

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

          lsn 188642964.

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

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

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

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

mysqlbackup completed OK!



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

Tips

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

The "Wrong key" error

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


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

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

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

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

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

 mysqlbackup: INFO:

IMPORTANT: Please check that mysqlbackup run completes successfully.

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

           prints "mysqlbackup completed OK!".

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

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



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

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

Recognizing encrypted backups

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

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


the file command detects the backups images as follows:

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


Thursday Sep 19, 2013

Backing up selective innodb tables using MEB.

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

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

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


The Selective Backup Operation

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

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

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

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


The Restore(copy-back) Operation

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

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


Advantages of using MEB for tts Backup

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

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

Thursday Oct 04, 2012

Optimistic non-locking copy of InnoDB .frm files


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

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

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

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

 


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


1. What are these .frm files?


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

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


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


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


3. How is data consistency ensured?


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


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


4. How does MEB handle data inconsistency?


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


5. What is the option and explain its usage?


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

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


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

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


Let me show the usage with example output:


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

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

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


INFO: Starting with following command line ...

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

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


INFO: Got some server configuration information from running server.


IMPORTANT: Please check that mysqlbackup run completes successfully.

           At the end of a successful 'backup' run mysqlbackup

           prints "mysqlbackup completed OK!".


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

                      Server Repository Options:

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

 datadir                          =  /mysql/trydb/

 innodb_data_home_dir             =  

 innodb_data_file_path            =  ibdata1:10M:autoextend

 innodb_log_group_home_dir        =  /mysql/trydb/

 innodb_log_files_in_group        =  2

 innodb_log_file_size             =  5242880


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

                      Backup Config Options:

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

 datadir                          =  /logs/backupWithFrmAll/datadir

 innodb_data_home_dir             =  /logs/backupWithFrmAll/datadir

 innodb_data_file_path            =  ibdata1:10M:autoextend

 innodb_log_group_home_dir        =  /logs/backupWithFrmAll/datadir

 innodb_log_files_in_group        =  2

 innodb_log_file_size             =  5242880


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

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

mysqlbackup: INFO: System tablespace file format is Antelope.

mysqlbackup: INFO: Found checkpoint at lsn 1656792.

mysqlbackup: INFO: Starting log scan from lsn 1656320.

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

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

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

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

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

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

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

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

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

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

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

         (This is the highest lsn found on page)

         Scanned log up to lsn 1656792.

         Was able to parse the log up to lsn 1656792.

         Maximum page number for a log record 0

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

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

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


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

  Parameters Summary         

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

  Start LSN                  : 1656320

  End LSN                    : 1656792

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


mysqlbackup completed OK!


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

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


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


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


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

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


INFO: Starting with following command line ...

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

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


INFO: Got some server configuration information from running server.


IMPORTANT: Please check that mysqlbackup run completes successfully.

           At the end of a successful 'backup' run mysqlbackup

           prints "mysqlbackup completed OK!".


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

                      Server Repository Options:

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

 datadir                          = /mysql/trydb/

 innodb_data_home_dir             =  

 innodb_data_file_path            =  ibdata1:10M:autoextend

 innodb_log_group_home_dir        =  /mysql/trydb

 innodb_log_files_in_group        =  2

 innodb_log_file_size             =  5242880


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

                      Backup Config Options:

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

 datadir                          =  /logs/backupWithFrm/datadir

 innodb_data_home_dir             =  /logs/backupWithFrm/datadir

 innodb_data_file_path            =  ibdata1:10M:autoextend

 innodb_log_group_home_dir        =  /logs/backupWithFrm/datadir

 innodb_log_files_in_group        =  2

 innodb_log_file_size             =  5242880


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

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

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

mysqlbackup: INFO: System tablespace file format is Antelope.

mysqlbackup: INFO: Found checkpoint at lsn 1656792.

mysqlbackup: INFO: Starting log scan from lsn 1656320.

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

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

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

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

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

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

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

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

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

         (This is the highest lsn found on page)

         Scanned log up to lsn 1656792.

         Was able to parse the log up to lsn 1656792.

         Maximum page number for a log record 0

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

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

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


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

  Parameters Summary         

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

  Start LSN                  : 1656320

  End LSN                    : 1656792

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


mysqlbackup completed OK!


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

table3.frm table3.ibd


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


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


You may also like:

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



About

MySQL MEB Team Blog

Search

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