Tuesday Jan 31, 2012

MySQL Events in Germany, France & Sweden

Following the success of our OTN MySQL Developer Day in London last October, we are running additional MySQL Developer Days in Europe!

Join us in:

The MySQL Developer Day is a one-stop shop for you to learn all the essential MySQL skills. In this free, one-day seminar, we will cover everything you need to know to successfully design, develop, and manage your MySQL databases. You'll also learn the guidelines and best practices in performance tuning and scalability. Space is limited so register now!

Additionally, we will also be holding a MySQL High Availability Seminar in Stockholm on February 23rd. During this free Oracle seminar we will review the various options and technologies at your disposal to implement highly available & highly scalable MySQL infrastructures, as well as best practices in terms of architectures. Register Now!

And…remember our MySQL Embedded Online Forum today from 9.00 am to 12.00 pm PT!

Monday Jan 23, 2012

MySQL Embedded Online Forum

Next Tuesday, January 31st, it’s going to be all about MySQL Embedded!

We will be running an Online Forum from 9.00 am to 12.00 pm PT. Oracle’s MySQL Embedded experts will deliver 4 technical sessions addressing “what all ISVs & OEMs should know about embedding MySQL…but often don’t”.

By attending the Online Forum you will learn:

  • Why MySQL is a great embedded database choice for both startups and the largest software, hardware, and appliance vendors in the world, and how its features ensure costs remain low throughout an application's life cycle
  • MySQL installation options that require minimal or zero end-user effort, and how to easily build them into your application
  • How to secure MySQL embedded in applications, appliances, and devices
  • Tips to simplify your integration with MySQL using the most popular MySQL connectors, and guidance on selecting the settings that will maximize your application’s performance
  • Why MySQL delivers excellent performance and how to go beyond the default settings to optimize MySQL’s queries, reporting, and search capabilities

Check out the full agenda and register here!

Wednesday Jan 18, 2012

MySQL: An Introduction for Oracle DBAs

Patrick Hurley has been successfully delivering his presentation entitled “MySQL: The Least an Oracle Professional Needs to Know” at various conferences.

Oracle DBAs may appreciate his blog post “MySQL: An Introduction for Oracle DBAs”:

You are an Oracle Database Administrator. You enjoy looking after Oracle databases and you are really good at it. In the corner of the machine room, on the edge of your peripheral vision, is a server running a database called MySQL….

…One day your manager calls you into his office, “Can you just have a quick look at that MySQL database server, the one with the website and the blog on it? You know, make sure it’s secure and stuff. Shouldn’t be too hard for an Oracle DBA like yourself.”

You know absolutely nothing about MySQL. Where do you start?

Continues….Read the blog!

Additional information Oracle DBAs may find useful:

Thursday Jan 12, 2012

MySQL Enterprise Backup: Redo-log-only Incremental Backups

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 .

Wednesday Jan 11, 2012

MySQL Enterprise Backup: Taking Incremental Backups without specifying LSN

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.

Tuesday Jan 10, 2012

Announcing MySQL Enterprise Backup 3.7.0

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,

 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.

Incremental Backup without specifying LSN:
The new option --incremental-base=dir:/  helps you to perform incremental backup without needing to specify lsn value. The input to --incremental-base=dir:/ option is location of any previous backup over which incremental backup is to be done.

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.



Oracle Database Firewall Certified with MySQL Enterprise Edition

Yesterday Oracle announced that the latest release of Oracle Database Firewall introduced support for MySQL Enterprise Edition.

To address threats like SQL injections, Oracle Database Firewall establishes a defensive perimeter around databases, while monitoring and enforcing normal application behavior in real-time to help prevent SQL injection attacks as well as unauthorized attempts to access sensitive information.

Oracle Database Firewall represents a new addition to the growing list of MySQL Enterprise Oracle Certifications.

Numerous organizations are using both the Oracle database and MySQL, typically relying on MySQL for their web-based and departmental applications. Oracle MySQL Enterprise Certifications make it easier, faster and safer for them to deploy and manage both databases within their existing environment, using the same set of tools.

According to the most recent IOUG Data Security Survey, only 36 percent of respondents have taken steps to ensure their applications are not subject to SQL injection attacks (1). Oracle Database Firewall now protects MySQL databases against data breaches without requiring any changes to existing applications, the database infrastructure or the existing operating system of the target database.

Read the press release to learn more.

For more information about MySQL Enterprise Edition, get our “MySQL Enterprise Edition Product Guide”.

(1) “Databases Are More at Risk Than Ever: 2011 IOUG Data Security Survey,” October 2011

Monday Jan 09, 2012

MySQL for Beginners for a great start to 2012

Start off 2012 with the MySQL for Beginners course. You can get this training from your desk with the MySQL for Beginners Live Virtual Delivery and now you can even learn at your own pace with the all new MySQL for Beginners self-paced online delivery.

All you need to take the MySQL for Beginners course is a basic level of computer literacy.

With this course you will learn how this powerful relational database management system can make your life easier and more fun! This class covers all the basics and will get you on your way, with a solid foundation.

Globo.com Cuts Costs by 65% with MySQL Enterprise Edition

Globo.com is the latest business initiative of Organizações Globo, a Brazilian media group that owns the television networks Rede Globo, Globosat, and TV Globo Internacional, as well as radio broadcast, publishing, and production companies. Globo.com gathers and publishes content through the Internet to Portuguese-speaking audiences worldwide.

Read our case study to find out how globo.com improved the performance and scalability of its Web portal while improving flexibility and reducing costs by 65% with MySQL Enterprise Edition.

To learn more about MySQL Enterprise Edition, get our “MySQL Enterprise Edition Product Guide”.

About

Get the latest updates on products, technology, news, events, webcasts, customers and more.

Twitter


Facebook

Search

Archives
« January 2012 »
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
13
14
15
16
17
19
20
21
22
24
25
26
27
28
29
30
    
       
Today