List:General Discussion« Previous MessageNext Message »
From:Rick James Date:May 22 2012 4:28pm
Subject:RE: Reducing ibdata1 file size
View as plain text  
To shrink ibdata1:
1. Dump everything
2. Stop mysql
3. change to innodb_file_per_table = 1
4. change allocation in my.cnf (my.ini) to something smaller, say 50M,AUTOEXTEND
5. remove (rm / delete) ibdata1
6. restart mysql
7. reload data

Step 3:  innodb_file_per_table gives you more fine-grained control -- you can rebuild
(ALTER TABLE) individual tables, thereby giving back unused space to the OS.
Step 4:  ibdata1 is still needed, but will probably not grow nearly as much once you have
file_per_table.


> -----Original Message-----
> From: Manivannan S. [mailto:manivannan_s@stripped]
> Sent: Monday, May 21, 2012 6:04 AM
> To: mysql@stripped
> Subject: Reducing ibdata1 file size
> 
> Hi ,
> 
>                 I am trying to reduce the ibdata1 data file in MySQL.
> In MySQL data directory the ibdata1 data file is always increasing
> whenever I am creating a new database and inserting some data into
> database. If I drop the existing database, the table structures only
> dropped from the server but data still exist in the ibdata1 data file.
> 
> How to reduce the ibdata1 file size in both LINUX and WINDOWS machine.
> 
> Do you have any idea how to solve this problem. Thanks for any
> feedback.
> 
> 
> 
> Thanks
> Manivannan S
> 
> DISCLAIMER: This email message and all attachments are confidential and
> may contain information that is privileged, confidential or exempt from
> disclosure under applicable law.  If you are not the intended
> recipient, you are notified that any dissemination, distribution or
> copying of this email is strictly prohibited. If you have received this
> email in error, please notify us immediately by return email or to
> mailadmin@stripped and destroy the original message.  Opinions,
> conclusions and other information in this message that do not relate to
> the official business of SPAN, shall be understood to be neither given
> nor endorsed by SPAN.
Thread
Reducing ibdata1 file sizeManivannan S .21 May
  • Re: Reducing ibdata1 file sizeClaudio Nanni21 May
  • Re: Reducing ibdata1 file sizeJohan De Meersman21 May
    • Re: Reducing ibdata1 file sizeKishore Vaishnav22 May
      • Re: Reducing ibdata1 file sizeClaudio Nanni22 May
        • Re: Reducing ibdata1 file sizeKishore Vaishnav22 May
          • Re: Reducing ibdata1 file sizeAnanda Kumar22 May
            • Re: Reducing ibdata1 file sizeKishore Vaishnav22 May
              • Re: Reducing ibdata1 file sizeReindl Harald22 May
                • Re: Reducing ibdata1 file sizeKishore Vaishnav22 May
                  • Re: Reducing ibdata1 file sizeAnanda Kumar22 May
                  • Re: Reducing ibdata1 file sizeReindl Harald22 May
                    • Re: Reducing ibdata1 file sizeJohan De Meersman22 May
                      • Re: Reducing ibdata1 file sizeReindl Harald22 May
                        • Re: Reducing ibdata1 file sizeJohan De Meersman22 May
                          • Re: Reducing ibdata1 file sizeReindl Harald22 May
                            • Re: Reducing ibdata1 file sizeJohan De Meersman22 May
                              • Re: Reducing ibdata1 file sizeReindl Harald22 May
                      • Re: Reducing ibdata1 file sizeBaron Schwartz22 May
              • Re: Reducing ibdata1 file sizeAnanda Kumar22 May
          • Re: Reducing ibdata1 file sizeClaudio Nanni22 May
  • RE: Reducing ibdata1 file sizeRick James22 May
Re: Reducing ibdata1 file sizeJohan De Meersman22 May
  • Re: Reducing ibdata1 file sizeAnanda Kumar22 May
    • Re: Reducing ibdata1 file sizeJohan De Meersman22 May
      • Re: Reducing ibdata1 file sizeAnanda Kumar22 May
        • Re: Reducing ibdata1 file sizeJohan De Meersman22 May
          • Re: Reducing ibdata1 file sizeReindl Harald22 May
            • Re: Reducing ibdata1 file sizeJohan De Meersman22 May
              • Re: Reducing ibdata1 file sizeAnanda Kumar22 May
                • Re: Reducing ibdata1 file sizeAnanda Kumar22 May
Re: Reducing ibdata1 file sizeJan Steinman22 May
  • Re: Reducing ibdata1 file sizeJohan De Meersman22 May
  • Re: Reducing ibdata1 file sizeClaudio Nanni22 May
    • RE: Reducing ibdata1 file sizeKay [DAS] Rozeboom22 May
    • Re: Reducing ibdata1 file sizeJan Steinman22 May