Shulou(Shulou.com)06/01 Report--

This article mainly introduces the use of masterha_master_switch tools to achieve automatic switching of MySQL high-availability MHA, the content of the article is carefully selected and edited by the author, with a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand the use of masterha_master_switch tools to achieve automatic switching of MySQL high-availability MHA bar.

Introduction: masterha_master_switch is integrated into the MHA package as a switching tool

Installation: after compiling and installing MHA manager, the binary executable program masterha_master_switch is generated in / usr/local/bin/.

Use:

Masterha_master_switch-- helpUsage: # For master failover masterha_master_switch-- master_state=dead-- global_conf=/etc/masterha_default.cnf-- conf=/usr/local/masterha/conf/app1.cnf-- dead_master_host=host1 # For online master switch masterha_master_switch-- master_state=alive-- global_conf=/etc/masterha_default.cnf-- conf=/usr/local/masterha/conf/app1.cnf See online reference (http://code.google.com/p/mysql-master-ha/wiki/masterha_master_switch) for details.

Here, I am used to putting a set of master and slave configurations in app1.cnf and changing them to business-related names, such as mainBusiness.cnf

Analysis:

Goal: to obtain the online switching logic environment of masterha_master_switch: MHA manager 192.168.1.8 MHA node1+MySQL5.7+GTID 192.168.1.109+PORT3109 master MHA node1+MySQL5.7+GTID 192.168.1.110+PORT3110 slave

Configuration file content:

Manager_workdir=/data/mha/mainBusiness # set the working directory of MHA manager_log=/data/mha/mainBusiness/manager.log # log output of MHA manager remote_workdir=/data/mha/ # working directory master_binlog_dir= / data/mysql/3109/log/ on the preset MHA node side / data/mysql/3110/log/ # binlog directory on the default MHA node side # secondary_check_script= masterha_secondary_check-s 192.168.1.109-s 192.168.1.110 Secondary_check_script= masterha_secondary_check-s 192.168.1.109-s 192.168.1.110-- user=root-- master_host=192.168.1.109-- master_port=3109 ping_interval=1 # set the MHA manager detection interval (1 second) [server1] hostname=MySQL-Cent7-IP001109ip=192.168.1.109port=3109ssh_user=rootssh_port=22candidate_master=1 # set whether the node can be promoted as the main node 1 Yes, 0 No check_repl_delay=0 # check the master-slave backwardness of this instance after a failure, 0 No, 1 is whether the node can be promoted to master by setting [server2] hostname=MySQL-Cent7-IP001110ip=192.168.1.110port=3110ssh_user=rootssh_port=22candidate_master=1 # 1 Yes, 0 No check_repl_delay=0 # check the backwardness of the master and slave of this instance after a failure, 0 No 1 is executed on the MHA manager side: $masterha_master_switch-- master_state=alive-- conf=/etc/mha/mainBusiness.cnf-- orig_master_is_new_slave#--master_state indicates online switching #-- orig_master_is_new_slave specifies that the original master is attached to the new master as the slave library

The MHA manager output is as follows

# output segment # [info] MHA::MasterRotate version 0.57. [info] Starting online master switch.. # start switching [info] [info] * Phase 1: Configuration Check Phase.. online # Phase 1, check the configuration [info] [warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.. # I don't use global parameter files here, so some errors will be skipped. It doesn't matter [info] Reading application default configuration from / etc/mha/mainBusiness.cnf.. # the program reads the configuration [info] Reading server configuration from / etc/mha/mainBusiness.cnf.. [info] GTID failover mode = 1 # enable GTID failover mode [info] Current Alive Master: MySQL-Cent7-IP001109 (192.168.1.109 info 3109) # list the current master instance [info] Alive Slaves: # list the current surviving slave instances [info] MySQL-Cent7-IP001110 (192.168.1.110 log-bin:enabled 3110) Version=5.7.19-log (oldest major version between slaves) log-bin:enabled [info] GTID ON # from the GTID mode [info] Replicating from 192.168.1.109 (192.168.1.109) [info] Primary candidate for The new Master (candidate_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 MySQL-Cent7-IP001109 (192.168.1.109)? (YES/no): # for transaction consistency considerations, the program asks whether to temporarily close non-transactional tables

