MySQL Forums
Forum List  »  General

Re: Separate databases in separate files
Posted by: Rick James
Date: July 02, 2011 11:19PM

That's an InnoDB-specific question.

innodb_file_per_table = 1

Changing that in my.cnf (my.ini), plus restarting mysql, will cause all _new_ tables to be in their own .ibd file (tablespace).

To move existing tables to separate files: ALTER TABLE foo ENGINE=InnoDB;

But then you will ask how to shrink ibdata1. That's a tougher question. The answer is
1. Use mysqldump to dump all the tables.
2. Stop mysql
3. Delete ibdata1
4. Shrink the definition in my.cnf (if it is more than, say, 50M).
5. Start mysql; it will recreate ibdata1 with the size indicated. Note: ibdata1 is needed for a lot of things, so it cannot be empty.
6. Restore the tables using the commandline mysql and the dump file from step 1.

Options: ReplyQuote


Subject
Written By
Posted
Re: Separate databases in separate files
July 02, 2011 11:19PM


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.