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

This article mainly gives you a brief introduction to the MHA high availability cluster, and you can check the relevant professional terms on the Internet or find some related books to supplement them. Here, we will not dabble in, go straight to the topic, and hope to bring you some practical help.

Overview of MHA

Japanese DeNA company youshimaton (now works for Facebook company) development

A set of excellent high availability software for failover and master-slave upgrade in MySQL high availability environment

The composition of MHA

MHA Manager (Management Node)

MHA Node (data node)

Characteristics of MHA

In the process of automatic failover, MHA attempts to save binary logs from the down primary server to ensure that the data is not lost to the maximum extent.

Using semi-synchronous replication of MySQL 5.5can greatly reduce the risk of data loss

Experimental thinking

1. MHA architecture

(1) Database installation

(2) one master and two followers

(3) MHA building

2. Fault simulation

(1) failure of the main library

(2) the alternative master library becomes the main library.

(3) from Library 2, point the alternate master library as the master library.

Deployment environment

1. Experimental environment

Server role IP address Service package master192.168.142.130mha4mysql-nodeslave1192.168.142.131mha4mysql-nodeslave2192.168.142.132mha4mysql-nodemanager192.168.142.133Textmha4mysql-manager, mha4mysql-node

2. Experimental requirements

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

3. Experimental ideas.

(1) install MySQL database

(2) configure MySQL with one master and two slaves

(3) install MHA software

(4) configure password-free authentication

(5) configure MySQL MHA high availability

(6) simulate master failover

Step 1: install the database on three MySQL nodes

(please use 5.6.36 for MySOL version and 2.8.6 for cmake version)

1. Install the environment on which compilation depends

Yum install-y install ncurses-devel gcc gcc-c++ perl-Module-Install

2. Remote mount

Mkdir / abcmount.cifs / / 192.168.1421/mha / abc/

3. Install the gmake compiler

Cd / abc/mha/tar zxvf cmake-2.8.6.tar.gz-C / opt/cd / opt/cmake-2.8.6/./configuregmake & & gmake install

4. Install MySQL database

Cd / abc/mha/tar zxvf mysql-5.6.36.tar.gz-C / opt/cd / 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=/etcmake & & make installcp 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/profileuseradd-M-s / sbin/nologin mysqlchown-R mysql.mysql / usr/local/mysql/usr/local/mysql/scripts/mysql_install_db\-- basedir=/usr/local/mysql\-- datadir=/usr/local/mysql/data\-- user=mysql

5. Modify the main configuration file / etc/my.cnf file of master. The server-id of three servers cannot be the same.

Vim / etc/ my.cnf [mysqld] server-id = 1log_bin = master-binlog-slave-updates = true step 2: configure slave server 1

Modify the main configuration file of mysql

# modify or add the following in / etc/my.cnf. [mysqld] server-id = 2log_bin = master-binrelay-log = relay-log-binrelay-log-index = slave-relay-bin.index step 3: configure slave server 2

1. Modify the main configuration file of mysql: / etc/my.cnf

Vim / etc/ my.cnf [MySQL] server-id = 3log_bin = master-binrelay-log = relay-log-binrelay-log-index = slave-relay-bin.index

two。 Make two soft connections on master, slave1 and slave2 respectively

Ln-s / usr/local/mysql/bin/mysql / usr/sbin/ln-s / usr/local/mysql/bin/mysqlbinlog / usr/sbin/

Start mysql on 3.master, slave1 and slave2, and check the status of the startup.

# start mysql/usr/local/mysql/bin/mysqld_safe-- user=mysql & # check the service port status netstat-ntap | grep 330 disable firewall and security features systemctl stop firewalld.service setenforce 0 step 4: configure MySQL with one master and two slaves

1.mysq master-slave configuration is relatively simple to pay attention to is authorization, authorizing two users on all database nodes, one is the synchronous use of user myslave from the slave library, and the other is the use of manager to monitor user mha

Grant replication slave on *. * to 'myslave'@'192.168.142.%' identified by' 123 grant all privileges on *. * to 'mha'@'192.168.142.%' identified by' manager';flush privileges

