Tested in Debian 8 / MySQL 5.5
If you’re getting this error on mysql error log:
InnoDB: Error: page 4352 log sequence number 12151412585 InnoDB: is in the future! Current system log sequence number 8204. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files
It’s due to a sequence mismatch in the InnoDB log, so it’s probable mysql doesn’t start. One thing you can do is restore a backup but, what happens if there is no backup?… Well, you can try to recover mysql using innodb-force-recovery.
First add this to the [mysqld] section in the my.cnf file:
innodb-force-recovery = 6
And then restart mysql:
~ $ /etc/init.d/mysql start
In the mysql error log you should get something like this:
InnoDB: Waiting for the background threads to start InnoDB: 5.5.52 started; log sequence number 0 InnoDB: !!! innodb_force_recovery is set to 6 !!! [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306 [Note] - '127.0.0.1' resolves to '127.0.0.1'; [Note] Server socket created on IP: '127.0.0.1'. [Note] Event Scheduler: Loaded 0 events [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.52' socket: '/var/run/mysqld/mysqld.sock' port: 3306
If not, you are in trouble, look for a backup wherever… If yes, the next step is make a backup of all databases:
~ $ mysqldump -u root --all-databases > dump.sql
If dump ends successfully you are lucky. Now stop mysql:
~ $ /etc/init.d/mysql stop
Comment the innodb-force-recovery line from my.cnf file, and move ibdata*, ib_logfile* and all database folders, except mysql, to a temp dir:
~ $ cd /var/lib/mysql ~ $ mv ibdata* ib_logfile* database1 database2 (...) /tmp/
Start mysql, it will create new ibdata and ib_logfiles:
~ $ /etc/init.d/mysql start
In the error log:
InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: 127 rollback segment(s) active. InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created InnoDB: Waiting for the background threads to start InnoDB: 5.5.52 started; log sequence number 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306 [Note] - '127.0.0.1' resolves to '127.0.0.1'; [Note] Server socket created on IP: '127.0.0.1'. [Note] Event Scheduler: Loaded 0 events [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.52' socket: '/var/run/mysqld/mysqld.sock' port: 3306
Mysql is working fine in a clean eviroment, the last step is restore the prior dump:
~ $ mysql -u root < dump.sql
At this time you should have all mysql databases, working like they were before crash.