1

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.

4

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.

  • +1 and thank you. I had to use innodb_force_recovery=5 to get service.msc to open the MySQL service. It would not let me drop the new/corrupted table from the MySQL command line client, so I am currently doing a mysqldump of the entire set of databases on the MySQL server instance. The bloated corrupted new table brings total size over 100GB, so it may take a while. Can you please give explicit step by step instructions for how to restore on a fresh instance of innoDB, in language for a beginner? I am just starting to use MySQL. I don't know things you assume I know. – CodeMed May 23 '14 at 22:20
  • Are you telling me it is safe to delete ibdata1? The database is so big that I might not be able to complete mysqldump on my machine without the extra space that would result from deleting ibdata1, and even then, I still might not have enough disk space. I have deleted over 100GB, and the remaining 80GB ibdata1 is the only remaining optional content on my computer. – CodeMed May 24 '14 at 5:08
  • Side remark: innodb_force_recovery=5 doesn't allow writes to a table, but it allows you to DROP it. Once InnoDB tablespace is corrupt InnoDB doesn't provide any means to repair it. So if you end up with the corrupted ibdata1 you have to re-create your database from the scratch. (I will update the answer to describe the steps) – akuzminsky May 24 '14 at 15:07
  • Don't delete any files until the database is fully restored. You can either stream mysqldump's output to a remote server or take the dump from the remote server remote# mysqldump -h mysql-server.X.com -A --skip-lock-tables > all.db – akuzminsky May 24 '14 at 15:22
  • I couldn't get mv command to work because I'm using windows 7 and mv is a Linux command. I tried robocopy, xcopy, & move, but got errors from all of them, so I finally just moved the data by manually dragging and dropping in the windows explorer gui. I don't have a mysql.old file, but I have the files that used to be in the data directory now stored in a bkup directory. Exception that I deleted a 260GB newtable.idb file whose creation caused this problem. The mkdir command worked, but mysql install_db gives error 2003 cant connect to mysql server on localhost 10061 – CodeMed May 24 '14 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.