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

1. A brief introduction to MHA

MHA (Master High Availability) is currently a relatively mature solution for MySQL high availability. It was developed by youshimaton, a Japanese DeNA company (now working for Facebook). It is a set of excellent high availability software for failover and master-slave upgrade in MySQL high availability environment. In the process of MySQL failover, MHA can automatically complete the failover operation of the database within 30 seconds, and in the process of failover, MHA can ensure the consistency of the data to the maximum extent so as to achieve high availability in the real sense.

Hosted to github, the corresponding address is as follows: manager address, https://github.com/yoshinorim/mha4mysql-managernode address, https://github.com/yoshinorim/mha4mysql-node as of the writing time of this document, the latest version is 0.58 advantages

You can refer to the official documentation to introduce https://github.com/yoshinorim/mha4mysql-manager/wiki/Advantages in detail.

It is roughly summarized as follows:

1. Master-slave switching is very fast, usually 10-30s2, solving the problem of data consistency to the maximum extent 3, no need to modify the existing MySQL architecture and configuration 4, no additional servers 5, no performance loss 6, no storage engine restrictions 2. MHA building overview

Database architecture: one master and two slaves

Master:192.168.142.48slave1:192.168.142.49slave2:192.168.142.50

Mha architecture:

Manager:192.168.142.49node:192.168.142.48192.168.142.49192.168.142.50

Keepalived architecture

VIP:192.168.142.235 node: 192.168.142.48192.168.142.49

Build the environment version

Database version: 5.7.18Log MySQL Community Server (GPL) MHA version: v0.58 Mha4mysql management house 0.58 + mha4mysql-node-0.58Keepalived:keepalived-1.4.3Linux host: centos7.2

List of hosts and installation services

Host IP installation service 192.168.142.48mysql master; mha node; keepalived192.168.142.49mysql slave; mha node & & mha manager; keepalived192.168.142.50mysql slave; mha node

General steps of building

1. Construction of keepalived 2, configuration mutual trust between linux machines 3, construction of mysql master-slave environment 4, installation and configuration of MHA 5, configuration file testing, master-slave replication in ssh connectivity test cluster 6, starting MHA management node 3. Construction of keepalived

Rpm installation is used here, keepalived is installed on 192.168.142.48 and 192.168.142.49 respectively, or source code compilation can be used.

1. Install yum install keepalived-Y2 and modify the configuration file vim / etc/keepalived/keepalived.conf! Configuration File for keepalivedglobal_defs {notification_email {root@localhost} notification_email_from Alexandre.Cassen@firewall.loc smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id LVS_DEVEL} vrrp_instance VI_1 {state MASTER interface eth0 virtual_router_id 55 priority 100 advert_int 1 authentication {auth_type PASS auth_pass 1111} virtual_ipaddress {192.168. 142.235}} note: where the primary MASTER has the highest priority (100) BACKUP priority (90) lower 3, start, shut down, view systemctl start keepalivedsystemctl stop keepalivedsystemctl status keepalived4. Configure mutual trust between linux machines

There are many ways to establish ssh mutual trust between servers. Here is a more convenient way to create trust between servers.

1. Create the secret key (enter for three times in a row) # ssh-keygen-t rsa2, enter the .ssh directory to view the file [root@192-168142-48] # cd / root/.ssh/ & & lsid_rsaid_rsa.pub3, modify the id_rsa.pub name mv id_rsa.pub authorized_keys4, Copy the / root/.ssh folder to 192.168.142.49 and 192.168.142.50 to establish mutual trust between the three machines (if the port is not the default 22 Need to specify additional parameters-P port) scp-P 8822-r / root/.ssh 192.168.142.49:/root/scp-P 8822-r / root/.ssh 192.168.142.50:/root/5. Construction of mysql master-slave environment

1. Manually build or automate scripts to configure mysql instances on three hosts respectively. Here you can refer to the method of building a single instance of MySQL, which will not be discussed in detail.

2. Configure replication user l and monitor user on each host database instance

1. Copy the user GRANT SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *. * TO 'repl'@'192.168.142.%' IDENTIFIED BY' 123456 'flush PRIVILEGES;2, monitor user GRANT ALL PRIVILEGES ON. * TO' root'@'192.168.142.%' IDENTIFIED BY '123456' flush PRIVILEGES

3. Configure slave synchronization

