List:General Discussion« Previous MessageNext Message »
From:Andrew Moore Date:October 3 2011 10:14am
Subject:Re: how to shrink ibdata1
View as plain text  
File per table is required if you want to implement compression via the
barracuda file format.
On 3 Oct 2011 06:39, "Adarsh Sharma" <adarsh.sharma@stripped> wrote:
> 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