Mysql – drop table from outside

MySQL

A poorly designed script ran on my MySQL 5.6 instance for 24 hours before I stopped it by killing the MySQL command line client and rebooting the machine while the script was still running. The script created a new table and then inserted at least 70 million rows of data into the new table. Now service.msc in windows 7 will not start MySQL as a windows service. It gives error 1053.

How can I get MySQL to launch as a windows service?

Here is what I have done do far:

Deleted the ib_logfile* files  
Deleted the myservername.err file  
Changed the following settings in my.ini:  
    innodb_additional_mem_pool_size = 100M
    innodb_buffer_pool_size = 128M
    innodb_log_file_size = 50M  
Restarted the computer a few times, trying to start the MySQL windows service a few times

I notice the size of the ibdata1 file is 76,812,288 KB

I have thought to just delete the newly created table, which would reduce the size of the database by a huge factor and get things back to what they were before the bad script ran. It would be nice if I could just drop the table without opening the mysql windows service, or somehow bring the database back to the state it was in just before the script started running, and then just restart the windows service without the damage that is causing it not to start now. But I do not know how. Can anyone show me how to get windows to start the MySQL service again?

I uploaded the myservername.err file to a file sharing site. You can read it by clicking on this link. Please understand that this error file continues to grow into hundreds of megabytes in size. The file sharing site just has the first few megabytes. I imagine it gets repetitive after that.

Best Answer

Your data is corrupt. You might attempt starting the server iterating through the http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html documentation. If it starts you can attempt to save the large table (mysqldump/mydumper export) or drop it if it's not needed.

Related Solutions

Mysql – Create second instance of thesqld on windows xp

=> only set my_ibt.ini in the pateh C:\RailsInstaller\ => my_xxx.ini

[mysqld_xxx]
#if already installed then use that basedir
basedir = "C:\RailsInstaller\MYSQL"

#where you want to set your database
datadir="C:\RailsInstaller\MYSQL\data"
port = 43307
enable-named-pipe
socket = mypipe1

In the command promt which runs as administrator go to
\MYSQL\bin> And run Command :

mysqld --install mysqld_xxx --defaults-file=C:\RailsInstaller\my_xxx.ini

Service successfully installed.

Then :

net start mysqld_xxx

The mysqld_ibt service is starting. The mysqld_ibt service was started successfully.

Mysql – forcing innodb recovery of a corrupted database

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.

Best Answer

Related Solutions

Mysql – Create second instance of thesqld on windows xp

Mysql – forcing innodb recovery of a corrupted database

Related Question