Mysql – Offline thesql dump

For InnoDB tables, --single-transaction will ensure that the dump of each table is consistent, but it will not necessarily be consistent with the other tables, since it locks and dumps them one at a time rather than locking them all for the duration of the dump. And you will, of course, have delayed writes waiting for the lock to release if you try to write to a table while it's being dumped.

A better bet is to use an LVM or filesystem that allows you to take snapshots, then lock the entire database for just long enough to take a snapshot, which you can then back up at your leisure.

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.