List:General Discussion« Previous MessageNext Message »
From:Christophe Date:March 21 2014 8:47pm
Subject:Database migration from default configuration to innodb_file_per_table
View as plain text  
Hi list,

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

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.

Problem :

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 ?

Regards .
Christophe.
Thread
Database migration from default configuration to innodb_file_per_tableChristophe21 Mar 2014
  • Re: Database migration from default configuration to innodb_file_per_tableshawn l.green22 Mar 2014
    • Re: Database migration from default configuration to innodb_file_per_tableChristophe23 Mar 2014
      • Re: Database migration from default configuration to innodb_file_per_tableChristophe26 Mar 2014