Recover mysql database - mysql/mysqldump gives "table <database>.<tablename> 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?

Solution

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.

Comments

[gertvdijk]

Please check the MySQL logging for errors. If InnoDB fails to start, MySQL just loads only MyISAM tables by default and only knows of the table declaration (.frm files), not the data inside. This could cause your problems. If InnoDB crash recovery fails you're out of luck. Plain copies of /var/lib/mysql of a running database is not a backup, as you can't restore it. I suggest to use a proper backup solution like mysqldump or the Percona Xtrabackup solution.

[Matthew]

@gertvdijk: It was not a running copy, as I stated this was an emergency recovery from a dead ubuntu using a live cd (boot from install disc). Also stated in my question, mysqldump is exactly what I'm trying to do. I'm well aware that a copy of the mysql/ directory is not a proper backup.

[Matthew]

It's not just the archive table. All of the tables fail the same way. It just stops trying after it encounters the first error. If I --force it errors out on every table.

[Sammitch]

OK, so then do this for everything then. Have you tried mysqlcheck yet?

[Matthew]

I did try mysqlcheck --repair at one point near the beginning and it gave errors but I can't remember what they were. I kept looking for a solution after checking here and got something though, posting an answer shortly.

[Yes Barry]

Shouldn't it be service mysqld start.

[Matthew]

Nope, for me on Linux Mint it's just service mysql start (as root of course).

[Matthew]

@mmmshuddup: I was just on a Centos machine and that linux distro uses mysqld, so it will be mysqld for people using Centos and probably other distributions derived from Red Hat. Maybe distributions based on Debian use just mysql without the 'd' for the commands?