MySQL Optimization – How to Optimize a MySQL Database Using MySQL Workbench

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).

OK, I finally found a solution. To give proper credit, I had to blend answers provided by two people: RiggsFolly's answer to this question on resetting the root password in wamp, which is what I'm using, and jm666's answer for this question on restoring a deleted root user in MySQL using MAMP on OS X.

For Wamp Users:

1. Click on the Wamp system tray icon to bring up the tray menu. Click MySQL > Service > Stop Service.

2. Edit your MySQL's my.ini file...that's your configuration file for your SQL database, and it's generally located in the root folder for your MySQL database (try looking in wamp > bin > mysql > mysql5.5 [or whatever version]). Use a text editor, and scroll down in the my.ini file to [wampmysqld]. Immediately after this entry, insert a new line containing the following command:

   skip-grant-tables
   This tells the MySQL database that you want to override any restrictions such as root user or password parameters. This is a major security hole, so once we are done with our repairs, we'll need to undo this! Save the my.ini file.

3. Resume MySQL services in Wamp (click MySQL > Service > Start/Resume Service).

4. Now open the MySQL console: click MySQL > MySQL Console

5. A "DOS-like" terminal window will open up (if it asks you for a password, simply press enter). You should see a MySQL prompt. Copy the following block of code and paste it at the prompt.

   INSERT INTO mysql.user
   SET user = 'root', 
   host = 'localhost', 
   password = '', 
   Select_priv = 'y',
   Insert_priv = 'y',
   Update_priv = 'y',
   Delete_priv = 'y',
   Create_priv = 'y',
   Drop_priv = 'y',
   Reload_priv = 'y',
   Shutdown_priv = 'y',
   Process_priv = 'y',
   File_priv = 'y',
   Grant_priv = 'y',
   References_priv = 'y',
   Index_priv = 'y',
   Alter_priv = 'y',
   Show_db_priv = 'y',
   Super_priv = 'y',
   Create_tmp_table_priv = 'y',
   Lock_tables_priv = 'y',
   Execute_priv = 'y',
   Repl_slave_priv = 'y',
   Repl_client_priv = 'y',
   Create_view_priv = 'y',
   Show_view_priv = 'y',
   Create_routine_priv = 'y',
   Alter_routine_priv = 'y',
   Create_user_priv = 'y',
   Event_priv = 'y',
   Trigger_priv = 'y',
   Create_tablespace_priv = 'y';
   

6. Press enter. You should get some sort of message saying that one line (of database code) was affected, with some warnings (probably because you are messing with the root user...I think you can safely ignore these). Close the MySQL terminal.

7. Remember that security hole we opened in step two? Time to fix that. Reopen your my.ini SQL configuration file, and remove the skip-grant-tables line you added. Save the my.ini file and close.

8. RESTART your MySQL Service (same as before, but click on MySQL > Service > Restart Services instead).

THAT should do the trick!

A note on the root user in MySQL for newbies: for some strange reason, even if you create another user as a superuser, MySQL seems to need this user to operate...however, if you don't set up a password for root, it notes this as a security hole. Whatever you do, don't delete it: you're better off just setting some wildly obscure password for it and leaving it alone if you want to use another superuser account. It would seem that there should be more protections to help prevent unwary users from doing this (a warning, or something)....that's my twelve cents on the issue.