Rebuild a mysql slave adding innodb_file_per_table without locking the master

Question

I would like to rebuild a mysql slave, adding innodb_file_per_table to the configuration in order to reduce the disk space used (idbdata1 has grown a lot). I want to do it without locking the master's tables and I sketched up a procedure that seems to work, but I would like to know if it is really accurate. I tested this on two vms.

Here are the configurations for the test:

On the master:

server-id               = 10
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = test
#binlog_ignore_db       = include_database_name

On the slave:

server-id               = 20
log_bin                 = /var/log/mysql/mysql-bin.log
relay_log               = /var/log/mysql/mysql-relay-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = test

Before starting the procedure:

On the slave:

Seconds_Behind_Master: 0

At this point, this is the procedure I tried to rebuild the slave with the new configuration and then let him resume the replication where it stopped

1. on the slave: stop slave
2. on the master: insert into t1 values (4,1) # I insert values in tables t1 in database test to validate that the slave is able to recover the replication data that occured during the process
3. on the slave: mysqldump -u root -p --opt test > test_slave.sql
4. on the master: insert into t1 values (5,1)
5. on the slave: drop database test
6. on the master: insert into t1 values (6,1)
7. on the slave:

change configuration to set innodb_file_per_table:

innodb_file_per_table
innodb_flush_method=O_DIRECT

8. on the master: insert into t1 values (7,1)
9. on the slave:

store bin log replication position from the last stop slave:

mysql -u root -p -e "show slave status \G;" > status

10. on the slave: service mysql restart
11. on the slave: stop slave
12. on the master: insert into t1 values (8,1)
13. on the slave:

create database && import dump:

mysql -u root -p -e "create database test;"
mysql -u root -p -b test < test_slave.sql

14. on the master: insert into t1 values (9,1)
15. on the slave:

reconfigure the replication to start from the last stop slave position (with the master_log_pos and master_log_file stored in the status file):

change master to MASTER_HOST='db01',MASTER_USER='myuser',MASTER_PASSWORD='mypasswd',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=477;

16. on the slave: start slave

After the 16th step, all inserts made on the master during the process appear in test.t1 on the slave and the replication is working.

               Slave_IO_State: Waiting for master to send event
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
             Master_Server_Id: 10

It appears to be successful. However, if someone sees a pitfall in this procedure (that I can't see on this limited lab environment) it would be great to share :)

Thank you,

Answer 1

really all could be shorter:

1. Stop mysql on Slave, change settings, Start MySQL on Slave
2. Login to MySQL; Stop slave; exit from mysql client
3. make dump from Master direct from Slave:

mysqldump --host=Master_IP --port=Master_port -u user_with_enought_rights -p --single-transaction --all-databases --master-data=1 > dump.sql

4. restore dump on Slave

mysql -u root -p < dump.sql

5. start Slave

It will not lock master (in case of InnoDB tables) and automatically adjust Slave to Master position