1. Execute show master status;, record on master corresponding to the number of File name and Position column. Here, install and configure CHANGE MASTER TO MASTER_HOST='192.168.142.48',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_PORT=5700,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;6. MHA for mysql-bin.000001 1542, slave1 and slave2, respectively.

You can refer to the official documentation to introduce https://github.com/yoshinorim/mha4mysql-manager/wiki/Installation in detail.

1. Install the node node on each host, which can be installed by rpm or compiled by source code

1. Download https://github.com/yoshinorim/mha4mysql-node/releases, select the corresponding rpm package, download 2, install dependent yum install perl-DBD-MySQL-y3, and install node node rpm-ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm.

2. One of the hosts (192.168.142.49) can install the manager node. Here, it can be installed by rpm or compiled by source code.

1. Download https://github.com/yoshinorim/mha4mysql-manager/releases, select the corresponding rpm package, download 2, install dependent yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager-y3, and install manager node rpm-ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm.

3. Create a configuration file for MHA

App1.cnf, the full version of the personal installation and configuration instance has been placed on github. Please refer to it and view it. The following is a link.

Https://github.com/dwwang1992/configs-of-MHA/blob/master/app1.cnf

4. Script corresponding to master_ip_failover_script

Master_ip_failover, the full version of the personal installation and configuration instance has been placed on github. Please refer to it and view it. The following is a link.

Https://github.com/dwwang1992/configs-of-MHA/blob/master/master_ip_failover

5. Script corresponding to master_ip_online_change_script

Master_ip_online_change, the full version of the personal installation and configuration instance has been placed on github. Please refer to it and view it. The following is a link.

Https://github.com/dwwang1992/configs-of-MHA/blob/master/master_ip_online_change

6. Script corresponding to report_script

Send_report, the full version of the personal installation and configuration instance has been placed on github. Please refer to it and view it. The following is a link.

Https://github.com/dwwang1992/configs-of-MHA/blob/master/send_report

7. Profile testing testing ssh connectivity [root@hz-192-168142-49 scripts] # masterha_check_ssh-- conf=/data/mha/app1.cnf Fri May 25 14:24:34 2018-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.Fri May 25 14:24:34 2018-[info] Reading application default configuration from / data/mha/app1.cnf..Fri May 25 14:24:34 2018-[info] Reading server configuration from / data/mha/app1.cnf..Fri May 25 14:24:34 2018-[info] Starting SSH connection tests..Fri May 25 14:24:37 2018-[debug] Fri May 25 14:24:34 2018-[debug] Connecting via SSH from root@192.168.142. 48 (192.168.142.48) to root@192.168.142.49 (192.168.142.49) Fri May 25 14:24:35 2018-[debug] ok.Fri May 25 14:24:35 2018-[debug] Connecting via SSH from root@192.168.142.48 (192.168.142.48 ok.Fri May 8822) to root@192.168.142.50 (192.168.142.50 debug 8822). Fri May 25 14:24 36 2018-[debug] ok.Fri May 25 14:24:38 2018-[debug] Fri May 25 14:24:35 2018-[debug] Connecting via SSH from root@192.168.142.49 (192.168.142.49 Fri May 8822) to root@192.168.142.48 (192.168.142.48 Connecting).. Fri May 25 14:24:35 2018-[debug] ok.Fri May 25 14:24:35 2018-[debug] Connecting Via SSH from root@192.168.142.49 (192.168.142.49 debug 8822) to root@192.168.142.50 (192.168.142.50 debug 8822).. Fri May 25 14:24:37 2018-[debug] ok.Fri May 25 14:24:39 2018-[debug] Fri May 25 14:24:35 2018-[debug] Connecting via SSH from root@192.168.142.50 (192.168.142.50 Ze8822) to root@192 .168.142.48 (192.168.142.48 Fri May 8822).. Fri May 25 14:24:37 2018-[debug] ok.Fri May 25 14:24:37 2018-[debug] Connecting via SSH from root@192.168.142.50 (192.168.142.50 ok.Fri May 8822) to root@192.168.142.49 (192.168.142.49498822).. Fri May 25 14:24:39 2018-[debug] ok.Fri May 25 14: 24:39 2018-[info] All SSH connection tests passed successfully. Master-slave replication [root@hz-192-168142-49 scripts] # masterha_check_repl-- conf=/data/mha/app1.cnf .192.168.142.48 (192.168.142.48) (current master) +-- 192.168.142.49 (192.168.142.49) +-- 192.168.142.50 (192.168.142.50) Fri May 25 14:25:57 2018-[info] Checking replication health on 192.168.142.49..Fri May 25 14:25:57 2018-[info] ok.Fri May 25 14:25:57 2018-[info] Checking replication health on 192.168.142.50..Fri May 25 14:25:57 2018-[info] ok.Fri May 25 14:25:57 2018-[info] Checking master_ip_failover_script status:Fri May 25 14:25:57 2018- [info] / data/mha/scripts/master_ip_failover-command=status-ssh_user=root-orig_master_host=192.168.142.48-orig_master_ip=192.168.142.48-orig_master_port=5700-orig_master_ssh_port=8822Unknown option: orig_master_ssh_portIN SCRIPT TEST====systemctl stop keepalived==systemctl start keepalived===Checking the Status of the script.. OK Fri May 25 14:25:57 2018-[info] OK.Fri May 25 14:25:57 2018-[warning] shutdown_script is not defined.Fri May 25 14:25:57 2018-[info] Got exit code 0 (Not master dead) .MySQL Replication Health is OK.8. Start the MHA management node

