Skip to content

Rage Against the Shell

Linux tips and other things…

  • Home
  • Contact
  • Privacy Policy

MySQL innodb checksum mismatch

Posted on September 25, 2015 - September 25, 2016 by Mr. Reboot

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!

Posted in MySQL

Post navigation

Configure a NTP server
NTP daemon status

7 Comments

  1. Viktor says:
    August 13, 2019 at 7:59 pm

    working for me!!! Thanks

    Reply
  2. Rodrigo says:
    June 12, 2020 at 4:43 pm

    Perfecto recupere la data
    Gracias

    Reply
    1. Mr. Reboot says:
      June 13, 2020 at 10:08 am

      Thank you for your comment!

      Reply
  3. Eric says:
    January 13, 2022 at 10:23 pm

    Perfecto, solo tuvimos que reiniciar el equipo al final y levanto todo bien.

    Gracias

    Reply
    1. Mr. Reboot says:
      January 14, 2022 at 6:40 am

      Great, thank you!

      Reply
  4. Mike says:
    September 12, 2022 at 7:07 pm

    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

    Reply
    1. Mr. Reboot says:
      September 13, 2022 at 9:49 am

      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.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Calendar

September 2015
M T W T F S S
 123456
78910111213
14151617181920
21222324252627
282930  
« Aug   Oct »

Categories

  • Apache
  • Cisco
  • Command line
  • Distros
  • Dovecot
  • File systems
  • Gadgets
  • GlusterFS
  • MySQL
  • Nginx
  • NTP
  • Opendkim
  • Pacemaker + Corosync
  • Postfix
  • Raspberrypi
  • SSH
  • SSL
  • Varnish

RSS RSS

  • Using qrencode January 16, 2022
  • Compile varnish module vmod_vsthrottle April 22, 2020
  • SSH vpn with sshuttle April 9, 2020
  • Disable swap in systemd December 16, 2019
  • Getting the parent process pid October 12, 2018
Proudly powered by WordPress | Theme: micro, developed by DevriX.