Mysql – Simple MySQL table copy from one server to another

You could try to use MySQL's CHECK TABLE and REPAIR TABLE functions (but make sure you grab a copy of the table first):

mysql -uroot -p database1
CHECK TABLE table1

If that doesn't work, try using:

CHECK TABLE table1 EXTENDED

According to the CHECK TABLE documentation:

EXTENDED is to be used only after you have run a normal check but still get strange errors from a table when MySQL tries to update a row or find a row by key. This is very unlikely if a normal check has succeeded.

If any errors are reported by either of the above, try:

REPAIR TABLE table1

If that doesn't work, you can try:

REPAIR TABLE table1 EXTENDED

Finally, if that still doesn't work, you may be able to use myisamchk while MySQL is offline - according to the documentation:

REPAIR TABLE does not implement all the options of myisamchk

Is there a way to drop/delete the temporary table that was created during the optimize process without having the mysql server running?

Unfortunately, no.

InnoDB is a very complex beast, and there's a lot of interplay between ibdata,.ibd, ib_logfile, and .frm files that means manual tinkering is, at best, extremely dangerous.

Before you try anything, make a complete copy of all MySQL files to a remote server or another directory if at all possible. When your situation is as delicate is this, it's easy to make a change that breaks the database irreparably.

Your best bet (as Boris mentioned above) is to try and salvage some space somewhere - anywhere. Compress or truncate some log files. Move some non-critical MyISAM tables (if you have any) off of the server until you have things at least partially under control. Move a few .myd files to another directory and symlink them if you need to (be really careful doing this to InnoDB files).

If you're not using using innodb_file_per_table, then you're going to end up with a giant ibdata file and no free disk space. Rolando has given an extensive answer about steps to resolve this issue, and you should follow that rather than the rest of these steps.

If you are using innodb_file_per_table:

Once you have started MySQL successfully, you can check to see if the temporary table still exists (the easiest way to do this is to try desc `#sql-420a_65fb`, but there are also more complex ways).

If it still exists, you can drop it with:

drop table #mysql50##sql-420a_65fb

With innodb_file_per_table on, this will remove the temp table's .frm and .ibd files, and you will get some of your disk space back.

I went into the console and ran the optimize command and since it's an InnoDB database it cloned the table to a temporary one (is that accurate?).

Yes, that's accurate. MySQL will create a temporary table with a name that looks something like #sql-12a_345b, and copy the data across it so that it's fresh and non-fragmented. Once it's complete, it would normally swap the tables around and then drop the old (fragmented) table).