From: Reindl Harald
Date: October 2 2011 8:20pm
Subject: Re: how to shrink ibdata1
List-Archive: http://lists.mysql.com/mysql/225890
Message-Id: <4E88C774.1040309@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="------------enig573614413DE185DD959DF945"

--------------enig573614413DE185DD959DF945
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable



Am 02.10.2011 22:10, schrieb Dhaval Jaiswal:
> Hi All,
>=20
> How to shrink the Mysql ibdata1 files.
>=20
> The actual size of database is only hardly 10G, but ibdata1 is showing =
73G
> full. I did some googling and found the following procedure.
>=20
>     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
>=20
> When you start MySQL in step 5 the ibdata1 and ib-log files will be
> recreated.
>=20
>=20
> Is this the only option with mysql? Can't we update the statistics of
> database & reduce the file size.
>=20
> 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


--------------enig573614413DE185DD959DF945
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk6Ix3QACgkQhmBjz394Ann8dgCfTpKkqZOn5xMEJHjUllQ8jNEV
HHIAni8krDaFboSwjj1TGBDvpvqJJMo1
=qjax
-----END PGP SIGNATURE-----

--------------enig573614413DE185DD959DF945--