Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 27

Displaying posts with tag: MEB (reset)

Single database backup and restore with MEB
+3 Vote Up -0Vote Down
I was recently asked about if MySQL Enterprise Backup would be able to restore single databases.

My initial answer was that this was complicated, but might be doable with the Transportable Table Space (TTS) option.

But first let's go back to the basics. A common way of working with mysqldump is to get a list of databases and then loop through the databases and dump the data and schema to a SQL file. But both backups and restores will take a lot of time if the size of the database grows. And it's a luke-warm backup at best instead of a hot backup. So that's why we have MySQL Enterprise Backup.

MySQL Enterprise Backup allows you to make a hot backup of InnoDB tables by copying the datafiles while watching the InnoDB redo log files.

On disk the data from the InnoDB storage engine consists of a system tablespace (one of more







  [Read more...]
Streaming backups onto the Cloud Storage
Employee +0 Vote Up -0Vote Down


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

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

A few facts about Cloud Storage:

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









  [Read more...]
Cloning a slave using Mysql Enterprise Backup on a GTID enabled server
Employee +0 Vote Up -0Vote Down

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

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

This offers the following advantages:

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

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

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

For a gtid enabled server, the following properties

  [Read more...]
MySQL Enterprise Backup 3.10: Teasing compression.
Employee +0 Vote Up -0Vote Down

Ok, so I wanted to look into the new compression options of MEB 3.10.

And I would like to share my tests with you. Remember, they’re just this, tests, so please feel free to copy n paste and obtain your own results and conclusions, and should I say it, baselines, in order to compare future behaviour, on your own system.

An Oracle Linux 6.3 virtual machine with 3Gb RAM, 2 virtual threads, on a 1x quad core, windows laptop. Not pretty, but hey.

So, these tests are solely about backup. I’ll do restore when I get some *more* time.

 

First up, lets compare like with like, i.e. MEB version 3.9 & 3.10:

Let’s make this interesting, hence, want to use as much resources available as possible, read, write, process threads and number of buffers.

mysqlbackup --user=root --password=oracle
  [Read more...]
MySQL Enterprise Backup 3.10: Teasing compression.
Employee +0 Vote Up -0Vote Down

Ok, so I wanted to look into the new compression options of MEB 3.10.

And I would like to share my tests with you. Remember, they’re just this, tests, so please feel free to copy n paste and obtain your own results and conclusions, and should I say it, baselines, in order to compare future behaviour, on your own system.

An Oracle Linux 6.3 virtual machine with 3Gb RAM, 2 virtual threads, on a 1x quad core, windows laptop. Not pretty, but hey.

So, these tests are solely about backup. I’ll do restore when I get some *more* time.

 

First up, lets compare like with like, i.e. MEB version 3.9 & 3.10:

Let’s make this interesting, hence, want to use as much resources available as possible, read, write, process threads and number of buffers.

mysqlbackup --user=root --password=oracle
  [Read more...]
Data Encryption with MySQL Enterprise Backup 3.10
Employee +0 Vote Up -0Vote Down

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

  [Read more...]
–use-tts backup & restore
Employee +0 Vote Up -0Vote Down

In addition to my recent post, I just had to go into using the –use-tts for specific tables and selective backup sets.

As all my schemas were employeesn, I thought it would be a good idea to run:

mysqlbackup -uroot --socket=/tmp/mysql.sock --backup-dir=/home/mysql/MEB/restore \
--with-timestamp --use-tts --include=employees* backup

as I want all the tables. If I only wanted a specific table, say ‘salaries’ I could have done:

mysqlbackup -uroot --socket=/tmp/mysql.sock --backup-dir=/home/mysql/MEB/restore \
--with-timestamp --use-tts --include=employees*\.*salaries backup

and backup just the ‘salaries’ tables but from all the employeesn databases.

then.. be a mean little dba:

drop database employees;
drop database employees10;
drop database
  [Read more...]
MySQL Enterprise Backup: parallel config & backup n restore results.
Employee +0 Vote Up -0Vote Down

In this post I go into some performance metrics and time spent on using MySQL Enterprise Backup instead of mysqldump, and seeing how far I could go with some parallel configuration.

Setup:

It’s on an old laptop:

–Ubuntu 12.04 LTS, 32bit Intel Pentium M 1.86Ghz, 2Gb –Source disk:  internal 80Gb ATA ST9808211A –Destination:  external 1Tb SAMSUNG HD103SI –MySQL Enterprise Edition 5.6.15 –MySQL Enterprise Backup 3.9.0 –Employees sample database duplicated via MySQL Utilities 1.3.6 (on Win7 PC) to generate a ~5Gb MySQL Server. And to simulate data size, I used the MySQL Utilities:
mysqldbcopy --source=root:pass@host:3356 --destination=root:pass@host:3356 employees:employees1 \
employees:employees2 employees:employees3 employees:employees4 ...
  [Read more...]
MySQL Enterprise Backup: parallel config & backup n restore results.
Employee +0 Vote Up -0Vote Down

In this post I go into some performance metrics and time spent on using MySQL Enterprise Backup instead of mysqldump, and seeing how far I could go with some parallel configuration.

Setup:

It’s on an old laptop:

–Ubuntu 12.04 LTS, 32bit Intel Pentium M 1.86Ghz, 2Gb –Source disk:  internal 80Gb ATA ST9808211A –Destination:  external 1Tb SAMSUNG HD103SI –MySQL Enterprise Edition 5.6.15 –MySQL Enterprise Backup 3.9.0 –Employees sample database duplicated via MySQL Utilities 1.3.6 (on Win7 PC) to generate a ~5Gb MySQL Server. And to simulate data size, I used the MySQL Utilities:
mysqldbcopy --source=root:pass@host:3356 --destination=root:pass@host:3356 employees:employees1 \
employees:employees2 employees:employees3 employees:employees4 ...
  [Read more...]
MEB integration with Workbench
Employee +0 Vote Up -0Vote Down

The latest versions of MySQL Workbench starting from version 6 have the capability to integrate with MySQL Enterprise Backup (MEB).

Though all the functionality of MEB is not supported, it is still a great tool to begin your backup and get your backup setup in minutes. Workbench also provides the additional capability to be able to schedule your backups.

The sections below illustrate the setup and usage of the Workbench UI to be able to setup and execute backup using MEB.

This is a quick start guide to setup, make a backup and do a recovery using MySQL Workbench and MEB.

The installation of WB and MEB is not discussed; it should be a simple automated install for both packages.

Setup

Workbench now comes with a new MySQL enterprise options in its navigator menu on the left. You need to click the Online

  [Read more...]
Hello World
Employee +2 Vote Up -0Vote Down

So here goes …

While there are already some blog postings from me at Oracle’s MySQL Enterprise Backup (MEB) team blog at https://blogs.oracle.com/mysqlenterprisebackup/, this blog is for my personal views and views outside of MEB.

Why the name with 2c.
The first challenge before you think of blogging is to convice yourself that maybe your views will have some value for a reader. The second is to find a name of the blog. Its not that I am fully convinced of the former but there are some things that need to be written and since the need is mine the fulfillment of the need should also come from me.

The name is to ensure to keep my words humble and respect views which may differ from mine; maybe conflicting and still right. Different people have differing views and this blog is just one view as seen


  [Read more...]
Backing up full server instance using MySQL Enterprise Backup
Employee +0 Vote Up -0Vote Down
Introduction:

MySQL Enterprise Backup(MEB) takes fast, consistent backups of MySQL server data, and helps in restoring the server to source server's data at the time of backup. But most of the times it is as much important to have same source server's state(server configuration like server global variables, plugins), as data. As backups become more frequent, server variables modified, plugins added or removed, it is very difficult to keep track of this changing server states for every backup. MEB 3.9.0 helps user in providing a complete backup so that the restored server can run with exactly the same state as that of the source server at the time of the backup.


  [Read more...]
MySQL Enterprise Backup: PITR Partial Online Recovery
Employee +3 Vote Up -0Vote Down

Here’s a look at using MySQL Enterprise Backup in a specific example:

Consider a Backup Policy

– Full Backup of the environment. – Complemental Incremental backups & online BinLogs. And the Restore: – Logical Restore. – Online, Zero impact. – Partial, single database, group of tables.

The Backup

A working environment, with 4 databases, of which 2 will require restoration. Full backup with MySQL Enterprise Backup:
mysqlbackup --user=root --socket=/tmp/mysql.sock \
  --backup-dir=/home/mysql/voju5/backup/ \
  --with-timestamp backup

Test preparation

Create 4 different databases, where the structure & content is the same.
create database v5_1; use v5_1; create table `voju5` (
  `ID` int(7) NOT NULL AUTO_INCREMENT,
  `Name` char(20) NOT NULL DEFAULT '‘,
  [Read more...]
MySQL Enterprise Backup: PITR Partial Online Recovery
Employee +0 Vote Up -0Vote Down

Here’s a look at using MySQL Enterprise Backup in a specific example:

Consider a Backup Policy

– Full Backup of the environment. – Complemental Incremental backups & online BinLogs. And the Restore: – Logical Restore. – Online, Zero impact. – Partial, single database, group of tables.

The Backup

A working environment, with 4 databases, of which 2 will require restoration. Full backup with MySQL Enterprise Backup:
mysqlbackup --user=root --socket=/tmp/mysql.sock \
  --backup-dir=/home/mysql/voju5/backup/ \
  --with-timestamp backup

Test preparation

Create 4 different databases, where the structure & content is the same.
create database v5_1; use v5_1; create table `voju5` (
  `ID` int(7) NOT NULL AUTO_INCREMENT,
  `Name` char(20) NOT NULL DEFAULT '‘,
  [Read more...]
Improved Backup Progress in MySQL Enterprise Backup(MEB) 3.8.2
Employee +0 Vote Up -0Vote Down

Nowadays huge amount of data is stored in databases and it is very inevitable for business needs. With parallel backup, MEB has best in class performance and continues to improve. With terabytes of data, it can take hours to backup the data. As a DBA you'd like to know how much of the backup is done, how much data in MB still remains and also what's currently happening in backup.

So MEB 3.8.2 has an enhanced progress indicator which will help the DBA in many ways like

  • You can analyze current and expected space requirements so that you can release some space by removing files on a file system or stop the backup if the space isn't there,to try other options like compressed backup.

      [Read more...]
    MySQL Enterprise Backup 3.8.2 has been released!
    Employee +3 Vote Up -0Vote Down

    MySQL Enterprise Backup v3.8.2, a maintenance release of online MySQL backup tool, is now available for download from My Oracle Support  (MOS) website as our latest GA release.  It will also be available via the Oracle Software Delivery Cloud in approximately 1-2 weeks. A brief summary of the changes in MySQL Enterprise Backup version 3.8.2 is given below.


      A. Functionality Added or Changed: 

    • MySQL Enterprise Backup has a new --on-disk-full command line option. mysqlbackup could hang when the disk became full, rather than detecting the low space condition. mysqlbackup now monitors disk space when running backup commands, and users can now specify the action to take at a disk-full condition with the --on-disk-full option. For more details, refer this

      [Read more...]
    How to Back Up Selected MySQL Databases
    Employee +3 Vote Up -0Vote Down

    I recently had to do a backup of a 30 very large databases within a MySQL instance that had a total of 60+ databases.  I did NOT want to back up the other databases. In fact, the other databases had very large tables in them and I had very little disk space, so I could not afford to back them up. In this post I will share what I learned.

    I’ve simplified the problem to illustrate the idea.  My goal in this post is to backup one database when an instance has many, with mixed InnoDB and MyIsam tables.

    • I have 3 databases called dbtest1, dbtest2, and dbtest3, in one instance of MySQL .  I only want to back up  the tables in database dbtest2

      [Read more...]
    Truly Parallel backup (MySQL Enterprise Backup 3.8 and later)
    Employee +0 Vote Up -0Vote Down
    How do you implement a parallel algorithm for a software which needs to be streamed to tapes?
    How do you ensure that you have the capability to be able to tune the level of parallelism for varying input and output devices and varying levels of load?
    These were some of the questions that we needed to answer when we were trying to implement multi-threading capability for MySQL Enterprise Backup (MEB).
    The trivial way of achieving parallelism is by having the multiple threads pick up the different files (in a file per table) scenario. But this did not seem adequate because:
    a) The sizes of these files (corresponding to the tables) could be different and then one large file would limit the level of parallelism since it would be processed by a single thread.
    b) If you have to stream the backup how do you reconcile these




      [Read more...]
    MySQL Enterprise Backup 3.8.1 release for 5.6 Server
    Employee +0 Vote Up -0Vote Down

    The MySQL Enterprise Backup 3.8.1 release's main goal was support MySQL 5.6 server. But also beyond that primary goal MEB team added some valuable new options and features to ensure you'll get most from the new features in 5.6 as well. At a glance, here are some of the highlights,

    MEB copy of InnoDB undo log tablespaces

    MySQL 5.6 introduces a new feature to store undo logs in separate files called as undo tablespaces for improved performance. These undo tablespaces are logically part of system  tablespace. All the commands associated with MEB - "backup", "apply-log" and "copy-back"  now take care of the undo tablespaces in the same way as they process the system tablespace. MEB now supports innodb_undo_directory[logs][tablespace] option variables. When backup is executed, undo datafiles (up to number

      [Read more...]
    MySQL Connect Conference: My Experience
    Employee +0 Vote Up -0Vote Down
    It was a great experience to attend the MySQL Connect Conference for the first time ever. Personally I was very much enthralled to present about "How to make MySQL Backups" besides attending different sessions to absorb more knowledge about the technical prospects of MySQL. One of the agenda items in my presentation was "MySQL Enterprise Backup" functionality and features. There were total of 40 attendees in the session, who were very much interested about the MySQL Enterprise Backup product and gave positive feedback as well as areas of improvements on our product. Some of our features brought lot of  [Read more...]
    MEB: Taking Incremental Backup using last successful backup
    Employee_Team +3 Vote Up -0Vote Down

    Introduction

    In MySQL Enterprise Backup v3.7.0 (MEB 3.7.0) a new option '–incremental-base' was introduced. Using this option a user can take in incremental backup without specifying the '–start-lsn' option. Description of this option can be found here. Instead of '–start-lsn' the user can provide the

      [Read more...]
    Story of success: MySQL Enterprise Backup (MEB) was successfully integrated with IBM Tivoli Storage Manager (TSM) via System Backup to Tape (SBT) interface.
    Employee_Team +3 Vote Up -0Vote Down

    Since version 3.6 MEB supports backups to tape through the SBT interface.

    The officially supported tool for such backups to tape is Oracle Secure Backup (OSB).

    But there are a lot of other Storage Managers. MEB allows to use them through the SBT interface. Since version 3.7 it also has option --sbt-environment which allows to pass environment variables, not needed by OSB, to third-party managers. At the same time MEB can not guarantee it would work with all of them.

    This month we were contacted by a customer who wanted to use IBM Tivoli Storage Manager (TSM) with MEB. We could only say them same thing I wrote in previous paragraph: this solution is supposed to work, but you have to be pioneers of this technology. And they agreed.

    They agreed to be the pioneers and so the story begins.

    MEB requires







      [Read more...]
    MySQL Enterprise Backup: Redo-log-only Incremental Backups
    Employee_Team +3 Vote Up -0Vote Down
    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

      [Read more...]
    MySQL Enterprise Backup: Redo-log-only Incremental Backups
    Employee_Team +0 Vote Up -0Vote Down
    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

      [Read more...]
    MySQL Enterprise Backup: Taking Incremental Backups without specifying LSN
    Employee_Team +5 Vote Up -0Vote Down
    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:   [Read more...]
    MySQL Enterprise Backup: Taking Incremental Backups without specifying LSN
    Employee_Team +0 Vote Up -0Vote Down
    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:   [Read more...]
    Monitoring Your MySQL Backup
    Employee +2 Vote Up -0Vote Down

    In California we are always thinking about backups.   Living near an earthquake fault line makes this necessary.  For me, it is the Hayward Fault (it runs from goal post to goal post  in University of Californa Berkeley stadium).  We are strongly advised to have backup systems for water, food, and medical emergencies.  It’s necessary to

      [Read more...]
    Showing entries 1 to 27

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.