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

MHA High availability Cluster articles Catalog

1. Brief introduction to MHA:

2. Deploy MHA:

Step 1: install mysql on three master and slave servers

Step 2: modify the main configuration file of mysql: / etc/my.cnf. Note that the server-id of the three servers cannot be the same.

Step 3: three servers start the mysql service

Step 4: configure Mysql master-slave synchronization (one master and two slaves)

Step 5: install MHA

Step 6: start MHA

Introduction to MHA: introduction to MHA (Master High Availability) (1)

At present, it is a relatively mature solution in the aspect of high availability of MySQL. It is developed by youshimaton (now works for Facebook) of DeNA company in Japan. 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 in order to achieve high availability in the real sense.

(2) the software consists of two parts:

MHA Manager (management node) and MHA Node (data node). MHA Manager can be deployed on a separate machine to manage multiple master-slave clusters, or it can be deployed on a slave node. MHA Node runs on each MySQL server, and MHA Manager regularly detects the master nodes in the cluster. When the master fails, it automatically promotes the slave of the latest data to the new master, and then redirects all other slave to the new master. The entire failover process is completely transparent to the application.

(3) working principle:

1. In the process of MHA automatic failover, MHA tries to save binary logs from the down master server to ensure that the data is not lost as much as possible, but this is not always feasible. For example, if the primary server hardware fails or cannot be accessed through ssh, MHA cannot save binary logs and only fails over and loses the latest data. With semi-synchronous replication of MySQL 5.5, the risk of data loss can be greatly reduced. MHA can be combined with semi-synchronous replication. If only one slave has received the latest binary log, MHA can apply the latest binary log to all other slave servers, thus ensuring data consistency across all nodes.

2. Order:

① saves binary log events (binlog events) from crashed master

② identifies the slave with the latest updates

③ Application differential Relay Log (relay log) to other slave

④ application binary log events saved from master (binlog events)

⑤ promotes a slave to a new master

⑥ causes other slave to connect to the new master for replication

2. Deploy MHA: service master (192.168.45.133) mha4mysql-nodeslave1 (192.168.45.130) mha4mysql-nodeslave2 (192.168.45.134) mha4mysql-nodemanager (192.168.45.135) mha4mysql-manager, mha4mysql-node (1) to be installed by the role:

This case requires that the MySQL database should be automatically switched in case of failure through MHA monitoring, which does not affect the business.

(2) ideas:

Install the MySQL database

Configure MySQL with one master and two slaves

Install MHA softwar

Configure password-free authentication

Configure MySQL and MHA for high availability

Simulate master failover

(3) operating system: Cent0S7 … Version 6, MHA version is version 0.57. Install the mysql database on three servers

(please use 5.6.36 for MySQL version, use 2.8.6 for cmake version.) because the installation operations of all three sets are the same, the installation sequence on master is demonstrated here.

Modify the host name for the convenience of the experiment

# modify the server name, change the host name of the manster server to mansterhostnamectl set-hostname manster su # modify the server name, change the host name of the slave1 server to slaveqhostnamectl set-hostname slave1 su # modify the server name Change the hostname of the slave2 server to slave2hostnamectl set-hostname slave2 su# installation and compilation according to the lazy environment yum-y install gcc gcc-c++ ncurses ncurses-devel bison perl-Module-Install cmake# mount package mount.cifs / / 192.168.100.3/mha / mntPassword for root@//192.168.100.3/mha: # install the gmake compiler software cd / mnttar zxvf cmake-2.8.6.tar.gz-C / optcd / opt/cmake-2.8.6 /. / configuregmake & & gmake install# install mysql database [root@localhost cmake-2.8.6] # cd / mnt [root@localhost mnt] # tar zxvf mysql-5.6.36.tar.gz-C / opt# compile mysqlcd / opt/mysql-5.6.36/cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\-DDEFAULT_CHARSET=utf8\-DDEFAULT_COLLATION=utf8_general_ci\-DWITH_EXTRA_CHARSETS=all\-DSYSCONFDIR=/etc# install make & & make install# settings Environment variable cp support-files/my-default.cnf / etc/my.cnfcp support-files/mysql.server / etc/rc.d/init.d/mysqldchmod + x / etc/rc.d/init.d/mysqldchkconfig-- add mysqldecho "PATH=$PATH:/usr/local/mysql/bin" > > / etc/profilesource / etc/profile# create mysql database And authorize groupadd mysqluseradd-M-s / sbin/NOlogin mysql-g mysqlchown-R mysql.mysql / usr/local/mysqlmkdir-p / data/mysql# to initialize the database / usr/local/mysql/scripts/mysql_install_db\-basedir=/usr/local/mysql\-- datadir=/usr/local/mysql/data\-- user=mysql II. Modify the main configuration file of mysql: / etc/my.cnf Note that the server-id of the three servers cannot be the same-configure the master server: vim / etc/ my.cnf [MySQL] server-id = enable binary log _ bin = master-bin# allow synchronization from the server log-slave-updates = true--- configuration slave server 1:vim / etc/ my.cnf [MySQL] server-id = enable binary log _ bin = master-bin# use relay day Synchronize relay-log = relay-log-binrelay-log-index = slave-relay-bin.index--- configuration from server 2:vim / etc/ my.cnf [MySQL] server-id = 3log_bin = master-binrelay-log = relay-log-binrelay-log-index = slave-relay-bin.index 3. Three servers start the mysql service # create these two soft links ln-s / usr/local/mysql/bin/mysql / usr/sbin/ln-s / usr/local/mysql/bin/mysqlbinlog / usr/sbin/# on the three servers to start mysql/usr/local/mysql/bin/mysqld_safe-- user=mysql & # turn off the firewall and security function systemctl stop firewalld.servicesetenforce 04. configure Mysql master-slave synchronization (one master and two slaves)

