1

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

store bin log replication position from the last stop slave:

mysql -u root -p -e "show slave status \G;" > status
  1. on the slave: service mysql restart
  2. on the slave: stop slave
  3. on the master: insert into t1 values (8,1)
  4. on the slave:

create database && import dump:

mysql -u root -p -e "create database test;"
mysql -u root -p -b test < test_slave.sql
  1. on the master: insert into t1 values (9,1)
  2. 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;
  1. 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,

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

  1. restore dump on Slave

mysql -u root -p < dump.sql

  1. start Slave

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

  • 1
    You could consider moving small tables (under 1MB) back into ibdata1: (1) set file_per_table=0; (2) ALTER TABLE ..ENGINE=InnoDB for small tables (3) set file_per_table=1. 5.7's tablespace-per-database provides an even better plan. – Rick James Feb 28 '17 at 20:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.