Enable innodb_file_per_table

Note: You may wish to convert MyISAM tables to InnoDB tables before you proceed.

innodb_file_per_table is by default ON Mysql 5.6.6 and onwards. There is plenty of stuff on Google about pros & cons ofinnodb_file_per_table.

This post details how to enable innodb_file_per_table on an existing database. Because innodb_file_per_table affects new tables only, created after innodb_file_per_table is enabled, we need to recreate old databases to force innodb_file_per_table on old tables and reclaim some disk space.

Backup First

Create a dir to take backups:

cd ~
mkdir backup
cd backup

Copy mysql data files (raw)

If all goes well, we will not need this.

For better results, shut down PHP and other apps/scripts which update mysql. You can keep Nginx running and server non-logged in visitors cached content.

service mysql stop && cp -ra /var/lib/mysql mysqldata && service mysql start

Take mysqldump

As soon as above line completes, take a mysqldump of all databases

mysqldump --routines --events --flush-privileges --all-databases > all-db.sql

Drop Databases

Create a sql file to drop all databases EXCEPT mysql database

mysql -e "SELECT DISTINCT CONCAT ('DROP DATABASE ',TABLE_SCHEMA,' ;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA <> 'mysql' AND TABLE_SCHEMA <> 'information_schema';" | tail -n+2 > drop.sql

Verify if drop.sql has correct database names and then execute drop.sql queries.

mysql < drop.sql

Verify all InnoDB tables gone

SELECT table_name, table_schema, engine FROM information_schema.tables WHERE engine = 'InnoDB';

Remove InnoDB files

Stop mysql server first

service mysql stop

Then

rm /var/lib/mysql/ibdata1 && rm /var/lib/mysql/ib_logfile0 && rm /var/lib/mysql/ib_logfile1

At this point most likely you will have only /var/lib/mysql/mysql directory only.

Enable innodb_file_per_table

Open my.cnf file

vim /etc/mysql/my.cnf

Add following lines

innodb_file_per_table = 1
innodb_file_format = barracuda

Time to import from mysqldump

Start mysql server now

service mysql start

Run mysql import

mysql < all-db.sql

Force mysql_upgrade (to generate performance_schema)

mysql_upgrade --force

That’s All!

16 thoughts on “Enable innodb_file_per_table

  1. Excellent tutorial. Thanks!

    Some small remarks:

    — On my Debian 7 a command like this:

    service mysql.server stop && cp -ra /var/lib/mysql mysqldata && service mysql.server start

    results in :

    mysql.server: unrecognized service

    Simply removing the ‘.server’ from that statement resolves the issue 🙂

    — There is a ‘-‘ missing in front of ‘-events’ in this line:

    mysqldump --routines -events --flush-privileges --all-databases > all-db.sql

      1. I did not know that somebody will find it so useful. I always have that kind of file on server.

        There is no point in hiding mysql password once root-access to server is compromised! 😉

        I will move that portion to a dedicated page so it be used in other cases as well.

  2. >>>Add following lines

    innodb_file_per_table = 1
    innodb_file_format = barracuda

    Add following lines in [mysqld] section, not in the end of config

    [mysqld]
    #
    # * Basic Settings
    #
    user = mysql
    pid-file = /var/run/mysqld/mysqld.pid
    socket = /var/run/mysqld/mysqld.sock
    port = 3306
    basedir = /usr
    datadir = /var/lib/mysql
    tmpdir = /tmp
    lc-messages-dir = /usr/share/mysql
    skip-external-locking
    innodb_file_per_table = 1

  3. thanks for the tutorial !
    in the drop statements, you might wanna wrap the db-names in accent marks. I got an error with dasherized db-names

    mysql -e "SELECT DISTINCT CONCAT ('DROP DATABASE `',TABLE_SCHEMA,'` ;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA <> 'mysql' AND TABLE_SCHEMA <> 'information_schema';" | tail -n+2 > drop.sql
    
    1. forgot to escape…

      mysql -e "SELECT DISTINCT CONCAT ('DROP DATABASE \`',TABLE_SCHEMA,'\` ;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA <> 'mysql' AND TABLE_SCHEMA <> 'information_schema';" | tail -n+2 > drop.sql
      
  4. Works like a charm! Thanks.

    
    #!/bin/bash -x
    
    if test "$USER" != "root" ; then
      echo "Must be root ..."
      exit 1
    fi
    
    mysql_dba=root
    mysql_pass=your_mysql_dba_password
    
    mkdir /var/lib/mysql.bak
    cd /var/lib/mysql.bak
    service mysql stop && cp -ra /var/lib/mysql mysqldata && service mysql start
    mysqldump -u$mysql_dba -p$mysql_pass --routines --events --flush-privileges --all-databases > all-db.sql
    mysql -u$mysql_dba -p$mysql_pass -e "SELECT DISTINCT CONCAT ('DROP DATABASE ',TABLE_SCHEMA,' ;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA <> 'mysql' AND TABLE_SCHEMA <> 'information_schema';" | tail -n+2 > drop.sql
    cat drop.sql
    mysql -u$mysql_dba -p$mysql_pass < drop.sql
    echo "SELECT table_name, table_schema, engine FROM information_schema.tables WHERE engine = 'InnoDB';" > innotables.sql
    mysql -u$mysql_dba -p < innotables.sql
    service mysql stop
    rm /var/lib/mysql/ibdata1 && rm /var/lib/mysql/ib_logfile0 && rm /var/lib/mysql/ib_logfile1
    cp -p /etc/mysql/my.cnf /etc/mysql/my.cnf.date +%Y.%m.%d.%H.%M.%S
    sed -e 's/([mysqld])/\1\ninnodb_file_per_table = 1\ninnodb_file_format = barracuda /' /etc/mysql/my.cnf > /tmp/my.cnf
    mv /tmp/my.cnf /etc/mysql/my.cnf
    service mysql start
    mysql -u$mysql_dba -p$mysql_pass < all-db.sql
    mysql_upgrade -u$mysql_dba -p$mysql_pass --force
    # eof
    
  5. Great article – thanks! However I am running into an issue…

    I follow all the steps, and when I go to the step to import the sql data at the end, mysql errors out and says that a mysql.innodb_something_index_something (sorry can’t remember the exact table) tablespace needs to be truncated or something like that. How can I get passed this error? I’ve been stuck for hours trying various things. Thanks in advance.

Comments are closed on this article.
Please use our free support forum for support and discussions.