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

In daily work, you will encounter the following scenarios, such as mysql database upgrade, master server hardware upgrade, etc., when you need to switch the write operation to another server, so how to switch online? At the same time, the switching process is required to be short, and the impact on the business is relatively small.

MHA provides such an elegant way to block only 0.5 seconds of business time, during which the business cannot read and write.

Cluster information

Role IP address ServerID type

Master 192.168.244.10 1 write

Candicate master 192.168.244.20 2 read

Slave 192.168.244.30 3 read

Monitor host 192.168.244.40 Monitoring Cluster Group

For the specific steps and principles of building MHA, please refer to another blog:

Deployment and principle of MySQL High availability solution MHA

To switch online

1. Turn off MHA monitoring

# masterha_stop-conf=/etc/masterha/app1.cnf

two。 Online handoff

# / usr/local/bin/masterha_master_switch-conf=/etc/masterha/app1.cnf-master_state=alive-new_master_host=192.168.244.20-new_master_port=3306-orig_master_is_new_slave-running_updates_limit=10000

Among them

-- orig_master_is_new_slave is the slave that switches the original master to the new master and is not added by default.

-- running_updates_limit defaults to 1s, that is, if the master-slave delay time (Seconds_Behind_Master), or if the dml operation in master show processlist is greater than 1s, the switch will not be performed.

Output of online switching

