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 30 of 60 Next 30 Older Entries

Displaying posts with tag: recovery (reset)

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...]
New Webinar: Repair and Recovery for your MySQL, MariaDB and MongoDB/TokuMX Clusters
+1 Vote Up -0Vote Down
December 19, 2013 By Severalnines


Database clusters are pretty sophisticated distributed systems with complex dependencies between nodes. The failure of a node will generally impact the overall cluster, as the remaining nodes need to reconfigure themselves to continue to operate without the failed node. Since re-introducing a node will also affect the existing cluster, the timing could therefore be dependent on the state of the other nodes in the cluster. Repair and restarts often needs to be performed


  [Read more...]
Point-in-time Recovery in MySQL Galera Cluster
+1 Vote Up -0Vote Down
October 28, 2013 By Severalnines

Data protection is vital for DB admins, especially when it involves data that is accessed and updated 24 hours a day. Clustering and replication are techniques that provide protection against failures, but what if a user or DBA issues a detrimental command against one of the databases? A user might erroneously delete or update the contents of one or more tables, drop database objects that are still needed during an update to an application, or run a large batch update that fails midway. How do we recover lost data? 

 

In a previous post, we showed you how to do a full restore from backup. Great, now you’ve

  [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...]
Checking B+tree leaf nodes list consistency in InnoDB
+1 Vote Up -0Vote Down

If we have InnoDB pages there are two ways to learn how many records they contain:

  • PAGE_N_RECS field in the page header
  • Count records while walking over the list of records from infimum to supremum

In some previous revision of the recovery tool a short summary was added to a dump which is produced by the constraints_parser.

But if a page is lost and page_parser hasn’t found it, all records from this page are lost. In other words per-page recovery statistics gives us little idea about whether or not a recovered table is complete.

To cover this flaw a new tool index_check is introduced in

  [Read more...]
How to recover table structure from InnoDB dictionary
+2 Vote Up -0Vote Down

To recover a dropped or corrupt table with Percona Data Recovery Tool for InnoDB you need two things: media with records(ibdata1, *.ibd, disk image, etc.) and a table structure. Indeed, there is no information about the table structure in an InnoDB page. Normally we either recover the structure from .frm files or take it from some old backup.

A new tool sys_parser can recover the table structure from InnoDB dictionary.

Why do we need a new tool anyway? It is absolutely critical to have an accurate table definition to ensure a successful recovery. Even an unnoticeable difference like NULL or NOT NULL can shift all

  [Read more...]
Recovery after DROP & CREATE
+3 Vote Up -0Vote Down

In a very popular data loss scenario a table is dropped and empty one is created with the same name. This is because  mysqldump in many cases generates the “DROP TABLE” instruction before the “CREATE TABLE”:

DROP TABLE IF EXISTS `actor`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

If there were no subsequent CREATE TABLE the recovery

  [Read more...]
5 Things You Overlooked with MySQL Dumps
+1 Vote Up -1Vote Down

Read the original article at 5 Things You Overlooked with MySQL Dumps

1. Point In Time Recovery If you’ve never done point in time recovery, it’s time to take a second look. With a standard mysqldump you restore your database to the time when the backup happened. Only do them once a day, then you can lose as much as 24 hours of data. Enter point-in-time recovery, [...]

For more articles like these go to Sean Hull's Scalable Startups

