I have very bad news for you.
You should not have deleted the ibdata1 file. Here is why:
ibdata1 contains four type of information:
- table metadata
- MVCC data
- data pages (with innodb_file_per_table enabled)
- index pages (with innodb_file_per_table enabled)
Each InnoDB table created has a numercial id assigned to it via some auto increment metadata feature to each ibd file. That internal tablespace id (ITSID) is embedded in the .ibd file. That number is checked against the list of ITSIDs maintained, guess where, ... ibdata1.
I also have very good news for you along with some bad news.
It is possible to reconstruct ibdata1 to have the correct ITSIDs but it takes work to do it. While I personally have not done procedure alone, I assisted a client at my employer's web hosting to do this. We figured this out together but since the client hosed ibdata1, I let him do most of the work (30 InnoDB tables).
Anyway, here a past post I made in the DBA StackExchange. I answered another question whose root cause was the mixing up of ITSIDs.
To cut right to the chase, here is the article explaining what to do with reference to ITSID and how to massage ibdata1 into acknowledging the presence of the ITSID contained within the .ibd file.
I am sorry there is no quick-and-dirty method for recovering the .ibd file other than playing games with ITSIDs.
UPDATE 2011-10-17 06:19 EDT
Here is your original innodb configuration from your question:
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M
innodb_lock_wait_timeout = 18000
Please notice that innodb_log_file_size is there twice. Look carefully...
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G <----
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M <----
innodb_lock_wait_timeout = 18000
The last setting of innodb_log_file_size takes precedence. MySQL expected to start up with the log files being 5M. Your ib_logfile0 and ib_logfile1 were 1G when you tried to start up mysqld. It saw a size conflict and took the path of least resistance, which was to disable InnoDB. That's why InnoDB was missing from show engines;
. Mystery solved !!!
UPDATE 2011-10-17 11:07 EDT
The error message was deceptive because innodb_log_file_size was smaller than the log files (ib_logfile0 and ib_logfile1), which were 1G at the time. What's interesting is this: Corruption was reported because the file was expected to be 5M and the files were bigger. If the situation were reversed and the innodb log files were smaller than the declared size in my.cnf you should get something like this in the error log:
110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 33554432 bytes!
110216 9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
110216 9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
In this example, the log files were already existing as 5M and the setting for innodb_log_file_size was bigger (in this case, 32M).
For this particular question, I blame MySQL (eh Oracle [still hate saying it]) for the inconsistent error message protocol.
There is the presence of a bug in this case for two reasons:
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p1'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p2'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p3'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p4'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p5'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p6'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p7'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p8'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p9'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p10'
REASON #1 : Any table starting with #sql
is a temp table. If these tables are still present in any database folder and the datetime stamp, simply delete them.
REASON #2 : Look carefully at the suffix of each table. #P#p?
resembles a partition tag. This would indicate an attempt to create a temp table using partitiions. That's sounds insane. There was bug report on this back in Feb 16, 2006 for MySQL 5.1.7-beta (closed Mar 15, 2006). The bug report is based on trying to do this manually. Is mysql attempting to do this internally ?
IMHO I would upgrade mysql away from MySQL 5.1 up to MySQL 5.5
Best Answer
You will need to tune InnoDB with the following
You should then download and run
mysqltuner.pl
You should get output resembling this
You then need to tune the per-connection settings See my post How costly is opening and closing of a DB connection? for the buffer settings to tune and my other post Maximum possible memory usage: 16.2G (874% of installed RAM) for an example of how to tune the settings.
UPDATE 2014-10-02 12:39 EDT
Please look back at Maximum possible memory usage: 16.2G (874% of installed RAM). OP was requesting 874% of RAM, which is similar to your situation (You are requesting 771% of RAM in your pastebin). Perhaps dropping max_connections to 1000 would safeguard mysqld's RAM usage.
Login to mysql and run
Then, run mysqltuner.pl and the numbers will change and mysqld will no longer hit thje ceiling on memory. Once you are satisfied with the RAM usage (you should get RAM usage under 75%), add this to my.cnf
or whatever number you find make RAM usage under 75%
GIVE IT A TRY !!!