Archive for the ‘backup’ Category

MySQL Enterprise Backup: Redo-log-only Incremental Backups

Январь 12th, 2012
The latest release of MySQL Enterprise Backup (MEB 3.7.0) introduces a new method for performing incremental hot backups - the redo-log-only incremental backup. This new method of incremental backups allows for highly compact and fast incremental backups and MEB users now have the choice between data-file based incremental backups and the redo-log-only incremental backups.

In data-file based incremental backups (performed using the '--incremental' option) MEB scans all InnoDB datafiles but copies to the backup only modified pages. The main benefit of this is that an incremental backup is much smaller than a full backup but the downside is that during the process of taking an incremental backup MEB still reads all data-files.

With the new redo-log-only incremental hot backups MEB copies just redo logs accumulated since the previous backup. So, no scanning of the data-files is needed and just sequential copy of the redo log is performed. The redo-log-only incremental backup and data-file based incremental backup treat the non-InnoDB data in the same way: the backup of InnoDB data is incremental but the backup of non-InnoDB data is not. Some important aspects of this backup method are:

  • Incremental redo-log-only backup is not always possible. Redo log in InnoDB is implemented with fixed-size circular log files. This means that oldest log entries are overwritten by newer ones after some time. Incremental backup using only redo log is possible only from the log position that is not yet overwritten.
  • Efficiency of the method depends on how the database is modified. If many database pages are modified, but each page is modified only once or a few times, then copying just redo log might work well. On the other hand, if only a small fraction of the database pages is modified, but each page is modified many times, then this method might give poorer performance.

Let us consider a typical usage scenario in which the redo-log-only incremental hot backup is used to back up a database once a day. This requires that InnoDB log files are large enough to hold at least one day's worth of redo logs. This also means that InnoDB log files are pretty large: for a terabyte sized database with 1% of datafile pages modified each day the minimum combined log file size would be 10 gigabytes.

Our experiments showed that the redo-log-only incremental backup method offers significant performance improvements over the normal incremental backup when the database is suitable for this method: the backup process takes less time and the resulting backup is smaller.

For taking redo-log-only incremental hot backups the user needs to issue the incremental backup command with the '–incremental-with-redo-log-only' option instead of the normal '–incremental' option. An example:

$ mysqlbackup --incremental-with-redo-log-only --incremental-backup-dir=/media/backups/incr_bak1 --start-lsn=18974478 backup

Redo-log-only incremental backups are also compatible with the '–incremental-base' option introduced in MEB 3.7.0. An example:

$ mysqlbackup --incremental-with-redo-log-only --incremental-backup-dir=/media/backups/incr_bak1 --incremental-base=dir:/media/backups/fullback backup

To ensure the LSN values match up exactly between successive incremental backups using this option, we recommend always using the --incremental-base option when you use the --incremental-with-redo-log-only option. Using the --incremental-base option has also been described in the blog post 'Taking Incremental Backups without specifying LSN'.

It should be noted that there may be times when MEB cannot perform the redo-log-only incremental hot backup. These are the times when the redo logs of the database have been over-written and page modifications reside only within the pages themselves. In such cases the data-file based incremental backup should be taken since it will successfully backup the remaining redo-logs as well as the data files. Also, incremental backup produced by redo-log-only method is different from the current incremental backup. So, the apply-log step can not be performed on a redo-log-only backup by older versions of MEB i.e. prior to MEB 3.7.0 .


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in backup, enterprise, incremental, MEB, mysql

Комментарии отключены

 

MySQL Enterprise Backup: Redo-log-only Incremental Backups

Январь 12th, 2012
The latest release of MySQL Enterprise Backup (MEB 3.7.0) introduces a new method for performing incremental hot backups - the redo-log-only incremental backup. This new method of incremental backups allows for highly compact and fast incremental backups and MEB users now have the choice between data-file based incremental backups and the redo-log-only incremental backups.

In data-file based incremental backups (performed using the '--incremental' option) MEB scans all InnoDB datafiles but copies to the backup only modified pages. The main benefit of this is that an incremental backup is much smaller than a full backup but the downside is that during the process of taking an incremental backup MEB still reads all data-files.

With the new redo-log-only incremental hot backups MEB copies just redo logs accumulated since the previous backup. So, no scanning of the data-files is needed and just sequential copy of the redo log is performed. The redo-log-only incremental backup and data-file based incremental backup treat the non-InnoDB data in the same way: the backup of InnoDB data is incremental but the backup of non-InnoDB data is not. Some important aspects of this backup method are:

  • Incremental redo-log-only backup is not always possible. Redo log in InnoDB is implemented with fixed-size circular log files. This means that oldest log entries are overwritten by newer ones after some time. Incremental backup using only redo log is possible only from the log position that is not yet overwritten.
  • Efficiency of the method depends on how the database is modified. If many database pages are modified, but each page is modified only once or a few times, then copying just redo log might work well. On the other hand, if only a small fraction of the database pages is modified, but each page is modified many times, then this method might give poorer performance.

Let us consider a typical usage scenario in which the redo-log-only incremental hot backup is used to back up a database once a day. This requires that InnoDB log files are large enough to hold at least one day's worth of redo logs. This also means that InnoDB log files are pretty large: for a terabyte sized database with 1% of datafile pages modified each day the minimum combined log file size would be 10 gigabytes.

Our experiments showed that the redo-log-only incremental backup method offers significant performance improvements over the normal incremental backup when the database is suitable for this method: the backup process takes less time and the resulting backup is smaller.

For taking redo-log-only incremental hot backups the user needs to issue the incremental backup command with the '–incremental-with-redo-log-only' option instead of the normal '–incremental' option. An example:

$ mysqlbackup --incremental-with-redo-log-only --incremental-backup-dir=/media/backups/incr_bak1 --start-lsn=18974478 backup

Redo-log-only incremental backups are also compatible with the '–incremental-base' option introduced in MEB 3.7.0. An example:

$ mysqlbackup --incremental-with-redo-log-only --incremental-backup-dir=/media/backups/incr_bak1 --incremental-base=dir:/media/backups/fullback backup

To ensure the LSN values match up exactly between successive incremental backups using this option, we recommend always using the --incremental-base option when you use the --incremental-with-redo-log-only option. Using the --incremental-base option has also been described in the blog post 'Taking Incremental Backups without specifying LSN'.