Related posts:
  • Ten things to remember about MySQL backups
  • Easy MySQL replication with hotbackups
  •   [Read more...]
    Server Ownership Legalities
    +0 Vote Up -0Vote Down

    As I reported via Twitter late last week, we encountered an issue that got some of our mail delivery delayed by about a day and a half. I’ll explain more about what happened as I believe in openness on these matters, and also the experience has educational content for others.

    Our mail server doesn’t have direct external interaction, it’s shielded by two relays that handle both the inbound MX and the outbound queue. This setup works remarkably well in terms of exposure to spam and other malicious activity. As previously discussed, it appears that it’s more difficult to make mail server infra more resilient without expending lots more time/effort and infrastructure expenditure. Just because of the way the common tools for mail delivery and imap are built, having two or more of each in a semi-active setup gets quite complex. Complexity is in itself a risk so it has to

      [Read more...]
    With InnoDB’s Transportable Tablespaces, Recovering Data from Stranded .ibd Files is a Thing of the Past
    +2 Vote Up -0Vote Down

    Being a data recovery specialist and having recovered countless GBs of corrupted, and/or stranded, InnoDB data in my days, I am very happy to hear about the new InnoDB Transportable Tablespaces coming in MySQL 5.6!

    Back in the day, if you had a stranded .ibd file (the individual InnoDB data file with –innodb-file-per-table option), you basically had nothing (even though that file contained all of the data). This was because unless you had the original instance that that particular .ibd file (table) originated from, there was no way to load it, import, or dump from it. So it was not of much use, though all the data was *right* there.

    Thus I created the method of Recovering an InnoDB table from only an .ibd file (I should note that this was before

      [Read more...]
    What a Hosting Provider did Today
    +0 Vote Up -0Vote Down
    I found Dennis the Menace, he now has a job as system administrator for a hosting company. Scenario: client has a problem with a server becoming unavailable (cause unknown) and has it restarted. MySQL had some page corruption in the InnoDB tablespace. The hosting provider, being really helpful, goes in as root and first deletes ib_logfile* then ib* in /var/lib/mysql. He later says “I am sorry if I deleted it. I thought I deleted the log only. Sorry again.”  Now this may appear nice, but people who know what they’re doing with MySQL will realise that deleting the iblogfiles actually destroys data also. MySQL of course screams loudly that while it has FRM files it can’t find the tables. No kidding! Then, while he’s been told to not touch anything any more, and I’m trying to see if I can recover the deleted files on ext3 filesystem (yes there are tools  [Read more...]
    MySQL Enterprise Backup 3.6 - New backup streaming, integration with Oracle Secure Backup and other common backup media solutions
    Employee_Team +5 Vote Up -0Vote Down
    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 (http://mysql.com/products/enterprise/backup.html) ("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

      [Read more...]
    Connecting orphaned .ibd files
    +2 Vote Up -0Vote Down

    There are two ways InnoDB can organize tablespaces. First is when all data, indexes and system buffers are stored in a single tablespace. This is typicaly one or several ibdata files. A well known innodb_file_per_table option brings the second one. Tables and system areas are split into different files. Usually system tablespace is located in ibdata1 file and every InnoDB table has two files e.g.  actor.frm and actor.ibd.

    The annoying thing about .ibd files you can’t easily copy the an .ibd file to another MySQL server. If you try to very often you’ll get an error in the log:

    InnoDB: Error: tablespace id is 10 in the data dictionary
    InnoDB: but in file ./sakila/actor.ibd it is 15!

    However sometimes you have to connect the .ibd file to an alien ibdata1.

    There are several situation when

      [Read more...]
    MySQL data backup: going beyond mysqldump
    +1 Vote Up -0Vote Down
    A user on a linux user group mailing list asked about this, and I was one of the people replying. Re-posting here as I reckon it’s of wider interest. > [...] tens of gigs of data in MySQL databases. > Some in memory tables, some MyISAM, a fair bit InnoDB. According to my > understanding, when one doesn’t have several hours to take a DB > offline and do dbbackup, there was/is ibbackup from InnoBase.. but now > that MySQL and InnoBase have both been ‘Oracle Enterprised’, said > product is now restricted to MySQL Enterprise customers.. > > Some quick searching has suggested Percona XtraBackup as a potential > FOSS alternative. > What backup techniques do people employ around these parts for backups > of large mixed MySQL data sets where downtime *must* be minimised? > > Has your backup plan ever been put to the test? You  [Read more...]
    Oracle Solaris Cluster 3.3 available
    Employee +2 Vote Up -2Vote Down
    On September 8, 2010 Oracle announced the availability of Oracle Solaris Cluster 3.3
    
    Oracle Solaris Cluster 3.3, built on the solid foundation of Oracle Solaris, offers the 
    most extensive Oracle enterprise High Availability and Disaster Recovery solutions for the 
    largest portfolio of mission-critical applications.
    
    Integrated and thoroughly tested with Oracle's Sun servers, storage, connectivity 
    solutions and Solaris 10 features, Oracle Solaris Cluster is now qualified with Solaris 
    Trusted Extensions, supports Infiniband for general networking or storage usage, and can 
    be deployed with Oracle Unified Storage in Campus Cluster configurations. It extends its 
    applications support to new Oracle applications such as Oracle Business Intelligence, 
    PeopleSoft, TimesTen, and MySQL Cluster.
    
    The single, integrated HA and DR solution enables multi-tier deployments in virtualized 
    environments.
      [Read more...]
    Oracle Solaris Cluster 3.3 available
    Employee +0 Vote Up -0Vote Down
    On September 8, 2010 Oracle announced the availability of Oracle Solaris Cluster 3.3
    
    Oracle Solaris Cluster 3.3, built on the solid foundation of Oracle Solaris, offers the 
    most extensive Oracle enterprise High Availability and Disaster Recovery solutions for the 
    largest portfolio of mission-critical applications.
    
    Integrated and thoroughly tested with Oracle's Sun servers, storage, connectivity 
    solutions and Solaris 10 features, Oracle Solaris Cluster is now qualified with Solaris 
    Trusted Extensions, supports Infiniband for general networking or storage usage, and can 
    be deployed with Oracle Unified Storage in Campus Cluster configurations. It extends its 
    applications support to new Oracle applications such as Oracle Business Intelligence, 
    PeopleSoft, TimesTen, and MySQL Cluster.
    
    The single, integrated HA and DR solution enables multi-tier deployments in virtualized
      [Read more...]
    Oracle Solaris Cluster 3.3 available
    Employee +0 Vote Up -0Vote Down
    On September 8, 2010 Oracle announced the availability of Oracle Solaris Cluster 3.3
    
    Oracle Solaris Cluster 3.3, built on the solid foundation of Oracle Solaris, offers the 
    most extensive Oracle enterprise High Availability and Disaster Recovery solutions for the 
    largest portfolio of mission-critical applications.
    
    Integrated and thoroughly tested with Oracle's Sun servers, storage, connectivity 
    solutions and Solaris 10 features, Oracle Solaris Cluster is now qualified with Solaris 
    Trusted Extensions, supports Infiniband for general networking or storage usage, and can 
    be deployed with Oracle Unified Storage in Campus Cluster configurations. It extends its 
    applications support to new Oracle applications such as Oracle Business Intelligence, 
    PeopleSoft, TimesTen, and MySQL Cluster.
    
    The single, integrated HA and DR solution enables multi-tier deployments in virtualized
      [Read more...]
    How long is recovery from 8G innodb_log_file
    +3 Vote Up -0Vote Down

    In my previous posts I highlighted that one of improvements in Percona Server is support of innodb_log_file_size > 4G. This test was done using Percona Server 5.5.7, but the same performance expected for InnoDB-plugin and MySQL 5.5.

    The valid question how long is recovery in this case, so let's test it. I took the same tpcc-mysql 1000W workload with 52GB and 144GB innodb_buffer_pool_size with data located on Virident tachIOn card and killed mysqld after 30 mins of work.

    The recovery time after start is:
    for 52GB innodb_buffer_pool_size:

    PLAIN
      [Read more...]
    Why do I recommend switching over from MyISAM to Innodb!
    +1 Vote Up -0Vote Down
    Although MyISAM has been the default storage engine for MySQL but its soon going to change with the release of MySQL server 5.5. Not only that, more and more people are shifting over to the Innodb storage engine and the reasons for that is the tremendous benefits, not only in terms of performance, concurrency, ACID-transactions, foreign key constraints, but also because of the way it helps out the DBA with hot-backups support, automatic crash recovery and avoiding data inconsistencies which can prove to be a pain with MyISAM. In this article I try to hammer out the reasons why you should move on to using Innodb instead of MyISAM.
    Lost innodb tables, xfs and binary grep
    +4 Vote Up -0Vote Down

    Before I start a story about the data recovery case I worked on yesterday, here's a quick tip - having a database backup does not mean you can restore from it. Always verify your backup can be used to restore the database! If not automatically, do this manually, at least once a month. No, seriously - in most of the recovery cases I worked on, customers did have some sort of backup, but it just wasn't working, complete and what not. Someone set it up and never bothered to check if it still works after a while.

    Anyway, this post is not really about the backups but rather about few interesting things I learned during last recovery case.

    First, some facts about the system and how data was lost:

    • MySQL had a dedicated partition on XFS file system
      [Read more...]
    An argument for not using mysqldump
    +2 Vote Up -0Vote Down

    I have a 5G mysqldump which takes 30 minutes to restore from backup.  That means that when the database reaches 50G, it should take 30x10=5 hours to restore.  Right?  Wrong.

    Mysqldump recovery time is not linear.  Bigger tables, or tables with more indexes will always take more time to restore.

    If I restore from a raw backup (LVM snapshot, xtrabackup, innodb hot backup), it is very easy to model how much longer recovery time will take:

    Backup is 80G
    Copy is at 70MB/s.
    10G is already complete.
    = ((80-10) * 1024)/70/60 = ~17 minutes

    I can tell progress with mysqldump by monitoring the rate at which show global status like 'Handler_write'; increases and compare it to my knowledge of about how many rows are in each table.  But progress != a magic number like "17





      [Read more...]
    Rectifying Corruption Issues in MyISAM Table in MySQL 5.0.22 Database
    +0 Vote Up -0Vote Down
    MySQL is the most popular open-source database in the world. It has the distinction of being the only database that is used in every continent. Yes, even Antartica!!! The reasons for such popularity is its availability, performance, robustness, etc. It provides three types of database engines, MyISAM, InnoDB, and MySQL Archive. Out of these, the MyISAM table is the default table type. It is fast, simple, and allows full-text searching. Although it is reliable, it can become corrupt and, thus, inaccessible following a system crash. You should use appropriate corrective measures to repair MySQL database. However, if you are not able to do so then you should use a third-party MySQL recovery software to do so.

    Consider a scenario wherein you have MySQL 5.0.22 database running in your system. You use it for MediaWiki installation that is reported as

      [Read more...]
    Restore MyISAM tables by MySQL Recovery Software
    +0 Vote Up -0Vote Down
    Any corruption in MyISAM table is a terrible situation for Database Administrator. These MyISAM tables are the most important components in the MySQL Database to store personal as well as professional data, and the corruption in these tables may inaccessibility to the database. In this situation, a database backup allows you to restore the database and access the records in the table. Duplicate file (Backup) of the database is mostly stored on a different storage media, the changes of database corruption still exist. In that situation, you will need to perform MySQL database repair by the using of third party software.

    Some error messages that enable you to identify that your MyISAM table (student) is corrupt:

    “Student.frm is locked against change”
    Or
    “Got error message student from table handler”

    Or







      [Read more...]
    BlitzDB Crash Safety and Auto Recovery
    +0 Vote Up -0Vote Down

    Crash Safety is a big deal in the database league. Lack of durability can lead to all sorts of terrible things upon a catastrophic event. Many projects, especially in the so called NoSQL world compromises crash safety in return for higher QPS. The argument there is that the availability of the overall system should be accomplished by replication since a database server can’t be rescued if the physical disk breaks. I happen to agree with this philosophy but I am also aware that this isn’t a correct answer for everyone. So, what will I do with BlitzDB?

    Several relational database hackers have pointed out that BlitzDB isn’t any safer than MyISAM since it doesn’t guarantee crash safety. This is currently true but I plan on making BlitzDB much safer than MyISAM by providing following features.

  • Auto Recovery Routine (startup option)
  • Tokyo
  •   [Read more...]
    Recover BLOB fields
    +2 Vote Up -0Vote Down

    For a long time long types like BLOB, TEXT were not supported by Percona InnoDB Recovery Tool. The reason consists in a special way InnoDB stores BLOBs.

    An InnoDB table is stored in a clustered index called PRIMARY. It must exist even if a user hasn't defined the primary index. The PRIMARY index pages are identified by 8-bytes number index_id. The highest 4 bytes are always 0, so index_id is often notated as o:<4 bytes number>, e.g. 0:258. The pages are ordered in a B-tree. Primary index is used as a key. Inside a page records are stored in a linked list.

    InnoDB page by default is 16k. Obviously if a record is too long, a single page can't store it. If the total record size is less than UNIV_PAGE_SIZE/2 - 200 (this is roughly 7k) then the full record is stored in the page of PRIMARY index. Let's call it internal. In InnoDB sources they have type FIL_PAGE_INDEX*.

      [Read more...]
    Showing entries 1 to 30 of 60 Next 30 Older Entries

    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.