Recover mysql database - mysqldump gives "table <tablename> doesn't exist (1146)"
- by Matthew
Backstory
Ubuntu died (wouldn't boot) and I couldn't fix it. I booted a live cd to recover the important stuff and saved it to my NAS. One of the things I backed up was /var/lib/mysql.
Reinstalled with Linux Mint because I was on Ubuntu 10.0.4 this was a good opportunity to try a new distro (and I don't like Unity). Now I want to recover my old mediawiki, so I shut down mysql daemon, cp -R /media/NAS/Backup/mysql/mediawiki@002d1_19_1 /var/lib/mysql/, set file ownership and permissions correctly, and start mysql back up.
Problem
Now I'm trying to export the database so I can restore the database, but when I execute the mysqldump I get an error:
$ mysqldump -u mediawikiuser -p mediawiki-1_19_1 -c | gzip -9 > wiki.2012-11-15.sql.gz
Enter password:
mysqldump: Got error: 1146: Table 'mediawiki-1_19_1.archive' doesn't exist when using LOCK TABLES
Things I've tried
I tried using --skip-lock-tables but I get this:
Error: Couldn't read status information for table archive ()
mysqldump: Couldn't execute 'show create table `archive`': Table 'mediawiki-1_19_1.archive' doesn't exist (1146)
I tried logging in to mysql and I can list the tables that should be there, but trying to describe or select from them errors out the same way as the dump:
mysql> show tables;
+----------------------------+
| Tables_in_mediawiki-1_19_1 |
+----------------------------+
| archive |
| category |
| categorylinks |
...
| user_properties |
| valid_tag |
| watchlist |
+----------------------------+
49 rows in set (0.00 sec)
mysql> describe archive;
ERROR 1146 (42S02): Table 'mediawiki-1_19_1.archive' doesn't exist
I believe mediawiki was installed using innodb and binary data.
Am I screwed or is there a way to recover this?
