Change to innodb_file_per_table in MySQL securely

Why would you change

A lot of people experience problems with innodb and MySQL, if they do not set the tablespace to use a file per table. This is due to one single file holding all the data of your tables and databases, and this can cause a lot of problems. The ibdata1 file which holds the data can never get smaller, only bigger, and this is why you suddenly may have to change to innodb_file_per_table to keep your server alive.

There is a lot of other advantages like management, flexibility, storage opportunities, and ability to handle your data much easier in general.

Beside my other articles, this is on a CentOS machine.

Prepare

First of all we stop the services which can make your MySQL database inconsistent, in my case i shut down everything which have something to do with the web server (nginx/php-fpm/varnish):

Then i make sure there is no connections active, by checking open processes from php-fpm, and eventually log into your mysql prompt to look up some information (connections from status, and full processlist):

Dump all databases

When everything is OK, we dump our database with the mysqldump tool. Make sure you have space enough, for a lot of data. I dump to a file i call all.sql, with verbose output, so we know what is going on:

$ mysqldump --verbose -u root -p password --all-databases > all.sql

You could eventually use a & in the end to make the process run int he background.

When done, you should have your file in the directory you were running the mysqldump commando from:

$ ls -hl all.sql
-rw-r--r-- 1 root root 83G Jun 24 01:58 all.sql

Do the configuration

Now we close the MySQL service, so we are ready to do a bit of changes:

$ /etc/init.d/mysql stop

Now we enable innodb_file_per_table in our MySQL configuration by appending the following string into it. I use Vim, $ vi /etc/my.cnf:

innodb_file_per_table = 1

Change the name of your current datadir (/var/lib/mysql), which holds all your MySQL data. When just changing the name of the directory, we can always go back. I call mine mysql_old:

$ mv /var/lib/mysql /var/lib/mysql_old

Now we are ready to turn on MySQL again:

$ /etc/init.d/mysql start

When MySQL is running again, we are ready to create the new datadir, and later restore from the dump. We will run the mysql_secure_installation tool, to make the directory:

$ mysql_secure_installation

To keep your MySQL users from your old data, copy the mysql-database from the old directory to the new:

$ cp -R /var/lib/mysql_old/mysql /var/lib/mysql/

Restore

With all your our old users etc. from the mysql-database, innodb_file_per_table enabled, the dump, and the old datadir for backup, your should be ready to restore your databases from the dump, into your new datadir with innodb_file_per_table.

Be sure nothing external is running when you restore, as i got problems with an external backup system.

What is happening is that we run it with verbose output, so we now what is going on, and then we write 2> output to a error-log. We specify that with 2>, and this is because 1> (or >) is for stdout, and 2> is for stderr. To merge these two, you have maybe seen this before: 2>&1. This will send stderr to stdout.

You could eventually use a & in the end to make the process run int he background.

To keep an eye open for what is going on, as a restore can take a very long time, you could use the following two lines (one to check the processlist with mysqladmin, and one to follow your error-log: