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.

We currently have 3 MySQL Servers running;

  • the first which contains production data and is due to be retired

    mysqld Ver 5.5.28-0ubuntu0.12.04.2 for debian-linux-gnu on x86_64 ((Ubuntu))

    mysql> SHOW MASTER STATUS; Empty set (0.00 sec)

  • the second which contains out of date snapshot of production data and is to take over from the first.

    mysqld Ver 5.5.28-0ubuntu0.12.04.3-log for debian-linux-gnu on x86_64 ((Ubuntu))

    Master Status

  • the third which is already a slave of the second server.

    mysqld Ver 5.5.29-0ubuntu0.12.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))

    mysql> SHOW MASTER STATUS; Empty set (0.01 sec)

My question is would I go about setting up the following?:

First Server (master) -> Second Server (slave) -> Third Server (slave)

Tutorial links welcome! I tried to Google, but I'm sure I've gotten the wrong keywords

share|improve this question
    
1) How much data are we talking ? 2) How much downtime are you willing to accept? 3) Are all three DB servers running the same version of MySQL ? –  RolandoMySQLDBA Jan 31 '13 at 16:21
    
50MB, server 1 can't go down for more than a minute, server 2/3 are expendable for now, No. –  Jharwood Jan 31 '13 at 16:39
    
OK you said 50MB. Do you have binary logging enabled on the first server ??? –  RolandoMySQLDBA Jan 31 '13 at 16:41
    
doesn't appear to have it running –  Jharwood Jan 31 '13 at 16:43
    
Please run SHOW MASTER STATUS; on all three DB Servers and tell me what you see. –  RolandoMySQLDBA Jan 31 '13 at 16:44

1 Answer 1

up vote 1 down vote accepted

Based on our chat conversation, here is what was discussed

  • Server1 is Stand Alone
  • Server2 is a Master
  • Server3 is a Slave to Server2

This implies that binary logging is enabled in Server2.

To Make Server1 a Master of Server2, perform the following:

STEP 01 : On Server2, add this to /etc/my.cnf

[mysqld]
log-slave-updates

STEP 02 : On Server3, run STOP SLAVE;

STEP 03 : On Server2, run service mysql restart

STEP 04 : On Server3, run START SLAVE;

STEP 06 : On Server1, add this to /etc/my.cnf

[mysqld]
log-bin=mysql-bin

STEP 07 : On Server1, run service mysql restart

STEP 08 : Set Replication From Server1 to Server2

See Clarification about master slave configuration in mysql

OPTIONAL

Once you have MySQL Replication Going From Server1 to Server2 to Server3, your can properly load all data into all three MySQL Instances by doing the following on Server1:

mysqldump -u... -p... --all-databases --routines --triggers > mysqldata.sql
mysql -u... -p... < mysqldata.sql

This will do three(3) things

  • Repopulate everything into Server1
  • MySQL Replication will handle populating Server2 from Server1
  • MySQL Replication will handle populating Server3 from Server2

Since your data is 50MB in total, this should be execute very quickly.

share|improve this answer

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.