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

$ /etc/init.d/php-fpm stop
$ /etc/init.d/nginx stop
$ /etc/init.d/varnish stop

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

mysql> show status like '%onn%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Aborted_connects         | 8      |
| Connections              | 124789 |
| Max_used_connections     | 42     |
| Ssl_client_connects      | 0      |
| Ssl_connect_renegotiates | 0      |
| Ssl_finished_connects    | 0      |
| Threads_connected        | 5      |
+--------------------------+--------+

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

To restore from the dump, run the following command:

$ mysql --verbose -u root -p password < all.sql 2> mysqldump_errors.log

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:

$ mysqladmin -u root -p password -i 1 processlist
$ tail -f mysqldump_errors.log

When the dump is ready, start all your services again:

$ /etc/init.d/mysqld start
$ /etc/init.d/php-fpm start
$ /etc/init.d/nginx start
$ /etc/init.d/varnish start

Alternative MySQL datadir

I needed to create an alternative datadir (nfs/iscsi), to have space for everything. In case you need to this aswell, this was how i did it:

First i stop all services i connection to this:

$ /etc/init.d/mysqld stop
$ /etc/init.d/php-fpm stop
$ /etc/init.d/nginx stop
$ /etc/init.d/varnish stop

Then i copy (recursively) everything from my datadir our to a new datadir:

$ cp -R /var/lib/mysql/* /var/lib/mysql.new/

Now we enable write our new datadir and socket location in our MySQL configuration by changing the following lines to your new destination. I use Vim, $ vi /etc/my.cnf:

datadir                 = /var/lib/mysql.new
socket                  = /var/lib/mysql.new/mysql.sock

If you get problems with your mysql.sock, create a symlink from your old dir to your new dir:

$ ln -s /var/lib/mysql/mysql.sock /var/lib/mysql.new/mysql.sock

That should be it. Start all your services again:

$ /etc/init.d/mysqld start
$ /etc/init.d/php-fpm start
$ /etc/init.d/nginx start
$ /etc/init.d/varnish start