1. The master-slave configuration of mysql is relatively simple. What needs to be noted is authorization.

Authorize two users on all database nodes, one is to synchronize the use of user myslave from the database, and the other is to monitor the use of manager

Mysql-u root-p / / enter the database mysql > grant replication slave on *. * to 'myslave'@'192.168.45.%' identified by' 123 * MySQL > grant all privileges on *. * to 'mha'@'192.168.45.%' identified by' manager';mysql > flush privileges / / refresh the database # add the following authorization (not required in theory) to the database in the form of hostname (MHA check is in the form of hostname) mysql > grant all privileges on *. * to 'mha'@'master' identified by' manager';mysql > grant all privileges on *. * to 'mha'@'slave1' identified by' manager';mysql > grant all privileges on *. * to 'mha'@'slave2' identified by' manager' View binaries and syncpoint show master status on the master server

Set up synchronization on two slave servers # execute the following command on both slave servers to synchronize the logs of the master server mysql > change master to master_host='192.168.45.133',master_user='myslave',master_password='123',master_log_file='master-bin.000001',master_log_pos=1292;mysql > start slave; / / Open slavemysql > show slave status\ G; / / View slave

Set the two slave libraries to read-only mode mysql > set global read_only=1;mysql > flush privileges / / refresh the database installation MHA install MHA on all servers in a lazy environment, first install the Epel source yum install epel-release-- nogpgcheck-yyum install-y perl-DBD-MySQL\ perl-Config-Tiny\ perl-Log-Dispatch\ perl-Parallel-ForkManager\ perl-ExtUtils-CBuilder\ perl-ExtUtils-MakeMaker\ perl-CPAN install node# on all servers to modify the server name Change the hostname of the manager server to the managerhostnamectl set-hostname manager su# mount package mount.cifs / / 192.168.100.3/mha / mnt# extract install nodecd ~ tar zxvf / mnt/mha4mysql-node-0.57.tar.gzcd mha4mysql-node-0.57/perl Makefile.PL make & & make install install manager on the manger server (Note: be sure to install the node component to install the manager component) # turn off the firewall systemctl stop firewalld.service setenforce 0 # decompress and install managercd ~ tar zxvf / mnt/mha4mysql-manager-0.57.tar.gzmha4mysql-manager-0.57/perl Makefile.PL make & & make installmanager server will generate several tools under the usr/local/bin directory after installation: masterha_check_repl check mysql replication status masterha_master_monitor check master downtime masterha_check_ssh check MHA SSH configuration masterha_master_switch control failover masterha_check_status check when Pre-MHA running status masterha_conf_host add or remove configuration server information masterha_stop turn off the script for managermasterha_manager to start manager several scripts that are generated under / usr/local/bin after node installation (usually triggered by MHA Manager script No human action required) apply_diff_relay_logs: identify differential relay log events and apply them to other slave Save_binary_logs: save and copy master binary logs; filter_mysqlbinlog: remove unnecessary ROLLBACK events (MHA no longer uses this tool); purge_relay_logs: clear relay logs (do not block SQL threads) Configure password-less access 1, configure password-free authentication ssh-keygen-t rsassh-copy-id 192.168.45.133ssh-copy-id 192.168.45.130ssh-copy-id 192.168.45.1342 for all database nodes on manager, configure password-free authentication ssh-keygen-t rsassh-copy-id 192.168.45.130ssh-copy-id 192.168.45.1343 for database nodes slave1 and slave2 on master, No password authentication ssh-keygen-t rsassh-copy-id 192.168.45.133ssh-copy-id 192.168.45.1344 configured to database nodes master' and slave2 on slave1, and no password authentication ssh-keygen-t rsassh-copy-id 192.168.45.133ssh-copy-id 192.168.45.130 configured to database nodes slave1 and master on slave2

