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?
migrated from stackoverflow.com Mar 5 '13 at 13:45
This question came from our site for professional and enthusiast programmers.
There are two approaches you can try with no or minimal downtime
Given the following:
APPROACH #1 : Data is 100% InnoDB
This is very straightforward.
STEP01) If the Master does not have
STEP02) If the Master does not have
STEP03) If you have do Steps 1 and/or 2 on the Master, do
STEP04) Create MySQL Replication User on the Master
STEP05) Create a mysqldump as a point-in-time snapshot on the Master
When done, line 22 of
STEP06) Create replication status on the Slave with
STEP 07) Load the mysqldump into the Slave
Don't worry about replication starting at the right place. Remember, I said line 22 contains the command with correct binary log and position.
STEP 08) Run
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 !!!
Your approach is fine.
There are 2 possible pitfalls:
Do you know about innobackex tool which can helps to migrate data?
It has an option
Hope it helps