Tested on Ubuntu 12 and 14 / Debian 7
When you find the error InnoDB: Error: checksum mismatch in data file ./ibdata1 (or ./ibdataX) at the mysql error log, it means that ibdata checksum is not correct and there are transactions that have not been completed and wrote to the ibdata, for example due to a low memory, oom killer, mysql service crash etc.
This could be the error at mysql error log:
... InnoDB: Error: checksum mismatch in data file ./ibdata1 InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB InnoDB: created in this failed attempt. InnoDB only wrote those InnoDB: files full of zeros, but did not yet use them in any way. InnoDB: But be careful: do not remove old data files which contain InnoDB: your precious data! [ERROR] Plugin 'InnoDB' init function returned error. [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. [ERROR] Unknown/unsupported storage engine: InnoDB [ERROR] Aborting ...
You can control the checksum, certainly it will show one or more errors:
~ $ innochecksum -d ibdata1
To solve it writing all changes to the ibdata file, first start mysql in recovery mode, at level 4. Add this to the my.cnf under [mysqld] tag:
innodb_force_recovery = 4
Start mysql:
~ $ /etc/init.d/mysql start
Now at the error log we can see:
...InnoDB: Log scan progressed past the checkpoint lsn 5294845868 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the InnoDB: doublewrite buffer... InnoDB: Warning: database page corruption or a failed InnoDB: file read of space 0 page 0. InnoDB: Trying to recover it from the doublewrite buffer. InnoDB: Recovered the page from the doublewrite buffer. InnoDB: Warning: database page corruption or a failed InnoDB: file read of space 0 page 371. InnoDB: Trying to recover it from the doublewrite buffer. InnoDB: Recovered the page from the doublewrite buffer. InnoDB: Warning: database page corruption or a failed InnoDB: file read of space 0 page 197. InnoDB: Trying to recover it from the doublewrite buffer. InnoDB: Recovered the page from the doublewrite buffer. InnoDB: Doing recovery: scanned up to log sequence number 5294846217 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Waiting for the background threads to start InnoDB: 5.5.44 started; log sequence number 5294846217 InnoDB: !!! innodb_force_recovery is set to 4 !!! ...
It will make a data recovery. Now you have to disable innodb_force_recovery at my.cnf and kill mysql:
~ $ killall -9 mysqld
Usually mysql should start after kill, if not you can start it:
~ $ /etc/init.d/mysql start
At this time mysql should be already working fine!
working for me!!! Thanks
Perfecto recupere la data
Gracias
Thank you for your comment!
Perfecto, solo tuvimos que reiniciar el equipo al final y levanto todo bien.
Gracias
Great, thank you!
Hi, I have tried to do the recovery as you post above, but it won’t start after I put it inside the my.cnf file. I am not sure what’s the problem, can you suggest what might be the problem? my mysqld.log is showing exactly the same as yours. And the command innochecksum is not found as well, is it because I am using Ubuntu 18.04 and mysql version 5.5
Hi Mike. I think corruption is more severe in your case. Try to start with recovery level 6. If mysql starts, the thing you can do is making a whole dump of all databases (mysqldump –all-databases), backup mysql datadir (default is /var/lib/mysql) physically as prevention, stop mysql, delete ibdata1 file, start againg without recovery, and finally import the prior dump. So you should get a clean datadir.
I have made this operation several times when there is no another way to start mysql.