From: Andrew Moore Date: October 3 2011 10:14am Subject: Re: how to shrink ibdata1 List-Archive: http://lists.mysql.com/mysql/225900 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=000e0cd7199692cca704ae623dfb --000e0cd7199692cca704ae623dfb Content-Type: text/plain; charset=ISO-8859-1 File per table is required if you want to implement compression via the barracuda file format. On 3 Oct 2011 06:39, "Adarsh Sharma" 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 >> >> > --000e0cd7199692cca704ae623dfb--