MySQL MHA application practice (scheme actual combat)

1. Environment and conditions

A master-slave architecture environment already exists

Hostname IP address Master-Slave role MHA role leo1192.168.3.2MasterMHA-nodeleo2192.168.3.3slaveMHA-nodeleo3192.168.3.4slaveMHA-node,MHA-managervip192.168.3.6

Master and slave ensure that the state of master and slave is normal.

Mysql > show slave status\ Gterio and SQL processes are YES

Parameter change

Relay_log_purge = 0 # do not automatically delete relay log to facilitate downtime repair data log_bin = / data/log/leo-bin # Open binlog from the library to facilitate downtime repair data expire_logs_days=7log-slave-updates=1

2. Configure SSH

When MHA is running, it will connect to other node servers through SSH service to detect or copy data, so password-free login is required in the cluster.

Execute the following commands within leo1-3:

Ssh-keygen-t rsassh-copy-id-I ~ / .ssh/id_rsa.pub 192.168.3.2ssh-copy-id-I ~ / .ssh/id_rsa.pub 192.168.3.3ssh-copy-id-I ~ / .ssh/id_rsa.pub 192.168.3.4

3. Install MHA Node

Install MHA Node on all mysql nodes

1) install Perl language yum install perl-DBD-MySQL-y # because MHA is developed with perl 2) install MHA Node rpm-ivh mha4mysql-node-0.56-0.el6.noarch.rpm download address https://code.google.com/archive/p/mysql-master-ha/downloads 3) create command soft connection easy to use: ls-s / app/mysql/bin/mysqlbinlog / usr/bin/mysqlbinlog ls-s / app/mysql/bin/mysql / usr/bin/mysql 4) check the MHA account grant all privileges on *. * to mha@'192.168.3%' identified by 'mha' Select user,host from mysql.user

4 、 MHA-Manager

Manager can be installed on any machine.

1) use epel source to install perl environment wget-O / etc/yum.repo.d/epel.repo http://mirrors.aliyum.com/repo/eprl-6.repo 2) install management node since package yum intall-u perl-Config-Tiny* epel-release perl-Log-Dispatch* perl-Parallel* per-Time* 3) install MHA-Node rpm-ivh mha4mysql-node-0.56-0.el6.noarch .rpm 4) install MHA-Manger rpm-ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

5. Configure the MHA management node

Mkdir-p / etc/mha mkdir-p / var/log/mha/app1 vi / etc/mha/app1.cnf [server default] manager_log=/var/log/mha/app1/manager.log manager_workdir=/var/log/mha/app1.log mater_binlog_dir=/app/mysql/data # # MHA saves the main library binlog path user=mha # # mysql Database Authorization yonghu password=mha ping_interval=2 # # Monitoring ping package interval repl_user=rep # # Master-Slave replication user repl_password=leo123 ssh_user=root repot_script=/usr/local/send_report # # failure When the script secondary_check_script=/usr/local/bin/masterha_secondary_check-s leo3-s leo2-- user=root-- master_host=leo1-- master_ip=192.168.3.2-- master_port=3306 # manager cannot be contacted after it occurs, it will be contacted via leo2, Leo3 check the status of leo1 shutdown_script= "" [server1] hostname=192.168.3.2 port=3306 [server2] hostname=192.168.3.4 port=3306 candidate_master=1 # # when this parameter is set, server2 will give priority to master library check_repl_delay=0 # # MHA negligent master-slave replication delay [server3] hostname=192.168.3.3 port=3306

6. Start and test

1) check ssh private login master_check_ssh-conf=/etc/mha/app1.cnf2) check master-slave replication status master_check_repl-conf=/etc/mha/app1.cnf3) start MHA nohup master_manager-- conf=/etc/mha/app1.cnf-- remove_dead_master_conf-- ignore_last_failver

/ var/log/mha/app1/manager.log 2 > & 1 & # # remove_dead_master_conf deletes master information in the configuration file # # ignore_last_failver last failover

7. Configure VIP drift

1) use keepalived 2) fill the configuration file with the following parameters master_ip_failover_script=/usr/local/bin/master_ip_failover 3) add the following parameters to the script: my = $vip= '192.168.3.6; my $key= 0 my $ssh_start_vip = "/ sbin/ifconfig eth0:$key $vip"; my $ssh_stop_vip = "/ sbin/ifconfig eth0:$key down"; chmod + x / etc/mha/master_ip_failover