Mysql file backup

Question

I moved my sites from one windows 2003 to windows 2008 web server. I have some php forum, I copy the data directory of mysql from old server to new one. there are some databases that when I check by PHPmyadmin I see hast 230 tables but when I try to query on some tables, I face error message : TABLE NAMe dose not exist. I check the data folder and frm files exist there. in log file I see this error message:

Log Name:      Application
Source:        MySQL
Date:          07/11/2010 01:34:47 ب.ظ
Event ID:      100
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      00000
Description:
Cannot find or open table 00000/datastore from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.


For more information, see Help and Support Center at http://www.mysql.com.  
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="MySQL" />
    <EventID Qualifiers="49152">100</EventID>
    <Level>2</Level>
    <Task>0</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2010-07-11T09:04:47.000000000Z" />
    <EventRecordID>48742</EventRecordID>
    <Channel>Application</Channel>
    <Computer>vmi386</Computer>
    <Security />
  </System>
  <EventData>
    <Data>Cannot find or open table npshopforum/datastore from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.

is there a way that I can recover these database

Is the Windows 2003 server still up and running ? Do you still have access to the database in that server ? — proy, Jul 11 '10 at 10:38

score 0 · Accepted Answer · answered Jul 11 '10 at 11:42

0

This question has already been asked too often but here is the summary:

Dump and load. i.e. Dump the database(s) from the original to file and then load them into the new machine.

MySQL migration using file copies can work but more often than not will cause problems. This is especially the case if you use Innodb tables. Of course you already know that now.

answered Jul 11 '10 at 11:42

John Gardeniers

27,458
12
55
109

thanks for your reply.I find the problem, I copied the innodb file (ibdata1) to where I set in innodb_data_home_dir. and now every thins is OK. I copied data files as wrote in mysql web site > database backup. the problem was the innodb_data_home_dir value was diffrent to where I copied the innodb data file and after i correct it,it worked – Ashian Jul 11 '10 at 12:39

Mysql file backup

1 Answers1