MySQL: ibdata files do not shrink on database deletion [innodb]


One very interesting thing I noticed with MySQL was that if you delete a database, ibdata file doesn’t shrink by that much space to minimize disk usage. I deleted the database and checked usage of /usr/local/mysql/var folder and noticed that ibdata file is still the same size. So the problem I face now is, how do I claim back this space?

After searching for a bit on google about this problem, apparently only way you can do that is by exporting your mysql databases, delete ibdata1 file, import databases. This creates new ibdata file with correct space usage. Atleast there is a way to get around this issue. But honestly, too much pain on production boxes where we might be trying to remove old databases to reclaim some of the hard drive space.

An preventive measure one can use is to use option: innodb_file_per_table (‘put innodb_file_per_table’ in your my.cnf file under [mysqld] section). This will create individual files for tables under database directory. So now when I delete the database, all the space is returned since the directory is now deleted along with database along with all the tables inside the directory. In my test after you put option innodb_file_per_table your my.cnf, you will have to still do export/import to be able to minimize disk usage and have the ability to delete database at your leisure without worrying about reclaiming the disk space. Here are the steps I took. DISCLAIMER: Please make backup of your data and use following steps at your own risk. Doing it on test server is HIGHLY recommended. Please don’t come back and tell me that you lost your data because you followed my steps. They work for me and they may not work for you!

