ITPub博客

首页 > 数据库 > MySQL > MHA在线切换

MHA在线切换

原创 MySQL 作者:goolen 时间:2014-11-20 17:34:44 0 删除 编辑
环境:
Master:192.168.100.48
Slave1:192.168.100.50
Slave2:192.168.100.141 (MHA管理节点)

+++停掉MHA监控
[root@test11g masterha]# masterha_stop --conf=/etc/masterha/mha.cnf

+++切换:
[root@test11g masterha]# masterha_master_switch --conf=/etc/masterha/mha.cnf --master_state=alive --new_master_host=192.168.100.50 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
Thu Nov 20 16:54:22 2014 - [info] MHA::MasterRotate version 0.56.
Thu Nov 20 16:54:22 2014 - [info] Starting online master switch..
Thu Nov 20 16:54:22 2014 - [info] 
Thu Nov 20 16:54:22 2014 - [info] * Phase 1: Configuration Check Phase..
Thu Nov 20 16:54:22 2014 - [info] 
Thu Nov 20 16:54:22 2014 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Nov 20 16:54:22 2014 - [info] Reading application default configuration from /etc/masterha/mha.cnf..
Thu Nov 20 16:54:22 2014 - [info] Reading server configuration from /etc/masterha/mha.cnf..
Thu Nov 20 16:54:22 2014 - [info] GTID failover mode = 0
Thu Nov 20 16:54:22 2014 - [info] Current Alive Master: 192.168.100.48(192.168.100.48:3306)
Thu Nov 20 16:54:22 2014 - [info] Alive Slaves:
Thu Nov 20 16:54:22 2014 - [info]   192.168.100.50(192.168.100.50:3306)  Version=5.5.18-log (oldest major version between slaves) log-bin:enabled
Thu Nov 20 16:54:22 2014 - [info]     Replicating from 192.168.100.48(192.168.100.48:3306)
Thu Nov 20 16:54:22 2014 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu Nov 20 16:54:22 2014 - [info]   192.168.100.141(192.168.100.141:3306)  Version=5.5.18-log (oldest major version between slaves) log-bin:enabled
Thu Nov 20 16:54:22 2014 - [info]     Replicating from 192.168.100.48(192.168.100.48:3306)

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.100.48(192.168.100.48:3306)? (YES/no): YES
Thu Nov 20 16:54:39 2014 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Thu Nov 20 16:54:39 2014 - [info]  ok.
Thu Nov 20 16:54:39 2014 - [info] Checking MHA is not monitoring or doing failover..
Thu Nov 20 16:54:39 2014 - [info] Checking replication health on 192.168.100.50..
Thu Nov 20 16:54:39 2014 - [info]  ok.
Thu Nov 20 16:54:39 2014 - [info] Checking replication health on 192.168.100.141..
Thu Nov 20 16:54:39 2014 - [info]  ok.
Thu Nov 20 16:54:39 2014 - [info] 192.168.100.50 can be new master.
Thu Nov 20 16:54:39 2014 - [info] 
From:
192.168.100.48(192.168.100.48:3306) (current master)
 +--192.168.100.50(192.168.100.50:3306)
 +--192.168.100.141(192.168.100.141:3306)

To:
192.168.100.50(192.168.100.50:3306) (new master)
 +--192.168.100.141(192.168.100.141:3306)
 +--192.168.100.48(192.168.100.48:3306)

Starting master switch from 192.168.100.48(192.168.100.48:3306) to 192.168.100.50(192.168.100.50:3306)? (yes/NO): yes
Thu Nov 20 16:54:51 2014 - [info] Checking whether 192.168.100.50(192.168.100.50:3306) is ok for the new master..
Thu Nov 20 16:54:51 2014 - [info]  ok.
Thu Nov 20 16:54:51 2014 - [info] 192.168.100.48(192.168.100.48:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Thu Nov 20 16:54:51 2014 - [info] 192.168.100.48(192.168.100.48:3306): Resetting slave pointing to the dummy host.
Thu Nov 20 16:54:51 2014 - [info] ** Phase 1: Configuration Check Phase completed.
Thu Nov 20 16:54:51 2014 - [info] 
Thu Nov 20 16:54:51 2014 - [info] * Phase 2: Rejecting updates Phase..
Thu Nov 20 16:54:51 2014 - [info] 
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes
Thu Nov 20 16:55:40 2014 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Thu Nov 20 16:55:40 2014 - [info] Executing FLUSH TABLES WITH READ LOCK..
Thu Nov 20 16:55:40 2014 - [info]  ok.
Thu Nov 20 16:55:40 2014 - [info] Orig master binlog:pos is mysql-bin.000005:1267.
Thu Nov 20 16:55:40 2014 - [info]  Waiting to execute all relay logs on 192.168.100.50(192.168.100.50:3306)..
Thu Nov 20 16:55:40 2014 - [info]  master_pos_wait(mysql-bin.000005:1267) completed on 192.168.100.50(192.168.100.50:3306). Executed 0 events.
Thu Nov 20 16:55:40 2014 - [info]   done.
Thu Nov 20 16:55:40 2014 - [info] Getting new master's binlog name and position..
Thu Nov 20 16:55:40 2014 - [info]  mysql-bin.000004:264
Thu Nov 20 16:55:40 2014 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.100.50', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=264, MASTER_USER='backup', MASTER_PASSWORD='xxx';
Thu Nov 20 16:55:40 2014 - [info] 
Thu Nov 20 16:55:40 2014 - [info] * Switching slaves in parallel..
Thu Nov 20 16:55:40 2014 - [info] 
Thu Nov 20 16:55:40 2014 - [info] -- Slave switch on host 192.168.100.141(192.168.100.141:3306) started, pid: 28984
Thu Nov 20 16:55:40 2014 - [info] 
Thu Nov 20 16:55:40 2014 - [info] Log messages from 192.168.100.141 ...
Thu Nov 20 16:55:40 2014 - [info] 
Thu Nov 20 16:55:40 2014 - [info]  Waiting to execute all relay logs on 192.168.100.141(192.168.100.141:3306)..
Thu Nov 20 16:55:40 2014 - [info]  master_pos_wait(mysql-bin.000005:1267) completed on 192.168.100.141(192.168.100.141:3306). Executed 0 events.
Thu Nov 20 16:55:40 2014 - [info]   done.
Thu Nov 20 16:55:40 2014 - [info]  Resetting slave 192.168.100.141(192.168.100.141:3306) and starting replication from the new master 192.168.100.50(192.168.100.50:3306)..
Thu Nov 20 16:55:40 2014 - [info]  Executed CHANGE MASTER.
Thu Nov 20 16:55:40 2014 - [info]  Slave started.
Thu Nov 20 16:55:40 2014 - [info] End of log messages from 192.168.100.141 ...
Thu Nov 20 16:55:40 2014 - [info] 
Thu Nov 20 16:55:40 2014 - [info] -- Slave switch on host 192.168.100.141(192.168.100.141:3306) succeeded.
Thu Nov 20 16:55:40 2014 - [info] Unlocking all tables on the orig master:
Thu Nov 20 16:55:40 2014 - [info] Executing UNLOCK TABLES..
Thu Nov 20 16:55:40 2014 - [info]  ok.
Thu Nov 20 16:55:40 2014 - [info] Starting orig master as a new slave..
Thu Nov 20 16:55:40 2014 - [info]  Resetting slave 192.168.100.48(192.168.100.48:3306) and starting replication from the new master 192.168.100.50(192.168.100.50:3306)..
Thu Nov 20 16:55:40 2014 - [info]  Executed CHANGE MASTER.
Thu Nov 20 16:55:40 2014 - [info]  Slave started.
Thu Nov 20 16:55:40 2014 - [info] All new slave servers switched successfully.
Thu Nov 20 16:55:40 2014 - [info] 
Thu Nov 20 16:55:40 2014 - [info] * Phase 5: New master cleanup phase..
Thu Nov 20 16:55:40 2014 - [info] 
Thu Nov 20 16:55:40 2014 - [info]  192.168.100.50: Resetting slave info succeeded.
Thu Nov 20 16:55:40 2014 - [info] Switching master to 192.168.100.50(192.168.100.50:3306) completed successfully.

+++检查是否切换成功:
+++主库:
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      264 |              |                  |
+------------------+----------+--------------+------------------+

+++从库上:
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.50
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 264
               Relay_Log_File: coredb-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

+++测试主从复制是否正常:
主库上:
mysql> use test;
Database changed
mysql> create table goolen2(id int);
Query OK, 0 rows affected (0.13 sec)

mysql> insert into goolen2 values(1120);
Query OK, 1 row affected (0.00 sec)

+++查询从库:
[root@coredb ~]# mysql -uroot -p -h 192.168.100.48 -e "select * from test.goolen2"
Enter password: 
+------+
| id   |
+------+
| 1120 |
+------+

[root@coredb ~]# mysql -uroot -p -h 192.168.100.141 -e "select * from test.goolen2"  
Enter password: 
+------+
| id   |
+------+
| 1120 |
+------+


来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/23249684/viewspace-1340697/ ,如需转载,请注明出处,否则将追究法律责任。

下一篇: MHA状况检查
请登录后发表评论 登录
全部评论

注册时间:2011-10-20

  • 博文量
    121
  • 访问量
    731656