Professional Web Applications Themes

Recovering Database - MySQL

We have a mysql database that we had setup on a second partition, our first partition went haywire and had to reinstall the server os. We reinstalled the os and mysql but when we reattached the data folder that was already there on the second drive it will not recognize the database. It is setup as Innodb database and have the frm files and two log files and one datafile. Running mysqlcheck shuts downs the mysql service. Any help would be greatly appreciated....

Sponsored Links
  1. #1

    Default Recovering Database

    We have a mysql database that we had setup on a second partition, our first
    partition went haywire and had to reinstall the server os. We reinstalled
    the os and mysql but when we reattached the data folder that was already
    there on the second drive it will not recognize the database. It is setup
    as Innodb database and have the frm files and two log files and one
    datafile. Running mysqlcheck shuts downs the mysql service. Any help
    would be greatly appreciated.
    Sponsored Links
    Chico Guest

  2. #2

    Default Re: Recovering Database

    == Quote from Chico Che (com)'s article 

    copy the log files out of the data directory and try to start the server. don't
    delete them just copy them to a different location.


    --
    POST BY: lark with PHP News Reader
    lark Guest

  3. #3

    Default Re: Recovering Database

    lark <net> wrote in
    news:cxBbi.3231$news.prodigy.net:
     
    >
    > copy the log files out of the data directory and try to start the
    > server. don't delete them just copy them to a different location.
    >
    >
    > --
    > POST BY: lark with PHP News Reader
    >[/ref]

    I get the following on the err.log
    070612 21:30:17 InnoDB: Log file .\ib_logfile0 did not exist: new to be
    created
    InnoDB: Setting log file .\ib_logfile0 size to 10 MB
    InnoDB: Database physically writes the file full: wait...
    070612 21:30:18 InnoDB: Log file .\ib_logfile1 did not exist: new to be
    created
    InnoDB: Setting log file .\ib_logfile1 size to 10 MB
    InnoDB: Database physically writes the file full: wait...
    InnoDB: Cannot initialize created log files because
    InnoDB: data files are corrupt, or new data files were
    InnoDB: created when the database was started previous
    InnoDB: time but the database was not shut down
    InnoDB: normally after that.
    070612 21:30:19 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin
    \mysqld-nt: ready for connections.
    Version: '5.0.15-nt' socket: '' port: 3306 Official MySQL binary

    Is the data file unfixable?
    Chico Guest

  4. #4

    Default Re: Recovering Database

    Chico Che wrote: 
    >> copy the log files out of the data directory and try to start the
    >> server. don't delete them just copy them to a different location.
    >>
    >>
    >> --
    >> POST BY: lark with PHP News Reader
    >>[/ref]
    >
    > I get the following on the err.log
    > 070612 21:30:17 InnoDB: Log file .\ib_logfile0 did not exist: new to be
    > created
    > InnoDB: Setting log file .\ib_logfile0 size to 10 MB
    > InnoDB: Database physically writes the file full: wait...
    > 070612 21:30:18 InnoDB: Log file .\ib_logfile1 did not exist: new to be
    > created
    > InnoDB: Setting log file .\ib_logfile1 size to 10 MB
    > InnoDB: Database physically writes the file full: wait...
    > InnoDB: Cannot initialize created log files because
    > InnoDB: data files are corrupt, or new data files were
    > InnoDB: created when the database was started previous
    > InnoDB: time but the database was not shut down
    > InnoDB: normally after that.
    > 070612 21:30:19 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin
    > \mysqld-nt: ready for connections.
    > Version: '5.0.15-nt' socket: '' port: 3306 Official MySQL binary
    >
    > Is the data file unfixable?[/ref]

    What messages do you get when the correct log files are in the directory
    and you try to start it?

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: Recovering Database

    Chico Che wrote: 
    >> copy the log files out of the data directory and try to start the
    >> server. don't delete them just copy them to a different location.
    >>
    >>
    >> --
    >> POST BY: lark with PHP News Reader
    >>[/ref]
    >
    > I get the following on the err.log
    > 070612 21:30:17 InnoDB: Log file .\ib_logfile0 did not exist: new to be
    > created
    > InnoDB: Setting log file .\ib_logfile0 size to 10 MB
    > InnoDB: Database physically writes the file full: wait...
    > 070612 21:30:18 InnoDB: Log file .\ib_logfile1 did not exist: new to be
    > created
    > InnoDB: Setting log file .\ib_logfile1 size to 10 MB
    > InnoDB: Database physically writes the file full: wait...
    > InnoDB: Cannot initialize created log files because
    > InnoDB: data files are corrupt, or new data files were
    > InnoDB: created when the database was started previous
    > InnoDB: time but the database was not shut down
    > InnoDB: normally after that.
    > 070612 21:30:19 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin
    > \mysqld-nt: ready for connections.
    > Version: '5.0.15-nt' socket: '' port: 3306 Official MySQL binary
    >
    > Is the data file unfixable?[/ref]


    i can't answer that question right now! however, there is one more thing
    you can do. innodb has an auto-recovery option that must be turned on in
    the configuration file for it to recover and repair the files. so
    include this in your "my.ini" file:
    --innodb-force_recovery = 4
    start the server after this without the log files in the data directory.
    if the server comes and you can access the table, take a backup of your
    table and make sure you take frequent backups from now on.

    if the server does not start and spits out error messages, try starting
    it with the log files in the data directory and the
    --innodb_force_recovery =4 in the "my.ini" file.

    if none of these works, i don't know what else you can do except try to
    recover them from previous backups (if you have any).

    good luck.
    lark Guest

  6. #6

    Default Re: Recovering Database

    lark <net> wrote in news:qATbi.3690$bP5.1885
    newssvr19.news.prodigy.net:
     

    received a bunch of errors

    070614 9:14:03 InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files...
    InnoDB: Restoring possible half-written data pages from the doublewrite
    InnoDB: buffer...
    070614 9:14:03 InnoDB: Starting log scan based on checkpoint at
    InnoDB: log sequence number 0 10387270.
    InnoDB: Doing recovery: scanned up to log sequence number 0 10418688
    070614 9:14:03 InnoDB: Error: page 7 log sequence number 0 13341729
    InnoDB: is in the future! Current system log sequence number 0 10418671.
    InnoDB: Your database may be corrupt or you may have copied the InnoDB
    InnoDB: tablespace but not the InnoDB log files. See
    http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    InnoDB: Database page corruption on disk or a failed
    InnoDB: file read of page 11.
    InnoDB: You may have to recover from a backup.
    070614 9:14:03 InnoDB: Page dump in ascii and hex (16384 bytes):


    and then a bunch ascii data followed by:

    InnoDB: stored checksum 1575180971, prior-to-4.0.14-form stored checksum
    1527615986
    InnoDB: Page lsn 0 13347710, low 4 bytes of lsn at page end 9222184
    InnoDB: Page number (if stored to page already) 11,
    InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
    InnoDB: Page may be an index page where index id is 0 3
    InnoDB: (index CLUST_IND of table SYS_INDEXES)
    InnoDB: Database page corruption on disk or a failed
    InnoDB: file read of page 11.
    InnoDB: You may have to recover from a backup.
    InnoDB: It is also possible that your operating
    InnoDB: system has corrupted its own file cache
    InnoDB: and rebooting your computer removes the
    InnoDB: error.
    InnoDB: If the corrupt page is an index page
    InnoDB: you can also try to fix the corruption
    InnoDB: by dumping, dropping, and reimporting
    InnoDB: the corrupt table. You can use CHECK
    InnoDB: TABLE to scan your table for corruption.
    InnoDB: See also http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
    InnoDB: about forcing recovery.
    070614 9:14:03 InnoDB: Error: page 5 log sequence number 0 14450097
    InnoDB: is in the future! Current system log sequence number 0 10418671.
    InnoDB: Your database may be corrupt or you may have copied the InnoDB
    InnoDB: tablespace but not the InnoDB log files. See
    http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    070614 9:14:03 InnoDB: Error: page 6 log sequence number 0 14453295
    InnoDB: is in the future! Current system log sequence number 0 10418671.
    InnoDB: Your database may be corrupt or you may have copied the InnoDB
    InnoDB: tablespace but not the InnoDB log files. See
    http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    070614 9:14:03 InnoDB: Error: page 45 log sequence number 0 14453076
    InnoDB: is in the future! Current system log sequence number 0 10418671.
    InnoDB: Your database may be corrupt or you may have copied the InnoDB
    InnoDB: tablespace but not the InnoDB log files. See
    http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    070614 9:14:03 InnoDB: Error: page 551 log sequence number 0 14453295
    InnoDB: is in the future! Current system log sequence number 0 10418671.
    InnoDB: Your database may be corrupt or you may have copied the InnoDB
    InnoDB: tablespace but not the InnoDB log files. See
    http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    070614 9:14:03 InnoDB: Starting an apply batch of log records to the
    database...
    InnoDB: Progress in percents: 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 070614 9:14:03
    InnoDB: Error: page 8 log sequence number 0 13347750
    InnoDB: is in the future! Current system log sequence number 0 10418671.
    InnoDB: Your database may be corrupt or you may have copied the InnoDB
    InnoDB: tablespace but not the InnoDB log files. See
    http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    070614 9:14:03 InnoDB: Error: page 9 log sequence number 0 13347745
    InnoDB: is in the future! Current system log sequence number 0 10418671.
    InnoDB: Your database may be corrupt or you may have copied the InnoDB
    InnoDB: tablespace but not the InnoDB log files. See
    http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    070614 9:14:03 InnoDB: Error: page 10 log sequence number 0 13347740
    InnoDB: is in the future! Current system log sequence number 0 10418671.
    InnoDB: Your database may be corrupt or you may have copied the InnoDB
    InnoDB: tablespace but not the InnoDB log files. See
    http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    070614 9:14:03 InnoDB: Error: page 55 log sequence number 0 14453320
    InnoDB: is in the future! Current system log sequence number 0 10418671.
    InnoDB: Your database may be corrupt or you may have copied the InnoDB
    InnoDB: tablespace but not the InnoDB log files. See
    http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    070614 9:14:03 InnoDB: Error: page 58 log sequence number 0 14452095
    InnoDB: is in the future! Current system log sequence number 0 10418671.
    InnoDB: Your database may be corrupt or you may have copied the InnoDB
    InnoDB: tablespace but not the InnoDB log files. See
    http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    InnoDB: Database page corruption on disk or a failed
    InnoDB: file read of page 59.
    InnoDB: You may have to recover from a backup.
    070614 9:14:03 InnoDB: Page dump in ascii and hex (16384 bytes):

    and so on....

    Chico Guest

  7. #7

    Default Re: Recovering Database

    == Quote from Chico Che (com)'s article 
    > received a bunch of errors
    > 070614 9:14:03 InnoDB: Database was not shut down normally!
    > InnoDB: Starting crash recovery.
    > InnoDB: Reading tablespace information from the .ibd files...
    > InnoDB: Restoring possible half-written data pages from the doublewrite
    > InnoDB: buffer...
    > 070614 9:14:03 InnoDB: Starting log scan based on checkpoint at
    > InnoDB: log sequence number 0 10387270.
    > InnoDB: Doing recovery: scanned up to log sequence number 0 10418688
    > 070614 9:14:03 InnoDB: Error: page 7 log sequence number 0 13341729
    > InnoDB: is in the future! Current system log sequence number 0 10418671.
    > InnoDB: Your database may be corrupt or you may have copied the InnoDB
    > InnoDB: tablespace but not the InnoDB log files. See
    > http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    > InnoDB: Database page corruption on disk or a failed
    > InnoDB: file read of page 11.
    > InnoDB: You may have to recover from a backup.
    > 070614 9:14:03 InnoDB: Page dump in ascii and hex (16384 bytes):
    > and then a bunch ascii data followed by:
    > InnoDB: stored checksum 1575180971, prior-to-4.0.14-form stored checksum
    > 1527615986
    > InnoDB: Page lsn 0 13347710, low 4 bytes of lsn at page end 9222184
    > InnoDB: Page number (if stored to page already) 11,
    > InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
    > InnoDB: Page may be an index page where index id is 0 3
    > InnoDB: (index CLUST_IND of table SYS_INDEXES)
    > InnoDB: Database page corruption on disk or a failed
    > InnoDB: file read of page 11.
    > InnoDB: You may have to recover from a backup.
    > InnoDB: It is also possible that your operating
    > InnoDB: system has corrupted its own file cache
    > InnoDB: and rebooting your computer removes the
    > InnoDB: error.
    > InnoDB: If the corrupt page is an index page
    > InnoDB: you can also try to fix the corruption
    > InnoDB: by dumping, dropping, and reimporting
    > InnoDB: the corrupt table. You can use CHECK
    > InnoDB: TABLE to scan your table for corruption.
    > InnoDB: See also http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
    > InnoDB: about forcing recovery.
    > 070614 9:14:03 InnoDB: Error: page 5 log sequence number 0 14450097
    > InnoDB: is in the future! Current system log sequence number 0 10418671.
    > InnoDB: Your database may be corrupt or you may have copied the InnoDB
    > InnoDB: tablespace but not the InnoDB log files. See
    > http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    > 070614 9:14:03 InnoDB: Error: page 6 log sequence number 0 14453295
    > InnoDB: is in the future! Current system log sequence number 0 10418671.
    > InnoDB: Your database may be corrupt or you may have copied the InnoDB
    > InnoDB: tablespace but not the InnoDB log files. See
    > http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    > 070614 9:14:03 InnoDB: Error: page 45 log sequence number 0 14453076
    > InnoDB: is in the future! Current system log sequence number 0 10418671.
    > InnoDB: Your database may be corrupt or you may have copied the InnoDB
    > InnoDB: tablespace but not the InnoDB log files. See
    > http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    > 070614 9:14:03 InnoDB: Error: page 551 log sequence number 0 14453295
    > InnoDB: is in the future! Current system log sequence number 0 10418671.
    > InnoDB: Your database may be corrupt or you may have copied the InnoDB
    > InnoDB: tablespace but not the InnoDB log files. See
    > http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    > 070614 9:14:03 InnoDB: Starting an apply batch of log records to the
    > database...
    > InnoDB: Progress in percents: 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 070614 9:14:03
    > InnoDB: Error: page 8 log sequence number 0 13347750
    > InnoDB: is in the future! Current system log sequence number 0 10418671.
    > InnoDB: Your database may be corrupt or you may have copied the InnoDB
    > InnoDB: tablespace but not the InnoDB log files. See
    > http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    > 070614 9:14:03 InnoDB: Error: page 9 log sequence number 0 13347745
    > InnoDB: is in the future! Current system log sequence number 0 10418671.
    > InnoDB: Your database may be corrupt or you may have copied the InnoDB
    > InnoDB: tablespace but not the InnoDB log files. See
    > http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    > 070614 9:14:03 InnoDB: Error: page 10 log sequence number 0 13347740
    > InnoDB: is in the future! Current system log sequence number 0 10418671.
    > InnoDB: Your database may be corrupt or you may have copied the InnoDB
    > InnoDB: tablespace but not the InnoDB log files. See
    > http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    > 070614 9:14:03 InnoDB: Error: page 55 log sequence number 0 14453320
    > InnoDB: is in the future! Current system log sequence number 0 10418671.
    > InnoDB: Your database may be corrupt or you may have copied the InnoDB
    > InnoDB: tablespace but not the InnoDB log files. See
    > http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    > 070614 9:14:03 InnoDB: Error: page 58 log sequence number 0 14452095
    > InnoDB: is in the future! Current system log sequence number 0 10418671.
    > InnoDB: Your database may be corrupt or you may have copied the InnoDB
    > InnoDB: tablespace but not the InnoDB log files. See
    > http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
    > InnoDB: Database page corruption on disk or a failed
    > InnoDB: file read of page 59.
    > InnoDB: You may have to recover from a backup.
    > 070614 9:14:03 InnoDB: Page dump in ascii and hex (16384 bytes):
    > and so on....[/ref]

    what this with or without the log files in the data directory?
    --
    POST BY: lark with PHP News Reader
    lark Guest

  8. #8

    Default Re: Recovering Database


    with log files in there
    Chico Guest

  9. #9

    Default Re: Recovering Database

    == Quote from Chico Che (com)'s article 

    did you try it without the log files in the data directory?
    --
    POST BY: lark with PHP News Reader
    lark Guest

  10. #10

    Default Re: Recovering Database

    lark <net> wrote in news:rwfci.25503$YL5.7283
    newssvr29.news.prodigy.net:
     
    >
    > did you try it without the log files in the data directory?
    > --
    > POST BY: lark with PHP News Reader
    >[/ref]

    I get the same error I posted before
    Chico Guest

  11. #11

    Default Re: Recovering Database

    == Quote from Chico Che (com)'s article 
    > >
    > > did you try it without the log files in the data directory?
    > > --
    > > POST BY: lark with PHP News Reader
    > >[/ref]
    > I get the same error I posted before[/ref]

    sounds like your files are damaged beyond repair. one more thing you can do is set
    the auto_recover to 6 instead of 4.

    if this doesn't work, i don't think there's anything that can be done to correct it.
    --
    POST BY: lark with PHP News Reader
    lark Guest

  12. #12

    Default Re: Recovering Database

    >

    It starts the database but when you try doing anything with the actual
    database that we need it shuts down mysql service
    Chico Guest

  13. #13

    Default Re: Recovering Database

    Chico Che <com> wrote in news:Xns994F9B8791395Chicoche
    216.196.97.131:
     
    >
    > It starts the database but when you try doing anything with the actual
    > database that we need it shuts down mysql service
    >[/ref]

    including trying a dump?

    Good Guest

  14. #14

    Default Re: Recovering Database

    == Quote from Chico Che (com)'s article 
    > It starts the database but when you try doing anything with the actual
    > database that we need it shuts down mysql service[/ref]

    when it shuts down the server, what error messages do you get in
    1-mysql error logs
    2-windows event logs
    --
    POST BY: lark with PHP News Reader
    lark Guest

  15. #15

    Default Re: Recovering Database



    We can actually retrieve 24 of 27 tables with all index data but when do
    anything with the other 3 tables it shuts down mysqlnt.exe
    Chico Guest

Similar Threads

  1. Need help recovering
    By Michael Garriss in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 19th, 08:47 PM
  2. Recovering a .fla file
    By weilerm1983 in forum Macromedia Flash Sitedesign
    Replies: 5
    Last Post: July 30th, 03:53 PM
  3. Recovering from a time out
    By Gerard Samuel in forum PHP Development
    Replies: 0
    Last Post: July 18th, 06:23 AM
  4. ARG: Help recovering from hotbackup
    By Chucky in forum Oracle Server
    Replies: 4
    Last Post: December 21st, 07:02 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