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!

Character Sets and Collations

PREVIOUS

Enable Remote Access (Grant)

NEXT

Comments

RavanH

October 6, 2013

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. RavanH
  
  October 6, 2013
  
  Plus: maybe at the start of this how-to refer to the useful mysql user/password tip on http://rtcamp.com/wordpress-nginx/tutorials/mysql/mysqltuner/ to make all run as smoothly as it did for me 🙂
  1. Rahul Bansal
    
    October 14, 2013
    
    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. Rahul Bansal
  
  October 14, 2013
  
  Sorry but mysql.server was carried from previous tutorial – http://rtcamp.com/tutorials/mysql/mysql-5-6-ubuntu-12-04/
  
  I updated tutorial to use commands with mysql only as we are using percona-mysql now and it did not rename mysql binary to mysql.server
Jan

October 27, 2013

Nice Tutorial. There’s a typo in the mysqldump, though, “-events” should probably be “–events”.
1. Rahul Bansal
  
  October 28, 2013
  
  Yep. It was a typo. Thanks for correcting me. 🙂
ipeacocks

December 10, 2013

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

gutschik

February 27, 2014

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

gutschik

February 27, 2014

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

Rahul Bansal

February 28, 2014

Thanks for your tip. Might help others. 🙂

Robert Nader

February 27, 2014

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

Rahul Bansal

February 28, 2014

Thanks for your codes. 🙂

It will be better to use https://gist.github.com/ or http://pastebin.com/ or some service likt that to paste code snippets. 🙂

I just hope WordPress did not changed your codes!

RTJ

March 2, 2014

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.
1. Rahul Bansal
  
  March 3, 2014
  
  Please post exact error message. Better post it on http://dba.stackexchange.com/

Features

Useful Links

Product

Other Products

GoDAM

Subscribe for curated product updates, tips, and announcements.

"*" indicates required fields

Company

This field is for validation purposes and should be left unchanged.

Email*

SIGN ME UP