Home

Convert global InnoDB tablespace into per-table files

2009, August 24 - 10:57 — müzso
By default MySQL stores InnoDB tables and indexes in a single file called "ibdata1" (which is on Debian in the /var/lib/mysql directory). Unfortunately this file is never "recycled", thus once space is allocated to it, it's never released (see this feature request). Not even after dropping the database that held the given table. However there's an InnoDB option called innodb_file_per_table that allows you to store each InnoDB table in a separate file (just as MyISAM does) and after dropping the table the file is removed and thus the occupied space is recycled.

So the solution is to use this option. But it affects only newly created tables, there's no automatic table conversion involved. However executing an ALTER TABLE ... ENGINE=InnoDB on the given table acts just as if you did drop the table and loaded it back from a backup. You can use the following query to create the ALTER TABLE statements for all of your InnoDB tables:
SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` ENGINE=', t.`ENGINE`, ';') AS stmt
FROM `information_schema`.`TABLES` t
WHERE t.`ENGINE` = 'InnoDB'
ORDER BY t.`TABLE_SCHEMA`, t.`TABLE_NAME`

One thing to note is that the above procedure will not decrease the size of your current ibdata1 file. To achieve that you've only one option. Instead of executing "alter table" commands, you've to dump/backup _all_ your InnoDB tables, drop all these tables, add the extra innodb option to my.cnf and import the innodb tables back into the database. A more complete checklist:
  1. Backup/dump all InnoDB tables.
  2. Drop all InnoDB tables.
  3. Stop MySQL.
  4. Add the innodb_file_per_table option to my.cnf (to the [mysqld] section).
  5. Delete the ibdata1 file and any ib_logfile* files in the MySQL database directory.
  6. Start MySQL.
  7. Import tables from the backup that you created in the first step.

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
2010, April 29 - 18:55 — bpelhos (not verified)

Why not this way?

(0. Backup/dump all InnoDB tables for safety reasons only.)
1. Stop MySQL.
2. Add the innodb_file_per_table option to my.cnf (to the [mysqld] section).
3. Start MySQL.
4. ALTER all InnoDB tables.
5. Delete the ibdata1 file and any ib_logfile* files in the MySQL database directory.

2010, April 29 - 19:34 — müzso

Re: Why not this way?

Because despite the innodb_file_per_table option, the InnoDB engine will still store some (not much) table related data in the global tablespace file (ibdata1). If you do delete ibdata1, MySQL will automatically generate a new one and you'll loose access to all your InnoDB tables (MySQL won't find/list them if you try to reference them in a query, insert, update, delete, ...). Unfortunately I had to learn this the hard way. To move your InnoDB tables into a per-file storage and get rid of the old (large) ibdata1 file at the same time, you've to export/dump your table definition+data, add the innodb_file_per_table option, drop the global tablespace file (ibdata1) and re-create the tables and load their data. There's simply no other way (AFAIK).

And from this there're a few facts that follow:
  • You cannot transport an InnoDB table from one server to the other by just moving the tablespace file (*.ibd) and the table descriptor (*.frm) of the given table. If you do try this, you'll find that on the destination server MySQL doesn't see the new InnoDB table. If you try to hack yourself through by first creating the table with the same CREATE TABLE statement as was used in the source database, and then shutting down MySQL and replacing the ibd+frm files with the ones that you copied over from the other server, you'll get an error message during startup of the database. The only problem preventing this from working is that InnoDB records some kind of a table "index" both in the per-table ibd file and in the global tablespace file (ibdata1). There's a counter and upon the creation of a new InnoDB table the value of this counter is incremented and the current value is assigned to the new table (and this is saved both in the global tablespace and the per-table file). And if these identifiers do not match, MySQL reports an error. There're a few blog posts that describe methods of hacking the global tablespace file to allow this kind of InnoDB table "import", but they're certainly not for the faint of heart and require hours of work/digging (in the easier case creating new "dummy" InnoDB tables to forcefully increase the counter in the global tablespace to the value in the imported table or hexediting the global tablespace file).
  • If you back up your InnoDB tables by backing up the table files, you must make a copy of the global tablespace file too. The InnoDB table files (*.idb) are pretty much worthless without the respective ibdata1 file.
Syndicate content