MySQL Forums :: InnoDB :: changing from 1 big file to file_per_table


Advanced Search

changing from 1 big file to file_per_table
Posted by: terry norman ()
Date: November 15, 2011 07:12AM

Hi all, I've searched but not found the exact situation I'm in, so I'd be grateful for any advice. Using InnoDB, first I used just one file, ibdata1. Fine. Then I wanted to try innodb_file_per_table. At the same time I wanted to bring my db a bit more up to date so my intention was to drop the test db then restore it from a dump from another machine. I did the "drop database" command. Then I noticed that the ibdata1 file was still unchanged in size. It hadn't shrunk. So if I carried on I'd end up with the ibdata1 file massively oversized, plus all the individual files resulting from innodb_file_per_table as well. Any messing about with the ibdata1 file (e.g. mv) was punished severely on trying to load the database server. OK. Now generally the instructions to reclaim space from ibdata1 e.g. http://forums.mysql.com/read.php?22,430513,431249#msg-431249 specify dumping the db - which I can't do because I've dropped it !
However I'm sort of in the position I would be in after a dump because I've got the dump file (from elsewhere). I wonder if mysqldump does something to allow the replacement of ibdata1? Flush logs? Thanks in advance for any advice. - Terry.

Options: ReplyQuote


Subject Views Written By Posted
changing from 1 big file to file_per_table 2038 terry norman 11/15/2011 07:12AM
Re: changing from 1 big file to file_per_table 925 Rick James 11/16/2011 09:54AM
Re: changing from 1 big file to file_per_table 769 terry norman 11/17/2011 06:18AM
Re: changing from 1 big file to file_per_table 774 Rick James 11/18/2011 08:50AM
Re: changing from 1 big file to file_per_table 785 terry norman 11/21/2011 03:48AM


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.