Professional Web Applications Themes

Retrieve INNODB data after moving folders - MySQL

Hi all, I've run into some serious trouble. Today my win32 mysql 5 server broke down for still unknown reasons. After trying to get it back to life for a few hours, I decided to copy the data folder to another machine to retrieve the data. Consequently I removed the software installation from the first box so I could reinstall it. Unfortunately I didn't realize that INNODB tables can't be transferred that easily. The few MyISAM tables that were in the datbases are viewable/usable on the second machine, but the INNODB's aren't. The database structure is there, I can see ...

  1. #1

    Default Retrieve INNODB data after moving folders

    Hi all,

    I've run into some serious trouble. Today my win32 mysql 5 server broke
    down for still unknown reasons. After trying to get it back to life for
    a few hours, I decided to copy the data folder to another machine to
    retrieve the data. Consequently I removed the software installation from
    the first box so I could reinstall it.
    Unfortunately I didn't realize that INNODB tables can't be transferred
    that easily. The few MyISAM tables that were in the datbases are
    viewable/usable on the second machine, but the INNODB's aren't. The
    database structure is there, I can see the tables, but they are non
    existent.

    Please tell me there is a way to restore this data from the .frm, .myi,
    ..myd and original mysql database....

    Thanks!

    Tim
    Tim Guest

  2. #2

    Default Re: Retrieve INNODB data after moving folders

    == Quote from Tim van den Hoff (nl)'s article 

    there may be a way! here's what i recommend:

    1-restart the server with the --innodb_force_recovery option set to a value in the
    range from 1 to 6. these values indicate increasing levels of caution in avoiding
    a crash, and increasing levels of tolerance for possible inconsistency in the
    recovered tables. a good value to start with is 4.


    2-When you start the server with --innodb_force_recovery set to a non-zero value,
    InnoDB treats the tablespace as read-only. Consequently, you should dump the
    InnoDB tables with mysqldump and then drop them while the option is in effect.
    Then restart the server without the --innodb_force_recovery option. When the
    server comes up, recover the InnoDB tables from the dump files.


    3-If the preceding steps fail, it's necessary to restore the InnoDB tables from a
    previous backup.
    --
    POST BY: lark with PHP News Reader
    lark Guest

  3. #3

    Default Re: Retrieve INNODB data after moving folders

    Tim van den Hoff <nl> wrote: 

    You should have copied the my.ini file too.
     

    What exact error message do you get? By default InnoDB puts all data in
    files ibdata1, ib_logfile0 and ib_logfile1 in the datadir. However this
    can be configured (in my.ini - therefore you must copy it).

    But there is still a chance your data is somewhere on your disk.
     

    Check for the above files. Are there any files in the copied datadir
    that are not called *.frm, *.MYD or *.MYI? Please check the MySQL
    error log for messages.


    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  4. #4

    Default Re: Retrieve INNODB data after moving folders

    Hi guys,

    Thanks for the responses.

    I've managed to restore all data, luckily. I've installed a new instance
    of MySQL 5, stopped the server, copied all of the old data folder
    (excluding the .ini file but including the standard INNODB files
    containing the data) and ran the server using --innodb_force_recovery,
    dumped the data and imported later after freshly installing the
    software. I had no access to this newsgroup, so I was unable to follow
    your advices. I might have done some unnecessary things, although my
    solution does seem similar to what you described.

    I also have found out what the 'cause' of the problem was: after a
    windows update on the win2k server, the system account which is used for
    the mysql service by default gave some trouble when loading the database
    service. It took forever to load, used lots of resources. After
    reassigning it to a specially created account with read/write rights on
    the mysql folder, the service started smoothly and accepted connections
    straight away.

    Thanks for the help!

    Tim

    Axel Schwenke schreef: 
    >
    > You should have copied the my.ini file too.

    >
    > What exact error message do you get? By default InnoDB puts all data in
    > files ibdata1, ib_logfile0 and ib_logfile1 in the datadir. However this
    > can be configured (in my.ini - therefore you must copy it).
    >
    > But there is still a chance your data is somewhere on your disk.

    >
    > Check for the above files. Are there any files in the copied datadir
    > that are not called *.frm, *.MYD or *.MYI? Please check the MySQL
    > error log for messages.
    >
    >
    > XL
    > --
    > Axel Schwenke, Support Engineer, MySQL AB
    >
    > Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    > MySQL User Forums: http://forums.mysql.com/[/ref]
    Tim Guest

  5. #5

    Default Re: Retrieve INNODB data after moving folders

    Tim van den Hoff <nl> wrote in
    news:469f62a0$0$13815$wanadoo.nl:

     


    Thanks for posting your solution for future reference!
    Good Guest

  6. #6

    Default Re: Retrieve INNODB data after moving folders

    Tim van den Hoff <nl> wrote:
     

    you're welcome
     

    The dumping and reloading step was not neccessary. You need to set the
    innodb_force_recovery option only to recover from a (partially) damaged
    tablespace. If you copy *all* files, there is no problem to copy the
    data directory between different computers. But you have to set the
    same datafile and logfile sizes in my.cnf. If you're using the
    defaults, this is of course a no-brainer.


    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

Similar Threads

  1. Cannot retrieve data in utf-8 from php
    By Miki in forum PHP Development
    Replies: 0
    Last Post: October 21st, 08:15 AM
  2. retrieve data
    By Jochen Daum in forum PHP Development
    Replies: 2
    Last Post: August 14th, 09:52 AM
  3. Retrieve Data
    By meipv in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 29th, 07:44 PM
  4. Moving folders, image links not updating
    By atomseed webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 10th, 11:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139