List:General Discussion« Previous MessageNext Message »
From:Dhaval Jaiswal Date:October 2 2011 8:25pm
Subject:Re: how to shrink ibdata1
View as plain text  
Currently in my database only 5 tables are of innodb engine.

"innodb_file_per_table" will create each file per table is it ? what are the
other benefits of it.

If so than again i have to follow the dump & restore procedure.



On Mon, Oct 3, 2011 at 1:50 AM, Reindl Harald <h.reindl@stripped>wrote:

>
>
> Am 02.10.2011 22:10, schrieb Dhaval Jaiswal:
> > Hi All,
> >
> > How to shrink the Mysql ibdata1 files.
> >
> > The actual size of database is only hardly 10G, but ibdata1 is showing
> 73G
> > full. I did some googling and found the following procedure.
> >
> >     Do a mysqldump of all databases, procedures, triggers etc
> >     Drop all databases
> >     Stop mysql
> >     Delete ibdata1 and ib_log files
> >     Start mysql
> >     Restore from dump
> >
> > When you start MySQL in step 5 the ibdata1 and ib-log files will be
> > recreated.
> >
> >
> > Is this the only option with mysql? Can't we update the statistics of
> > database & reduce the file size.
> >
> > I am currently using 5.0.X.
>
> "innodb_file_per_table" is your friend, but way too late
> that should have been considered BEFORE the setup
>
> now you have only the option dump your databases
> reset the server
> configure innodb_file_per_table
> restore the backup
>
>


-- 
Regards,
Dhaval Jaiswal

Thread
how to shrink ibdata1Dhaval Jaiswal2 Oct
  • Re: how to shrink ibdata1Reindl Harald2 Oct
    • Re: how to shrink ibdata1Dhaval Jaiswal2 Oct
      • Re: how to shrink ibdata1Reindl Harald2 Oct
        • Re: how to shrink ibdata1Dhaval Jaiswal2 Oct
    • Re: how to shrink ibdata1Adarsh Sharma3 Oct
      • Re: how to shrink ibdata1Andrew Moore3 Oct