List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:February 4 2005 8:58pm
Subject:Re: Multiple Tablespace
View as plain text  
Sabeer,

----- Original Message ----- 
From: "Sabeer MZ" <sabeer.mz@stripped>
Newsgroups: mailing.database.myodbc
Sent: Friday, February 04, 2005 7:33 PM
Subject: Multiple Tablespace


> Hi ALL,
>
> Im using mySQL Distrib 4.0.18 and using single Tablespace
> (/data/ibdata/ibdata1).
> Now Im planning to upgrade to 4.1.8 and move single tablespace to
> Using Per-Table Tablespaces.

there is a critical bug in innodb_file_per_table in <= 4.1.8.

You should upgrade to 4.1.9.

> Please share your expertise in moving single TB to using per-table TB
> with my existing data also.

1) Dump your tables.

2) Add to my.cnf:

innodb_file_per_table

3) Rebuild the whole InnoDB installation according to the advice at 
http://dev.mysql.com/doc/mysql/en/error-creating-innodb.html
Note that with multible tablespaces, a single file ibdata1:10M:autoextend is 
enough. Normally, it does not grow bigger than about 100 MB.

4) Import the table dumps to MySQL. Note that importing big tables to InnoDB 
can take days.

As a sidenote: Jan and Jani are working on a fast index build algorithm. In 
2006, the import may be quite fast.

> Thanks
> Sabeer

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php 

Thread
Multiple TablespaceSabeer MZ4 Feb
Re: Multiple TablespaceHeikki Tuuri4 Feb