Configure MHA1 and copy the relevant scripts on the manager node to the / usr/local directory cp-ra / root/mha4mysql-manager-0.57/samples/scripts/ / usr/local/bin/ls scripts/ master_ip_failover: script managed by VIP during automatic switchover; master_ip_online_change: management of VIP during online switchover; power_manager: script for shutting down host after failure; send_report: script that sends alarm after failover Copy the script managed by VIP during the automatic switch to the / usr/local/bin/ directory: cp / usr/local/bin/scripts/master_ip_failover / usr/local/bin/ rewrite the master_ip_failover script: vim / usr _ bind _ bind _ perluse strict;use warnings FATAL: vim / usr _ bind _ bind _ Env perluse strict;use warnings FATAL = > 'all';use Getopt::Long My ($command, $ssh_user, $orig_master_host, $orig_master_ip,$orig_master_port, $new_master_host, $new_master_ip, $new_master_port); my $vip = '192.168.45.100 percent vip my $brdc =' 192.168.45.255 percent percent my $ifdev = 'ens33';my $key =' 1percent percent my $ssh_start_vip = "/ sbin/ifconfig ens33:$key $vip"; my $ssh_stop_vip = "/ sbin/ifconfig ens33:$key down"; my $exit_code = 0 # my $ssh_start_vip = "/ usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping-Q-A-c 1-I $ifdev $vip;iptables-F;"; # my $ssh_stop_vip = "/ usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key" GetOptions ('command=s' = >\ $command,'ssh_user=s' = >\ $ssh_user,'orig_master_host=s' = >\ $orig_master_host,'orig_master_ip=s' = >\ $orig_master_ip,'orig_master_port=i' = >\ $orig_master_port,'new_master_host=s' = >\ $new_master_host,'new_master_ip=s' = >\ $new_master_ip,'new_master_port=i' = >\ $new_master_port,); exit & main () Sub main {print "\ n\ nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\ n\ n"; if ($command eq "stop" | | $command eq "stopssh") {my $exit_code = 1 world eval {print "Disabling the VIP on old master: $orig_master_host\ n"; & stop_vip (); $exit_code = 0;}; if ($@) {warn "Got Error: $@\ n"; exit $exit_code;} exit $exit_code;} elsif ($command eq "start") {my $exit_code = 10 Eval {print "Enabling the VIP-$vip on the new master-$new_master_host\ n"; & start_vip (); $exit_code = 0;}; if ($@) {warn $@; exit $exit_code;} exit $exit_code;} elsif ($command eq "status") {print "Checking the Status of the script.. OK\ n "; exit 0;} else {& usage (); exit 1;}} sub start_vip () {`ssh $ssh_user\ @ $new_master_host\" $ssh_start_vip\ "`;} # A simple system call that disable the VIP on the old_mastersub stop_vip () {`ssh $ssh_user\ @ $orig_master_host\" $ssh_stop_vip\ "` } sub usage {print "Usage: master_ip_failover-- command=start | stop | stopssh | status-- orig_master_host=host-- orig_master_ip=ip-- orig_master_port=port-- new_master_host=host-- new_master_ip=ip-- new_master_port=port\ n" 4. Create the MHA software directory and copy the configuration file mkdir / etc/masterha cp / root/mha4mysql-manager-0.57/samples/conf/app1.cnf / etc/masterha/ # Edit the configuration file vim / etc/masterha/app1.cnf [server default] # manager configuration file manager_log=/var/log/masterha/app1/manager.log # manager log manager _ workdir=/var/log/masterha/app1#master location to save binlog The path here should be the same as the master_binlog_dir=/usr/local/mysql/data# setting of the bilog configured in master when the automatic failover is set to switch scripts. That is, the above script master_ip_failover_script=/usr/local/bin/master_ip_failover# setting manual switching script master_ip_online_change_script=/usr/local/bin/master_ip_online_change# this password is the password created earlier to monitor the user password=managerremote_workdir=/tmp# setting replication user password repl_password=123# setting replication user repl_user=myslave# setting Script reporl_script=/usr/local/send_reportsecondary_check_script=/usr/local/bin/masterha_secondary_check-s 192.168.45.130-s 192.168.45.130-s 192.168.45.13 setting failure shutdown failure script host shutdown_script= "" # setting the login user name of ssh ssh_user=root# setting monitoring user user= [server1] hostname=192.168.45.133port= 3306 [server2] candidate_master=1# is set as candidate master If this parameter is set, the master-slave switch will be upgraded from this slave library to the master library hostname=192.168.45.130check_repl_delay=0port= 3306 [server3] hostname=192.168.45.134port=3306 test ssh without password authentication. If normal, successful:masterha_check_ssh-conf=/etc/masterha/app1.cnf will be output at last.

Check the health status of the building masterha_check_repl-conf=/etc/masterha/app1.cnf

Configure the virtual ip/sbin/ifconfig ens33:1 192.168.45.100 on master to launch manager1, and start mha nohup masterha_manager on the manager server-- conf=/etc/masterha/app1.cnf-- remove_dead_master_conf-- ignore_last_failover

/ var/log/masterha/app1/manager.log 2 > & 1 & 2. Check the MHA status and you can see that the current master is the mysql node masterha_check_status-- conf=/etc/masterha/app1.cnf

Fault simulation 1. Start monitoring and observation log to record tailf / var/log/masterha/app1/manager.log2, turn off master server pkill-9 mysql

You can see the status of the slave library, and vip switches to one of the slave libraries:

At this point, the client can also connect to the database through the virtual ip:

Mysql-h 192.168.45.100-u root-p

-- the above is the MHA architecture file--

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.