MySQL Forums
Forum List  »  InnoDB

Re: Manually fill up innoDB file
Posted by: Rolando Edwards
Date: September 10, 2008 11:30AM

You should use 'innodb_file_per_table' in the /etc/my.cnf. That way, each table will have its own tablespace.

Before your data get's too cumbersome, you need to perform the following:

1) mysqldump all your data, trigger, and stored procedures to /root/MyData.sql
2) Add 'innodb_file_per_table' to the [mysqld] section of /etc/my.cnf
3) shutdown mysqld
4) rm -f /var/lib/mysql/ib* (Yes, delete the innodb files)
5) restart mysqld (This will rebuild ibdata1 and ib_logfile files)
6) Reload /root/MyData.sql into mysql (Each InnoDB table will be loaded into an external .ibd file)

The ibdata will contain only internal data dictionary info about the InnoDB tables. Each .ibd file will contain the table's row data and all associated indexes.

After this there is no need to monitor ibdata files for size. You can now defragment each InnoDB table individually with 'OPTIMIZE TABLE <tbl-name>'.

You can also move individual tablespaces to other locations and import them using 'ALTER TABLE <innodb-tbl> IMPORT TABLESPACE'.

Please read more about this at this URL : http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html

Have fun with it !!!

Rolando A. Edwards
MySQL DBA (CMDBA)
212-625-5307 (Work)
RolandoLogicWorx (AIM)
redwards@logicworks.net
155 Avenue of the Americas
New York, NY 10013

Options: ReplyQuote


Subject
Views
Written By
Posted
2422
September 09, 2008 06:09AM
Re: Manually fill up innoDB file
1871
September 10, 2008 11:30AM
1720
September 10, 2008 06:51PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.