Mysql – thesql crashed and not starting even after adding innodb_force_recovery

MySQL

My mysql server 5.7 crashed and not starting even after adding innodb_force_recovery. I tried innodb_force_recovery= 1 to 6 all values, But nothing worked.

please help what to do now and how to recover/fix corrupted InnoDB table manually or by using other library?

I don't have backup and only left with corrupted InnoDB table files of size ~10GB.

syslog shows following lines after each try:

May 21 10:02:33 server2 kernel: [ 3137.604417] audit: type=1400 audit(1495341153.370:19): apparmor="STATUS" operation="profile_replace" profile="unconfined" name="/usr/sbin/mysqld" pid=24185 comm="apparmor_parser"
May 21 10:02:33 server2 mysqld_safe: Logging to '/var/log/mysql/error.log'.
May 21 10:02:33 server2 mysqld_safe: Logging to '/var/log/mysql/error.log'.
May 21 10:02:33 server2 mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
May 21 10:02:33 server2 mysqld[24557]: /usr/sbin/mysqld (mysqld 5.7.17) starting as process 24557 ...
May 21 10:02:33 server2 mysqld[24557]: InnoDB: PUNCH HOLE support available
May 21 10:02:33 server2 mysqld[24557]: InnoDB: Mutexes and rw_locks use GCC atomic builtins
May 21 10:02:33 server2 mysqld[24557]: InnoDB: Uses event mutexes
May 21 10:02:33 server2 mysqld[24557]: InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
May 21 10:02:33 server2 mysqld[24557]: InnoDB: Compressed tables use zlib 1.2.3
May 21 10:02:33 server2 mysqld[24557]: InnoDB: Using Linux native AIO
May 21 10:02:33 server2 mysqld[24557]: InnoDB: Number of pools: 1
May 21 10:02:33 server2 mysqld[24557]: InnoDB: Using CPU crc32 instructions
May 21 10:02:33 server2 mysqld[24557]: InnoDB: Initializing buffer pool, total size = 1G, instances = 8, chunk size = 128M
May 21 10:02:33 server2 mysqld[24557]: InnoDB: Completed initialization of buffer pool
May 21 10:02:33 server2 mysqld[24557]: InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
May 21 10:02:33 server2 mysqld[24557]: InnoDB: Highest supported file format is Barracuda.
May 21 10:02:33 server2 mysqld[24557]: InnoDB: Log scan progressed past the checkpoint lsn 1903477238014
May 21 10:02:33 server2 mysqld[24557]: InnoDB: Doing recovery: scanned up to log sequence number 1903482480640
May 21 10:02:34 server2 mysqld[24557]: InnoDB: Doing recovery: scanned up to log sequence number 1903478876160
May 21 10:02:34 server2 mysqld[24557]: InnoDB: Doing recovery: scanned up to log sequence number 1903484119040
May 21 10:02:34 server2 mysqld[24557]: InnoDB: Doing recovery: scanned up to log sequence number 1903486713411
May 21 10:02:34 server2 mysqld[24557]: InnoDB: Database was not shutdown normally!
May 21 10:02:34 server2 mysqld[24557]: InnoDB: Starting crash recovery.
May 21 10:02:34 server2 mysqld[24557]: InnoDB: Space id and page no stored in the page, read in are [page id: space=0, page number=224284], should be [page id: space=0, page number=360462]
May 21 10:02:34 server2 mysqld[24557]: InnoDB: Database page corruption on disk or a failed file read of page [page id: space=0, page number=360462]. You may have to recover from a backup.
May 21 10:02:34 server2 mysqld[24557]: InnoDB: Page dump in ascii and hex (16384 bytes):
May 21 10:02:34 server2 mysqld[24557]: InnoDB: Uncompressed page, stored checksum in field1 1176070173, calculated checksums for field1: crc32 1474463089/3411817011, innodb 1277319011, none 3735928559, stored checksum in field2 2383263673, calculated checksums for field2: crc32 1474463089/3411817011, innodb 3335309735, none 3735928559,  page LSN 443 804368015, low 4 bytes of LSN at page end 801385784, page number (if stored to page already) 224284, space id (if created with >= MySQL-4.1.1 and stored already) 0
May 21 10:02:34 server2 mysqld[24557]: InnoDB: It is also possible that your operating system has corrupted its own file cache and rebooting your computer removes the error. If the corrupt page is an index page. You can also try to fix the corruption by dumping, dropping, and reimporting the corrupt table. You can use CHECK TABLE to scan your table for corruption. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
May 21 10:02:34 server2 mysqld[24557]: [FATAL] InnoDB: Aborting because of a corrupt database page in the system tablespace. Or,  there was a failure in tagging the tablespace  as corrupt.
May 21 10:02:34 server2 mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended

Best Answer

Isn't MySQL wonderful for giving so much advice in plain, human readable English in its log messages?!

InnoDB: It is also possible that your operating system has corrupted its own file cache and rebooting your computer removes the error. If the corrupt page is an index page. You can also try to fix the corruption by dumping, dropping, and reimporting the corrupt table. You can use CHECK TABLE to scan your table for corruption. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.

This is what you have already tried. When you still can't get it to start, it's obviously impossible to run any SQL queries or commands to solve this by dumping the database for reimporting. If you have also rebooted before innodb_force_recovery, this leaves us the last option, also found from the logs:

InnoDB: Database page corruption on disk or a failed file read of page [page id: space=0, page number=360462]. You may have to recover from a backup.

You do have backups, right?

Without a backup your last hope could be Percona Data Recovery Tool for InnoDB.

This project is a set of tools for recovering lost or corrupted MySQL data from InnoDB's data files. It was created at Percona, and open-sourced for the community to use.

I'm not in any affiliation with Percona and only recommend the open-sourced tools and documentation.

Related Topic