This tutorial can be used to recover data from broken mysql, for example if you get error
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
or any other
InnoDB: Doing recovery: scanned up to log sequence number 2939851178008
150629 19:19:22 InnoDB: Error: page 7 log sequence number 2951677991096
InnoDB: is in the future! Current system log sequence number 2939851178008.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
first of all I recommending and it’s very important to create copy of mysql directory, for example if it’s in /var/lib/mysql/ copy mysql to mysql2 to have backup, now open my.cnf
and in [mysqld] add
innodb_force_recovery = 1
innodb_flush_method = O_DIRECT
now try to start mysql if no success change
innodb_force_recovery = 4
after this we will need to create dump of mysql db which we need
mysqldump -u root -p dbname> db.sql
you should try this on both innodb_force_recovery 1 and 4 if we get dump file, stop mysql create new directory in /var/lib/ for example mysqlnew and open my.cnf for editing
change datadir to your folder
now start mysql normally it will create new db in mysqlnew folder, so there will not be old db, we should create user and db
#Connect to mysql
create database dbname;
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'dbname'@'localhost';
#back to linux shell
now we should import our mysql dump in created db
#import dump into db
mysql -u root -p dbname < dumpfilename.sql
#check db tables and try to recovery
mysqlcheck -o --all-databases
Now you should have old db imported.