Shulou(Shulou.com)05/31 Report--

This article introduces you how to manually switch in Mysql MHA deployment, the content is very detailed, interested friends can refer to, I hope it can be helpful to you.

Mysql MHA deployment-manually switch

Architecture description:

Reference: http://www.zhaibibei.cn/mysql/mha/

1 check the existing status of the slave library (188223)

-show slave status\ G

2 View the management node log (222)

View the current status of MHA with the following command facts

Tail-f / etc/mha/manager/mha.log

3 shutting down the management process of MHA

[root@rac4 ~] # masterha_stop-conf=/etc/mha/mha.conf

MHA Manager is not running on mha (2:NOT_RUNNING).

4. Manual switching

The relevant commands are as follows:

Masterha_master_switch-master_state=alive-orig_master_is_new_slave-conf=/etc/mha/mha.conf

-master_state=alive representative tells MHA that the original master is still alive and does not need to be deleted from the configuration file

-orig_master_is_new_slave parameter indicates that the original master will automatically synchronize the new master

There are some other parameters as follows

-running_updates_limit if the write operation time of the main library exceeds this parameter, the switch will be exited

-interactive=0 represents direct confirmation. You do not need to enter YES

The switching process is as follows:

[root@rac4] # masterha_master_switch-master_state=alive-orig_master_is_new_slave-conf=/etc/mha/mha.conf

Sat Mar 14 22:17:37 2020-[info] MHA::MasterRotate version 0.56.

Sat Mar 14 22:17:37 2020-[info] Starting online master switch..

Sat Mar 14 22:17:37 2020-[info]

Sat Mar 14 22:17:37 2020-[info] * Phase 1: Configuration Check Phase..

Sat Mar 14 22:17:37 2020-[info]

Sat Mar 14 22:17:37 2020-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.

Sat Mar 14 22:17:37 2020-[info] Reading application default configuration from / etc/mha/mha.conf..

Sat Mar 14 22:17:37 2020-[info] Reading server configuration from / etc/mha/mha.conf..

Sat Mar 14 22:17:38 2020-[info] GTID failover mode = 1

Sat Mar 14 22:17:38 2020-[info] Current Alive Master: rac1 (192.168.2.187 Current Alive Master 3306)

Sat Mar 14 22:17:38 2020-[info] Alive Slaves:

Sat Mar 14 22:17:38 2020-[info] rac2 (192.168.2.188 Version=5.7.28-log) Version=5.7.28-log (oldest major version between slaves) log-bin:enabled

Sat Mar 14 22:17:38 2020-[info] GTID ON

Sat Mar 14 22:17:38 2020-[info] Replicating from 192.168.2.187 (192.168.2.187 Replicating from 3306)

Sat Mar 14 22:17:38 2020-[info] Primary candidate for the new Master (candidate_master is set)

Sat Mar 14 22:17:38 2020-[info] rac3 (192.168.2.223) Version=5.7.28-log (oldest major version between slaves) log-bin:enabled

Sat Mar 14 22:17:38 2020-[info] GTID ON

Sat Mar 14 22:17:38 2020-[info] Replicating from 192.168.2.187 (192.168.2.187 Replicating from 3306)

Sat Mar 14 22:17:38 2020-[info] Not candidate for the new Master (no_master is set)

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on rac1 (192.168.2.187)? (YES/no): YES