two。 According to the theory, the following three authorizations do not need to be added, but when doing the case experiment environment, check the mysql master / slave to report errors through MHA, and report that the two slave libraries cannot connect to the master database through the host name, so all databases plus the following authorization

Grant all privileges on *. * to 'mha'@'master' identified by' manager';grant all privileges on *. * to 'mha'@'slave1' identified by' manager';grant all privileges on *. * to 'mha'@'slave2' identified by' manager';# refresh database flush privileges

3. View binaries and synchronization points on a master host

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | master-bin.000001 | 1292 | +- -+

4. Perform synchronization on slave1 and slave2 respectively

Change master to master_host='192.168.142.130',master_user='myslave',master_password='123',master_log_file='masterbin.000001',master_log_pos=1292;start slave;# enables slave

5. Check that both IO and SQL threads represent yes and represent normal synchronization.

Show slave status\ G; Slave_IO_Running: Yes Slave_SQL_Running: Yes# must set two slaves to read-only mode # set two slaves to read-only mode set global read_only=1;# refresh database flush privileges Step 5: install MHA software (MHA dependent environments are installed on all servers Install epel source first) # turn off firewall and security features systemctl stop firewalld.service setenforce "install MHA dependent environment 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 (install node on all servers) tar zxvf / abc/rpm/MHA/mha4mysql-node-0.57.tar .gzcd mha4mysql-node-0.57/perl Makefile.PLmake & & make install step 6: install manager components on MHA-manager (Note: install node components before installing manager components) tar zxvf / abc/rpm/MHA/mha4mysql-manager-0.57.tar.gzcd mha4mysql-manager-0.57/perl Makefile.PL makemake installmanager will generate several tools under / usr/local/bin after installation: masterha_conf_host # add or remove Configured server information masterha_stop # close managermasterha_manager # start manager script masterha_check_repl # check mysql replication masterha_master_monitor # check master downtime masterha_check_ssh # check MHA SSH configuration status masterha_master_switch # control failover (automatic or manual) masterha_check_status # detect when After the pre-MHA running status node installation, several scripts are also generated under / usr/local/bin (these tools usually start from MHA Manager's scripts No artificial operation is required) apply_diff_relay_logs # identifies different relay log events and applies them to other slavefilter_mysqlbinlog # remove unnecessary ROLLBACK events (MHA no longer uses this tool) purge_relay_logs # clears relay logs (does not block SQL threads) save_binary_logs # saves and Copy the binary log of master step 7: configure no password authentication

(1) configure password-free authentication to all database nodes on manager

# because there is no password authentication, press enter key ssh-keygen-t rsa ssh-copy-id 192.168.142.130ssh-copy-id 192.168.142.131ssh-copy-id 192.168.142.132 all the way

(2) configure password-free authentication to database nodes slave1 and slave2 on master

Ssh-keygen-t rsassh-copy-id 192.168.142.131ssh-copy-id 192.168.142.132

(3) configure password-free authentication to database nodes master and slave2 on slave1

Ssh-keygen-t rsassh-copy-id 192.168.142.130ssh-copy-id 192.168.142.132

(4) configure password-free authentication to database nodes master and slave1 on slave2

Ssh-keygen-t rsassh-copy-id 192.168.142.130ssh-copy-id 192.168.142.131 step 8: configure MHA

1. Copy the relevant script on the manager node to the / usr/local/bin directory

Cp-ra / root/mha4mysql-manager-0.57/samples/scripts / usr/local/bin# copy will have four executable files # View directory permissions ll / usr/local/bin/scripts/-rwxr-xr-x. 1 1001 1001 3648 May 31 2015 master_ip_failover # script managed by VIP when switching automatically-rwxr-xr-x. 1 1001 1001 9870 May 31 2015 master_ip_online_change # Management of VIP during online switching-rwxr-xr-x. 1 1001 1001 11867 May 31 2015 power_manager # script to shut down the host after a failure-rwxr-xr-x. 1 1001 1001 1360 May 31 2015 send_report # script that sends alerts after failover

