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 中文
Previous 30 Newer Entries Showing entries 31 to 60 of 61 Next 1 Older Entries

Displaying posts with tag: recovery (reset)

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...]
InnoDB recovery gets even faster in Plugin 1.1, thanks to native AIO
+2 Vote Up -0Vote Down

InnoDB Plugin 1.1 doesn’t add any recovery specific improvements on top of what we already have in Plugin 1.0.7. The details on the latter are available in this blog. Yet, when I tried to recover another big recovery dataset I created, I got the following results for total recovery time:

  • Plugin 1.0.7: 46min 21s
  • Plugin 1.1: 32min 41s

Plugin 1.1 recovery is 1.5 times faster. Why would that happen? The numerous concurrency improvements in Plugin 1.1 and MySQL 5.5 can’t really affect the recovery. The honor goes to Native Asynchronous IO on Linux. Let’s try without it:

  • Plugin 1.1 with –innodb-use-native-aio=0: 49min 07s

which is about the same as 1.0.7 time. My numerous

  [Read more...]
Debugging problems with row based replication
+3 Vote Up -3Vote Down

MySQL 5.1 introduces row based binary logging.  In fact, the default binary logging format in GA versions of MySQL 5.1 is 'MIXED' STATEMENT*;   The binlog_format  variable can still be changed per sessions which means it is possible that some of your binary log entries will be written in a row-based fashion instead of the actual statement which changed data, even when the global setting on the master is to write binary logs in statement mode.   The row-based format does offer advantages particularly if triggers or stored procedures are used, or if non deterministic functions like RAND() are used in DML statements.

A statement based replication slave can get out of sync with the master fairly easily, especially if data is changed on the slave.   It is possible for a statement to execute successfully on a slave even if the data is not 100% in

  [Read more...]
InnoDB's tablespace ids and Partitions
+3 Vote Up -0Vote Down
There are times when what you have is a partially running database and a bunch of backup innodb tablespace files (the .ibd files). If you're using innodb_file_per_table, then you have a separate .ibd file for each InnoDB table.

