Re: issue with rename table to new database

[Rick James]

> deleted the ib_data file
Vital data about ALL tables is in ibdata1 !

I'm not clear on what went wrong, but here are some things to ponder...

If you are trying to shrink ibdata1 in the process, you must
1. dump ALL the tables you want to keep
2. stop mysqld
3. delete the entire data tree
4. restart mysqld
5. reload the dump.
This will cleanly keep or toss any .frm and .ibd files -- #3 removes all of them; #5 rebuilds the desired ones.

If you are running with innodb_file_per_table, do you really need to rebuild ibdata1? Or was it bloated for some reason?

To clean up _one_ table that is already in its own _ibd_ file,
0. SET innodb_file_per_table = 1; -- just in case
1. CREATE TABLE new LIKE real;
2. INSERT INTO new SELECT * FROM real WHERE ... > NOW() - INTERVAL 3 MONTH;
3. RENAME TABLE real TO old, new TO real;
4. DROP TABLE old;
That is no need for dump and reload.

It might be better to separate the tasks...
* CREATE/INSERT/RENAME to shrink the one in question (as above)
* Set file_per_table, then ALTER to get into .ibd files (for at least the big tables)
* Shrink ibdata1.