Rebuild a mysql slave adding innodb_file_per_table without locking the master 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


on the slave: stop slave
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
on the slave: mysqldump -u root -p --opt test &gt; test_slave.sql
on the master: insert into t1 values (5,1)
on the slave: drop database test
on the master: insert into t1 values (6,1)
on the slave:


change configuration to set innodb_file_per_table:

innodb_file_per_table
innodb_flush_method=O_DIRECT



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


store bin log replication position from the last stop slave:

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



on the slave: service mysql restart
on the slave: stop slave
on the master: insert into t1 values (8,1)
on the slave:


create database &amp;&amp; import dump:

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



on the master: insert into t1 values (9,1)
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;



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,
really all could be shorter:


Stop mysql on Slave, change settings, Start MySQL on Slave
Login to MySQL; Stop slave; exit from mysql client
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



restore dump on Slave



  mysql -u root -p &lt; dump.sql



start Slave


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