The original master,3109 end is displayed as follows

# output segment 1##SET wait_timeout=86400; # set connection timeout to prevent SELECT @ @ global.server_id As Value; SELECT VERSION () AS Value when switching # get server-idSELECT @ @ global.gtid_mode As Value; # get whether you use GTID replication SHOW GLOBAL VARIABLES LIKE 'log_bin'; # check whether you have enabled binlogSHOW MASTER STATUS; # get your own transaction execution SELECT @ @ global.datadir AS Value # get the storage location of your own data file SELECT @ @ global.slave_parallel_workers AS Value; # check whether multithreaded replication SHOW SLAVE STATUS; # is used to obtain the transaction execution SELECT @ @ global.read_only As Value when you get yourself as a slave library # get whether you have enabled read-only SELECT @ @ global.relay_log_purge As Value; # check whether you have enabled automatic relay log removal

The original slave,3110 end is displayed as follows

SELECT @ @ global.server_id As Value;SELECT VERSION () AS Value;SELECT @ @ global.gtid_mode As Value;SHOW GLOBAL VARIABLES LIKE 'log_bin';SHOW MASTER STATUS;SELECT @ @ global.datadir AS Value;SELECT @ @ global.slave_parallel_workers AS Value;SHOW SLAVE STATUS;SELECT @ @ global.read_only As Value;SELECT @ @ global.relay_log_purge As Value;SELECT @ @ global.relay_log_info_repository AS Value # where there is difference, get your own relay information preservation form (table) SELECT Relay_log_name FROM mysql.slave_relay_log_info; # difference, get the name of the relay file you are using, SELECT @ @ global.datadir AS Value; SHOW SLAVE STATUS;SELECT Repl_slave_priv AS Value FROM mysql.user WHERE user = 'repl'; # difference, and check whether the replication user has replication permission.

The first part is a summary:

Read the configuration file to confirm the master-slave relationship and replication mode; according to the master-slave relationship replication mode, connect to the master library: set the necessary parameters, obtain the replication details / connect the slave library: get the replication details, obtain relay information, obtain repl account and confirm permissions

After connecting the output of the MHA manager to confirm the input of yes

The output on the MHA manager side is as follows:

# output segment # [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time.. [info] ok. # Note that although the non-transaction table is closed here, the related statement [info] Checking MHA is not monitoring or doing failover.. does not appear in the packet capture statement above # check whether MHA is working, and require MHA manager to stop running [info] Checking replication health on MySQL-Cent7-IP001110.. when switching. # check the master-slave health level [info] ok. [info] Searching new master from slaves.. # start selecting a new master [info] Candidate masters from the configuration file: # list candidate slave library [info] MySQL-Cent7-IP001109 (192.168.1.109 info) Version=5.7.19-log log-bin:enabled [info] GTID ON # check GTID open [info] MySQL-Cent7-IP001110 (192.168.1. 110info 3110) Version=5.7.19-log (oldest major version between slaves) log-bin:enabled [info] GTID ON # check GTID open [info] Replicating from 192.168.1.109 (192.168.1.109 log-bin:enabled 3109) [info] Primary candidate for the new Master (candidate_master is set) [info] Non-candidate masters: [info] Searching from candidate_ Master slaves which have received the latest relay log events.. [info] # selects the latest relay log from all slave libraries as the new master library From:MySQL-Cent7-IP001109 (192.168.1.109) (current master) +-- MySQL-Cent7-IP001110 (192.168.1.110) To:MySQL-Cent7-IP001110 (192.168.1.110) (new master) +-- MySQL-Cent7-IP001109 (192.168.1.109: 3109) Starting master switch from MySQL-Cent7-IP001109 (192.168.1.109) to MySQL-Cent7-IP001110 (192.168.1.110)? (yes/NO): # Program asks if it is possible to switch

