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][1] !!!
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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

enable mysql bin logs
create lvm snapshot
mark master status
-
start 2'nd mysql instance from snapshot
dump all data
-
start new mysql instance with innodb_file_per_table=1 in new data_dir
start import of dumped data
start replication from master
-
when slave=master stop both servers
move slave data_dir to /var/lib/mysql and start main mysql instance.

LET’S BEGIN:

Preparation:

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

1
2

log-bin=/var/lib/mysql/mysqld-bin
server-id=1

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:

1

service mysql restart

After restart in mysql directory should appear such files:

1
2

-rw-rw----    1 mysql mysql 1021 Jul 26 10:20 mysqld-bin.000001
-rw-rw----    1 mysql mysql   41 Jul 26 10:20 mysqld-bin.index

create user for slave replication:

1

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'127.0.0.1' IDENTIFIED BY 'password';

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:

1

iptables -I INPUT -i eth0 -p tcp --dport 3306 -j REJECT

Connect to mysql and flush all data to disk:

1
2

mysql> Flush tables with read lock;
mysql> show master status;

it should look like this:

1
2
3
4
5
6
7

mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000001 | 18848349 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

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

1

lvcreate -s -n mysql_snap -L 50G /dev/VolGroup/Volume

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

1

mysql> unlock tables

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

1

iptables -D INPUT 1

Starting 2’nd MySQL from snapshot:

mount snapshot:

1

mount /dev/VolGroup/mysql_snap /mnt

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

1

cp /etc/my.cnf /etc/my2.cnf

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.

1

/usr/bin/mysqld_safe --defaults-file=/etc/my2.cnf --datadir=/mnt/var/lib/mysql --socket=/mnt/var/lib/mysql/mysql.sock --log-error=/mnt/var/lib/mysql/mysqld.log --pid-file=/mnt/var/lib/mysql/mysqld.pid --user=mysql --port=1234

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