List:General Discussion« Previous MessageNext Message »
From:shawn Date:March 22 2014 4:35am
Subject:Re: Database migration from default configuration to innodb_file_per_table
View as plain text  
Hello Christophe,

On 3/21/2014 4:47 PM, Christophe wrote:
> 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 ?

The system is operating exactly as designed. The ibdata* file(s) contain 
more than just your data and indexes. This is the common tablespace and 
it contains all the metadata necessary to identify where *all* your 
InnoDB tables actually are (where they are in a tablespace and which 
tablespace they are in) and several other things about them. In the 
terms of the InnoDB developers, this is the "data dictionary".  This 
means that once you blow it away, MySQL has no details about any where 
any of your InnoDB tables are, exactly as the message says.

The table names are visible in a SHOW TABLES command because that is 
essentially performing a directory listing of any .FRM files in that 
database's folder. Without both parts (the definition in the .FRM file 
and the metadata in the common tablespace) your tables are broken. If 
you have the .frm file, you can find out which columns you have defined, 
what data types they are, if the table is partitioned or not and what 
your indexes and other constraints look like. The .frm file cannot tell 
the InnoDB engine which tablespace a table is in or what offset the root 
page of the table is within the tablespace. That information was stored 
in the ibdata file that you erased during your test run.

The proper way to change the size of your common tablespace is 
documented here in the user manual

Search for the section header "Decreasing the Size of the InnoDB Tablespace"

Best regards,
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Database migration from default configuration to innodb_file_per_tableChristophe21 Mar
  • Re: Database migration from default configuration to innodb_file_per_tableshawn l.green22 Mar
    • Re: Database migration from default configuration to innodb_file_per_tableChristophe23 Mar
      • Re: Database migration from default configuration to innodb_file_per_tableChristophe26 Mar