From: Rick James
Date: May 22 2012 4:28pm
Subject: RE: Reducing ibdata1 file size
List-Archive: http://lists.mysql.com/mysql/227508
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148724EBA2@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

To shrink ibdata1:
1. Dump everything
2. Stop mysql
3. change to innodb_file_per_table =3D 1
4. change allocation in my.cnf (my.ini) to something smaller, say 50M,AUTOE=
XTEND
5. remove (rm / delete) ibdata1
6. restart mysql
7. reload data

Step 3:  innodb_file_per_table gives you more fine-grained control -- you c=
an rebuild (ALTER TABLE) individual tables, thereby giving back unused spac=
e 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
>=20
> Hi ,
>=20
>                 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.
>=20
> How to reduce the ibdata1 file size in both LINUX and WINDOWS machine.
>=20
> Do you have any idea how to solve this problem. Thanks for any
> feedback.
>=20
>=20
>=20
> Thanks
> Manivannan S
>=20
> 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.