MySQL Replication without stopping master

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?

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

Given the following:

• Master IP is 10.1.20.30

• Slave IP is 10.1.20.40

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

[mysqld]
server-id=100

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

[mysqld]
log-bin=mysql-bin

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

mysql> GRANT SELECT,REPLICATION USER,REPLICATION CLIENT ON *.*
TO repluser@'10.1.2.30' 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

CHANGE MASTER TO
MASTER_HOST='10.1.20.30',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;

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.

STEP 08) Run SHOW SLAVE STATUS\G

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

• Jul 08, 2011 - MySQL slave replication reset with no Master Downtime (using MyISAM)

• May 23, 2011 - How can I move a database from one server to another?

• Apr 08, 2011 - Create a MySQL slave from another slave, but point it at the master

Give it a Try !!!

StackExchange Database Administrators Q#35977, answer score: 13