login: root

changing mysql to innodb_file_per_table=1 with lvm and replication almost without downtime

This conversion was made with mysql 5.5, but it should work with older versions, as replications on mysql were introduced since mysql 3.23.15.

Don’t forget the Disclaimer !!!
It requires more than the basic knowledge in linux, lvm and mysql. I do NOT take ANY responsibility for Your data loss!!!

Long story short:



lets enable relay logs on main mysql instance. Add lines in my.cnf

logbin – place where to keep master binlogs
server-id – unique ID per replication servers (for example master=1, slave=2, 2’nd slave=3 etc.)

Restart mysql:

After restart in mysql directory should appear such files:

create user for slave replication:

replication instance will run on localhost, and it will connect to master mysql by using replication user and ‘password’.

Preparing snapshot for MySQL:

advice: you may open few consoles to server, to be able to paste commands quicker, as MySQL downtime depends on how quick those steps are done.

Reject new connections to master mysql:

Connect to mysql and flush all data to disk:

it should look like this:

Don’t forget to note master status somewhere, as data will be used on mysql slave server

-s – create snapshot
-n – snapshot name
-L – snapshot size
/dev/VolGroup/Volume – Volume, that snapshot is created from (mysql data_dir should be on it)
You can view all info with vgs and lvs commands (Volume name, free space left on Volume Group)

After creating snapshot, You can enable all connections to mysql:

remove iptables rule, that it wouldn’t drop connections any more:

Starting 2’nd MySQL from snapshot:

mount snapshot:

create separate /etc/my.cnf file, that it wouldn’t compromise master binlog files:

and remove the lines that would compromise the master mysql (remove binlog lines, change the lines pointing to the master mysql path, snapshot) and save the file.

Start 2’nd MySQL:
I strongly suggest running this mysql instance in screen as internet outages may disturb all of Your work.

change paths relative to Your mounted mysql dir, parameters should be self-explanatory.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: