MYSQL-MHA High Availability Experimental test

Description

CentOS 6.5 mysql 5.5.37 mha4mysql-manager-0.55 mha4mysql-node-0.54

Manager 192.168.1.1
DB1 192.168.1.2
DB2 192.168.1.3
DB3 192.168.1.4

configuring MySQL Master-Slave
DB1:

Server-id = 1
READ_ONLY = 1
Relay_log_purge=0
Binlog_format=mixed

DB2,DB3 Ignore

DB1 Master

Grant Replication Slave on * * to [e-mail protected] ' 192.168.1.% ' identified by ' passwd '; flush privileges;stop slave; #设置复制权 Limited account grant all privileges on * * to ' mha_manager ' @ ' 192.168.1.% ' identified by ' 123456 '; reset Master;show master Status\g

DB2 Preparation

Grant Replication Slave on * * to [e-mail protected] ' 192.168.1.% ' identified by ' passwd '; GRANT all privileges on * * to ' mha_manager ' @ ' 192.168.1.% ' identified by ' 123456 '; flush privileges;reset master;slave Stop Change Master to master_host= ' 192.168.1.2 ', master_port=3306,master_user= ' mharep ', master_password= ' passwd ', master _log_file= ' mysql-bin.000001 ', master_log_pos=107;slave start;show slave status\g;

DB3 from

Reset Master;slave stop; GRANT all privileges on * * to ' mha_manager ' @ ' 192.168.1.% ' identified by ' 123456 '; flush Privileges;change Master to master _host= ' 192.168.1.2 ', master_port=3306,master_user= ' mharep ', master_password= ' passwd ', master_log_file= ' Mysql-bin.000001 ', Master_log_pos=107;slave start;show slave status\g;

Configure Dual-Machine Trust
Manager

Ssh-keygen-t rsafor i in db1 DB2 db3;do ssh-copy-id-i ~/.ssh/id_rsa.pub [email protected] $i;d One

DB1 Master

ssh-keygen-t RSA for I in Manager DB2 db3;do ssh-copy-id-i ~/.ssh/id_rsa.pub [email protected] $i;d One

DB2 preparation and DB3 from the configuration slightly

Installing MYSQL-MHA
Https://downloads.mariadb.com/files/MHA

wget Https://downloads.mariadb.com/files/MHA/mha4mysql-manager-0.55-0.el6.noarch.rpmwget https:// downloads.mariadb.com/files/mha/mha4mysql-node-0.54-0.el6.noarch.rpm

DB1 main DB2 prepared DB3 from
Yum Install Perl-dbd-mysql
Yum Localinstall mha4mysql-node-0.54-0.el6.noarch.rpm

Manager

Yum Install Perl cpanyum install perl-dbd-mysql perl-config-tiny perl-log-dispatch Perl-parallel-forkmanager

#这两个yum没有安装上 Perl-log-dispatch Perl-parallel-forkmanager

wget http://downloads.naulinux.ru/pub/NauLinux/6x/x86_64/sites/School/RPMS/ Perl-log-dispatch-2.27-1.el6.noarch.rpmwget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/ Perl-parallel-forkmanager-0.7.5-2.2.el6.rf.noarch.rpmwget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/ Rpms/perl-mail-sender-0.8.16-1.el6.rf.noarch.rpmwget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/ perl-mail-sendmail-0.79-1.2.el6.rf.noarch.rpm

RPM-IVH perl-mail-sender-0.8.16-1.el6.rf.noarch.rpm
RPM-IVH perl-mail-sendmail-0.79-1.2.el6.rf.noarch.rpm
Yum Localinstall perl-log-dispatch-2.27-1.el6.noarch.rpm
Yum Localinstall perl-parallel-forkmanager-0.7.5-2.2.el6.rf.noarch.rpm
Yum Localinstall mha4mysql-node-0.54-0.el6.noarch.rpm
Yum Localinstall mha4mysql-manager-0.55-0.el6.noarch.rpm

Configure MYSQL-MHA
[Email protected] ~]# masterha_<tab>
Masterha_check_repl Masterha_conf_host Masterha_master_switch
Masterha_check_ssh Masterha_manager Masterha_secondary_check
Masterha_check_status Masterha_master_monitor Masterha_stop

Tar-xzf mha4mysql-manager-0.55.tar.gz
LS mha4mysql-manager-0.55/samples/conf
MASTERHA_DEFAULT.CNF app1.cnf