The master,3109 side is displayed as follows

# output segment 2##USE `unknown_ database`; FLUSH NO_WRITE_TO_BINLOG TABLES;-- refresh the table and cache it to the hard disk without recording this entry to binlogSELECT GET_LOCK ('MHA_Master_High_Availability_Monitor',' 0') AS Value -- add an analog lock to prevent the problem of switching programs from being multi-open. Such programs think that if they fail to obtain a lock with the same name, they will fail to exit. # GET_LOCK (str,time)-- str is the name of the lock, 0 indicates a persistent lock, and multiple GET_LOCKSHOW PROCESSLIST with different names can exist after 5.7.5.

The original slave,3110 end is displayed as follows

# output segment 2##USE `unknown_ database`; SELECT GET_LOCK ('MHA_Master_High_Availability_Failover',' 0') AS Value;SHOW SLAVE STATUS;SHOW SLAVE STATUS

The second part is a summary:

1. After confirming that the closed table can be saved, refresh the table and cache it to disk to avoid losing data, and add an analog lock to prevent the switching program from opening too much to cause switching exception 2. Select the slave library with the latest data from the candidate library and set it as the master library to switch to

After receiving the output from MHA manager to confirm that it can be switched.

The output on the MHA manager side is as follows:

# output segment # [info] Checking whether MySQL-Cent7-IP001110 (192.168.1.110 is ok for the new master.. [info] ok. # check whether the new main library selected in the previous step can really become the main [info] MySQL-Cent7-IP001109 (192.168.1.109 SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host. # did not detect the residual information of the original master (now used as a slave) as a slave library, regardless of whether 3721 first hung it on an empty master [info] MySQL-Cent7-IP001109 (192.168.1.109 Resetting slave pointing to the dummy host): Resetting slave pointing to the dummy host. [info] * * Phase 1: Configuration Check Phase completed. [info] # configuration check phase ends [info] * Phase 2: Rejecting updates Phase.. [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): # the switchover program detected that the address of the host virtual IP address switching is not defined. If only the master-slave identity is switched, the application will also be written to the original master, which needs to be set as read-only. Ask if you really want to switch, here we just do the switching experiment, first observe the output of the next two instances, and then switch directly.

The original master,3109 end is displayed as follows

USE `unknown_ database`; FLUSH NO_WRITE_TO_BINLOG TABLES;-refresh table cache to disk to avoid synchronous data loss SELECT GET_LOCK ('MHA_Master_High_Availability_Monitor',' 0') AS Value; SHOW PROCESSLIST;SHOW SLAVE STATUS;CHANGE MASTER TO MASTER_HOST='dummy_host' # use the original owner as a slave to switch to a trumped-up host SHOW SLAVE STATUS;RESET SLAVE / *! 50516 ALL * /; # try to clear the remaining slave attribute information. If the host is not the master, you need it! SELECT RELEASE_LOCK ('MHA_Master_High_Availability_Monitor') As Value

The original slave,3110 end is displayed as follows

USE `unknown_ database`; SELECT GET_LOCK ('MHA_Master_High_Availability_Failover',' 0') AS Value;SHOW SLAVE STATUS;SHOW SLAVE STATUS;SHOW PROCESSLIST

The third part summarizes:

1. Check the details of the selected slave library to confirm that it can really be used as the new master library 2. Deal with the data comparison between slave libraries. If there is only one master and slave, the original master database will be linked to a trumped-up empty master first. If there are more than two slave libraries, you need to clean up the original slave records of the slave database.

Output confirmation from MHA manager, and after forced handover:

The output on the MHA manager side is as follows:

