Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Is all the data InnoDB ?? –  RolandoMySQLDBA Mar 5 '13 at 14:55
add comment

migrated from stackoverflow.com Mar 5 '13 at 13:45

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 1 down vote accepted

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

Give it a Try !!!

share|improve this answer
    
Thanks i took approach 1. –  JCS Mar 6 '13 at 10:30
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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