HOWTO: Reconfigure MySQL to use innodb_file_per_table with zero downtime

By
minaev
-
1235

InnoDB is a very good storage engine for MySQL that combines reasonable performance with wide popularity and, as a consequence, a good set of tools for diagnostics and fine-tuning. One of its downsides is that it is inefficient when it comes to the disk space management. While an extent of HDD space was added to the storage, InnoDB will not return it back even when you delete tables or databases. To add some flexibility, you should use innodb_file_per_table option. Unfortunately, if you have a running database, you cannot just enable this option. You will have to make a dump of the database and restore it on a new instance of MySQL with the option enabled from the very beginning. This scenario means that the database will be inaccessible from the moment you start mysqldump to the moment you finish restoring the data in the new instance. Is there a way to minimize the downtime?

Yes, you can run mysqldump on a backup of your database. But, then you lose the data written to the database from the moment you make the backup to the moment the new instance is ready. But that’s a bit closer to the solution. You can also set up replication between the original database and the new one and then, when the new instance catches up with the old one, your task is completed. And the backup can be done online, without stopping MySQL, if you use Xtrabackup tool by Percona.

 

 

So, the basic steps you have to follow are:

  • Configure your original database as master.
  • Make a backup of the original database using Xtrabackup.
  • Restore the backup and run a second instance of MySQL.
  • Run mysqldump on the second instance.
  • Stop the second instance, but do not delete it yet.
  • Create a new database and start the third instance of MySQL with the enabled option innodb_file_per_table.
  • Restore the dump by feeding it into the third instance of MySQL.
  • Configure the third instance as slave and run the replication.
  • When the initial replication finishes and the slave catches up with the master, reconfigure your clients to use the new instance.
  • That’s it. You can stop the first instance now and delete it.

Now, the same steps in more details.

Preparation

Create directories for the new database:

$ mkdir /var/lib/mysql2
$ mkdir /var/log/mysql2

Configure database as master

Configure your old server as master by adding the following to my.cnf:

[mysqld]
server-id = 1
log_bin   = /var/log/mysql/mysql-bin.log
expire_logs_days = 1
max_binlog_size  = 100M

Actually, you can change most of these settings on the fly, using SET GLOBAL command. The only thing you cannot do so, is to enable binlogs. So, unless you have this option enabled, you will have to restart MySQL and this will be the only time when your database will be inaccessible (and the word “zero” in the title will be a lie, then). If binlogs are already enabled in your configuration, though, the downtime will really be zero.

Now, create a MySQL user necessary for replication by issuing MySQL command:

grant replication slave on *.* to 'slave1'@'127.0.0.1' identified by
'ZZZZZZZZZZZ';

Backup the database and restore it

$ innobackupex-1.5.1 --defaults-file=/etc/mysql/my.cnf --user=root 
--password=XXXXXXXX --no-timestamp /backup/full
$ innobackupex-1.5.1 --apply-log /backup/full/
$ chown -R mysql:mysql /backup/full
$ /usr/sbin/mysqld --basedir=/usr --datadir=/backup/full/ 
--user=mysql --pid-file=/var/run/mysqld/mysqld2.pid 
--socket=/var/run/mysqld/mysqld2.sock 
--innodb_log_group_home_dir=/backup/full/ --port=3307

The first command performs the backup. The second command adds to the backup the data written to the database while the backup was in progress. The third command changes ownership of the backup files. The fourth command runs the second instance of MySQL using the backed up data as the datadir. The instance uses port 3307 for communication. Note also that we try not to interfere with the first instance, by using different socket and pid files.

Somewhere in the end of the output produced by the first command you will find two important things: the name of the binlog file and position in it. You’ll need these values to set up replication on the slave database. Or, you can find the same values in the file xtrabackup_binlog_info located in the backup directory:

$ cat /backup/full/xtrabackup_binlog_info
mysql-bin.000001        3874

Dump the data

$ mysqldump -uroot -p --port=3307 --protocol=TCP --quick --all-databases >dump.sql

No comment.

Now, shut down the second database:

$ mysqladmin -uroot -p --port=3307 --protocol=TCP shutdown

Create new database

$ mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql2/

Create new configuration file

$ cp /etc/mysql/my.cnf /etc/mysql/my2.cnf

Add the following lines to the new file:

innodb_file_per_table = 1
server-id = 2

I have to admit I’m not sure if this step is important, because all the necessary options may be given in the command line, as we’ll do in the second step.

Run the third instance of MySQL

$ mysqld --defaults-file=/etc/my/my2.cnf --basedir=/usr 
--datadir=/var/lib/mysql2 --user=mysql 
--pid-file=/var/run/mysqld/mysqld2.pid 
--socket=/var/run/mysqld/mysqld2.sock --port=3307 
--server-id=2 --innodb_file_per_table

Be careful to indicate other directories, so the two instances do not grow into each other, like Siam twins.

Restore the data

$ mysql -uroot -p --port=3307 --protocol=TCP 

	Start replication

	Issue the following MySQL commands in the third instance:
change master to master_host='127.0.0.1', master_user='slave1',
master_password='ZZZZZZZZ', master_port=3306,
master_log_file='mysql-bin.000001', master_log_pos=3874;
start slave;



	The values for master_log_file and master_log_pos are those we got on the first step, either from the output of innobackupex script or from xtrabackup_binlog_info file.



	That's it. Now, you can reconfigure the client software to use the database on TCP port 3307, and it will continue operating seamlessly.



	Then, issue the command:


$ mysql -uroot -p -e "show slave statusG"|grep Seconds



	When MySQL will reply with Seconds_Behind_Master: 0, you can shut the original database down and delete its files. Some years later, when you have, say, three minutes when you can afford to stop MySQL, you can move its files to the original /var/lib/mysql directory and switch to port 3306.



	You can find some more articles on administration of MySQL and Linux in my blog DM@Work.

RELATED ARTICLESMORE FROM AUTHOR