0

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?
0

1 Answer 1

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.

2
  • 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. Commented Apr 3, 2013 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. Commented Apr 3, 2013 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.