MySQL Replication without stopping master

Question

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?

Answer 1

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 !!!

Answer 2

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