It should be noted that there may be times when MEB cannot perform the redo-log-only incremental hot backup. These are the times when the redo logs of the database have been over-written and page modifications reside only within the pages themselves. In such cases the data-file based incremental backup should be taken since it will successfully backup the remaining redo-logs as well as the data files. Also, incremental backup produced by redo-log-only method is different from the current incremental backup. So, the apply-log step can not be performed on a redo-log-only backup by older versions of MEB i.e. prior to MEB 3.7.0 .


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in backup, enterprise, incremental, MEB, mysql

Комментарии отключены

 

MySQL Enterprise Backup: Taking Incremental Backups without specifying LSN

Январь 11th, 2012
In its latest release MySQL Enterprise Backup (MEB 3.7.0) rolled out a new feature called 'incremental-base' which can save a lot of time and effort of the users when taking incremental backups. Let us understand this new feature and how it can be helpful:

What is an incremental backup ?

With MySQL Enterprise Backup v3.6.0 the functionality of performing incremental backups was introduced. An incremental backup is one in which only the changes made since your last backup are saved. So let's say you took a full backup of your MySQL database on 1/1/2011 and its size was 1TB. Now on 1/5/2011 the size of your database has reached to 1.1TB and you want to take another backup. Without incremental backups you would have to take a full backup and effectively backup the entire 1.1TB database For a typical user this is going to take a lot of time and disk space! Incremental backup feature comes to the rescue in such situations because with incremental backups you can save only the changes made in your database since the last backup. And this,of course, is very fast and space saving.

Taking incremental backup prior to MEB 3.7.0

Every backup done using MEB saves with itself meta-data which describes the backup along with its various parameters. This meta-data also includes two values - Start Log Sequence Number (start_lsn) and End Log Sequence Number (end_lsn). A Log Sequence Numbers is a unique ID of a log record made by the MySQL Server when any DDL/DML operations were performed. So a backup consists of all the modifications that were made from the start_lsn to the end_lsn.

Now suppose you want to take an incremental backup. This means that you want to backup only those modifications that were made in the database(s) after your last backup. Later, during the time of recovery, you will incorporate these additional changes (of incremental backup) into the previous full backup. MEB 3.6+ allows you to do this with the '–incremental' option and the '–start-lsn' option where '–start-lsn' is the end_lsn of your last backup. On the command line:

$ mysqlbackup --incremental --incremental-backup-dir=/media/data/backups/incr_bak1 --start-lsn=18974478 backup

This would speedily produce a backup of fractional size as compared to the full backup and when you want to prepare your full backup for recovery you need to use the command 'apply-incremental-backup':

$ mysqlbackup --backup-dir=/media/data/backups/full_bak --incremental-backup-dir=/media/data/backups/incr_bak1 apply-incremental-backup

And there you are! Your full backup is now incorporated with all the page modifications saved in the incremental backup and is ready to be restored whenever you want. Note that when you are using apply-incremental-backup over a full backup make sure that you have used the apply-log command over the full backup before applying the incremental backup.

So this was how you took an incremental backup before MEB 3.7.0

Taking incremental backup with MEB 3.7+

In the method described above, you should have noticed that you either need to look it up or keep saved the value end_lsn of the previous backup after which you want to take an incremental backup. With the new option '–incremental-base' introduced in MEB 3.7.0 things become much easier. The backup defined by 'incremental_base' is simply the 'base' backup for your new incremental backup i.e. the backup whose end_lsn you want to use as the start_lsn for your incremental backup. So looking up the old backup directory or saving the end_lsn of your previous backup is no longer required. When you want to take an incremental backup use the –incremental-base option with the 'dir' prefix (as shown below) instead of the –start-lsn and you are ready to take a backup. On the command line:

$ mysqlbackup --incremental --incremental-backup-dir=/media/backups/incr-bak2 --incremental-base=dir:/media/backups/fullbackup backup

The theory behind incremental backup remains the same as described with the only difference that you do not need to provide the start_lsn explicitly - just point to your old backup (called the 'base' backup) using the '–incremental-base' option and MEB will extract its end_lsn automatically.

Behind the scenes

The picking up of the end_lsn of the 'base' backup is not as straight forward as it seems. To protect the backup and to make sure that the correct end_lsn is extracted MEB compares the end_lsn in the backup_history table of MySQL server (for the last backup done at the location specified by –incremental-base=dir:) with that found in the backup_variables.txt file of the 'base' backup and MEB aborts operation with an error in case the LSNs do not match. This is probably the case if the meta files of your base backup are corrupt or the values in the backup_history table are altered.

Moving the backup

Consider the case when you moved your old backup to a new location. When the old backup was performed the MySQL server saved all the details of the backup in the mysql.backup_history table. This also included the field 'backup_destination'. For the new incremental backup if you now provide –incremental-base=dir:<new location> MEB will first try to query the server's backup_history table for any previous backups performed at this location. If it doesn't find any such backups, it will extract the end_lsn found in the meta data files at the new location of your base backup and continue with the incremental backup. Similarly, if you provide –incremental-base=dir:<old location> MEB will extract the end_lsn of the previous backup done at that location from the backup_history table. After this, if it cannot find any backup at the old location (since it has been moved) it will silently continue with the incremental backup using the end_lsn found in the server's backup_history table. MEB will not continue with the backup operation if the end_lsn can be extracted from both the backup_variables.txt file as well as the server's backup_history table and the two values do not match! The description above can be summarized as follows:

A: end_lsn could be extracted from backup_variables.txt file

B: end_lsn could be extracted from backup_history table


A B both LSNs match successful backup
yes no - yes
no yes - yes
yes yes yes yes
yes yes no no
no no - no

So MEB allows you to use the '–incremental-base' option even after you have moved your previous backups. In case of any confusion or difficulty you can always use the '–start-lsn' option to provide the start_lsn explicitly.


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in backup, community, enterprise, incremental, MEB, mysql

Комментарии отключены

 

Announcing MySQL Enterprise Backup 3.7.0

Январь 11th, 2012

The MySQL Enterprise Backup (MEB) Team is pleased to announce the release of MEB 3.7.0, with several exciting and advanced features to benefit a wide audience. Included in this release are,

  • Redo Log only Incremental Backup
  • Incremental Backup without specifying LSN
  • Validation of Backup Image using checksums
  • Hot Backup of InnoDB .frm files
  • Performance Improvements and
  • Enhancements for Third-Party Media Managers 

 The gist and usefulness of all these new features are described in short below,

