Mysql – forcing innodb recovery of a corrupted database

innodbMySQL

A poorly designed script selected records from two existing tables and added records to a newly created table for 24 hours until I killed it by terminating the MySQL 5.6 command line client and restarting the server. I estimate that the script inserted over 70 million rows into the new table. The result is that service.msc will not start the MySQL service. I took various steps to address this problem, including deleting the log files and the myservername.err file, then restarting the server. The new myservername.err file contains a long stream of repetitive warnings which state that I need to force an innodb recovery because the database is corrupted. How can I accomplish this?

I want to: 

1.) start the corrupted database, and then 
2.) do a rollback to the status of the database before the script was run.

I could either select a time point, or simply delete the newly created table because the only activity in the database since things worked well has been the running of the poorly designed script. What steps do I need to take to restart the corrupted database and then rollback the database to its precorrupted state?

I uploaded the error log to a file sharing site. You can read it by clicking on this link.

What I imagine is that I need to add the following line to my.ini, immediately after the [mysqld] line: 

[mysqld]
innodb_force_recovery=?

But what number do I use instead of ?? And then what code do I use to do the rollback? Do I simply write drop table new_table_name? Or do I do a rollback command to a specific date? (I moved the log files to a different folder, so would I need to reimport the log files?) What would this look like in code?

EDIT:

I finished mysqldump, then moved the contents of the /data directory into a backup location. I then moved /data/MySQL subdirectory contents back into the /mysql subdirectory of the otherwise empty /data directory. But when I try to start the MySQL windows server, I get error 1067. I uploaded the contents of the newly created myservername.err file to a file sharing site. Here is the link to view the error log.

Best Answer

InnoDB complains about wrong LSN because ib_logfile-s were deleted. You shouldn't have deleted those. But MySQL can survive after that. Next time InnoDB updates a page it will overwrite its LSN in the header and the error message will go away. Just wait till it starts (the error log shows neither successful nor unsuccessful start).

To get 100% clean tablespace you need to start MySQL with innodb_force_recovery=4, take mysqldump and restore it on a fresh instance of InnoDB (by fresh I mean you have to delete ibdata1, and all databases directories).

UPDATE:

At this point MySQL is started with innodb_force_recovery=x (x != 0)

  • Take dump of all databases:

    # mysqldump --skip-lock-tables -A > alldb.sql

  • Check where MySQL keeps its files(in my case it's /var/lib/mysql/):

    # mysql -NBe "SELECT @@datadir"
/var/lib/mysql/

  • Stop MySQL

    # mysqladmin shut

  • Move old MySQL files to safe place 

    # mv /var/lib/mysql /var/lib/mysql.old

  • Create new system database

    # mkdir /var/lib/mysql
# mysql_install_db

  • Start MySQL

    # /etc/init.d/mysql start

  • Restore the dump

    # mysql < alldb.sql

Restore may take long time if the database is big. Another trick may work in that case. Run ALTER TABLE ... ENGINE INNODB on each InnoDB table. It will rebuild all InnoDB indexes and thus the errors will go away.

Related Question