ibdata file size

MySQL & MariaDB Other MySQL® Questions
1

I have a ibdata file and i have 3 databases. In one of them i had tables with size around 10 GB, each one.

Now i made a truncate for both tables but the space in the ibdata file never shrinks.

I ran a optimize table, alter table and the ibdata size looks the same.

How can i shrinks ibdata file without make a dump a restore again?

Thanks for your help.

2

You can’t shrink an InnoDB tablespace.

You will have to dump/restore to reclaim the space:
0) dump mysql database

  1. shut down mysql
  2. move away any InnoDB data and log files
  3. start mysql and import database

If you want to reclaim space on drop/truncate in the future, then you will need to use --innodb-file-per-table:
http://dev.mysql.com/doc/refman/5.1/en/multiple-tablespaces. html

–Swany

3

Hi Justin,

Thanks for your answer.

is this issue considered as a bug? Why the space is not eliminated?

the only solution is to apply the steps that you give me?. Other alternative?

Thanks,