Redo Log Only Incremental Backup:
This is a new type of incremental backup that copies only the InnoDB redo log accumulated since the previous full or incremental backup. The original incremental backup technique copies from the InnoDB data files only those pages that were modified since the previous backup. This incremental backup technique based on the redo log is much faster in most cases than incremental backups that read the data files, if incremental backups are taken frequently. You can choose this new method by specifying the --incremental-with-redo-log-only option on the mysqlbackup command line.

Performance Improvements:
Performance of backup-related I/O operations is improved, particularly on Windows, by reusing I/O library code and best practices from the MySQL Server product. To avoid memory fragmentation and overhead from frequent malloc() / free() sequences, the mysqlbackup command now does all read, compress, uncompress, and comparison operations within a fixed-size buffer that remains allocated while the command runs.

Validation of Backup Image using Checksums:
The new validate option of the mysqlbackup command tests the individual files within a single-file backup using a checksum mechanism. Validation helps to verify the integrity of the single-file backup image as the image file is moved between servers and thereby ensure that backups are reliable and consistent.

Hot Backup of InnoDB .frm files:
With this new feature, you do not have to manually copy the .frm files to perform restore. The new option --only-innodb-with-frm performs an InnoDB-only backup and backs up even the .frm files of InnoDB tables in non locking mode. Formerly, the InnoDB-only backup required putting the database briefly into a read-only state and copying the .frm files within your own backup script.

Enhancements to Third-Party Media Managers:
 To customize the interactions between MySQL Enterprise Backup and media management software (MMS), the --sbt-environment option lets you pass application-specific environment settings to the MMS (for example, Oracle Secure Backup). Each vendor that uses the SBT programming interface could implement its own set of environment variables. The --sbt-environment variable lets you pass environment variable values from any invocation method (for example, a Makefile) rather than setting and unsetting the variables within a wrapper shell script.

For more information about MEB features and examples, please see the MEB documentation located <http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/index.html>. My sincere thanks to Lars Thalmann, Sanjay Manwani and all the MEB team members, who have provided valuable features and improvements for every release.

Download the MEB 3.7.0 package from the Oracle Software Delivery Cloud web site <https://edelivery.oracle.com/>. MySQL Enterprise customers can begin deploying MEB 3.7.0 immediately. Users without a MySQL Enterprise license can evaluate MEB 3.7.0 for free for 30 days; please try it out and send your feedback to mysql-backup_ww@oracle.com.




PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in backup, database, enterprise, mysql

Комментарии отключены

 

Focus on Backup

Ноябрь 28th, 2011

In the latest episode of our “Meet The MySQL Experts” podcast, Sveta Smirnova from the MySQL technical support organization gives us an overview of the common MySQL backup practices and tools, and talks about the benefits of using MySQL Enterprise Backup.

Enjoy the podcast!


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in backup, mysql

Комментарии отключены

 

My slides of MySQL Meetup Viadeo / LeMUG Paris

Ноябрь 22nd, 2011

I was glad to present how to schedule and monitor mysqldump with ZRM community last week in Paris as part of the MySQL Meetup Viadeo / LeMUG

You can find my slides below, enjoy ! :

Thanks to Olivier and all the viadeo team in Paris for this event.
And, of course, thanks to all attendees.
We need more events like that in France !

PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in admin, backup, lemug, Live !, mysqldump, Viadeo, zrm

Комментарии отключены

 

Green HDs and RAID Arrays

Сентябрь 26th, 2011

Some so-called “Green” harddisks don’t like being in a RAID array. These are primarily SATA drives, and they gain their green credentials by being able reduce their RPM when not in use, as well as other aggressive power management trickery. That’s all cool and in a way desirable – we want our hardware to use less power whenever possible! – but the time it takes some drives to “wake up” again is longer than a RAID setup is willing to tolerate.

First of all, you may wonder why I bother with SATA disks at all for RAID. I’ve written about this before, but they simply deliver plenty for much less money. Higher RPM doesn’t necessarily help you for a db-related (random access) workload, and for tasks like backups which do have a lot of speed may not be a primary concern. SATA disks have a shorter command queue than SAS, so that means they might need to seek more – however a smart RAID controller would already arrange its I/O in such a way as to optimise that.

The particular application where I tripped over Green disks was a backup array using software RAID10. Yep, a cheap setup – the objective is to have lots of diskspace with resilience, and access speed is not a requirement.

Not all Green HDs are the same. Western Digital ones allow their settings to be changed, although that does need a DOS tool (just a bit of a pest using a USB stick with FreeDOS and the WD tool, but it’s doable), whereas Seagate has decided to restrict their Green models such that they don’t accept any APM commands and can’t change their configuration.

I’ve now replaced Seagates with (non-Green) Hitachi drives, and I’m told that Samsung disks are also ok.

So this is something to keep in mind when looking at SATA RAID arrays. I also think it might be a topic that the Linux software RAID code could address – if it were “Green HD aware” it could a) make sure that they don’t go to a state that is unacceptable, and b) be tolerant with their response time – this could be configurable. Obviously, some applications of RAID have higher demands than others, not all are the same.


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in array, backup, Green, harddisk, hd, HDD, MariaDB, mysql, raid, SAS, SATA, Software and tools

Комментарии отключены

 

5 Steps to an Enterprise Backup

Сентябрь 8th, 2011

I’d like to focus this blog on using MySQL in the Enterprise and kickoff with a series of posts on “Enterprise Backup” building on the new features in both MySQL Enterprise Backup (MEB) and MySQL Enterprise Monitor (MEM).  The new features in MEB 3.6 provide the capabilities to stream backups directly to another server, interface with backup media management software, and take advantage of tape encryption.  MEM 2.3.5 now has a Backup Advisor that helps monitor your backups.  In this and a subsequent  blog post, I’ll go through a progression of backups building up on a fairly straightforward vanilla single file backup with MEB as follows:

  • Backing up to a Single File
  • Add streaming to your Single File backup
  • Stream your Single File Backup to a Media Management System
  • Encrypt your Backup Tapes using your Media Management System
  • Monitoring your Backup with MySQL Enterprise Monitor or queries

MEB is a backup tool included with an enterprise subscription from Oracle/MySQL.  If you are interested in a trying it out, it can be downloaded as a trial from http://edelivery.oracle.com.  MEB was previously known as InnoDB Hot Backup, and provides hot, non-blocking backups for InnoDB tables, and “warm” backups for MyISAM tables.  MEM is also included in the enterprise subscription from Oracle/MySQL and can  be downloaded for a trial at the same site.  It provides proactive monitoring for your MySQL databases.