Sat Mar 14 22:19:01 2020-[info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..

Sat Mar 14 22:19:01 2020-[info] ok.

Sat Mar 14 22:19:01 2020-[info] Checking MHA is not monitoring or doing failover..

Sat Mar 14 22:19:01 2020-[info] Checking replication health on rac2..

Sat Mar 14 22:19:01 2020-[info] ok.

Sat Mar 14 22:19:01 2020-[info] Checking replication health on rac3..

Sat Mar 14 22:19:01 2020-[info] ok.

Sat Mar 14 22:19:01 2020-[info] Searching new master from slaves..

Sat Mar 14 22:19:01 2020-[info] Candidate masters from the configuration file:

Sat Mar 14 22:19:01 2020-[info] rac1 (192.168.2.187 rac1 3306) Version=5.7.28-log log-bin:enabled

Sat Mar 14 22:19:01 2020-[info] GTID ON

Sat Mar 14 22:19:01 2020-[info] rac2 (192.168.2.188 Version=5.7.28-log) Version=5.7.28-log (oldest major version between slaves) log-bin:enabled

Sat Mar 14 22:19:01 2020-[info] GTID ON

Sat Mar 14 22:19:01 2020-[info] Replicating from 192.168.2.187 (192.168.2.187 Replicating from 3306)

Sat Mar 14 22:19:01 2020-[info] Primary candidate for the new Master (candidate_master is set)

Sat Mar 14 22:19:01 2020-[info] Non-candidate masters:

Sat Mar 14 22:19:01 2020-[info] rac3 (192.168.2.223) Version=5.7.28-log (oldest major version between slaves) log-bin:enabled

Sat Mar 14 22:19:01 2020-[info] GTID ON

Sat Mar 14 22:19:01 2020-[info] Replicating from 192.168.2.187 (192.168.2.187 Replicating from 3306)

Sat Mar 14 22:19:01 2020-[info] Not candidate for the new Master (no_master is set)

Sat Mar 14 22:19:01 2020-[info] Searching from candidate_master slaves which have received the latest relay log events..

Sat Mar 14 22:19:01 2020-[info]

From:

Rac1 (192.168.2.187) (current master)

+-- rac2 (192.168.2.188 rac2 3306)

+-- rac3 (192.168.2.223)

To:

Rac2 (192.168.2.188) (new master)

+-- rac3 (192.168.2.223)

+-- rac1 (192.168.2.187 purl 3306)

Starting master switch from rac1 (192.168.2.187) to rac2 (192.168.2.188)? (yes/NO): yes

Sat Mar 14 22:19:32 2020-[info] Checking whether rac2 (192.168.2.188 is ok for the new master.. 3306)

Sat Mar 14 22:19:32 2020-[info] ok.

Sat Mar 14 22:19:32 2020-[info] rac1 (192.168.2.187 rac1): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.

Sat Mar 14 22:19:32 2020-[info] rac1 (192.168.2.187 rac1): Resetting slave pointing to the dummy host.

Sat Mar 14 22:19:32 2020-[info] * * Phase 1: Configuration Check Phase completed.

Sat Mar 14 22:19:32 2020-[info]

Sat Mar 14 22:19:32 2020-[info] * Phase 2: Rejecting updates Phase..

Sat Mar 14 22:19:32 2020-[info]

Sat Mar 14 22:19:32 2020-[info] Executing master ip online change script to disable write on the current master:

Sat Mar 14 22:19:32 2020-[info] / etc/mha/script/master_ip_online_change-- command=stop-- orig_master_host=rac1-- orig_master_ip=192.168.2.187-- orig_master_port=3306-- orig_master_user='monitor'-- orig_master_password='123456'-new_master_host=rac2-- new_master_ip=192.168.2.188-- new_master_port=3306-- new_master_user='monitor'- -new_master_password='123456'-orig_master_ssh_user=root-new_master_ssh_user=root-orig_master_is_new_slave

Sat Mar 14 22:19:32 2020 756531 Set read_only on the new master.. Ok.

Sat Mar 14 22:19:32 2020 761639 Set read_only=1 on the orig master.. Ok.

Sat Mar 14 22:19:32 2020 763740 Killing all application threads..

Sat Mar 14 22:19:32 2020 763785 done.

Disabling the VIP an old master: rac1

Eth0:2: ERROR while getting interface flags: No such device

Sat Mar 14 22:19:33 2020-[info] ok.

Sat Mar 14 22:19:33 2020-[info] Locking all tables on the orig master to reject updates from everybody (including root):

Sat Mar 14 22:19:33 2020-[info] Executing FLUSH TABLES WITH READ LOCK..

Sat Mar 14 22:19:33 2020-[info] ok.

Sat Mar 14 22:19:33 2020-[info] Orig master binlog:pos is mysql-bin.000009:194.

Sat Mar 14 22:19:33 2020-[info] Waiting to execute all relay logs on rac2 (192.168.2.188 Waiting to execute all relay logs on rac2 3306)

Sat Mar 14 22:19:33 2020-[info] master_pos_wait (mysql-bin.000009:194) completed on rac2 (192.168.2.188) Executed 0 events.

Sat Mar 14 22:19:33 2020-[info] done.

Sat Mar 14 22:19:33 2020-[info] Getting new master's binlog name and position..

Sat Mar 14 22:19:33 2020-[info] mysql-bin.000006:3938

Sat Mar 14 22:19:33 2020-[info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='rac2 or 192.168.2.188, MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx'

Sat Mar 14 22:19:33 2020-[info] Executing master ip online change script to allow write on the new master:

Sat Mar 14 22:19:33 2020-[info] / etc/mha/script/master_ip_online_change-- command=start-- orig_master_host=rac1-- orig_master_ip=192.168.2.187-- orig_master_port=3306-- orig_master_user='monitor'-- orig_master_password='123456'-new_master_host=rac2-- new_master_ip=192.168.2.188-- new_master_port=3306-- new_master_user='monitor'- -new_master_password='123456'-orig_master_ssh_user=root-new_master_ssh_user=root-orig_master_is_new_slave

Sat Mar 14 22:19:33 2020 227880 Set read_only=0 on the new master.

Enabling the VIP 192.168.2.189 on the new master: rac2

SIOCSIFADDR: No such device

Eth0:2: ERROR while getting interface flags: No such device

SIOCSIFNETMASK: No such device

Arping: Device eth0 not available.

Sat Mar 14 22:19:33 2020-[info] ok.

Sat Mar 14 22:19:33 2020-[info]

Sat Mar 14 22:19:33 2020-[info] * Switching slaves in parallel..

Sat Mar 14 22:19:33 2020-[info]

Sat Mar 14 22:19:33 2020-[info]-- Slave switch on host rac3 (192.168.2.223 started) started, pid: 12424

Sat Mar 14 22:19:33 2020-[info]

Sat Mar 14 22:19:34 2020-[info] Log messages from rac3.

Sat Mar 14 22:19:34 2020-[info]

Sat Mar 14 22:19:33 2020-[info] Waiting to execute all relay logs on rac3 (192.168.2.223 Waiting to execute all relay logs on rac3 3306)

Sat Mar 14 22:19:33 2020-[info] master_pos_wait (mysql-bin.000009:194) completed on rac3 (192.168.2.223) Executed 0 events.

Sat Mar 14 22:19:33 2020-[info] done.

Sat Mar 14 22:19:33 2020-[info] Resetting slave rac3 (192.168.2.223) and starting replication from the new master rac2 (192.168.2.188).

Sat Mar 14 22:19:33 2020-[info] Executed CHANGE MASTER.

Sat Mar 14 22:19:33 2020-[info] Slave started.

Sat Mar 14 22:19:34 2020-[info] End of log messages from rac3.

Sat Mar 14 22:19:34 2020-[info]

Sat Mar 14 22:19:34 2020-[info]-- Slave switch on host rac3 (192.168.2.223 succeeded) succeeded.

Sat Mar 14 22:19:34 2020-[info] Unlocking all tables on the orig master:

Sat Mar 14 22:19:34 2020-[info] Executing UNLOCK TABLES..

Sat Mar 14 22:19:34 2020-[info] ok.

Sat Mar 14 22:19:34 2020-[info] Starting orig master as a new slave..

Sat Mar 14 22:19:34 2020-[info] Resetting slave rac1 (192.168.2.187) and starting replication from the new master rac2 (192.168.2.188).

Sat Mar 14 22:19:34 2020-[info] Executed CHANGE MASTER.

Sat Mar 14 22:19:34 2020-[info] Slave started.

Sat Mar 14 22:19:34 2020-[info] All new slave servers switched successfully.

Sat Mar 14 22:19:34 2020-[info]

Sat Mar 14 22:19:34 2020-[info] * Phase 5: New master cleanup phase..

Sat Mar 14 22:19:34 2020-[info]

Sat Mar 14 22:19:34 2020-[info] rac2: Resetting slave info succeeded.

Sat Mar 14 22:19:34 2020-[info] Switching master to rac2 (192.168.2.188 completed successfully) completed successfully.

5. Handover phase

The current master is not writable when executing master_ip_online_change

The new main library is set to read-only

The old main library is set to read-only

Disable the original main library VIP

The old main library has a global lock

Get master information of the new main library

The new main library sets up VIP

New master library cancels read-only

Synchronize asynchronously from the library to the new master library

The original master library releases the global lock

Synchronize the original master library to the new master library

6. View the status after switching

-223

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.2.188

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 3938

Relay_Log_File: mysql-relay.000005

Relay_Log_Pos: 454

Relay_Master_Log_File: mysql-bin.000006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

-187

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: rac2

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 3938

Relay_Log_File: mysql-relay.000007

Relay_Log_Pos: 454

Relay_Master_Log_File: mysql-bin.000006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

.

7. Data testing

-188:

Mysql > use jumptest

Mysql > insert into cjc01 values (3000)

Query OK, 1 row affected (0.02 sec)

Mysql > select * from cjc01

+-+

| | id |

+-+

| | 100 |

| | 3 |

| | 3000 |

+-+

3 rows in set (0.00 sec)

-187:

Mysql > select * from cjc01

+-+

| | id |

+-+

| | 100 |

| | 3 |

| | 3000 |

+-+

3 rows in set (0.01sec)

-223:

Mysql > select * from cjc01

+-+

| | id |

+-+

| | 100 |

| | 3 |

| | 3000 |

+-+

3 rows in set (0.01sec)

So much for sharing about how to switch manually in Mysql MHA deployment. I hope the above content can be helpful to you and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.