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:
- Stop your mysql daemon/service (#
services stop mysql
) - 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!!)
- 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) - 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.. - Shut down your “temporary” MySQL server hitting
CTRL-C
on the shell opened at (3) - Restart MySQL with the defaults settings (or, if you like the shell output run:
mysqld --standalone --console
) - 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? 😉 )
Right on!
Im getting this error, when trying your command. any help will be appreciated
InnoDB: No valid checkpoint found.
InnoDB: If this error appears when you are creating an InnoDB database,
InnoDB: the problem may be that during an earlier attempt you managed
InnoDB: to create the InnoDB data files, but log file creation failed.
InnoDB: If that is the case, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/error-creating-innodb.html
111017 12:18:56 [ERROR] Default storage engine (InnoDB) is not available
111017 12:18:56 [ERROR] Aborting
111017 12:18:56 [Note] MYSQLD-NT: Shutdown complete
On which command you get the error? at point (3)?
Try to run the command in point (3) with the “data” directory completely empty.
Suppose i accidentally deleted my database by using the DROP database command and did not have any backup.How can i use ibdata directory to recover the database?
you are a lifesaver… bruv..God Bless you
Works, even on a database that causes classical mysql daemon crash immediately. Thank you, good sir!
It helped me to save my whole day and crucial data for one of my user.
Real life saver