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.

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.
–
gertvdijkNov 15 '12 at 22:06

@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.
–
MatthewNov 15 '12 at 22:17

2 Answers
2

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.

@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?
–
MatthewNov 22 '12 at 22:00

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.
–
MatthewNov 15 '12 at 22:19

OK, so then do this for everything then. Have you tried mysqlcheck yet?
–
SammitchNov 15 '12 at 22:21

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.
–
MatthewNov 15 '12 at 23:22