I am currently setting up a replication for my database one of the ways i am thinking of synchronizing the master and slave without write locking the master, is by first taking a log position then perform a backup, then restore the backup on the slave and start the slave with the log position(which will be before the backup was taken), one of the issues i see with this approach is the slave will be reapplying changes to it self which is not a problem in the type of system we have, does any one else see any other pitfalls with this approach? Or are there any other better options to achieve this?

  • Is all the data InnoDB ?? Commented Mar 5, 2013 at 14:55

2 Answers 2


There are two approaches you can try with no or minimal downtime

Given the following:

  • Master IP is
  • Slave IP is

APPROACH #1 : Data is 100% InnoDB

This is very straightforward.

STEP01) If the Master does not have server-id defined in my.cnf you will have to add it


STEP02) If the Master does not have log-bin defined in my.cnf you will have to add it


STEP03) If you have do Steps 1 and/or 2 on the Master, do service mysql restart (mandatory)

STEP04) Create MySQL Replication User on the Master

TO repluser@'' IDENTIFIED BY 'replpass';

STEP05) Create a mysqldump as a point-in-time snapshot on the Master

MYSQL_CONN="-uroot -ppassword"
MYSQLDUMP_OPTIONS="--master-data=1 --single-transaction --flush-privileges"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines --triggers --all-databases"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > MySQLData.sql 

When done, line 22 of MySQLData should have the binary log and position of the Master as of the moment the mysqldump was launched. To see it, just run

head -22 MySQLData.sql | tail -1

STEP06) Create replication status on the Slave with


STEP 07) Load the mysqldump into the Slave

mysql -u... -p... < MySQLData.sql 

Don't worry about replication starting at the right place. Remember, I said line 22 contains the command with correct binary log and position.


If Slave_IO_Running is Yes and Slave_SQL_Running is Yes, CONGRATULATIONS !!!

APPROACH #2 : Data is InnoDB/MyISAM Mix

Rather than reinvent the wheel, please read my earlier posts on using rsync to make a Slave

Give it a Try !!!

  • @RolandoMySQLDBA: For Approach #1, what happens if the dump takes many hours to make, but the master database continues to change while the dump is being made? Is mysqldump "smart" enough to ensure that the dump only contains the data that was present before the dump was initiated?
    – rinogo
    Commented Sep 2, 2015 at 17:48
  • 1
    @rinogo It's smart enough because of the use of -single-transaction Commented Sep 2, 2015 at 17:49
  • 1
    @rinogo See the MySQL Documentation on --single-transaction (dev.mysql.com/doc/refman/5.6/en/…) Commented Sep 2, 2015 at 17:52
  • 2
    @rinogo Based on that Documentation, make sure you don't run any DDL during the mysqldump. Otherwise, it will trigger an implicit commit and then the dump becomes inconsistent from that point on. Commented Sep 2, 2015 at 17:54
  • As always, excellent response, Rolando! Thanks so much! :)
    – rinogo
    Commented Sep 2, 2015 at 17:56

Your approach is fine.

There are 2 possible pitfalls:

  1. If you are using your slave in read-only mode than you should not forget to switch reading;
  2. After you perform migration and start slave your slave will require some time to reach the master current state (monitor seconds_behind_the_master). It can be fast or slow but you should remember about it. Time depends on 1) TPL (transactions per sec) 2) slave config;
    1. Before you start your slave do not forget to turn of applying logs from master to make changes in master config and only after it start slave.

Do you know about innobackex tool which can helps to migrate data? It has an option apply-log to apply all binlogs before your replica goes to prod. You just apply-log on folder with your replica and after it's done you can

  • stop server
  • move it to mysql datadir
  • start server with option -skip-slave-start
  • change master position

Hope it helps

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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