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!
Script:
#!/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