That said, here are the steps:

  1. Add innodb_file_per_table in your my.cnf file under [mysqld] section
  2. run following commands at the prompt. Your path to binaries might be different.
  3. #note: following assumes you are logged in as root
    mkdir -p /temp #temp dir to save our sql dump
    #lets make a backup of current database. -p is used if there is pw set
    /usr/local/mysql/bin/mysqldump -R -q -p --all-databases > /temp/all.sql
    #stop mysql so we can remove all the files in the dir
    /etc/init.d/mysql stop
    rm -fr /usr/local/mysql/var/* #remove all the files
    /usr/local/mysql/bin/mysql_install_db #install default dbs
    #change ownership so mysql user can read/write to/from files
    chown -R mysql.mysql /usr/local/mysql/var/
    #start mysql so we can import our dump
    /etc/init.d/mysql start
    #note there is no -p since defaults don't have mysql pw set
    /usr/local/mysql/bin/mysql < /temp/all.sql
    /etc/init.d/mysql restart

This should be all you need to do. At this point when you remove a database, it will delete the directory of the db and all the data contained within which in turn will give you your disk space back.

REMEMBER: Backup your data and be smart about using code found on internet. If you don’t know what you are doing, hire a consultant who does.


26 Responses to “ MySQL: ibdata files do not shrink on database deletion [innodb] ”

  1. June 26th, 2007 | 10:33 am

    Worked pretty awesomely! After restoring the data, I had some issues with authenticating to the server, so I logged in to MySQL and flushed the privileges, then restarted MySQL:

    $ mysql -u root -p
    Enter password:
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    mysql> quit
    Bye
    $ /etc/init.d/mysql restart

    Thanks for the help!

  2. Chris Wiegand
    September 7th, 2007 | 10:41 am

    You can also do a ‘ALTER TABLE blah ENGINE=MyISAM; ALTER TABLE blah ENGINE=InnoDB;’ and it will effectively recreate the table in InnoDB (although you’ll need to script out any InnoDB features that aren’t supported in MyISAM like foreign keys and reapply then).

  3. September 17th, 2007 | 12:31 am

    Hi all!

    Very interesting information! Thanks!

    G’night

  4. Dario Souza
    September 18th, 2007 | 8:16 pm

    Better yet, do an ALTER TABLE ENGINE=InnoDB; directly without converting it to/from myisam. That prevents you from losing foreign key constraints and some datatype conversions (int(12) to int(10) for instance) when you convert it to myisam.

  5. Me
    October 9th, 2007 | 12:12 am

    Seems to be even easier: just add that option to my.cnf and run “mysqloptimize –all-databases” and all your InnoDB tables should be converted to .idb files. Next, I’ll try to remove/unconfigure the ibdata file(s).

  6. Marijorge
    October 26th, 2007 | 9:30 am

    Hi,
    I´ve understood about the procedure above. But I have a question. Can I change the directory of creation .ibd files?
    Thanks

  7. October 26th, 2007 | 5:44 pm

    Along the lines of what “Me” said… Make the change to my.cnf, restart mysql, but then run a query similar to:
    OPTIMIZE NO_WRITE_TO_BINLOG TABLE [table1, table2 ... etc]

    This will optimize all your tables and recreate the ibd files under their respective db directories.

    I’m not sure at this point if you could just delete the main ibdata file because I didn’t try. Anyone else care to give it a shot?

  8. carl
    November 23rd, 2007 | 7:09 am

    you can’t delete the ibdata* files because they include innodb’s shared resources, in addition to any tables that might be stored there. if you delete those files, then your tables that you moved to their own files will no longer work.

  9. Brill Pappin
    February 29th, 2008 | 3:27 pm
  10. May 30th, 2008 | 7:45 am

    Thank-you; a life saver. The only issue I has was I needed to add the -p on the import step and enter an empty password. Well, that, and I nearly ran out of disk space trying to backup the DB. I’m down from 65% use to 20% used; again thanks for the powerfully helpful page.

    Good success to all,
    Earnie – http://livingeasynetwork.com

  11. September 2nd, 2008 | 11:31 am

    Read Cory’s note above.
    If you use phpMyAdmin you can easily mark the tables you want and choose to optimize them. This’ll clear the extra space abused by the ibd file.
    Don’t forget to add innodb_file_per_table in your my.cnf file under [mysqld] section and restart the server.

  12. adejo joseph
    January 8th, 2009 | 5:06 am

    good day sir/ma.i am joseph adejo from nigeria. a student of aptech world wide .i will love to know more about D.B.A becouse i have an interest to pursue it as a career.
    please i will need guide lines from you to know how and what to be doing as an upcoming D.B.A.
    THANKS ADEJO JOSEPH

  13. June 11th, 2009 | 7:17 am

    after deleting large number of records or dropping tables you should optimize the tables (to defrag)

    any new inserts will be inserted in the empty space made available by the previous drop/delete statements, so I guess there is not need to worry about data file size

  14. maria
    June 15th, 2009 | 9:12 am

    How to obtain the data from ibdata1, ib_logfile0 and ib_logfile1?

  15. Mike
    September 11th, 2009 | 11:11 am

    So what if I copied or moved all my .ibd files, then

    ALTER TABLE tbl_name DISCARD TABLESPACE;

    then did a full mysqldump, erased the huge innodb data file, then restored from the dump, then tried to import the .ibd files with

    ALTER TABLE tbl_name IMPORT TABLESPACE;

  16. vlastikw
    September 16th, 2009 | 12:07 pm

    Seems to be even easier: just add that option to my.cnf(my.ini) and run “mysqlcheck -u root –optimize –all-databases” and all your InnoDB tables should be converted to .idb files. Next, I’ll try to remove/unconfigure the ibdata file(s).

  17. joe
    September 21st, 2009 | 3:28 am

    Hi,
    I follow all the instructions: I have now the database directory with .idb files, but… I still have the idbdata1 big and growing, now is about 5 time the data. How is it possible that exists both idbdata1 and *.idb files, working together?
    REgards,
    JS

  18. May 17th, 2010 | 3:18 am

    [...] I found a basic step-by-step for taking care of this here: MySQL: ibdata files do not shrink on database deletion [innodb] | Technology: Learn and Share And at some other forums I also found that it is probably safe to move and symlink ibdata1, but it [...]

  19. August 2nd, 2010 | 1:38 pm

    [...] via MySQL: ibdata files do not shrink on database deletion [innodb] | Technology: Learn and Share. [...]

  20. September 6th, 2010 | 8:32 am

    How about performances?
    Is there any (public) comparison ?

    tnx in advance
    marco f.

  21. Karl Schmidt
    May 7th, 2011 | 5:00 pm

    There is another way — kind of sketchy, but it works for me YMMV.

    Change all the table types to myisam

    Verify there are no innodb tables left.

    turn of innodb with skip-innodb

    remove innodb files

    turn innodb back on

    Change table types back to innodb

    It is a real mess, but can be done on a working server with a short term risk due to the lack of ACID ..

    This has me looking at

  22. Karl Schmidt
    May 7th, 2011 | 5:02 pm

    This has me looking at postgresql

  23. Starting Gate Solutions
    August 10th, 2011 | 10:21 pm

    Just delete all your ib* files (after you backup of course) then restart and you’ll be good

  24. April 1st, 2014 | 10:21 pm

    Remarkable! Its actually remarkable piece of writing, I have got
    much clear idea regarding from this post.

  25. April 5th, 2014 | 8:50 pm

    Woah! I’m really enjoying the template/theme of this site.
    It’s simple, yet effective. A lot of times it’s very difficult to get that “perfect balance” between user
    friendliness and visual appearance. I must say that you’ve done a amazing job
    with this. In addition, the blog loads super quick for me
    on Firefox. Outstanding Blog!

  26. April 10th, 2014 | 1:02 pm

    Hello, I read your new stuff daily. Your humoristic style is witty, keep up the good work!

Leave a reply

*
To prove that you're not a bot, enter this code
Anti-Spam Image