• MySQL
  • Mysql space reduction procedure

    That ibdata1 isn't shrinking is a particularly annoying feature of MySQL. The ibdata1 file can´t actually be shrunk unless you delete all databases, remove the files and reload a dump.
    
    But you can configure MySQL so that each table, including its indexes, is stored as a separate file. In that way ibdata1 will not grow as large. According to Bill Karwin's comment this is enabled by default as of version 5.6 of MySQL.
    
    It was a while ago I did this. However, to setup your server to use separate files for each table you need to change my.cnf in order to enable this:
    
    [mysqld]
    innodb_file_per_table
    http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html
    
    As you want to reclaim the space from ibdata1 you actually have to delete the file:
    
    Do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases
    Drop all databases except the above 2 databases
    Stop mysql
    Delete ibdata1 and ib_log files
    Start mysql
    Restore from dump
    When you start MySQL in step 5 the ibdata1 and ib_log files will be recreated.
    
    Now you're fit to go. When you create a new database for analysis, the tables will be located in separate ibd* files, not in ibdata1. As you usually drop the database soon after, the ibd* files will be deleted.
    
    http://dev.mysql.com/doc/refman/5.1/en/drop-database.html
    
    You have probably seen this:
    http://bugs.mysql.com/bug.php?id=1341
    
    By using the command ALTER TABLE  ENGINE=innodb or OPTIMIZE TABLE  one can extract data and index pages from ibdata1 to separate files. However, ibdata1 will not shrink unless you do the steps above.
    

    Share this post


    About Bravo Web

    We're a web development company having over 15 years experience in custom website development, maintenance, e-commerce, and SEO.