Menu
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.
801
Save this answer. Show activity on this post. ibdata1 contains InnoDB dictionary which is vitally important for InnoDB. If you delete it any access to a table will fail with Table doesn't exist error.
The ibdata1 file can be shrinked if you delete all databases, remove the files and reload the mysqldump. We can configure MySQL so that each table, including its indexes, is stored as a separate file. It is enabled by default as of version 5.6. 6 of MySQL.
The file ibdata1 is the system tablespace for the InnoDB infrastructure. You can separate Data and Index Pages from ibdata1 by enabling innodb_file_per_table . This will cause any newly created InnoDB table to store data and index pages in an external .
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.
110
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