This post includes the MEB related single file backup, streaming the single file, interfacing with a media management system, and encrypting tapes.  My next post will cover the MEM Backup Advisor and the tables behind the scene.

The following abbreviations are used in this blog:

MEB MySQL Enterprise Backup

MEM MySQL Enterprise Monitor

MMS Media Management System (software for managing tapes and backups)

OSB Oracle Secure Backup

SBT Secure Backup to Tape Interface

Step 1: Backup to a Single File

You can now backup into a single file, which simplifies moving backup data around, especially if you have a large number of files to keep track of.  You do this by using the backup-to-image option of MySQL Enterprise Backup:

mysqlbackup
–backup-image= hr.mbi
–backup-dir=/backup-tmp
–user lynn
–password
backup-to-image

This places my single file backup in the file hr.mbi.  Since some small work files are still used, you need to indicate where these should go with the –backup-dir option.  If you are interested in these files, see the documentation in the “Files that Are Backed Up” section of the MySQL Enterprise Backup User’s Guide http://dev.mysql.com/doc/mysql-enterprise-backup/3.5/en/meb-files-overview.html .  There are also copies of these files inside your successful single file backup, so you are not required to keep them.  For example, backup_variables.txt contains the start and end log sequence numbers plus information indicating whether this was a compressed, partial, or incremental backup.  The file backup_create.xml lists the command line arguments and the environment that the backup was created in.

You can also convert an existing backup directory to a single file backup as follows:

mysqlbackup
– backup-image=/backup/my.mbi
–backup-dir=/var/mysql/backup
– user lynn
– password
backup-dir-to-image

Use list-image to list the contents of a single file backup (leaving out user and password for simplicity):

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

You will see a list of the contents of the single file backup with each database and table listed.  Here’s an example of the contents of a single file backup named MondayApril112022.mbi:

______________________________________________

mysqlbackup –backup_image=Sept2011.mbi list-image

IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful ‘list-image’ run mysqlbackup
prints “mysqlbackup completed OK!”.

