I'd like your advice, (one more time ;) ) about this case :
The context is :
A huge database using InnoDB engine from filling about several years
(without possible shrinking, as I've seen, except dropping all databases
to recreate them ... ) the ibdata file , which is taking over 9GiB on
We have to separate data from databases in two cases , whilst it is
running , after setting innodb_file_per_table in MySQL configuration ,
and restarting service.
Creating First database, containing at oldest 6 months of data.
Second database, considered "archive" containing data older than 6 month.
Not such a problem to separate actual data : using several mysqldump
with --where switch, which handles the case.
After this, Shell scripts using "INSERT INTO archive SELECT * FROM
realtime WHERE ..." seem to be reliable to do this.
*But*, in this one timed scheduled task in data migration (Previewed and
accepted by customer, by night / not tonight ... :) ), and *I'd like to
remove the ibdata1 file* , as it takes huge disk space.
Migration task also includes converting old tables (previously in
InnoDB), to alter them into .... InnoDB, to recreate the InnoDB file
using innodb_file_per_table parameter.
While testing this in lab, I came to fact that removing ibdata1 file,
cancels MySQL to get reference to any table in databases .
"use database" works ... but DESCRIBE table goes to : "table
database.table does not exist".
Is there anyway to handle this case ?