7

I looked into the ibdata file and it was 128 GB. I took out size of each table and a table was having 5 Million of record and it was showing 90 GB.

I truncated that table since it was not necessary and I viewed the ibdata1 size. Still it is showing 128 GB. Then I took out sizes of each table and sum of then is just 35-38 GB.

How can I get back the 90 GB of space on my Db server. I am using mysql with php on red-hat Linux server.

I guess- if we delete anything on DB it just removes the replication but space allotted to it sill not be deallocated till we do it manually. Is it the way.

gertvdijk
  • 24,056
  • 6
  • 41
  • 67
Manojkumar
  • 1,351
  • 5
  • 35
  • 63
  • 1
    Pssible duplicate of http://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql. In one word: you can't. Dump all databases, stop server, delete ibdata files, restart, reimport dump. – RandomSeed Dec 19 '12 at 13:12
  • 1
    Next time use the [`innodb_file_per_table`](http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html) option. Dropping large tables will allow you to reclaim space then. – gertvdijk Dec 19 '12 at 22:56
  • Highly recommend visiting the link @YaK provided. Definitely a duplicate and the post he/she is linking to has step-by-step instructions. – Tass Mar 05 '13 at 20:47

1 Answers1

5

Once space is consumed by your ibdata file it never goes away. The only way to 'shink' the data file is dump your dbs, drop them all, delete the ibdata file, restart mysql and reload the dump.

If you frequently drop entire tables, you can config innodb to use one data file per table. In this case, when you drop a table, you can delete that table specific ibdata file.

Ray
  • 40,256
  • 21
  • 101
  • 138
  • if I delete all my tables and drop the DB and delete ibdatafile, will it get auto-create if I reload the dump – Manojkumar Dec 19 '12 at 13:43
  • @NaanuManu yes, just make sure you have a full dump. All innodb data will be lost when you delete it. – Ray Dec 19 '12 at 13:49