List:General Discussion« Previous MessageNext Message »
From:Adarsh Sharma Date:October 3 2011 5:40am
Subject:Re: how to shrink ibdata1
View as plain text  
innnodb_file per table creates ibdata files for each table and What to 
do if some tables data are deleted frequently.
I have a innodb table which was truncated after 150GB in mysql database.


Reindl Harald 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
>
>   


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