[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1047
  • Last Modified:

MySQL database restored from backup, "Table doesn't exist"

Hi there,

We had a server crash that contained an in-house Wiki server. The OS is Windows 2003 Server with SP2.

I had installed MySQL (5.0) to be used by Wiki as it's database.

The back up we had for the server was only file based, meaning that I did not have a MySQL run database backup, only the windows file level data basked up.

I retored the backup to the new server and repected the original folder structure. Now, when I try to look at the MySQL database that Wiki used (wikidb) using "MySQL Control Centre", it is able to see that there is a datbase called Wikidb and that there are table in it, but when I try to look at any of the table I get a "table doesn't exist error.
I suspect that my database naiveté led me to believe that I could just copy the files over and the database would work,,,,,,,so what am I doing wrong?

Thanks for any help,

KB
0
kbartlett
Asked:
kbartlett
  • 9
  • 8
1 Solution
 
RurneCommented:
What is the result of running `show table status where name = 'myWikiTable'` (using corrupted table's name in place of 'myWikiTable')?
0
 
kbartlettAuthor Commented:
I'm using MySQL Control Centre to run the query. When I entered "show table status where name = 'wikidb.user', I was shown all the columns in the table. There were no fields below the column headings, just the top row with column titles.
0
 
RurneCommented:
Interesting.  Can you confirm that the user.frm file exists in the wikidb folder which you copied over?

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
kbartlettAuthor Commented:
Yes, it's there.
0
 
RurneCommented:
Ah.  It is indeed an InnoDb table and not a MyISAM.

In addition to the bare data, you will also need the tablespace and transaction log for your old database.  You will also need to `DETACH TABLESPACE` from your previous system, and then `ALTER TABLE wikidb.users IMPORT TABLESPACE`.  Simply copy/pasting the .ibd files will not work; additionally, there are a few concerns with working at a per-table tablespace setup:

http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html
http://bugs.mysql.com/bug.php?id=25491


IF your previous backup occurred without anything in-process in the transaction log, you should be able to rebuild your InnoDb tables by reimporting the tablespace.
0
 
kbartlettAuthor Commented:
Thanks, but both of the links refer to .ibd files that I cannot find. I searched the entire server with no luck.
0
 
RurneCommented:
Okay, so you're set up for domain-wide InnoDb data (meaning all tablespace info for all your InnoDb tables would be saved in ibdata1).  Additionally, you will need all your ib_logfile* files to rebuild the transaction log.  Barring that, you will have to restore from backup.
0
 
kbartlettAuthor Commented:
Thanks.

So I found the ibdata1 file and 2 versions of the ib_logfile* file: ib_logfile) and ib_logfile1. :-0

What do I do now?  Please remember I am but a humble MCSE network administrator and not a DBA so please go easy on the jargon. :-)

Thanks very much,

KB
0
 
RurneCommented:
No worries.

A simple way of forcing a rebuild, if all the mentioned files are in the right place, would be to run `ALTER TABLE wikidb.user ENGINE=InnoDb`.  Of course, make sure you have a backup of the data files before trying this, as this is not guaranteed to rebuild the namespace.

When you restart the server, you may see a file ending in .err in the data directory.  If you see this, please post it to ee-stuff.com and provide a link so we can further troubleshoot exactly what is failing on the import.
0
 
kbartlettAuthor Commented:
This looks promising.

I'm guessing that I'll need to do this for all the tables in the DB?
0
 
RurneCommented:
No, just for any table that is kept in .frm.  Anything saved in a .MYI file is a MyISAM table, which does not keep the transactional logs that we're trying to relink and rebuild.  For now, let's just try the user table to see how this particular rebuild works.
0
 
kbartlettAuthor Commented:
So,,, I tried the "alter table" route  and got  "table wikidb.user does not exist"
0
 
RurneCommented:
Hmmmm.  Do you a copy of the .err file that I mentioned before?
0
 
kbartlettAuthor Commented:
Here is my .err file

https://filedb.experts-exchange.com/incoming/ee-stuff/4157-zeta.zip

Please let me know if you see anything that I can do.

Thanks,
KB
0
 
RurneCommented:
It's looking like your ibdata file does not have the necessary data, or is out of synch, since all of your InnoDb tables are throwing a "Cannot find wikidb.x from the internal data dictionary" error.

Check that the [InnoDb] section in my.cnf does not have a "chroot=" specified, or if it does, that it points to your actual data directory.  Based on:

http://www.mysql.org/doc/refman/4.1/en/innodb-troubleshooting-datadict.html

It would seem that there is no data in ibdata for your actual tables, and that the .frm files are useless.  You may need to take a look into attempting to extract, from the ibdata files, the actual SQL to rebuild the files:

http://www.innodb.com/support/documentation/innodb-hot-backup-manual#options


You'd need to take whatever backup you have of the crashed server and create 2 .cnf files:

my-backup.cnf
the directories point to the folder which contains your backup files.

my-new-sync.cnf
the directories point to the new database with which you're trying to synch.


With the `ibbackup` utility, you can then:
ibbackup my-backup.cnf my-new-sync.cnf


And, if recoverable, should be able to pull the data from the old files into the new database.
0
 
kbartlettAuthor Commented:
Sorry, you've lost me. where would I find the my.cnf file?
0
 
RurneCommented:
From http://dev.mysql.com/doc/refman/5.0/en/option-files.html:

WINDIR\my.ini       
C:\my.cnf       
INSTALLDIR\my.ini       



Those should be your actual defaults.  What you actually need to do is create two new my.cnf files to feed to the ibbackup utility.  One of them (my-backup.cnf from my example) will point to your old ibdata files that have the data you want, and the other (my-new-sync.cnf) will point to another folder where you want to try to rebuild those files.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now