mysqlbackup: INFO: Backup Image MEB version string: 3.6.0 [01.07.2011 ]
[File]: [Size:             197]: backup-my.cnf
[File]: [Size:           5578]:  meta\backup_create.xml
[File]: [Size:    16777216]: datadir\ibdata1
[File]: [Size:      2097152]: datadir\ibdata1.$_append_$.1
[Dir]: datadir\crazybase
[File]: [Size:                65]: datadir\crazybase\db.opt
[Dir]: datadir\crazybase3
[File]: [Size:                65]: datadir\crazybase3\db.opt
[Dir]: datadir\mysql
[File]: [Size:                35]: datadir\mysql\backup_history.CSM
[File]: [Size:            5557]: datadir\mysql\backup_history.CSV
[File]: [Size:          71260]: datadir\mysql\backup_history.frm
[File]: [Size:                35]: datadir\mysql\backup_progress.CSM
[File]: [Size:            5423]: datadir\mysql\backup_progress.CSV
[File]: [Size:           33370]: datadir\mysql\backup_progress.frm
[File]: [Size:                 0]: datadir\mysql\columns_priv.MYD
[File]: [Size:             4096]: datadir\mysql\columns_priv.MYI
[File]: [Size:            8820]: datadir\mysql\columns_priv.frm
[File]: [Size:            1320]: datadir\mysql\db.MYD
[File]: [Size:            5120]: datadir\mysql\db.MYI
[File]: [Size:            9582]: datadir\mysql\db.frm
[File]: [Size:                 0]: datadir\mysql\event.MYD
[File]: [Size:            2048]: datadir\mysql\event.MYI
[File]: [Size:          10223]: datadir\mysql\event.frm
[File]: [Size:                 0]: datadir\mysql\func.MYD
[File]: [Size:            1024]: datadir\mysql\func.MYI
[File]: [Size:            8665]: datadir\mysql\func.frm
[File]: [Size:                35]: datadir\mysql\general_log.CSM
[File]: [Size:                 0]: datadir\mysql\general_log.CSV
[File]: [Size:             8776]: datadir\mysql\general_log.frm
[File]: [Size:           22078]: datadir\mysql\help_category.MYD
[File]: [Size:             3072]: datadir\mysql\help_category.MYI
[File]: [Size:             8700]: datadir\mysql\help_category.frm
[File]: [Size:            89241]: datadir\mysql\help_keyword.MYD
[File]: [Size:            16384]: datadir\mysql\help_keyword.MYI
[File]: [Size:              8612]: datadir\mysql\help_keyword.frm
[File]: [Size:              8928]: datadir\mysql\help_relation.MYD
[File]: [Size:            18432]: datadir\mysql\help_relation.MYI
[File]: [Size:              8630]: datadir\mysql\help_relation.frm
[File]: [Size:          418976]: datadir\mysql\help_topic.MYD
[File]: [Size:            20480]: datadir\mysql\help_topic.MYI
[File]: [Size:              8770]: datadir\mysql\help_topic.frm
[File]: [Size:                 0]: datadir\mysql\host.MYD
[File]: [Size:              2048]: datadir\mysql\host.MYI
[File]: [Size:              9510]: datadir\mysql\host.frm
[File]: [Size:                84]: datadir\mysql\inventory.MYD
[File]: [Size:              2048]: datadir\mysql\inventory.MYI
[File]: [Size:              8592]: datadir\mysql\inventory.frm
[File]: [Size:                 0]: datadir\mysql\ndb_binlog_index.MYD
[File]: [Size:              1024]: datadir\mysql\ndb_binlog_index.MYI
[File]: [Size:              8778]: datadir\mysql\ndb_binlog_index.frm
[File]: [Size:                 0]: datadir\mysql\plugin.MYD
[File]: [Size:              1024]: datadir\mysql\plugin.MYI
[File]: [Size:              8586]: datadir\mysql\plugin.frm
[File]: [Size:                 0]: datadir\mysql\proc.MYD
[File]: [Size:              2048]: datadir\mysql\proc.MYI
[File]: [Size:              9996]: datadir\mysql\proc.frm
[File]: [Size:                 0]: datadir\mysql\procs_priv.MYD
[File]: [Size:              4096]: datadir\mysql\procs_priv.MYI
[File]: [Size:              8875]: datadir\mysql\procs_priv.frm
[File]: [Size:               693]: datadir\mysql\proxies_priv.MYD
[File]: [Size:              5120]: datadir\mysql\proxies_priv.MYI
[File]: [Size:              8800]: datadir\mysql\proxies_priv.frm
[File]: [Size:                 0]: datadir\mysql\servers.MYD
[File]: [Size:              1024]: datadir\mysql\servers.MYI
[File]: [Size:              8838]: datadir\mysql\servers.frm
[File]: [Size:                35]: datadir\mysql\slow_log.CSM
[File]: [Size:                 0]: datadir\mysql\slow_log.CSV
[File]: [Size:              8976]: datadir\mysql\slow_log.frm
[File]: [Size:                  0]: datadir\mysql\tables_priv.MYD
[File]: [Size:              4096]: datadir\mysql\tables_priv.MYI
[File]: [Size:              8955]: datadir\mysql\tables_priv.frm
[File]: [Size:                 0]: datadir\mysql\time_zone.MYD
[File]: [Size:              8192]: datadir\mysql\time_zone.MYI
[File]: [Size:              8636]: datadir\mysql\time_zone.frm
[File]: [Size:               312]: datadir\mysql\time_zone_leap_second.MYD
[File]: [Size:              2048]: datadir\mysql\time_zone_leap_second.MYI
[File]: [Size:              8624]: datadir\mysql\time_zone_leap_second.frm
[File]: [Size:          111896]: datadir\mysql\time_zone_name.MYD
[File]: [Size:             12288]: datadir\mysql\time_zone_name.MYI
[File]: [Size:              8606]: datadir\mysql\time_zone_name.frm
[File]: [Size:          658733]: datadir\mysql\time_zone_transition.MYD
[File]: [Size:          733184]: datadir\mysql\time_zone_transition.MYI
[File]: [Size:              8686]: datadir\mysql\time_zone_transition.frm
[File]: [Size:            99788]: datadir\mysql\time_zone_transition_type.MYD
[File]: [Size:            38912]: datadir\mysql\time_zone_transition_type.MYI
[File]: [Size:              8748]: datadir\mysql\time_zone_transition_type.frm
[File]: [Size:               376]: datadir\mysql\user.MYD
[File]: [Size:              2048]: datadir\mysql\user.MYI
[File]: [Size:            10630]: datadir\mysql\user.frm
[Dir]: datadir\performance_schema
[File]: [Size:              8624]: datadir\performance_schema\cond_instances.frm
[File]: [Size:                61]: datadir\performance_schema\db.opt
[File]: [Size:              9220]: datadir\performance_schema\events_waits_current.frm
[File]: [Size:              9220]: datadir\performance_schema\events_waits_history.frm
[File]: [Size:              9220]:datadir\performance_schema\events_waits_history_long.frm
[File)]:[Size:              8878]: datadir\performance_schema\events_waits_summary_by_instance.frm
[File]: [Size:              8854]: datadir\performance_schema\events_waits_summary_by_thread_by_event_name.frm
[File]: [Size:              8814]: datadir\performance_schema\events_waits_summary_global_by_event_name.frm
[File]: [Size:              8654]: datadir\performance_schema\file_instances.frm
[File]: [Size:              8800]: datadir\performance_schema\file_summary_by_event_name.frm
[File]: [Size:              8840]: datadir\performance_schema\file_summary_by_instance.frm
[File]: [Size:              8684]: datadir\performance_schema\mutex_instances.frm
[File]: [Size:             8776]: datadir\performance_schema\performance_timers.frm
[File]: [Size:             8758]: datadir\performance_schema\rwlock_instances.frm
[File]: [Size:             8605]: datadir\performance_schema\setup_consumers.frm
[File]: [Size:             8637]: datadir\performance_schema\setup_instruments.frm
[File]: [Size:             8650]: datadir\performance_schema\setup_timers.frm
[File]: [Size:             8650]: datadir\performance_schema\threads.frm
[Dir]: datadir\pets
[File]: [Size:               65]: datadir\pets\db.opt
[Dir]: datadir\test
[File]: [Size:             8560]: datadir\test\names.frm
[Dir]: datadir\world
[File]: [Size:             8652]: datadir\world\bartstations.frm
[File]: [Size:             8710]: datadir\world\city.frm
[File]: [Size:             8630]: datadir\world\citychild.frm
[File]: [Size:             8646]: datadir\world\citylist2.frm
[File]: [Size:             9172]: datadir\world\country.frm
[File]: [Size:             8702]: datadir\world\countrylanguage.frm
[File]: [Size:             8590]: datadir\world\countrylist2.frm
[File]: [Size:             8590]: datadir\world\countryparent.frm
[File]: [Size:               65]: datadir\world\db.opt
[File]: [Size:              741]: datadir\world\europe_view.frm
[File]: [Size:             3584]: datadir\ibbackup_logfile
[File]: [Size:              176]: meta\backup_variables.txt
[File]: [Size:           38562]: meta\backup_content.xml
[File]: [Size:           15236]: meta\image_files.xml
mysqlbackup: INFO:  Backup image contents listed successfully.
Source Image Path= C:\temp\temp\Sep42011.mbi
mysqlbackup completed OK!

______________________________________________

Step 2:  Add Streaming to your Single File Backup
Streaming allows you to write the backup to a different server without ever storing it locally.  This limits the storage space you need on the local database server, and can be faster than copying it locally and then moving the backup to a different server.  You build on the single file option by using it in combination with OS features like pipes, ssh/scp, etc and take your input from standard output.

mysqlbackup
– backup-image=-backup-to-image | ssh user@host command arg1 arg2 …

where command is the combination of command, device, etc used during normal archiving (such as dd or tar)

Step 3:  Stream your Single File Backup to a Media Management System

You can backup to tape with media management software (MMS) like Oracle Secure Backup (OSB), Legato, Netbackup, etc. The MMS must support version 2 or higher of the System Backup to Tape (SBT)  Interface.   To see a list of Oracle partners who use the SBT interface, go to
http://www.oracle.com/technetwork/database/features/availability/bsp-088814.html
This interface was originally developed by Oracle as a standard way for third party backup media providers to integrate their solutions with RMAN, the Oracle Database Recovery Manager and Backup tool.  MySQL Enterprise Backup (MEB) 3.6 now supports this interface so if you are already using a media management solution like Oracle Secure Backup (OSB) or Netbackup, you can stream directly from MEB to the MMS.  Some MySQL sites have hundreds of servers backed up to many physical or virtual tape devices and can produce thousands of backup tapes on a regular basis.  These tapes may be maintained at different locations for various time periods.  An MMS gives you better control over the process and may add capabilities like backup policies, tape vaulting control, and tape encryption.  To interface with a media management system like OSB, you once again build on the single file backup command, but use a prefix on your image filename.  This special prefix, sbt:  sends the backup to the MMS instead of a local file
–backup-image=sbt:name
Your mysqlbackup command would look something like this:

mysqlbackup
–port=3306
–protocol=tcp
–user=lynn
–password
–backup-image=sbt:backup-hr-2011-09-06
–backup-dir=/backup
backup-to-image

Mysqlbackup defaults to the normal operating system paths and environment variables to locate the Secure Backup to Tape (SBT) library it needs to accomplish this.  What if you have more than one MMS?  No problem. You just specify the –sbt-lib-path parameter of the mysqlbackup command with the correct path and filename for your MMS.

Step 4: Encrypt your Backup Tapes using your Media Management System

Encryption adds an extra layer of security to tapes in case they are misplaced or stolen.  MEB does not in itself do encryption, but you can use OSB or another encryption enabled MMS to encrypt MySQL tapes.  In OSB you control encryption by defining a storage selector that applies specific features (like encryption) to a particular backup. You define the storage selector only once.  Then OSB will automatically select the appropriate database backup storage selector for the backup job.
In OSB a storage selector contains the database name, the hostname, and the name of the media to use for backups.  Optionally you can indicate whether encryption should be used, the type of backup (full, incremental) and restrictions on tape devices.  When OSB receives a backup command through MEB, it examines the defined database backup storage selectors to determine whether a backup storage selector matches the attributes of the just received backup job.  OSB uses the database name and backup type (ie full or incremental etc) to select the most appropriate backup storage selector.
OSB storage selectors are created either through Oracle Enterprise Manager or the command line interface to OSB.  In Oracle Enterprise Manager, storage selectors are defined from within the Maintenance tab under Backup Settings.
If you are not using Enterprise Manager, use the OSB command line interface (obtool) to define your storage selector.  Here is an example of a command which creates a storage selector called MySQLworld with encryption for the database worlddb:

mkssel  (stands for make storage selector)
–dbname worlddb
–host myserver2
–content full
–encryption on
MySQLworld

To restore MySQL data from tape , you use the –sbt-backup-name parameter as part of the restore operation.

A few notes about using MEB with OSB:  You must pre-authorize user access for MySQL for the backup to work with OSB.  For instructions, please see section 4.2.2 of the OSB Administrator’s Guide  “Creating a Preauthorized Oracle Secure Backup User”  http://download.oracle.com/docs/cd/E14812_01/doc/doc.103/e12834/osb_rman_backup.htm#BDCCCIIA

In this entry I’ve covered how to use some of the new features in MySQL Enterprise Backup to stream backups and interface with media management systems.  Next week I will cover the new Backup Advisor in MySQL Enteprise Monitor.

Lynn Ferrante has worked with databases in the enterprise for her whole career at MySQL, Oracle, Sybase, and Ingres.  She also worked on an open source project called GenMapp (University of California, San Francisco), and contributed to the development of many database applications in the fields of energy and environment



PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in backup, MEB, mysql, MySQL Enterprise, MySQL Enterprise Backup, MySQL Enterprise Monitor, Oracle Secure Backup

Комментарии отключены

 

Viewing RMAN jobs status and output

Август 26th, 2011

Yesterday I was discussing with a fellow DBA about ways to check the status of existing and/or past RMAN jobs. Good backup scripts usually write their output to some sort of log file so, checking the output is usually a straight-forward task. However, backup jobs can be scheduled in many different ways (crontab, Grid Control, Scheduled Tasks, etc) and finding the log file may be tricky if you don’t know the environment well.
Furthermore, log files may also have already been overwritten by the next backup or simply just deleted. An alternative way of accessing that information, thus, may come handy.

Fortunately, RMAN keeps the backup metadata around for some time and it can be accessed through the database’s V$ views. Obviously, if you need this information because your database just crashed and needs to be restored, the method described here is useless.

Backup jobs’ status and metadata

A lot of metadata about the RMAN backup jobs can be found in the V$RMAN_% views. These views show past RMAN jobs as well as jobs currently running. Once the jobs complete backup sets, metadata about the sets and pieces are also added to the control file and can be accessed through the V$BACKUP_% views.

For the queries in this post I need only four of those views:

NOTE: I haven’t tested the below in Oracle 10g or earlier.

In the query below I used these views to combine in a single query the information I’m usually interested in when verifying backup jobs:

set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  j.session_recid, j.session_stamp,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;

The output of the query above looks like the one below:

SESSION      SESSION                                             OUTPUT                                       ELAPSED TIME                                            OUT
  RECID        STAMP START_TIME          END_TIME                MBYTES STATUS     INPUT_TYPE    DOW          SECONDS TAKEN          CF     DF     I0     I1      L  INST