Start command

Nohup masterha_manager-conf=/data/mha/app1.cnf-remove_dead_master_conf-ignore_last_failover

/ data/mha/mha/app1/manager.log 2 > & 1 & for more information about the parameters, please see https://github.com/yoshinorim/mha4mysql-manager/wiki/masterha_manager on the official website. It is simply stated here-- remove_dead_master_conf. After the occurrence of failover, MHA will automatically remove the information about dead master-ignore_last_failover from the configuration file. By default, if failover exists before, it is not successful to start MHA again. You must delete the failover error file in the corresponding directory, (manager_workdir) / (app_name) .failover.error By setting this parameter, you can ignore whether there was an error last time and continue with failover

Close command

Masterha_stop-conf=/data/mha/app1.cnf

View statu

Masterha_check_status-- conf=/data/mha/app1.cnf9. Online manual switching process

1. First of all, you need to make sure that mha manager is turned off.

Masterha_stop-conf=/data/mha/app1.cnf

2. Manually switch the primary master

Masterha_master_switch-- conf=/data/mha/app1.cnf-- master_state=alive-- new_master_host=192.168.142.48-- new_master_port=5700-- orig_master_is_new_slave-- running_updates_limit=10000-- interactive=0 Parameter explanation: new_master_host: specify which one becomes the new master library new_master_port: specify the corresponding database port orig_master_is_new_slave: change the original master library into the slave library running _ updates_limit: specifies that any replication delay within 10000s can be switched interactive: indicates that no human intervention is required Automatic execution

3. Switching process

1. Check the current configuration information and the information of the master-slave server, including reading the configuration file of MHA and checking the health status of the current slave. 2. To prevent the update of the current master mainly through the following steps: 1 > wait 1.5s ($time_until_kill_threads*100ms), wait for the current connection to be disconnected 2 > execute read_only=1 Block new DML operation 3 > wait for 0.5s, wait for the current DML operation to complete 4 > kill drop all connections 5 > FLUSH NO_WRITE_TO_BINLOG TABLES 6 > FLUSH TABLES WITH READ LOCK3, wait for the new master to execute all relay log, and record the corresponding log bit Waiting to execute all relay logs on 192.168.142.49 (192.168.142.49). Getting new master's binlog name and position.. Mysql-bin.000001:24884, set the read_only of the new master to off, add VIP5, switch slave to the new master, wait for slave to finish applying the relay log generated by the original master-slave replication, and then perform the change master operation to switch to Waiting to execute all relay logs on 192.168.142.50 (192.168.142.50 Waiting to execute all relay logs on 5700) on the new master. Resetting slave 192.168.142.50 (192.168.142.50) and starting replication from the new master 192.168.142.49 (192.168.142.49).. 2. Release the lock Unlocking all tables on the orig master: Executing UNLOCK TABLES.. on the original master 3. Because the masterha_master_switch command line has the parameter-- orig_master_is_new_slave, the original master is also switched to the slave Starting orig master as a new slave.. of the new master. Resetting slave 192.168.142.48 (192.168.142.48 and starting replication from the new master 5700) and starting replication from the new master 192.168.142.49 (192.168.142.49 master 5700).. 6. Information about cleaning up the new master: Resetting slave info succeeded10. Automatic failover process

