Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 31 to 40 of 61 10 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...]
    10 Newer Entries Showing entries 31 to 40 of 61 10 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.