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
> -----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
> 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.