Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I needed to reclaim some disk space so I removed an old Magento database from my server. The database size was around 16 GB. I was surprised that once the database was deleted, it did not really free up that disk space.

I tried restarting the MySQL server, but it is still taking up that space.

If it matters, a Magento database uses innoDB pretty heavily.

Is there anything I can do to get this disk space back?

share|improve this question

2 Answers 2

up vote 7 down vote accepted

I wrote up a CleanUp procedure for InnoDB in StackOverflow

http://stackoverflow.com/a/4056261/491757 (Oct 29, 2010)

Here it is

To shrink ibdata1 once and for all you must do the following:

1) MySQLDump all databases into a SQL text file (call it SQLData.sql)

2) Drop all databases (except mysql schema)

3) Shutdown mysql

4) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

5) Delete ibdata1, ib_logfile0 and ib_logfile1

At this point, there should only be the mysql schema in /var/lib/mysql

6) Restart mysql

This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each

7) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

  • mytable.frm (Storage Engine Header)
  • mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this many times in my career as a MySQL DBA

In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 500MB.

Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul. !!!

share|improve this answer
    
Would you mind cop+pasting it here ? –  Iain Jul 20 '12 at 19:29
    
@lain : Copied and Pasted as Requested –  RolandoMySQLDBA Jul 20 '12 at 19:32
    
That's great cheers –  Iain Jul 20 '12 at 19:34

Currently the only way to reclaim the space used by a dropped InnoDB database is to dump all the databases, stop MySQL, delete the underlying storage files, restart MySQL, and finally import the database dump.

The MySQL documentation has further information about this.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.