Tue Apr::-[] MHA::MasterRotate version::-[] * Phase::-[:-[warning] Global configuration / etc/::-[] Reading application default configuration from / etc/masterha/::-[] Reading server configuration from / etc/masterha/::-[] GTID failover mode =::-[] Current Alive Master : (.:-[:-[]. Version=.--bin:enabledTue Apr::-[] Replicating from. (.::-[] Primary candidate::-[]. (.:) Version=.--bin:enabledTue Apr::-[] Replicating from. (.:. (:)? (YES/no): yesTue Apr:-[] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take:-[] Checking replication health on. :-Checking replication health on. :-[:-[]. :: [. (.: +--. (.: +--. (.:. (.: + -. (.: +--. (.:. (.) to. (.:)? (yes/::-[] Checking whether. (.:) is ok::-[:-[]. (.:-[]. (.::-[] * * Phase::-[:-[] * Phase::-[:-[] Executing master ip online change script to disable::-[] / usr/local / bin/master_ip_online_change-command=stop-orig_master_host=. -=. -orig_master_port=-- orig_master_user=-- orig_master_password=-- new_master_host=. -- new_master_ip=. -new_master_port=-- new_master_user=-- new_master_password=-- orig_master_ssh_user=root-- new_master_ssh_user=root-- orig_master_is_new_slaveTue Apr: Set read_only=: :-Orig master binlog:pos is mysql-bin.:-[] Waiting to execute all relay logs on. (.::-[] master_pos_wait (mysql-bin.:) completed on. (.:). Executed::-[]:-[] Getting new masterTue Apr::-[] mysql-bin.:-[=, MASTER_PORT=, MASTER_LOG_FILE=, MASTER_LOG_POS=, MASTER_USER=, MASTER_PASSWORD=;Tue Apr::-[] Executing master ip online change script to allow::-[] / usr/local/bin/master_ip_online_change-- command=start-- orig_master_host=. -=. -orig_master_port=-- orig_master_user=-- orig_master_password=-- new_master_host=. -- new_master_ip=. -new_master_port=-- new_master_user=-- new_master_password=-- orig_master_ssh_user=root-- new_master_ssh_user=root-- orig_master_is_new_slaveTue Apr:: Set read_only=. On the new master:. ::-[] * Switching slaves::-[:-[]-Slave switch on host. (.:) started, pid::-[:-[] Log messages from. :-[: -] Waiting to execute all relay logs on. (.::-[] master_pos_wait (mysql-bin.:) completed on. (.:) Executed::-[]:-[] Resetting slave. (.:) and starting replication from the new master. (.:). Tue Apr::-[] End of log messages from. :-[::-[]-Slave switch on host. (.::-[] Resetting slave. (.:) and starting replication from the new master. (.:).. Tue Apr::-[ * Phase::-[:-[].: Resetting slave::-[] Switching master to. (.:) completed successfully.

The principle of MHA online handover

1. Check current configuration information and master-slave server information

Including reading the configuration file / etc/masterha/app1.cnf of MHA and checking the health status of the current slave

two。 Block updates to the current master

Mainly through the following steps:

1 > wait 1.5s ($time_until_kill_threads*100ms) for the current connection to be disconnected.

2 > execute read_only=1 to block new DML operations

3 > wait 0.5s for the current DML operation to complete.

4 > kill drops all connections.

5 > FLUSH NO_WRITE_TO_BINLOG TABLES

6 > FLUSH TABLES WITH READ LOCK

3. Wait for the new master to finish executing all the relay log

Waiting to execute all relay logs on 192.168.244.20 (192.168.244.20)..

4. Set the read_only of the new master to off and add VIP

5. Slave switches to the new master.

1 > wait for slave (192.168.244.30) to apply the relay log generated by the original master-slave replication, and then perform the change master operation to switch to the new master.

2 > release the lock added on the original master.

3 > because the masterha_master_switch command line has the-- orig_master_is_new_slave parameter, the original master is also switched to the slave of the new master.

6. Clean up information about the new master.

The main reason is to perform the reset slave all operation to clear the previous replication information.

Conditions to be met for MHA online handover

MHA determines the current master-slave replication information before performing an online switchover, and the switchover action can only be performed if the following conditions are met:

1. All SLAVE IO and SQL threads are running.

two。 The Seconds_Behind_Master of all slave is less than or equal to the value of running_updates_limit. If this parameter is not specified, it defaults to 1s.

3. On master, through show processlist output, no DML operation takes longer than the value of running_updates_limit.

When switching online, open general log, the operation information of each server

Note: when executing the masterha_master_switch command, there will be two confirmations

1. It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.244.10 (192.168

.244.10)? (YES/no):

2. Starting master switch from 192.168.244.10 (192.168.244.10) to 192.168.244.20 (192.168.244.20)? (yes/NO):

There are two blanks in the middle of the following output, in which the output before the first blank corresponds to the first confirmation, and the output before the second confirmation corresponds to the second confirmation.

Original master 192.168.244.10

170412 16:52:38 23 Connect monitor@node4 on 23 Query set autocommit=1 23 Query SELECT CONNECTION_ID () AS Value170412 16:52:39 24 Connect monitor@node4 on 24 Query set autocommit=1 24 Query SELECT CONNECTION_ID () AS Value 24 Query SET wait_timeout=86400 24 Query SELECT @ @ global.server_id As Value 24 Query SELECT VERSION () AS Value 24 Query SELECT @ @ global.gtid_mode As Value 24 Query SHOW GLOBAL VARIABLES LIKE 'log_bin' 24 Query SHOW MASTER STATUS 24 Query SELECT @ @ global.datadir AS Value 24 Query SELECT @ @ global.slave_parallel_workers AS Value 24 Query SHOW SLAVE STATUS 24 Query SELECT @ @ global.read_only As Value 24 Query SELECT @ @ global.relay_log_purge As Value 170412 16:54:06 24 Query FLUSH NO_WRITE_TO_BINLOG TABLES 24 Query SELECT GET_LOCK ('MHA_Master_High_Availability_Monitor' '0') AS Value 24 Query SHOW PROCESSLIST 170412 16:55:51 24 Query SHOW SLAVE STATUS 24 Query CHANGE MASTER TO MASTER_HOST='dummy_host'170412 16:55:52 24 Query SHOW SLAVE STATUS 24 Query RESET SLAVE /! 50516 ALL * / 24 Query SELECT RELEASE_LOCK (' MHA_ Master_High_Availability_Monitor') As Value 24 Quit 25 Connect monitor@node4 on 25 Query set autocommit=1 25 Query SELECT CONNECTION_ID () AS Value 25 Query SET sql_log_bin=0 25 Query SHOW PROCESSLIST 25 Query SELECT @ @ global.read_only As Value 25 Query SET GLOBAL read_only=1 25 Query SELECT @ @ global.read_only As Value 25 Query SHOW PROCESSLIST 25 Query SET sql_log_bin=1 25 Quit 26 Connect monitor@node4 on 26 Query Set autocommit=1 26 Query SELECT CONNECTION_ID () AS Value 26 Query SET wait_timeout=86400 26 Query FLUSH TABLES WITH READ LOCK 26 Query SHOW MASTER STATUS170412 16:55:53 26 Query UNLOCK TABLES 26 Query CHANGE MASTER TO MASTER_HOST = '192.168.244.20' MASTER_USER = 'repl' MASTER_PASSWORD = MASTER_PORT = 3306 MASTER_LOG_FILE = 'mysql-bin.000010' MASTER_LOG_POS = 12026 Query SET GLOBAL relay_log_purge=0 26 Query START SLAVE 27 Connect Out repl@192.168.244.20:3306 26 Query SHOW SLAVE STATUS 26 Query SELECT RELEASE_LOCK (' MHA_Master_High_Availability_Failover ') As Value 26 Quit

New master 192.168.244.20

170412 16:52:38 23 Connect monitor@node4 on 23 Query set autocommit=1 23 Query SELECT CONNECTION_ID () AS Value170412 16:52:39 24 Connect monitor@node4 on 24 Query set autocommit=1 24 Query SELECT CONNECTION_ID () AS Value 24 Query SET wait_timeout=86400 24 Query SELECT @ @ global.server_id As Value 24 Query SELECT VERSION () AS Value 24 Query SELECT @ @ global.gtid_mode As Value 24 Query SHOW GLOBAL VARIABLES LIKE 'log_bin' 24 Query SHOW MASTER STATUS 24 Query SELECT @ @ global.datadir AS Value 24 Query SELECT @ @ global.slave_parallel_workers AS Value 24 Query SHOW SLAVE STATUS 24 Query SELECT @ @ global.read_only As Value 24 Query SELECT @ @ global.relay_log_purge As Value 24 Query SELECT @ @ global.relay_log_info_repository AS Value 24 Query SELECT @ @ global.datadir AS Value 24 Query SELECT @ @ global.relay_log_info_file AS Value 24 Query SHOW SLAVE STATUS 24 Query SELECT Repl_slave_priv AS Value FROM mysql.user WHERE user = 'repl' 170412 16:54:06 24 Query SELECT GET_LOCK (' MHA_Master_High_Availability_Failover' '0') AS Value 24 Query SHOW SLAVE STATUS 24 Query SHOW SLAVE STATUS 170412 16:55:52 24 Query SHOW PROCESSLIST 25 Connect monitor@node4 on 25 Query set autocommit=1 25 Query SELECT CONNECTION_ID () AS Value 25 Query SELECT @ @ global .read _ only As Value 25 Query SELECT @ @ global.read_only As Value 25 Quit 24 Query SHOW SLAVE STATUS 24 Query SELECT MASTER_POS_WAIT ('mysql-bin.000017' '120' 0) AS Result 24 Query STOP SLAVE SQL_THREAD 24 Query SHOW SLAVE STATUS 24 Query SHOW MASTER STATUS 26 Connect monitor@node4 on 26 Query set autocommit=1 26 Query SELECT CONNECTION_ID () AS Value 26 Query SET sql_log_bin=0 26 Query SELECT @ @ global.read_only As Value 26 Query SET GLOBAL read_only=0 26 Query SET sql_log_bin=1 26 Quit 24 Query SELECT @ @ global.read_only As Value 27 Connect repl@node3 on 27 Query SELECT UNIX_TIMESTAMP () 27 Query SHOW VARIABLES LIKE 'SERVER_ID' 27 Query SET @ master_heartbeat_period= 1799999979520 27 Query SET @ master_binlog_checksum= @ global.binlog_checksum 27 Query SELECT @ master_binlog_checksum 27 Query SELECT @ @ GLOBAL.GTID_MODE 27 Query SHOW VARIABLES LIKE 'SERVER_UUID' 27 Query SET @ slave_uuid=' 8a1093c8-1d00-11e7-954fmur000c299a5715' 27 Binlog Dump Log: 'mysql-bin.000010' Pos: 120170412 16:55:53 28 Connect repl@node1 on 28 Query SELECT UNIX_TIMESTAMP () 28 Query SHOW VARIABLES LIKE' SERVER_ID' 28 Query SET @ master_heartbeat_period= 179999999979520 28 Query SET @ master_binlog_checksum= @ global.binlog_checksum 28 Query SELECT @ master_binlog_checksum 28 Query SELECT @ @ GLOBAL.GTID_MODE 28 Query SHOW VARIABLES LIKE 'SERVER_UUID' 24 Query STOP SLAVE 28 Query SET @ slave_uuid= '2a6365e0-1d05-11e7-956dMur000c29c64704' 28 Binlog Dump Log: 'mysql-bin.000010' Pos: 12024 Query SHOW SLAVE STATUS 24 Query RESET SLAVE / *! 50516 ALL * / 24 Query SHOW SLAVE STATUS 24 Query SELECT RELEASE_LOCK (' MHA _ Master_High_Availability_Failover') As Value 24 Quit

Slave 192.168.244.30

170412 16:52:37 16 Connect monitor@node4 on 16 Query set autocommit=1 16 Query SELECT CONNECTION_ID () AS Value170412 16:52:38 17 Connect monitor@node4 on 17 Query set autocommit=1 17 Query SELECT CONNECTION_ID () AS Value17 Query SET wait_timeout=86400 17 Query SELECT @ @ global.server_id As Value 17 Query SELECT VERSION () AS Value 17 Query SELECT @ @ global.gtid_mode As Value 17 Query SHOW GLOBAL VARIABLES LIKE 'log_bin' 17 Query SHOW MASTER STATUS 17 Query SELECT @ @ global.datadir AS Value 17 Query SELECT @ @ global.slave_parallel_workers AS Value 17 Query SHOW SLAVE STATUS 17 Query SELECT @ @ global.read_only As Value 17 Query SELECT @ @ global.relay_log_purge As Value 17 Query SELECT @ @ global.relay_log_info_repository AS Value 17 Query SELECT @ @ global.datadir AS Value 17 Query SELECT @ @ global.relay_log_info_file AS Value 17 Query SHOW SLAVE STATUS 17 Query SELECT Repl_slave_priv AS Value FROM mysql.user WHERE user = 'repl' 170412 16:54:05 17 Query SELECT GET_LOCK (' MHA_Master_High_Availability_Failover' '0') AS Value 17 Query SHOW SLAVE STATUS17 Query SHOW SLAVE STATUS170412 16:55:50 17 Query SHOW SLAVE STATUS170412 16:55:51 17 Query SHOW SLAVE STATUS17 Query SELECT MASTER_POS_WAIT (' mysql-bin.000017','120' 0) AS Result 17 Query STOP SLAVE SQL_THREAD 17 Query SHOW SLAVE STATUS 17 Query STOP SLAVE 17 Query STOP SLAVE 17 Query SHOW SLAVE STATUS 17 Query RESET SLAVE 17 Query CHANGE MASTER TO MASTER_HOST = '192.168.244.20 'MASTER_USER =' repl' MASTER_PASSWORD = MASTER_PORT = 3306 MASTER_LOG_FILE = 'mysql-bin.000010' MASTER_LOG_POS = 120 17 Query SET GLOBAL relay_log_purge=0 17 Query START SLAVE 18 Connect Out repl@192.168.244.20:3306 17 Query SHOW SLAVE STATUS170412 16:55:52 17 Query SELECT RELEASE_LOCK (' MHA_Master_High_Availability_Failover') As Value 17 Quit

Transferred from: https://www.cnblogs.com/ivictor/p/6693968.html

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.