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 are using the Following:

  • MySQL 5.1.63-community-log MySQL Community Server (GPL)
  • OS : Red Hat Enterprise Linux Server release 5.4

We have three servers and all servers are enabled with binary logging option

  • Server A : Running master on it.
  • Server B : it is slave of Server A but also enable binary logs
  • Server C : it is slave of Server B.

Our goal to change master server of server c from server B to server A.

  • Is that possible in run time without any data corruption?
  • What are prerequisites for this?
  • How can we achieve this?
share|improve this question

migrated from stackoverflow.com Apr 3 '13 at 16:03

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

1 Answer 1

Step 01

On ServerB, run the following commands

STOP SLAVE;
SET GLOBAL innodb_max_dirty_pages_pct = 0;
FLUSH TABLES;

Step 02

On ServerC, run SHOW SLAVE STATUS\G

Repeat running SHOW SLAVE STATUS\G until Seconds_Behind_Master is 0

Then, run SET GLOBAL innodb_max_dirty_pages_pct = 0;

Step 03

On ServerB, run SHOW SLAVE STATUS\G

For the sake of example, let say SHOW SLAVE STATUS\G looks like this:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.64.68.253
                Master_User: replusername
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.003202
        Read_Master_Log_Pos: 577991837
             Relay_Log_File: relay-bin.010449
              Relay_Log_Pos: 306229695
      Relay_Master_Log_File: mysql-bin.003202
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 577991837
            Relay_Log_Space: 306229695
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0

Please note the following:

  • Master_Host (10.64.68.253)
  • Master_User (replusername)
  • Relay_Master_Log_File (mysql-bin.003202)
  • Exec_Master_Log_Pos (577991837)

Step 04

On ServerC, run the following:

STOP SLAVE;
SET GLOBAL innodb_max_dirty_pages = 0;
FLUSH TABLES;
CHANGE MASTER TO
MASTER_HOST='10.64.68.253',
MASTER_PORT=3306,
MASTER_USER='replusername',
MASTER_PASSWORD='replpassword',
MASTER_LOG_FILE='mysql-bin.003202',
MASTER_LOG_POS=577991837;
START SLAVE;

Step 05

On ServerC, run SHOW SLAVE STATUS\G

If Seconds_Behind_Master is a Number, CONGRATULATIONS !!!

Step 06

On ServerB, run SET GLOBAL innodb_max_dirty_pages_pct = 90;

On ServerC, run SET GLOBAL innodb_max_dirty_pages_pct = 90;

Give it a Try !!!

CAVEAT

If the majority of your data is MyISAM, ignore all commands that change innodb_max_dirty_pages_pct.

share|improve this answer
1  
I agree with everything here (+1), except perhaps the recommendation to set innodb_max_dirty_pages_pct so high. IMO it should be lower to ease innodb's checkpoint. Especially if the OP doesn't run the innodb plugin and has no other alternative to tuning checkpoints. –  Derek Downey Apr 3 '13 at 18:59
    
@DerekDowney since it's MySQL 5.1, I was using the default value of 90. MySQL 5.5 uses 75 but InnoDB caches so well that it becomes an afterthought. +1 on our checkpoint comment. –  RolandoMySQLDBA Apr 3 '13 at 19:06

Your Answer

 
discard

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