two。 Copy the scripts managed by VIP to the / usr/local/bin directory when copying the automatic switching above, where scripts are used to manage VIP

Cp / usr/local/bin/scripts/master_ip_failover / usr/local/bin

3. Rewrite the master_ip_failover script: (delete the original content and write directly to the following)

Vim / usr/local/bin/master_ip_failover #! / usr/bin/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); # add the content section my $vip =' 192.168.142.200 'sectional my $brdc =' 192.168.142.255 'leading my $ifdev =' ens33';my $key ='1' My $ssh_start_vip = "/ sbin/ifconfig ens33:$key $vip"; my $ssh_stop_vip = "/ sbin/ifconfig ens33:$key down"; my $exit_code = 0 role 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/masterhacp / root/mha4mysql-manager-0.57/samples/conf/app1.cnf / etc/masterhavim / 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 saves the location of binlog, and the path here is the same as the master_binlog_dir=/usr/local/mysql/data# configuration of bilog configured in master. Switch script when automatic failover is set. 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= "# set the login user name of ssh ssh_user=root# setting monitoring user user= [server1] hostname=192.168.142.port= 3306 [server2] candidate_master=1check_repl_delay=0hostname=192.168. 142. Port = 3306 [server3] hostname=192.168.142.port=3306

5. Test ssh password-less authentication

Masterha_check_ssh-conf=/etc/masterha/app1.cnfmasterha_check_repl-conf=/etc/masterha/app1.cnf# Note: for the first configuration, you need to manually enable the virtual IP/sbin/ifconfig ens33:1 192.168.142.200 on master.

6. Start MHA

Nohup masterha_manager-conf=/etc/masterha/app1.cnf-remove_dead_master_conf-ignore_last_failover

/ var/log/masterha/app1/manager.log 2 > & 1 &

7. Looking at the MHA status, you can see that the current master is a mysql1 node.

Masterha_check_status-conf=/etc/masterha/app1.cnf

8. Looking at the MHA log, you can also see that the current master is 192.168.142.130

Cat / var/log/masterha/app1/manager.log step 9: fault Simulation

1. Start monitoring and observation log recording

Tailf / var/log/masterha/app1/manager.log

2. Check the address change

Pkill-9 mysql # downtime mysql service VIP address will not disappear because the manager node stops MHA service The VIP address is transferred to the slave1 # View the vip address transfer ifconfigens33 from the server: flags=4163 mtu 1500 inet 192.168.142.131 netmask 255.255.255.0 broadcast 192.168.142.255 inet6 fe80::b81a:9df:a960:45ac prefixlen 64 scopeid 0x20 ether 00:0c:29:97:8e:66 txqueuelen 1000 (Ethernet) RX packets 1687418 bytes 1157627305 (1.0 GiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 1376468 bytes 170996461 (163.0 MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0ens33:1: flags=4163 mtu 1500 inet 192.168.142.200 netmask 255.255.255.0 broadcast 192.168.142.255 ether 00:0c:29:97:8e:66 txqueuelen 1000 (Ethernet)

3. Open a new terminal on mha-manager and install a mysql directly on yum.

Yum install mysql-y # gives permission on slave1, otherwise the mha-manager side will not be able to access the database: grant all on *. * to 'root'@'%' identified by' abc123';# logs in on mh-manager: mysql-h 192.168.142.200-uroot-pEnter password: # enter the password (1) to create a database school, and create a table info, write simple content MySQL [(none)] > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | test | +-+ 4 rows in set (0.00 sec) MySQL [(none)] > create database school Query OK, 1 row affected (0.00 sec) MySQL [(none)] > use school;Database changedMySQL [school] > create table info (id int); Query OK, 0 rows affected (0.01 sec) (2) after it is created and viewed in the database on slave1, the data mysql > show databases will be synchronized. +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | school | | test | +-+ (3) because slave1 and slave2 are synchronized with each other So data on slave2 should also be synchronized with mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | school | | test |

+-+

MHA high availability cluster detailed introduction to the first to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.