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 responses to “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

  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
    
  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.