1. First of all, you need to ensure that mha manager is running.

Nohup masterha_manager-conf=/data/mha/app1.cnf-remove_dead_master_conf-ignore_last_failover

/ data/mha/mha/app1/manager.log 2 > & 1 &

2. Once the primary master goes down, the new primary master will be automatically detected and selected.

3. The process of switching

When master_manager monitors that the mysqld service of the master library is stopped, first perform a SSH login check on the master library (save_binary_logs-- command=test), and then do a health check on the mysqld service (PING (SELECT) every 3 seconds for 3 times). The parameter secondary_check_script can be used for double check, and finally Master is down! According to the judgment of master failover, first, check which servers are in the current replication environment according to the configuration file, MHA will also check such as replication exception and whether there are some slave libraries with different master libraries, start failover (excluding the last failover failure or the failover interval is too short) 2, isolate master server, stop the VIP of the fault master library (provided you specify the relevant script For example, if you have master_ip_failover_script, you will call a script to stop VIP, and if you have a shutdown_script script, you will call a script to shut down master to avoid brain fissure. Specific in the configuration file app1.cnf) 3, select the new master library and try to supplement the data of the new master library 1, get the slave library with the highest synchronization location: compare the master_log_file and read_master_log_pos locations of all slave libraries to find out the execution location of the newest and oldest binlog location of the fault master library corresponding to the slave library 2, Save the binlog of dead master: execute the save_binary_logs command on the failed master library to get the binlog difference between the lastest slave synchronization location and the master (use the first synchronization slave library binlog location found in step 3.1 If the fault master database system is not hung up) and scp to mha manager server scp from root@192.168.142.48:/data/mha/mha/tmp/saved_master_binlog_from_192.168.142.48_5700_20180525155119.binlog to local:/data/mha/mha/app1/saved_master_binlog_from_192.168.142.48_5700_20180525155119.binlog succeeded. 3. Determine and decide the new master library: first use the command apply_diff_relay_logs-- command=find to take the binlog location of the corresponding master library that is the first and last synchronization location found in the previous step 3.1 as a parameter Execute this command on the slave library at the top of the synchronization location to find the relay log between the two binlog locations in the relay log and generate a file to restore the other slave libraries (this is to check whether the most synchronized slave library has a relay log starting from the oldest location, which is why relay logs executed in the MHA environment cannot be deleted Otherwise, the comparison will be more troublesome) then find and determine the new master library, and choose how to upgrade the new master library according to the configuration (check whether candidate_master=1 and no_master=1 are set, if there is a candidate master library, then the candidate master library wins the bid, but the candidate database is not necessarily the slave with the latest data, so you need to compare it with other slave libraries, of course, if the candidate master database happens to be the slave library at the front of the synchronization location. There is no need to compare relay log with other slave libraries. If the candidate master library is not set, then the slave library with the highest synchronization location wins the bid. Mha manager server will also copy the previously copied differential binlog to the new master library. 4. The new master library application log (if any errors occur from this stage, you need to recover manually) the new master library first needs to check with master_log_file=relay_master_log_file,read_master_log_pos=exec_master_log_pos to confirm that it has completed the replication. If the new master library is not the first slave library in the synchronization location, then you need to use the apply_diff_relay_logs-- command=generate_and_send command to compare whether there is a difference in the relay log between yourself and the slave library at the top of the synchronization location. If so, you need to generate a difference relay log (if the new master library is the slave library at the top of the synchronization location, you only need to execute the difference log sent by mha manager server) Then use these two differential logs to recover the data (apply_diff_relay_logs-- command=apply command). After the recovery is complete, get the binlog location and generate the change master statement to be used on the other slave library change master to the new master library, and set the read_only=0. Then bind the VIP to the new main library. To this step, the switching of the new master library is completed. 4. Other slave libraries are restored: complete other slave library data as far as possible (all slave libraries are executed in parallel) and use the apply_diff_relay_logs-command=generate_and_send command to judge the relay log difference between the relay log location and synchronization location of each slave library. And send the difference files from the front of the synchronization location to the corresponding slave library and use two difference logs to recover them in parallel: the binlog difference on mha manager server is copied to each slave library, and then each slave library confirms that it has completed the replication through master_log_file=relay_master_log_file,read_master_log_pos=exec_master_log_pos, and then applies two difference logs to restore data. Finally, execute reset slave and re-CHANG MASTER to the new main library. 5. Clean up the relevant information of the new master, and then switch the fault main library to the new main library to complete the Resetting slave info on the new master..11. The host after the failure has newly joined the resource group

The first method

1. Vim / data/mha/app1.cnf add server1 host information [server1] candidate_master=1client_bindir=/usr/local/mysql-5.7.18/bin/client_libdir=/usr/local/mysql-5.7.18/lib/hostname=192.168.142.48port=57002, point the server1 to the current master and act as a slave CHANGE MASTER TO MASTER_HOST='192.168.142.49',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_PORT=5700,MASTER_LOG_FILE='mysql-bin.000001' MASTER_LOG_POS=2488 3. Start mha managernohup masterha_manager-conf=/data/mha/app1.cnf-remove_dead_master_conf-ignore_last_failover

/ data/mha/mha/app1/manager.log 2 > & 1 &

The second method

1. Add server1 host information to the resource group masterha_conf_host-- command=add-- conf=/data/mha/app1.cnf-- block=server1-- hostname=192.168.142.48-- params= "candidate_master=1;client_bindir=/usr/local/mysql-5.7.18/bin/;client_libdir=/usr/local/mysql-5.7.18/lib/ through masterha_conf_host Port=5700 "parameter explanation: command: add or remove a host information to the configuration file conf: path to the configuration file hostname: host information ipblock: newly added block name params: list of additional parameters, (key1=value1;key2=value2) 2. Point the server1 to the current master as the slave role CHANGE MASTER TO MASTER_HOST='192.168.142.49',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_PORT=5700,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=2488;3, and launch mha managernohup masterha_manager-- conf=/data/mha/app1.cnf-- remove_dead_master_conf-- ignore_last_failover.

/ data/mha/mha/app1/manager.log 2 > & 1 & 12. MHA Clean relay log (purge_relay_logs)

1. Description:

MySQL database master-slave replication by default the relay logs of the slave database will be deleted automatically after the execution of the SQL thread, but for the MHA scenario, the recovery of some lagging slave libraries depends on the relay log of other slave libraries, so we should pay attention to the replication delay resource overhead caused by disabling automatic deletion and regular cleaning to clean up too much SQL. MHA can accomplish this task through purge_relay_logs script and with cronjob.

2. The function of purge_relay_logs

A. Create hard links for relay logs (minimize performance problems caused by bulk deletion of large files) b, SET GLOBAL relay_log_purge=1; FLUSH LOGS; SET GLOBAL relay_log_purge=0;c, delete relay log (rm-f / path/to/archive_dir/*)

3. The usage and related parameters of purge_relay_logs.

# usage # purge_relay_logs-- helpUsage: purge_relay_logs-- user=root-- password=rootpass-- host=127.0.0.1### parameter description-- user:mysql username, default to root--password:mysql password-- port: Port number-- host: hostname, default to 127.0.0.1--workdir: specify the location where the hard link for relay log is created. Default is / var/tmp. After successful execution of the script, The hard-linked relay log file is deleted because the creation of hard-link files in different partitions of the system will fail, so the specific location of the hard link needs to be performed. It is recommended to specify the same partition as relay log-disable_relay_log_purge: by default, the parameter relay_log_purge=1, the script does not do any processing, automatically exits, and the script will set relay_log_purge to 0 after cleaning relay log. Finally, set the parameter to OFF (0)

4. Custom cleaning relay log cronjob (set for each host)

The purge_relay_logs script automatically cleans up relay log without blocking SQL threads. Deploy the script to crontab directly for the continuously generated relay log to clean up regularly on a daily or hourly basis. 1. Edit the script Vim / dataUniverse vim / dataUniverse mhaqqunxunxunxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx (mhaxxxxxxxxxxxxxxxxxxxxxxxxxxxx (mhaxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx _ relay_log_purge > > $log_dir/purge_relay_logs.log 2 > & 12, Finally, add to the scheduled task: # # clear relay log00 01 * / bin/bash / data/mha/mha/purge_relay_logs.sh at 1: 00 a.m. every 2 days

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.