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!
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
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 🙂
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.
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 renamemysql
binary tomysql.server
Nice Tutorial. There’s a typo in the mysqldump, though, “-events” should probably be “–events”.
Yep. It was a typo. Thanks for correcting me. 🙂
>>>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
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
forgot to escape…
Thanks for your tip. Might help others. 🙂
Works like a charm! Thanks.
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!
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.
Please post exact error message. Better post it on http://dba.stackexchange.com/