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 14

Displaying posts with tag: MySQL Enterprise Backup (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...]
Using mysqldump and the MySQL binary log – a quick guide on how to backup and restore MySQL databases
+0 Vote Up -0Vote Down

Be sure to check out my other posts on mysqldump:
Scripting Backups of MySQL with Perl via mysqldump
Splitting a MySQL Dump File Into Smaller Files Via Perl
Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 1 of 2
Creating and restoring database



  [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...]
–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...]
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...]
Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 2 of 2
+1 Vote Up -0Vote Down

In part one of this post, I gave you a couple examples of how to backup your MySQL databases using mysqldump. In part two, I will show you how to use the MySQL Enterprise Backup (http://www.mysql.com/products/enterprise/backup.html) (MEB) to create a full and partial backup.


MySQL Enterprise Backup (http://www.mysql.com/products/enterprise/backup.html) provides enterprise-grade backup and recovery for MySQL. It delivers hot, online, non-blocking backups on multiple platforms including Linux, Windows, Mac & Solaris. To learn more, you may download a

  [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...]
Vote for MySQL[plus] awards 2011 !
+5 Vote Up -0Vote Down

First of all, I wish you a happy new year.
Many things happened last year, it was really exciting to be involved in the MySQL ecosystem.
I hope this enthusiasm will be increased this year, up to you !

To start the year, I propose the MySQL[plus] Awards 2011
It will only take 5 minutes to fill out these polls.
Answer with your heart first and then with your experience with some of these tools or services.

Polls will be closed January 31, so, vote now !
For “other” answers, please,  let me a comment with details.

Don’t hesitate to submit proposal for tools or services in the comments.






  [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...]
5 Steps to an Enterprise Backup
Employee +0 Vote Up -0Vote Down

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
  [Read more...]
Showing entries 1 to 14

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.