Recover mysql database - mysql/mysqldump gives "table . doesn't exist (1146)"

Question

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?

Answer 1

Found someone asking a similar question: MySQL > Table doesn't exist. But it does (or it should).

Mike Dacre had the answer that solved my problem. The problem was that the ib_logfile0 and ib_logfile1 (and maybe some of the other ib* files in the mysql/ root directory) were inconsistent with my new installation of mysql. You can't just drop in db files from the old mysql/ directory and expect it to work.

What I did to recover the database was to backup my current /var/lib/mysql/ on the fresh installation:

$ sudo service mysql stop # Stop mysql. Command could be different on different distros
$ sudo mv /var/lib/mysql ~/mysql.bku

Then copy the emergency backup directory to /var/lib

$ sudo cp -R /media/NAS/Backup/mysql /var/lib/

Then set the permissions appropriately (refer to ~/mysql.bku/ for reference if needed). There may be more efficient commands for this but I'm including what I know for completeness in case someone with less experience may need it.

$ sudo chown -R mysql:mysql /var/lib/mysql
$ sudo find /var/lib/mysql/ -type d -exec chmod 700 {} \;
$ sudo find /var/lib/mysql/ -type f -exec chmod 660 {} \;
$ sudo chmod 644 /var/lib/mysql/debian-5.1.flag # Not sure what this is but the permissions were a bit different so include it just in case

And start mysql again

$ sudo service mysql start # Again command might be different on different distros

Then I backed up the databases I needed:

$ mysqldump -u root -p mediawiki-1_19_1 -c | gzip -9 > wiki.2012-11-15.sql.gz

When I was finished I put the mysql/ directory back and then imported the databases from the dump files.

$ sudo service mysql stop
$ sudo mv /var/lib/mysql ~/mysql-discard # Too nervous to start typing "sudo rm -r /" for /var/lib/mysql, so move it away instead
$ sudo mv ~/mysql.bku /var/lib/mysql
$ sudo service mysql start

Problem solved, I have a proper export of the database now and mysql is running properly too. All that's left is following the Restoring a wiki from backup guide.

Answer 2

You can try:

1. Making sure the data files for the table actually exist in /var/db/mysql, or wherever your particular install/distro puts them.
2. Peruse the mysql error log, usually in the above directory.
3. Use mysqlcheck --repair.
4. Take mySQL offline and run a myisamchk.
5. Hope you either have a good backup somewhere else, or that the archive table wasn't super important.