Now, you have your running database with a bunch of tables, and you want to replace some of them with the backup .ibd files. According to the MySQL docs, you'd do this:
  • ALTER TABLE foo DISCARD TABLESPACE; (this deletes the current .ibd file)
  • copy the old .ibd file into your database directory
  • ALTER TABLE foo IMPORT TABLESPACE;
    Assuming your .ibd file was from the same database and you did not drop the table and recreate it sometime between when you made the backup .ibd and now, this should work.


  •   [Read more...]
    How PostgreSQL protects against partial page writes and data corruption
    +5 Vote Up -4Vote Down

    I explored two interesting topics today while learning more about Postgres.

    Partial page writes

    PostgreSQL’s partial page write protection is configured by the following setting, which defaults to “on”:

    full_page_writes (boolean)

    When this parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint… Storing the full page image guarantees that the page can be correctly restored, but at a price in increasing the amount of data that must be written to WAL. (Because WAL replay always starts from a checkpoint, it is sufficient to do this during the first change of each page after a checkpoint. Therefore, one way to reduce the cost of full-page writes is to increase the

      [Read more...]
    Tool of the Day: rsnapshot
    +0 Vote Up -0Vote Down

    rsnapshot is a filesystem snapshot utility for making backups of local and remote systems, based on rsync. Rather than just doing a complete copy every time, it uses hardlinks to create incrementals (which are from a local perspective a full backup also). You can specify how long to keep old backups, and all the other usual jazz. You’d generally have it connect over ssh. You’ll want/need to run it on a filesystem that supports hardlinks, so that precludes NTFS.

    In the context of MySQL, you can’t just do a filesystem copy of your MySQL data/logs, that would be inconsistent and broken. (amazingly, I still see people insisting/arguing on this – but heck it’s your business/data to gamble with, right?)

    Anyway, if you do a local mysqldump also, or

      [Read more...]
    MySQL Cluster Data Node restart times
    Employee +0 Vote Up -0Vote Down

    Restarts are required for certain, infrequent maintenance activities. Note that there is no loss of service while a single node restarts.

    When a data node restarts, it first attempts to load the data into memory from the local log files and then it will catch up with any subsequent changes by retrieveing them from the surviving node(s) in its node group.

     Based on this, you would expect the time taken to restart a data node to be influenced by:

  • The amount of data that was stored on the data node before the restart
  • Rate of updates being made to the data during the restart
  • Network performance (assuming the data is being updated during recovery)
  • The times will also be influenced bycertain configuration parameters, performance of the host machine and whether the multi-threaded data node (ndbmtd) is being used.

    To

      [Read more...]
    Recovery after DROP [ TABLE | DATABASE ]
    +2 Vote Up -0Vote Down

    In your recovery practice we often face the problem when data lost by execution DROP TABLE or DROP DATABASE statement. In this case even our InnoDB Data Recovery tool can't help, as table / directory with files was deleted (if you have innodb-file-per-table). And the same for MyISAM, all .MYD / .MYI / .frm - files are deleted in this case.

    So first step after DROP is to restore files, and for ext3 file system there are two utilities which can help of you are fast (and lucky) enough.
    First one is ext3grep http://code.google.com/p/ext3grep/, with some instruction on this page http://www.xs4all.nl/~carlo17/howto/undelete_ext3.html.
    And also there is

      [Read more...]
    Partial Binary Log Recovery
    Employee +1 Vote Up -0Vote Down

    I came across a situation recently where I was asked if it was possible to edit a binary log to remove a part of it to restore onto a slave server. Now the choice of doing something like a hexedit did not seem appealing, and the more experienced might suggest that it is simply a matter of using  mysqlbinlog with the --start-position and/or --stop-position options. However, the problem had arisen that required the binary log to played through the replication process onto the slave based on specific options in MySQL cluster, so using an SQL dump from the binary log was of no use.

    Initially this may seem like a daunting task where you will have to find some specialist tool or delve into the deep recesses of the binary log format, but a much simpler solution was found. The replication process allows the slave to be started up to a specific point in the log files. The

      [Read more...]
    Partial Binary Log Recovery
    Employee +0 Vote Up -0Vote Down

    I came across a situation recently where I was asked if it was possible to edit a binary log to remove a part of it to restore onto a slave server. Now the choice of doing something like a hexedit did not seem appealing, and the more experienced might suggest that it is simply a matter of using  mysqlbinlog with the --start-position and/or --stop-position options. However, the problem had arisen that required the binary log to played through the replication process onto the slave based on specific options in MySQL cluster, so using an SQL dump from the binary log was of no use.

    Initially this may seem like a daunting task where you will have to find some specialist tool or delve into the deep recesses of the binary log format, but a much simpler solution was found. The replication process allows the slave to be started up to a specific point in the log files.

      [Read more...]
    Partial Binary Log Recovery
    Employee +0 Vote Up -0Vote Down

    I came across a situation recently where I was asked if it was possible to edit a binary log to remove a part of it to restore onto a slave server. Now the choice of doing something like a hexedit did not seem appealing, and the more experienced might suggest that it is simply a matter of using  mysqlbinlog with the --start-position and/or --stop-position options. However, the problem had arisen that required the binary log to played through the replication process onto the slave based on specific options in MySQL cluster, so using an SQL dump from the binary log was of no use.

    Initially this may seem like a daunting task where you will have to find some specialist tool or delve into the deep recesses of the binary log format, but a much simpler solution was found. The replication process allows the slave to be started up to a specific point in the log files.

      [Read more...]
    InnoDB Conference Presentations Now Online
    Employee +2 Vote Up -0Vote Down

    Well, it took us a little while (we’ve been busy !), but we’ve now posted our presentations on InnoDB from the MySQL Conference and Expo 2009. You can download these presentations by Heikki Tuuri, Ken Jacobs and Calvin Sun from the InnoDB website, as follows:

      [Read more...]
    Solaris Cluster + MySQL Demo at a Exhibition Hall near you
    Employee +0 Vote Up -1Vote Down

    Hi,

    If by any chance you are at the MySQL user conference in Santa Clara conference center, there are some great possibilities to see MySQL and Solaris Cluster in action. We have a demo at the exhibition hall, where you can see MySQL with zone cluster and MySQL with SC Geographic Edition.

    I will host a birds of a feather at Wednesday 4/22/09 evening in meeting room 205 at 7 pm. The Title is "Configuring MySQL in Open HA Cluster, an Easy Exercise" check out the details here.  I will give a presentation at Thursday 4/23/09 morning 10:50 in Ballroom G, the title is "Solutions for High Availability and

      [Read more...]
    Solaris Cluster + MySQL Demo at a Exhibition Hall near you
    Employee +0 Vote Up -0Vote Down

    Hi,

    If by any chance you are at the MySQL user conference in Santa Clara conference center, there are some great possibilities to see MySQL and Solaris Cluster in action. We have a demo at the exhibition hall, where you can see MySQL with zone cluster and MySQL with SC Geographic Edition.

    I will host a birds of a feather at Wednesday 4/22/09 evening in meeting room 205 at 7 pm. The Title is "Configuring MySQL in Open HA Cluster, an Easy Exercise" check out the details here.  I will give a presentation at Thursday 4/23/09 morning 10:50 in Ballroom G, the title is "Solutions for High

      [Read more...]
    Solaris Cluster + MySQL Demo at a Exhibition Hall near you
    Employee +0 Vote Up -0Vote Down

    Hi,

    If by any chance you are at the MySQL user conference in Santa Clara conference center, there are some great possibilities to see MySQL and Solaris Cluster in action. We have a demo at the exhibition hall, where you can see MySQL with zone cluster and MySQL with SC Geographic Edition.

    I will host a birds of a feather at Wednesday 4/22/09 evening in meeting room 205 at 7 pm. The Title is "Configuring MySQL in Open HA Cluster, an Easy Exercise" check out the details here.  I will give a presentation at Thursday 4/23/09 morning 10:50 in Ballroom G, the title is "Solutions for High

      [Read more...]
    A quick note on MySQL troubleshooting and MySQL replication
    +0 Vote Up -0Vote Down

    PLEASE NOTE: I am currently reviewing and extending this document.

    While caring for a remarkable amount of MySQL server instances, troubleshooting becomes a common task. It might of interest for you which

    Recovering a crashed MySQL server

    After a server crash (meaning the system itself or just the MySQL daemon) corrupted table files are quite common. You’ll see this when checking the /var/log/syslog, as the MySQL daemon checks tables during its startup.

    Apr 17 13:54:44 live1 mysqld[2613]: 090417 13:54:44 [ERROR]
      /usr/sbin/mysqld: Table './database1/table1' is marked as
      crashed and should be repaired

    The MySQL daemon just told you that it found a broken

      [Read more...]
    Data Backup and Recovery for MySQL - a MySQL Time Machine is Born.
    Employee +0 Vote Up -0Vote Down

    Sure, you've heard it before: [some company's logo] has a new MySQL backup tool that promises to solve all of your data recovery needs. The good news is most of these tools work pretty well. However, they tend to suffer from a similar set of limitations. Most require sophisticated infrastructures or complex setup and maintenance and can become a resource drain for some organizations. You're probably wondering, "Why can't someone build a fully automated MySQL backup solution that you can just turn on and forget?"

    I am happy to say that the MySQL Developers at Sun are doing just that. In fact, a prototype will be demonstrated at the 2009 MySQL Users' Conference that will show the feasibility of a fully automated MySQL backup and recovery tool. It's being called the MySQL Time Machine and (with all due respect to all vendors with products of similar names) it allows you to

      [Read more...]
    Data Backup and Recovery for MySQL - a MySQL Time Machine is Born.
    Employee +0 Vote Up -0Vote Down

    Sure, you've heard it before: [some company's logo] has a new MySQL backup tool that promises to solve all of your data recovery needs. The good news is most of these tools work pretty well. However, they tend to suffer from a similar set of limitations. Most require sophisticated infrastructures or complex setup and maintenance and can become a resource drain for some organizations. You're probably wondering, "Why can't someone build a fully automated MySQL backup solution that you can just turn on and forget?"

    I am happy to say that the MySQL Developers at Sun are doing just that. In fact, a prototype will be demonstrated at the 2009 MySQL Users' Conference that will show the feasibility of a fully automated MySQL backup and recovery tool. It's being called the MySQL Time Machine and (with all due respect to all vendors with products of similar names) it allows you to

      [Read more...]
    Data Backup and Recovery for MySQL - a MySQL Time Machine is Born.
    Employee +0 Vote Up -0Vote Down

    Sure, you've heard it before: [some company's logo] has a new MySQL backup tool that promises to solve all of your data recovery needs. The good news is most of these tools work pretty well. However, they tend to suffer from a similar set of limitations. Most require sophisticated infrastructures or complex setup and maintenance and can become a resource drain for some organizations. You're probably wondering, "Why can't someone build a fully automated MySQL backup solution that you can just turn on and forget?"

    I am happy to say that the MySQL Developers at Sun are doing just that. In fact, a prototype will be demonstrated at the 2009 MySQL Users' Conference that will show the feasibility of a fully automated MySQL backup and recovery tool. It's being called the MySQL Time Machine and (with all due respect to all vendors with products of similar names) it allows you to

      [Read more...]
    Talk,Talk, Talk: Innobase Speaks
    +0 Vote Up -0Vote Down

    That should read “Talks, Talks, Talks” … There will be several presentations by InnoDB experts at the upcoming 2009 MySQL Conference and Expo. Whether you’re a newbie or an experienced DBA deeply familiar with InnoDB, you won’t want to miss these important talks about InnoDB:

      [Read more...]
    People are Talkin’ … about InnoDB
    +0 Vote Up -0Vote Down

    InnoDB executes a lot of transactions for MySQL users. And users and technical experts in the InnoDB community exchange lots of information about InnoDB … transacting in ideas … all the time. A great opportunity to participate in some of these transactions is the upcoming MySQL Conference and Expo.

    Does your InnoDB-based application need more transactions to execute more quickly and reliably? Come hear these noted speakers present information that can help you get the most out of InnoDB:

      [Read more...]
    How to use MySQL binlogs to undo a DROP statement
    +0 Vote Up -0Vote Down
    This post is for people who are trying to roll back unwanted modifications to their MySQL database. You cannot use the binary logs to undo unwanted changes to your data. The binary logs are for redoing statements, not undoing them. If you have a backup, you may be able to restore the backup and [...]
    MythTV recover Lost+Found
    +0 Vote Up -0Vote Down

    My MythTV store lives on an LVM volume that is spread over 2 disks, one of them is an external USB disk. So the cleaninglady seems to have touched a cable and after coming back from holiday I had a read-only filesystem that afer a remount had about 350Gb in lost+found with irrelevant filenames.

  • total 337407844
  • drwx------ 2 tv tv 4096 Dec 17 22:47 .
  • drwxrwxrwx 15 tv tv 4096 Dec 17 22:44 ..
  • -rw-r--r-- 1 root root 423343556 Dec 14 07:10 I303109.RCN
  • -rw-r--r-- 1 root root 2990538924 Dec 13 19:05 I303107.RCN
  • -rw-r--r-- 1 root root 1023691768 Dec 13 08:10 I319494.RCN
  • -rw-r--r-- 1 root root 1023622348 Dec 13 07:45 I327684.RCN
  • -rw-r--r-- 1 root root 423735892 Dec 13 07:10 I327682.RCN
  • -rw-r--r-- 1 root root 466749476 Dec 12 15:43 I135169.RCN
  • -rw-r--r-- 1 root root
  •   [Read more...]
    Checking and repairing partitions in MySQL 5.1
    +0 Vote Up -0Vote Down
    Talking with users at OSCON 2008, the biggest fear with new features was table corruption with partitioning. Until recently, this fear was substantiated by bug#20129, which reported inability of repairing corrupted partitions. The initial "fix" for this bug was to disable the offending commands.
    After a while, especially when the Support department complained loudly, the bug was reopen and a proper fix implemented. Now you can maintain a partition using ALTER TABLE ... CHECK/ANALYZE/OPTIMIZE/REPAIR PARTITION.
    I did some experiment, using MySQL sandbox and the employees test database, which supports partitioning.
    You need to download the latest MySQL 5.1 release (5.1.28), where the patch is available.
    To load



      [Read more...]
    MySQL Conference Liveblogging: Disaster Is Inevitable - Are You Prepared? (Tuesday 4:25PM)
    +0 Vote Up -0Vote Down
    • Suicide
      • having no backups
      • depending on slaves for backup
      • keeping backups on same SAN
      • having a single DBA - Frank didn't like this one at all
      • not keeping binlogs
    • Restoring from backup
      • how much time?
      • uncompressed backup ready to mount?
      • separate network for recovery?
    • In Fotolog, 1TB of data was severely hit.
      • first problem: backup was highly compressed (tar.gz)
      • uncompressing took hours
      • so keep uncompressed backups (at least last N days)
      • it should be mountable, rather than transferable
    • Frank going over recovery modes at
      [Read more...]
    InnoDB Recovery toolset Version 0.3 Released
    +0 Vote Up -0Vote Down

    Even though I didn’t go to MySQL conf this year (really sad about this), this week is gonna be most active in the community so I decided to do some community stuff too Today I’ve released version 0.3 of our innodb recovery toolkit. Now it became much faster, stable and accurate. At this moment it is possible to recover almost any table from corrupted/deleted tablespace without so much effort as it was before. Here is a short changes list (since 0.1 announced here):

    • More MySQL data types added: DECIMAL (both old and new), DATE, TIME
    • CHAR data type handling improved in table definitions generator
    • Indexes filtering added to
      [Read more...]
    MySQL (5.0): Recovering failed circular replication
    +0 Vote Up -0Vote Down
    There is a lot of information out there about how to setup circular replication but nothing about how to recover it when all else fails. This article will cover a quick and easy method I use. Depending on the size of your database and the interconnects between servers this method may not be suitable due to the need to copy all replicated databases from one good server to all other servers in the replication circle which requires a certain amount of down time respectively.

    OK, so one server or more is showing Slave_IO_Running and/or Slave_SQL_Running as No and there is some error about a failed query when you run "show slave status;" and no amount of effort to fix it is working. First DO NOT PANIC. It is broken, OK, tell yourself that and realise that trying to fix something when you are in a panicked state is only

      [Read more...]
    Data Recovery Toolkit for InnoDB Released
    +0 Vote Up -0Vote Down

    I’m returned from my 1-week vacation today and want to say - I’ve never been so productive as I was there Blue ocean, hot sun and white sand really helped me to finish my work on the first release of one really awesome project.

    Today I’m proud to announce our first public release of the Data Recovery Toolkit for InnoDB - set of tools for checking InnoDB tablespaces and recovering data from damaged tablespaces or from dropped/truncated InnoDB tables.

    This release already has a pretty decent set of features:

    • Supports both REDUNDANT (pre mysql 5.0) and COMPACT (mysql 5.0+) versions of tablespaces
    • Works
      [Read more...]
    Metalink Note on Datafile Recovery Will Corrupt Database
    +0 Vote Up -0Vote Down
    Thinking I had something new, I wrote this article about recovering deleted files. However, it turns out Frits Hoogland had already blogged about recovery of deleted files on linux, as Frits pointed out in a comment on my blog, where he also mentioned a metalink note on this matter. The note ID is: 444749.1 “Retrieve deleted [...]
    How to Recover Deleted Oracle Datafiles with No Downtime
    +0 Vote Up -0Vote Down
    So you have accidentally removed a datafile from your production database? First thing, DON’T PANIC! There’s an easy way to recover deleted datafiles, for as long as your database remains up. The procedure below works on linux, however this method conceivably can work for other platforms. This procedure will even work if your database is [...]
    Previous 30 Newer Entries Showing entries 31 to 60 of 61 Next 1 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.