I recently had my server out of order and I could only access to files (thanks to providential Linux-on-usb). I manage to backup my MySQL files (ibdata, ib_logfile1, ib_logfile2, and the tables *.frm files). No sql dump to be imported into a new MySQL installation.

I remembered a good tutorial on recovering database structure and data using my backups, but I couldn’t find anymore on the web.. I went step-by-step to recover my data: simply replacing the “data” directory inside the new installation setup will give you errors about InnoDB “sequence numbers” (and MySQL will suggest you to refer to “InnoDB force recovery feature“).

Going further these are the steps that I followed to get my data back:

1. Stop your mysql daemon/service (# services stop mysql)
2. Replace the new “data” directory with the backed-up one (don’t forget to work on a copy of your backup, and not with your one and only backup!!)
3. Start your mysqld with the innodb revocery parameters:
   # mysqld --standalone --console --innodb-force-recovery=4
   the console will stay open and you’ll see the log messages directly on your screen (CTRL-C will close the server)
4. Use your favorite db backup tool to create a new SQL dump of your database (myPhpAdmin, …) remember that starting your MySQL server with –innodb-force-revovery param you cann’t run any INSERT, DELETE or UPDATE sql command.
   You’ll wont backup the “mysql” and “performance_schema” tables..
5. Shut down your “temporary” MySQL server hitting CTRL-C on the shell opened at (3)
6. Restart MySQL with the defaults settings (or, if you like the shell output run: mysqld --standalone --console)
7. Remove all the databases and re-create them using your newly created SQL Dump

With this steps all the InnoDB log and data will be clean and the “sequence number errors” will be removed from your logs.

That’s it!

PS: of course you’ll need to have the right permissions to do what I’ve described before, so prepend your linux commands with “sudo” or run your Cygwin console with the ‘Administrator’ privileges (because you’re not using Microsoft CMD prompt… right? 😉 )