# output segment # [info] Locking all tables on the orig master to reject updates from everybody (including root): [info] Executing FLUSH TABLES WITH READ LOCK.. [info] ok. All tables prohibit writing [info] Orig master binlog:pos is 3109binlog.000070:536. [info] Waiting to execute all relay logs on MySQL-Cent7-IP001110 (192.168.1.110 Waiting to execute all relay logs on MySQL-Cent7-IP001110). [info] master_pos_wait (3109binlog.000070:536) completed on MySQL-Cent7-IP001110 (192.168.1.110 info 3110). Executed 0 events. [info] done.# will apply to itself transactions that have been obtained from the library but haven't had time to execute, and other slave libraries [info] Getting new Master`s binlog name and position.. [info] 3110binlog.000049:536 [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='MySQL-Cent7-IP001110 or 192.168.1.110, MASTER_PORT=3110, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx'; [info] Setting read_only=0 on MySQL-Cent7-IP001110 (192.168.1.110). [info] ok. # that is to switch all other slave libraries to the new master with the statement of change master, that is, the original slave library [info] [info] * Switching slaves in parallel.. [info] # is about to switch all slave libraries [info] Unlocking all tables on the orig master: [info] Executing UNLOCK TABLES.. [info] ok. # release the table lock of the original main library [info] Starting orig master as a new slave.. [info] Resetting slave MySQL-Cent7-IP001109 (192.168.1.109 Resetting slave MySQL-Cent7-IP001109) and starting replication from the new master MySQL-Cent7-IP001110 (192.168.1.110 and starting replication from the new master MySQL-Cent7-IP001110). [info] Executed CHANGE MASTER. [info] Slave started. [info] All new slave servers switched successfully. [info] [info] * Phase 5: New master cleanup phase.. [info] # the master and slave information of the new slave library will be cleaned up soon Switch to the main library [info] MySQL-Cent7-IP001110: Resetting slave info succeeded. [info] Switching master to MySQL-Cent7-IP001110 (192.168.1.110) completed successfully. # switching completed

The new slave,3109 side is displayed as follows:

# output segment 4##USE ``; SELECT CONNECTION_ID () AS Value;SET wait_timeout=86400;SET GLOBAL read_only=1; # set yourself to read-only SHOW MASTER STATUS;UNLOCK TABLES # release the table lock CHANGE MASTER TO MASTER_HOST='192.168.1.110', MASTER_PORT=3110, MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_AUTO_POSITION=1; # point yourself to the new master START SLAVE;SHOW SLAVE STATUS; # difference selected by MHA, and launch the slave identity SELECT RELEASE_LOCK ('MHA_Master_High_Availability_Failover') As Value # release the concurrent lock of the same application

The new master,3110 side is displayed as follows:

# output segment 4##SHOW SLAVE STATUS;SELECT MASTER_POS_WAIT ('3109 binlog.000070) AS Result; # check whether you have finished executing the differential transaction STOP SLAVE SQL_THREAD # stop the SQL thread SHOW SLAVE STATUS; SHOW MASTER STATUS; SELECT @ @ global.read_only As Value; SELECT @ @ global.read_only As Value from the library SET GLOBAL read_only=0; # cancel read-only status USE ``; SELECT UNIX_TIMESTAMP (); SELECT @ @ GLOBAL.SERVER_ID; SET @ master_heartbeat_period= 3000001024 SET @ master_binlog_checksum= @ @ global.binlog_checksum; SELECT @ master_binlog_checksum; SELECT @ @ GLOBAL.GTID_MODE; SELECT @ @ GLOBAL.SERVER_UUID SET @ slave_uuid= '28ea40ab-9bbd-11e7-8cd1-000c29c31069slave; # write UUIDSTOP SLAVE; to the slave library # stop yourself as a slave library identity SHOW SLAVE STATUS; RESET SLAVE / *! 50516 ALL * / # clear itself as all records of the slave library SHOW SLAVE STATUS;SELECT RELEASE_LOCK ('MHA_Master_High_Availability_Failover') As Value; # release the concurrent lock that stops the same application

After reading the above about the use of masterha_master_switch tools to achieve MySQL high-availability MHA automatic switching, many readers must have some understanding, if you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.