------- ------------ ------------------- ------------------- ---------- ---------- ------------- --------- ---------- ---------- ------ ------ ------ ------ ------ -----
  35877    759180320 2011-08-14 19:25:29 2011-08-14 19:26:09        213 COMPLETED  ARCHIVELOG    Saturday          40 00:00:40
  35880    759180438 2011-08-14 19:27:28 2011-08-14 19:28:02        217 COMPLETED  ARCHIVELOG    Saturday          34 00:00:34
  35883    759180496 2011-08-14 19:28:25 2011-08-14 19:28:57        219 COMPLETED  ARCHIVELOG    Saturday          32 00:00:32
  35886    759182786 2011-08-14 20:06:26 2011-08-14 20:24:30     14,100 FAILED     DB FULL       Saturday        1084 00:18:04
  35888    759184489 2011-08-14 20:34:50                              0 FAILED     DB FULL       Saturday
  35890    759189013 2011-08-14 21:50:13                              0 FAILED     ARCHIVELOG    Saturday
  35892    759203414 2011-08-15 01:50:14 2011-08-15 01:53:55        875 COMPLETED  ARCHIVELOG    Sunday           221 00:03:41
  35895    759207985 2011-08-15 03:08:15                              0 FAILED     ARCHIVELOG    Sunday
  35899    759213359 2011-08-15 04:36:27 2011-08-15 04:38:53      3,335 COMPLETED  DATAFILE FULL Sunday           146 00:02:26
  35907    759217815 2011-08-15 05:50:15 2011-08-15 05:50:52        229 COMPLETED  ARCHIVELOG    Sunday            37 00:00:37
  35911    759218083 2011-08-15 05:54:43 2011-08-15 10:15:49    193,016 COMPLETED  DB FULL       Sunday         15666 04:21:06
  35913    759232215 2011-08-15 09:50:15 2011-08-15 09:51:39        628 COMPLETED  ARCHIVELOG    Sunday            84 00:01:24
  ...
  36209    760053012 2011-08-24 21:50:13 2011-08-24 21:53:21      2,422 COMPLETED  ARCHIVELOG    Tuesday          188 00:03:08                                          1
  36212    760067412 2011-08-25 01:50:12 2011-08-25 01:54:17      2,514 COMPLETED  ARCHIVELOG    Wednesday        245 00:04:05                                          1
  36215    760081812 2011-08-25 05:50:12 2011-08-25 05:51:40        907 COMPLETED  ARCHIVELOG    Wednesday         88 00:01:28                                          1
  36218    760096215 2011-08-25 09:50:15 2011-08-25 09:51:36        737 COMPLETED  ARCHIVELOG    Wednesday         81 00:01:21        0      0      0      0      9     1
  36221    760110617 2011-08-25 13:50:17 2011-08-25 13:54:58      3,524 COMPLETED  ARCHIVELOG    Wednesday        281 00:04:41        0      0      0      0      9     1
  36224    760114813 2011-08-25 15:00:13 2011-08-25 19:34:55    209,491 COMPLETED  DB FULL       Wednesday      16482 04:34:42        3    203      0      0     17     1
  36228    760125016 2011-08-25 17:50:16 2011-08-25 17:52:33      1,464 COMPLETED  ARCHIVELOG    Wednesday        137 00:02:17        0      0      0      0      9     1
  36237    760139412 2011-08-25 21:50:12 2011-08-25 21:52:44      1,879 COMPLETED  ARCHIVELOG    Wednesday        152 00:02:32        1      0      0      0      8     1
  36240    760153813 2011-08-26 01:50:13 2011-08-26 01:57:04      5,123 COMPLETED  ARCHIVELOG    Thursday         411 00:06:51        1      0      0      0     17     1

Most of the columns above have either a obvious meaning or derive directly from the the V$ views, so they are explained in the Oracle documentation. The few columns I added to the output that need some additional explaination are the aggregations below:

    CF: Number of controlfile backups included in the backup set
    DF: Number of datafile full backups included in the backup set
    I0: Number of datafile incremental level-0 backups included in the backup set
    I1: Number of datafile incremental level-1 backups included in the backup set
    L: Number of archived log backups included in the backup set
    OUT INST: Instance where the job was executed and the output is available (see below)

Please note that the aggregations are only shown for the recent backup jobs in the example above, since they are purged from the catalog after a few days.

Another important thing to note is that in a RAC environment some fields for a RUNNING backup job may contain invalid information until the backup job is finished. To get consistent information, run this query on the node where the backup is running.

Backup set details

Once you found the general information about the backup sets available, you may need to get more information about the backup sets for one particular backup job. Each backup job is uniquely identified by (SESSION_RECID, SESSION_STAMP), which are listed by the query above.

The query below retrieves details for a backup job, given a pair of values for (SESSION_RECID, SESSION_STAMP):