Mkdir-p/usr/local/mha/scripts
CP mha4mysql-manager-0.55/samples/scripts/*/usr/local/mha/scripts

[[email protected] ~]# vim /usr/local/mha/mha.conf  [server default]user=mha_managerpassword=123456manager_workdir=/usr/local/mhamanager_log=/usr/local /mha/manager.logremote_workdir=/usr/local/mhassh_user=rootrepl_user=mhareprepl_password=passwdping_interval= 1secondary_check_script= masterha_secondary_check -s 192.168.1.2 -s 192.168.1.3  -s 192.168.1.4master_ip_failover_script=/usr/local/mha/scripts/master_ip_failover#shutdown_script= / Usr/local/mha/scripts/power_managerreport_script= /usr/local/mha/scripts/send_reportmaster_ip_online_ change_script= /usr/local/mha/scripts/master_ip_online_change[server1]hostname=192.168.1.2ssh_port= 22master_binlog_dir=/var/mysql/logcandidate_master=1[server2]hostname=192.168.1.3ssh_port=22master_binlog_dir= /var/mysql/logcandidate_master=1[server3]hostname=192.168.1.4ssh_port=22master_binlog_dir=/var/mysql/logno_ Master=1 

#验证ssh通讯
Masterha_check_ssh--conf=/usr/local/mha/mha.conf

650) this.width=650; "title=" qq20150108165603.jpg "alt=" wkiol1suedosfvm9aazr6nm0jaq282.jpg "src="/HTTP/ S3.51cto.com/wyfs02/m00/58/52/wkiol1suedosfvm9aazr6nm0jaq282.jpg "/>

#验证mysql复制
wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/extras/RPMS/perl-Net-Telnet-3.03-2.el6.rfx.noarch.rpm
Yum Localinstall perl-net-telnet-3.03-2.el6.rfx.noarch.rpm
[Email protected] ~]# MASTERHA_CHECK_REPL--conf=/usr/local/mha/mha.conf
Thu Jan 8 18:05:40-[WARNING] Global configuration file/etc/masterha_default.cnf not found. Skipping.
Thu Jan 8 18:05:40-[INFO] Reading application default configurations from/usr/local/mha/mha.conf.
Thu Jan 8 18:05:40-[INFO] Reading server configurations from/usr/local/mha/mha.conf.
Thu Jan 8 18:05:40-[INFO] mha::mastermonitor version 0.55.
Thu Jan 8 18:05:40-[INFO] Dead Servers:
Thu Jan 8 18:05:40-[INFO] Alive Servers:
Thu Jan 8 18:05:40-[INFO] 192.168.1.2 (192.168.1.2:3306)
Thu Jan 8 18:05:40-[INFO] 192.168.1.3 (192.168.1.3:3306)
Thu Jan 8 18:05:40-[INFO] 192.168.1.4 (192.168.1.4:3306)
Thu Jan 8 18:05:40-[info] Alive Slaves:
Thu Jan 8 18:05:40-[INFO] 192.168.1.3 (192.168.1.3:3306) version=5.5.37-log (oldest major Version between slaves ) log-bin:enabled
Thu Jan 8 18:05:40-[info] replicating from 192.168.1.2 (192.168.1.2:3306)
Thu Jan 8 18:05:40-[INFO] Primary candidate for the new Master (Candidate_master is set)
Thu Jan 8 18:05:40-[INFO] 192.168.1.4 (192.168.1.4:3306) version=5.5.37-log (oldest major Version between slaves ) log-bin:enabled
Thu Jan 8 18:05:40-[info] replicating from 192.168.1.2 (192.168.1.2:3306)
Thu Jan 8 18:05:40-[INFO] not candidate for the new Master (No_master is set)
Thu Jan 8 18:05:40-[INFO] current Alive master:192.168.1.2 (192.168.1.2:3306)
Thu Jan 8 18:05:40-[INFO] Checking slave configurations.
Thu Jan 8 18:05:40-[INFO] Checking replication filtering settings.
Thu Jan 8 18:05:40-[info] binlog_do_db=, binlog_ignore_db=
Thu Jan 8 18:05:40-[INFO] Replication filtering check OK.
Thu Jan 8 18:05:40-[INFO] Starting SSH connection tests.
Thu Jan 8 18:05:41-[INFO] All SSH connection tests passed successfully.
Thu Jan 8 18:05:41-[info] Checking MHA Node version:
Thu Jan 8 18:05:42-[INFO] Version check OK.
Thu Jan 8 18:05:42-[info] Checking SSH publickey authentication settings on the current master.
Thu Jan 8 18:05:42-[info] healthcheck:ssh to 192.168.1.2 is reachable.
Thu Jan 8 18:05:42-[info] Master MHA Node version is 0.54.
Thu Jan 8 18:05:42-[INFO] Checking Recovery script configurations on the current master.
Thu Jan 8 18:05:42-[INFO] executing command:save_binary_logs--command=test--start_pos=4--binlog_dir=/var/mys Ql/log--output_file=/usr/local/mha/save_binary_logs_test--manager_version=0.55--start_file=mysql-bin.000002
Thu Jan 8 18:05:42-[INFO] connecting to [email protected] (192.168.1.2).
Creating/usr/local/mha if not exists. Ok.
Checking output directory is accessible or not:
Ok.
Binlog found At/var/mysql/log, up to mysql-bin.000002
Thu Jan 8 18:05:42-[INFO] Master setting check done.
Thu Jan 8 18:05:42-[info] Checking SSH publickey authentication and Checking recovery script configurations on all Alive slave servers.
Thu Jan 8 18:05:42-[INFO] executing command:apply_diff_relay_logs--command=test--slave_user= ' Mha_manager '-- slave_host=192.168.1.3--slave_ip=192.168.1.3--slave_port=3306--workdir=/usr/local/mha--target_version=5.5.37- Log--manager_version=0.55--relay_log_info=/var/mysql/data/relay-log.info--relay_dir=/var/mysql/data/--slave_ Pass=xxx
Thu Jan 8 18:05:42-[INFO] connecting to [email protected] (192.168.1.3:22).
Checking Slave recovery Environment settings.
Opening/var/mysql/data/relay-log.info ... ok.
Relay Log found At/var/mysql/data, up to mysql-relay-bin.000007
Temporary relay log file is/var/mysql/data/mysql-relay-bin.000007
Testing MySQL connection and privileges. Done.
Testing Mysqlbinlog Output: Done.
Cleaning up test file (s): Done.
Thu Jan 8 18:05:42-[INFO] executing command:apply_diff_relay_logs--command=test--slave_user= ' Mha_manager '-- slave_host=192.168.1.4--slave_ip=192.168.1.4--slave_port=3306--workdir=/usr/local/mha--target_version=5.5.37- Log--manager_version=0.55--relay_log_info=/var/mysql/data/relay-log.info--relay_dir=/var/mysql/data/--slave_ Pass=xxx
Thu Jan 8 18:05:42-[INFO] connecting to [email protected] (192.168.1.4:22).
Checking Slave recovery Environment settings.
Opening/var/mysql/data/relay-log.info ... ok.
Relay Log found At/var/mysql/data, up to mysql-relay-bin.000006
Temporary relay log file is/var/mysql/data/mysql-relay-bin.000006
Testing MySQL connection and privileges. Done.
Testing Mysqlbinlog Output: Done.
Cleaning up test file (s): Done.
Thu Jan 8 18:05:43-[INFO] slaves settings check done.
Thu Jan 8 18:05:43-[INFO]
192.168.1.2 (Current master)
+--192.168.1.3
+--192.168.1.4

Thu Jan 8 18:05:43-[INFO] Checking replication Health on 192.168.1.3.
Thu Jan 8 18:05:43-[info] OK.
Thu Jan 8 18:05:43-[INFO] Checking replication Health on 192.168.1.4.
Thu Jan 8 18:05:43-[info] OK.
Thu Jan 8 18:05:43-[info] Checking master_ip_failover_script Status:
Thu Jan 8 18:05:43-[info]/usr/local/mha/scripts/master_ip_failover--command=status--ssh_user=root--orig_mast er_host=192.168.1.2--orig_master_ip=192.168.1.2--orig_master_port=3306

In SCRIPT test====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 192.168.1.100;/sbin/arping-i eth0-c 3-s 192.168.1.1 XX 192.168.1.1 >/dev/null 2>&1===

Checking the Status of the script. Ok
Thu Jan 8 18:05:46-[info] OK.
Thu Jan 8 18:05:46-[WARNING] shutdown_script is not defined.
Thu Jan 8 18:05:46-[INFO] Got exit code 0 (not master dead).

MySQL Replication Health is OK.

Start MHA
[Email protected] ~]# Masterha_manager--conf=/usr/local/mha/mha.conf &
View MHA
[Email protected] ~]# Masterha_check_status--conf=/usr/local/mha/mha.conf
MHA (pid:2686) is running (0:PING_OK), master:192.168.1.2

650) this.width=650; "title=" qq20150108183928.jpg "alt=" wkiol1suehgwwik5aapp_yskcte055.jpg "src="/HTTP/ S3.51cto.com/wyfs02/m01/58/52/wkiol1suehgwwik5aapp_yskcte055.jpg "/>

Stop MHA
[Email protected] ~]# masterha_stop--conf=/usr/local/mha/mha.conf

Verify MHA:

[Email protected] ~]# IP a #现在有VIP
[[Email protected] ~]# service mysqld stop #mysql停止, at this time VIP will do transfer
Shutting down MySQL ... success!
[Email protected] ~]# IP a #已经没有VIP

IP has been transferred to DB2, and on DB3 mysql> show slave status\g View Master_server_id:2

650) this.width=650; "title=" qq20150108201913.jpg "alt=" wkiom1sud8pxyxz0aaov3qg1v5a991.jpg "src="/HTTP/ S3.51cto.com/wyfs02/m01/58/55/wkiom1sud8pxyxz0aaov3qg1v5a991.jpg "/>

This article is from the "back Line Swallow" blog, please make sure to keep this source http://bbotte.blog.51cto.com/6205307/1600894

MYSQL-MHA High Availability Experimental test