MySQL Forums
Forum List  »  InnoDB

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.,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

Written By
changing from 1 big file to file_per_table
November 15, 2011 07:12AM

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.