set lines 220
set pages 1000
col backup_type for a4 heading "TYPE"
col controlfile_included heading "CF?"
col incremental_level heading "INCR LVL"
col pieces for 999 heading "PCS"
col elapsed_seconds heading "ELAPSED|SECONDS"
col device_type for a10 trunc heading "DEVICE|TYPE"
col compressed for a4 heading "ZIP?"
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col input_file_scan_only for a4 heading "SCAN|ONLY"
select
  d.bs_key, d.backup_type, d.controlfile_included, d.incremental_level, d.pieces,
  to_char(d.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(d.completion_time, 'yyyy-mm-dd hh24:mi:ss') completion_time,
  d.elapsed_seconds, d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes, s.input_file_scan_only
from V$BACKUP_SET_DETAILS d
  join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where session_recid = &SESSION_RECID
  and session_stamp = &SESSION_STAMP
order by d.start_time;

And the output is:

                                                                               ELAPSED DEVICE              OUTPUT SCAN
    BS_KEY TYPE CF?   INCR LVL  PCS START_TIME          COMPLETION_TIME        SECONDS TYPE       ZIP?     MBYTES ONLY
---------- ---- --- ---------- ---- ------------------- ------------------- ---------- ---------- ---- ---------- ----
     33429 L    NO                1 2011-08-25 15:00:24 2011-08-25 15:03:25        181 DISK       YES         812 NO
     33428 L    NO                1 2011-08-25 15:00:24 2011-08-25 15:03:08        164 DISK       YES         812 NO
     33429 L    NO                1 2011-08-25 15:00:24 2011-08-25 15:03:25        181 DISK       YES       1,187 NO
     33429 L    NO                1 2011-08-25 15:00:24 2011-08-25 15:03:25        181 DISK       YES         812 NO
     33429 L    NO                1 2011-08-25 15:00:24 2011-08-25 15:03:25        181 DISK       YES       1,187 NO
     33428 L    NO                1 2011-08-25 15:00:24 2011-08-25 15:03:08        164 DISK       YES       1,187 NO
     33428 L    NO                1 2011-08-25 15:00:24 2011-08-25 15:03:08        164 DISK       YES       1,187 NO
     33428 L    NO                1 2011-08-25 15:00:24 2011-08-25 15:03:08        164 DISK       YES         812 NO
     33430 L    NO                1 2011-08-25 15:03:11 2011-08-25 15:03:28         17 DISK       YES          89 NO
     33436 D    NO               45 2011-08-25 15:03:38 2011-08-25 19:05:51      14533 DISK       YES      91,898 NO
     33436 D    NO               45 2011-08-25 15:03:38 2011-08-25 19:05:51      14533 DISK       YES      91,898 NO
     33443 D    NO               52 2011-08-25 15:03:38 2011-08-25 19:31:51      16093 DISK       YES     106,166 NO
     33443 D    NO               52 2011-08-25 15:03:38 2011-08-25 19:31:51      16093 DISK       YES     106,166 NO
     33437 D    NO                2 2011-08-25 19:05:58 2011-08-25 19:16:24        626 DISK       YES       2,999 NO
     33438 D    NO                3 2011-08-25 19:16:28 2011-08-25 19:29:51        803 DISK       YES       4,948 NO
     33439 D    NO                1 2011-08-25 19:29:52 2011-08-25 19:29:52          0 DISK       YES           0 NO
     33440 D    NO                1 2011-08-25 19:29:53 2011-08-25 19:29:53          0 DISK       YES           0 NO
     33441 D    NO                1 2011-08-25 19:29:54 2011-08-25 19:29:54          0 DISK       YES           0 NO
     33442 D    NO                1 2011-08-25 19:29:55 2011-08-25 19:29:55          0 DISK       YES           0 NO
     33445 L    NO                1 2011-08-25 19:32:15 2011-08-25 19:34:11        116 DISK       YES         677 NO
     33445 L    NO                1 2011-08-25 19:32:15 2011-08-25 19:34:11        116 DISK       YES         618 NO
     33445 L    NO                1 2011-08-25 19:32:15 2011-08-25 19:34:11        116 DISK       YES         677 NO
     33446 L    NO                1 2011-08-25 19:32:15 2011-08-25 19:34:18        123 DISK       YES         618 NO
     33446 L    NO                1 2011-08-25 19:32:15 2011-08-25 19:34:18        123 DISK       YES         677 NO
     33446 L    NO                1 2011-08-25 19:32:15 2011-08-25 19:34:18        123 DISK       YES         618 NO
     33445 L    NO                1 2011-08-25 19:32:15 2011-08-25 19:34:11        116 DISK       YES         618 NO
     33446 L    NO                1 2011-08-25 19:32:15 2011-08-25 19:34:18        123 DISK       YES         677 NO
     33447 D    YES               1 2011-08-25 19:34:19 2011-08-25 19:34:20          1 DISK       NO           24 NO
     33448 D    YES               1 2011-08-25 19:34:48 2011-08-25 19:34:49          1 DISK       YES           3 NO
     33449 D    YES               1 2011-08-25 19:34:49 2011-08-25 19:34:50          1 DISK       NO           24 NO

Backup job output

And finally, sometimes it may be helpful to retrieve the job’s output from the metadata kept by the instance.
It maight be that the original log on disk, if any, may have been overwritten by a more recent backup, or just that selecting it from a V$ view may be easier than connecting to a server to find out were the log file is.

The tricky thing here, though, is that the view that contains the output, V$RMAN_OUTPUT, exists in memory only; the job’s output is not stored in the controlfile or anywhere else in the database. Thus, if the instance gets restarted, the contents of that view are reset.

To retrieve the job output for a specific backup job, identified by the (SESSION_RECID, SESSION_STAMP) pair, you can use the following query:

set lines 200
set pages 1000
select output
from GV$RMAN_OUTPUT
where session_recid = &SESSION_RECID
  and session_stamp = &SESSION_STAMP
order by recid;

Which shows:

OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
connected to target database: ORCL (DBID=4132479105)
using target database control file instead of recovery catalog

echo set on

backup archivelog all not backed up delete all input;

Starting backup at 26-AUG-11
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=233 instance=ORCL devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=261 instance=ORCL devtype=DISK
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
...
...
archive log filename=+ALOGS/ORCL/archivelog/2011_08_26/thread_2_seq_23561.462.933333703 recid=213400 stamp=933333707
archive log filename=+ALOGS/ORCL/archivelog/2011_08_26/thread_2_seq_23562.412.933335505 recid=213402 stamp=933335506
archive log filename=+ALOGS/ORCL/archivelog/2011_08_26/thread_2_seq_23563.632.933337017 recid=213404 stamp=933337020
Finished backup at 26-AUG-11

Starting Control File and SPFILE Autobackup at 26-AUG-11
piece handle=/fra/ORCL/autobackup/2011_08_26/o1_mf_s_933337284_34h856x2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-AUG-11

exit;

PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in backup, Group Blog Posts, gv$rman_output, metadata, oracle, output, query, restore, rman, Technical Blog, v, V$BACKUP_SET, V$BACKUP_SET_DETAILS, V$RMAN_BACKUP_JOB_DETAILS, v$rman_output, views

Комментарии отключены

 

MySQL Enterprise Backup 3.6 — New backup streaming, integration with Oracle Secure Backup and other common backup media solutions

Июль 19th, 2011
All DBAs understand the importance and priority of quick, reliable database backup and recovery operations.  In fact, dating back to my early days with MySQL, the most commonly requested product features from the MySQL user base have been around online, non-blocking backup solutions for running MySQL servers.  In response, Oracle now provides MySQL Enterprise Backup ("MEB") which performs high performant, online "hot" backups for MySQL databases.  MEB provides all of the backup/recovery features and functionality DBAs expect, all from a scriptable command line interface.  You can learn all about MEB in the related MySQL docs.

My congratulations and appreciation go out to Lars Thalmann and the MySQL Enterprise Backup engineering team for the recent release of MEB 3.6.  While there are many great improvements in this specific release, as an operational DBA I am most excited about the new support for single file streaming and for the SBT interface features, described here:

Single File Streaming - This allows DBAs to offload the footprint of backup images to a different server or storage device without having to store them locally on the MySQL database server.  This removes storage and related overhead from the server being backed up and speeds up total backup time by removing the need to copy local backup images (which even when compressed can be very large) over the network to their ultimate network destination.  You can learn about this specific MEB option along with a good usage example here.

Support for SBT interface - The "Secure Backup to Tape" interface was originally developed by Oracle as a standard way for third-party backup media providers to easily integrate their solutions with Oracle Recovery Manager ("RMAN").  SBT is now supported in MEB 3.6 so MySQL backup images can now be generated by and streamed directly to advanced enterprise backup media management solutions (Oracle Secure Backup, Symantec Netbackup, most others) that are already deployed within an environment.  This simplifies MySQL administration by enabling DBAs to incorporate MySQL backup/recovery operations and media rotation/retention policies into existing standard operating procedures.  You can learn all about this new option, again with a useful example here.

MySQL Enterprise Backup is part of the commercial MySQL Enterprise Edition but like all Oracle products is free to download and use without obligation for 30 days.  This is a great way to try it out to see if it fits your needs.  

You can download and begin working with MEB 3.6 now:

1. Go to Oracle eDelivery.
2. Enter some basic details and click through the agreement.
3. Select "MySQL Product Pack", then your platform, then Go.


I will keep you posted as new MySQL product features and interesting Oracle integrations become available.  As always, thanks for your continued support of MySQL! 
PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in backup, edition, enterprise, InnoDB, mysql, mysql server, recovery

Комментарии отключены

 
« Older Entries