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.
You would have play games with symlinks.
WARNING : The following only works with innodb_file_per_table enabled
For example, suppose your datadir was /opt/mysql/data. Each database woudl reside under that folder. If you have three databases (db1, db2, db3), then the folders are:
- /opt/mysql/data/db1
- /opt/mysql/data/db2
- /opt/mysql/data/db3
Before doing anything run this query:
mysql> select table_schema,table_name from information_schema.tables
where table_schema in ('db1','db2','db3');
Let's start by creating temp databases
mysql> create database tmpdb1;
mysql> create database tmpdb2;
mysql> create database tmpdb3;
Next, let's move every table you have db1 to tmpdb1, db2 to tmpdb2, db3 to tmpdb3
mysql -uroot -AN -e"SELECT CONCAT('ALTER TABLE ',db,'.',tb,' RENAME tmp',db,'.',tb,';') FROM information_schema.tables WHERE table_schema in ('db1','db2','db3')" > /root/MoveTables1.sql
mysql -uroot < /root/MoveTables1.sql
OK, we moved every .ibd file sideways to tmp databases
Go into mysql and make sure all the tables have been moved. Databases db1-db3 should be empty
mysql> use db1
mysql> show tables;
mysql> use db2
mysql> show tables;
mysql> use db3
mysql> show tables;
mysql> use tmpdb1
mysql> show tables;
mysql> use tmpdb2
mysql> show tables;
mysql> use tmpdb3
mysql> show tables;
Next, drop the original databases
mysql> drop database db1;
mysql> drop database db2;
mysql> drop database db3;
Go into the OS and create symlinks
ln -s /home/user2/mysql /opt/mysql/data/db1
ln -s /home/user2/mysql /opt/mysql/data/db2
ln -s /home/user3/mysql /opt/mysql/data/db3
chown -R mysql:mysql /opt/mysql/data/db1
chown -R mysql:mysql /opt/mysql/data/db2
chown -R mysql:mysql /opt/mysql/data/db3
Go into mysql and make sure the databases are visible
mysql> show databases;
Next, move the tables into the symlinked databases
mysql -uroot -AN -e"SELECT CONCAT('ALTER TABLE ',db,'.',tb,' RENAME ',SUBSTR(db,4),'.',tb,';') FROM information_schema.tables WHERE table_schema in ('tmpdb1','tmpdb2','tmpdb3')" > /root/MoveTables2.sql
mysql -uroot < /root/MoveTables2.sql
Finally, run the first query you started with
mysql> select table_schema,table_name from information_schema.tables
where table_schema in ('db1','db2','db3');
If the same display comes up, you did it successfully.
UPDATE
@AaronBrown was kind enough to remind me that spreading .ibd files does not buy you anything except for spread-out files. No performance gains whatsoever. Thanks Aaron.
Best Answer
Yes, that would be a good idea. I have mentioned a FaceBook Engineer's Blog about doing that, along with putting the DoubleWrite Buffer and Tablespace Files in other places, in my other posts
Feb 24, 2012
: Tuning dedicated Percona Server with XtraDB for OLTP (Also See Switching Gears TO RAID)Feb 06, 2014
: MySQL on SSD - what are the disadvantages?Jan 27, 2015
: what would be the primary disadvantages of splitting my tables into table spaces and/or separate schemataRAID5 ??? OMG, NO !!!!!
Please do not put anything InnoDB-related in RAID5
Feb 14, 2012
: Is raid 5 suitable for a mysql installation?Mar 24, 2013
: Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updatingSince you will need the the redo logs only in the event of crash recovery, I would go with RAID10 SAS Drives for the redo logs because they are sequentially written files. I would shy away from RAID1 because you want full redundancy of disk in the event of a disk crash. RAID5 does plenty of parity checking which can inhibit sequential writes.