MYSQL master-slave replication deployment MHA automatic failover 04/03 Update SLTechnology News&Howtos

MYSQL master-slave replication deployment MHA automatic failover

2025-04-03 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

one。 Environment description

Operating system: RHEL 6.4

Mysql: 5.6.27

Role

IP address

Hostnam

Server ID

Types

Main library

192.168.222.156

Oradb

ten

Write

Standby library, MHA management server

192.168.222.157

Mysqldb2

eleven

Read and monitor cluster groups

II. Deploy MHA

1. Install MHA NODE in the main library

1.1 install the required PERL module

Yum-y install * zlib-dev*

Install.sh

#! / bin/bash

Wget http://xrl.us/cpanm-- no-check-certificate

Mv cpanm / usr/bin

Chmod 755 / usr/bin/cpanm

Cat > / root/list grant all on *. * to 'root'@'192.168.222.%' identified by' 123456users; / * MHA monitoring users * /

4.4 check the name and location of Binlog, MASTER_LOG and MASTER_LOG_POS at the time of backup on the main database

Head-n 30 all.sql | grep-I "CHANGE MASTER TO"

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000068', MASTER_LOG_POS=325

4.5 copy backup to standby library

Scp all.sql root@192.168.222.157:/root/olm

4.6 Import data in standby database

Mysql > source / root/olm/all.sql

Mysql > flush privileges

Mysql >

Change master to master_host='192.168.222.156',master_user='repl',master_password='123456'

Master_port=3306,master_log_file='mysql-bin.000068',master_log_pos=325

Start slave

-- View replication status

Show slave status\ G

5. Configure MHA

5.1 create a MHA working directory and create related configuration files

Mkdir-p / etc/masterha

Vi / etc/masterha/app1.cnf

[server default]

Manager_log=/masterha/app1/app1.log

Manager_workdir=/masterha/app1

Master_binlog_dir=/app/oracle/oradata2/mysql-5.5.37/data

Master_ip_failover_script=/usr/local/bin/master_ip_failover

Master_ip_online_change_script=/usr/local/bin/master_ip_online_change

Password=123456

Ping_interval=1

Remote_workdir=/tmp

Repl_password=123456

Repl_user=repl

Secondary_check_script=/usr/local/bin/masterha_secondary_check-s mysqldb2-s oradb-- user=root-- master_host=oradb-- master_ip='192.168.222.156'-- master_port=3306

Shutdown_script= ""

Ssh_user=root

User=root

[server1]

Hostname=192.168.222.156

Port=3306

[server2]

Candidate_master=1

Check_repl_delay=0

Hostname=192.168.222.157

Port=3306

5.2 set up a script that periodically cleans up the relay log

Crontab-e

4 * / usr/local/bin/purge_relay_logs-- user=root-- password=123456-- disable_replay_log_purge-- port=3306-- workdir=/usr/local/mysql/data-- disable_relay_log_purge > > / usr/loca/masterha/log/purge_relay_logs.log 2 > & 1

5.2 Edit the / etc/bashrc file and add the following at the end of the file to avoid failing to find the mysqlbinlog when MHA performs a failover

PATH= "$PATH:/usr/local/mysql/bin"

Export PATH

5.3 check the configuration of SSH

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

5.4 check MYSQL replication

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

5.5 start MHA Manger monitoring

Tail-f / masterha/app1/app1.log

-- start MHA Manger monitoring in another SSH window. -- remove_dead_master_conf this parameter means that when a master-slave switch occurs, the IP of the old master library will be removed from the configuration file.

-- ignore_last_failover by default, if MHA detects continuous downtime and the two outages are less than 8 hours, then Failover will not occur. The reason for this restriction

-- to avoid the ping-pong effect. This parameter represents the file generated by the last switch triggered by MHA. By default, app1 will be generated under / masterha/paa1 after the switch occurs in MHA.

-- failover.complete file. If you find that the file exists in the directory next time, you will not be allowed to trigger the switch unless you receive rm-rf / masterha/paa1/app1 after the first switch. -- failover.complete, for convenience, we add the-- ignore_last_failover parameter every time we start MHA.

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

< /dev/null >

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

-- check whether MHA Manager monitoring is normal

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

App1 (pid:4758) is running (0:PING_OK), master:192.168.222.156

-by default, the status is 10:INITIALIZING_MONITOR within 10 seconds. When the status is changed to 0:PING_OK, it indicates that the monitoring to the MASTER has been enabled.

The command to turn off MHA is as follows:

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

6. Configure VIP

Download and install the cluster heartbeat software keepalived in the main library

Visit http://www.keepalived.org/software/ to download keepalived-1.2.18.tar.gz

Tar vzxf keepalived-1.2.18.tar.gz

Cd keepalived-1.2.18

. / configure-- prefix=/usr/local/keepalived

Make

Make install

Cp / usr/local/keepalived/etc/rc.d/init.d/keepalived / etc/rc.d/init.d/

Cp / usr/local/keepalived/etc/sysconfig/keepalived / etc/sysconfig/

Mkdir / etc/keepalived

Cp / usr/local/keepalived/etc/keepalived/keepalived.conf / etc/keepalived/

Cp / usr/local/keepalived/sbin/keepalived / usr/sbin/

Cd / etc/keepalived/

Vi keepalived.conf

-- here is the added configuration

[root@oradb ~] # cat / etc/keepalived/keepalived.conf

! Configuration File for keepalived

Global_defs {

Notification_email {

Acassen@firewall.loc

Failover@firewall.loc

Sysadmin@firewall.loc

}

Notification_email_from Alexandre.Cassen@firewall.loc

Smtp_server 127.0.0.1

Smtp_connect_timeout 30

Router_id MySQL-ha

}

Vrrp_instance VI_1 {

State BACKUP

Interface eth0

Virtual_router_id 51

Priority 150

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

192.168.222.158/24

}

Service keepalived start

Using the ip add command, you can see that the virtual IP 192.168.222.158 has been bound to the eth0. Note that you cannot see the virtual IP with ifconfig.

[root@oradb ~] # ip add

1: lo: mtu 16436 qdisc noqueue state UNKNOWN

Link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

Inet 127.0.0.1/8 scope host lo

Inet6:: 1/128 scope host

Valid_lft forever preferred_lft forever

2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000

Link/ether 00:0c:29:53:7e:f0 brd ff:ff:ff:ff:ff:ff

Inet 192.168.222.156/24 brd 192.168.222.255 scope global eth0

Inet 192.168.222.158/24 scope global secondary eth0

Inet6 fe80::20c:29ff:fe53:7ef0/64 scope link

Valid_lft forever preferred_lft forever

3: eth2: mtu 1500 qdisc pfifo_fast state UP qlen 1000

Link/ether 00:0c:29:53:7e:fa brd ff:ff:ff:ff:ff:ff

Inet 192.168.1.11/24 brd 192.168.1.255 scope global eth2

Inet6 fe80::20c:29ff:fe53:7efa/64 scope link

Valid_lft forever preferred_lft forever

6.2 download and install the cluster heartbeat software keepalived from the standby library

Visit http://www.keepalived.org/software/ to download keepalived-1.2.18.tar.gz

Tar vzxf keepalived-1.2.18.tar.gz

Cd keepalived-1.2.18

. / configure-- prefix=/usr/local/keepalived

Make

Make install

Cp / usr/local/keepalived/etc/rc.d/init.d/keepalived / etc/rc.d/init.d/

Cp / usr/local/keepalived/etc/sysconfig/keepalived / etc/sysconfig/

Mkdir / etc/keepalived

Cp / usr/local/keepalived/etc/keepalived/keepalived.conf / etc/keepalived/

Cp / usr/local/keepalived/sbin/keepalived / usr/sbin/

Cd / etc/keepalived/

Vi keepalived.conf

-- here is the added configuration

! Configuration File for keepalived

Global_defs {

Notification_email {

Acassen@firewall.loc

Failover@firewall.loc

Sysadmin@firewall.loc

}

Notification_email_from Alexandre.Cassen@firewall.loc

Smtp_server 127.0.0.1

Smtp_connect_timeout 30

Router_id MySQL-ha

}

Vrrp_instance VI_1 {

State BACKUP

Interface eth0

Virtual_router_id 51

Priority 120

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

192.168.222.158/24

}

Service keepalived start

6.2 introduction of keepalived by MHA

Edit the master_ip_failover script in the MHA monitoring node (slave library)

Vi / usr/local/bin/master_ip_failover

#! / usr/bin/env perl

# This program is free software; you can redistribute it and/or modify

# it under the terms of the GNU General Public License as published by

# the Free Software Foundation; either version 2 of the License, or

# (at your option) any later version.

# This program is distributed in the hope that it will be useful

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License

# along with this program; if not, write to the Free Software

# Foundation, Inc.

# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA

# # Note: This is a sample script and is not complete. Modify the script based on your environment.

Use strict

Use warnings FATAL = > 'all'

Use Getopt::Long

Use MHA::DBHelper

My (

$command, $ssh_user, $orig_master_host

$orig_master_ip, $orig_master_port, $new_master_host

$new_master_ip, $new_master_port, $new_master_user

$new_master_password, $cmd

);

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

'new_master_user=s' = >\ $new_master_user

'new_master_password=s' = >\ $new_master_password

);

Exit & main ()

Sub main {

If ($command eq "stop" | | $command eq "stopssh") {

# $orig_master_host, $orig_master_ip, $orig_master_port are passed.

# If you manage master ip address at global catalog database

# invalidate orig_master_ip here.

My $exit_code = 1

Eval {

# updating global catalog, etc

$exit_code = 0

}

If ($@) {

Warn "Got Error: $@\ n"

Exit $exit_code

}

Exit $exit_code

}

Elsif ($command eq "start") {

# all arguments are passed.

# If you manage master ip address at global catalog database

# activate new_master_ip here.

# You can also grant write access (create user, set read_only=0, etc) here.

My $exit_code = 10

Eval {

My $new_master_handler = new MHA::DBHelper ()

# args: hostname, port, user, password, raise_error_or_not

$new_master_handler- > connect ($new_master_ip, $new_master_port

$new_master_user, $new_master_password, 1)

# # Set read_only=0 on the new master

$new_master_handler- > disable_log_bin_local ()

Print "Set read_only=0 on the new master.\ n"

$new_master_handler- > disable_read_only ()

# # Creating an app user on the new master

Print "Creating app user on the new master..\ n"

# FIXME_xxx_create_user ($new_master_handler- > {dbh})

# $new_master_handler- > enable_log_bin_local ()

# $new_master_handler- > disconnect ()

# # Update master ip on the catalog database, etc

# FIXME_xxx

# $cmd = 'ssh root@192.168.222.156\. / lvs-admin stop'

# $cmd = 'ssh'.$ssh_user.'@'.$orig_master_ip.'\'. / lvs-admin stop\'

$cmd = 'ssh'. $ssh_user.'@'.$orig_master_ip.'\. / lvs-admin stop'

System ($cmd)

$exit_code = 0

}

If ($@) {

Warn $@

# If you want to continue failover, exit 10.

Exit $exit_code

}

Exit $exit_code

}

Elsif ($command eq "status") {

# do nothing

Exit 0

}

Else {

& usage ()

Exit 1

}

Sub usage {

"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"

}

In the script, the code with a green background is new and the code with a red background is annotated.

Add a script to turn off keepalived in the main library

Vi / root/lvs-admin

Case "$1" in

"stop")

Echo

Echo "stop keepalived."

Service keepalived stop

"start")

Echo

Echo "start keepalive."

Service keepalived start

Echo "please input you select!"

Esac

Chmod 755 / root/lvs-admin

7. Test master database failure MHA automatically promotes the standby library to the master library and drifts the virtual IP to the new master library (the original standby library)

7.1 when replication is stopped, binary logs are not transferred to the slave by default.

Mysql > stop slave

7.2 sysbench generates test data and creates a sbtest table with a total of 1 million pieces of data under the sbtest library on the main library

Mysql > create database sbtest

. / sysbench-num-threads=4-test=oltp-oltp-table-size=1000000-oltp-read-only=off-init-rng=on-max-requests=0\

-oltp-dist-type=uniform-- max-time=1800-- mysql-user=root-- db-driver=test-- mysql-table-engine=innodb\

-- oltp-test-mode=complex prepare

7.3 closing the master library MHA automatically promotes the backup library to the new master library and drifts the virtual IP to the new master library (the original backup library)

-- close the main library

Mysqladmin-u root shutdown

-- View MHA logs in real time on the MHA monitoring node (slave database)

Tail-f / masterha/app1/app1.log

Thu Feb 2 23:53:06 2017-[info] MHA::MasterMonitor version 0.57.

Thu Feb 2 23:53:07 2017-[error] [/ usr/local/share/perl5/MHA/ServerManager.pm, ln671] Master 192.168.222.156 Master 3306 from which slave 192.168.222.157 (192.168.222.157)

6) replicates is not defined in the configuration file!

Thu Feb 2 23:53:07 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. At / usr/local/share/perl5/MHA/Master

Monitor.pm line 329.

Thu Feb 2 23:53:07 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.

Thu Feb 2 23:53:07 2017-[info] Got exit code 1 (Not master dead).

Thu Feb 2 23:54:15 2017-[info] MHA::MasterMonitor version 0.57.

Thu Feb 2 23:54:16 2017-[error] [/ usr/local/share/perl5/MHA/ServerManager.pm, ln671] Master 192.168.222.156 Master 3306 from which slave 192.168.222.157 (192.168.222.157)

6) replicates is not defined in the configuration file!

Thu Feb 2 23:54:16 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. At / usr/local/share/perl5/MHA/Master

Monitor.pm line 329.

Thu Feb 2 23:54:16 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.

Thu Feb 2 23:54:16 2017-[info] Got exit code 1 (Not master dead).

Thu Feb 2 23:58:13 2017-[info] MHA::MasterMonitor version 0.57.

Thu Feb 2 23:58:14 2017-[error] [/ usr/local/share/perl5/MHA/ServerManager.pm, ln671] Master 192.168.222.156 Master 3306 from which slave 192.168.222.157 (192.168.222.157)

6) replicates is not defined in the configuration file!

Thu Feb 2 23:58:14 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. At / usr/local/share/perl5/MHA/Master

Monitor.pm line 329.

Thu Feb 2 23:58:14 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.

Thu Feb 2 23:58:14 2017-[info] Got exit code 1 (Not master dead).

Thu Feb 2 23:59:48 2017-[info] MHA::MasterMonitor version 0.57.

Thu Feb 2 23:59:49 2017-[error] [/ usr/local/share/perl5/MHA/ServerManager.pm, ln671] Master 192.168.222.156 Master 3306 from which slave 192.168.222.157 (192.168.222.157)

6) replicates is not defined in the configuration file!

Thu Feb 2 23:59:49 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. At / usr/local/share/perl5/MHA/Master

Monitor.pm line 329.

Thu Feb 2 23:59:49 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.

Thu Feb 2 23:59:49 2017-[info] Got exit code 1 (Not master dead).

Fri Feb 3 00:04:10 2017-[info] MHA::MasterMonitor version 0.57.

Fri Feb 3 00:04:11 2017-[error] [/ usr/local/share/perl5/MHA/ServerManager.pm, ln671] Master 192.168.222.156 Master 3306 from which slave 192.168.222.157 (192.168.222.157)

6) replicates is not defined in the configuration file!

Fri Feb 3 00:04:11 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. At / usr/local/share/perl5/MHA/Master

Monitor.pm line 329.

Fri Feb 3 00:04:11 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.

Fri Feb 3 00:04:11 2017-[info] Got exit code 1 (Not master dead).

Fri Feb 3 00:09:04 2017-[info] MHA::MasterMonitor version 0.57.

Fri Feb 3 00:09:05 2017-[error] [/ usr/local/share/perl5/MHA/ServerManager.pm, ln671] Master 192.168.222.156 Master 3306 from which slave 192.168.222.157 (192.168.222.157)

6) replicates is not defined in the configuration file!

Fri Feb 3 00:09:05 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. At / usr/local/share/perl5/MHA/Master

Monitor.pm line 329.

Fri Feb 3 00:09:05 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.

Fri Feb 3 00:09:05 2017-[info] Got exit code 1 (Not master dead).

Fri Feb 3 00:13:13 2017-[info] MHA::MasterMonitor version 0.57.

Fri Feb 3 00:13:14 2017-[error] [/ usr/local/share/perl5/MHA/ServerManager.pm, ln671] Master 192.168.222.156 Master 3306 from which slave 192.168.222.157 (192.168.222.157)

6) replicates is not defined in the configuration file!

Fri Feb 3 00:13:14 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. At / usr/local/share/perl5/MHA/Master

Monitor.pm line 329.

Fri Feb 3 00:13:14 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.

Fri Feb 3 00:13:14 2017-[info] Got exit code 1 (Not master dead).

Fri Feb 3 00:20:21 2017-[info] MHA::MasterMonitor version 0.57.

Fri Feb 3 00:20:23 2017-[error] [/ usr/local/share/perl5/MHA/ServerManager.pm, ln671] Master 192.168.222.156 Master 3306 from which slave 192.168.222.157 (192.168.222.157)

6) replicates is not defined in the configuration file!

Fri Feb 3 00:20:23 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. At / usr/local/share/perl5/MHA/Master

Monitor.pm line 329.

Fri Feb 3 00:20:23 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.

Fri Feb 3 00:20:23 2017-[info] Got exit code 1 (Not master dead).

Fri Feb 3 00:21:54 2017-[info] MHA::MasterMonitor version 0.57.

Fri Feb 3 00:21:55 2017-[info] GTID failover mode = 0

Fri Feb 3 00:21:55 2017-[info] Dead Servers:

Fri Feb 3 00:21:55 2017-[info] Alive Servers:

Fri Feb 3 00:21:55 2017-[info] 192.168.222.156 (192.168.222.156)

Fri Feb 3 00:21:55 2017-[info] 192.168.222.157 (192.168.222.157)

Fri Feb 3 00:21:55 2017-[info] Alive Slaves:

Fri Feb 3 00:21:55 2017-[info] 192.168.222.157 (192.168.222.157 Version=5.6.27-log (oldest major version between slaves) log-bin:enabled

Fri Feb 3 00:21:55 2017-[info] Replicating from 192.168.222.156 (192.168.222.156 Replicating from 3306)

Fri Feb 3 00:21:55 2017-[info] Primary candidate for the new Master (candidate_master is set)

Fri Feb 3 00:21:55 2017-[info] Current Alive Master: 192.168.222.156 (192.168.222.156)

Fri Feb 3 00:21:55 2017-[info] Checking slave configurations..

Fri Feb 3 00:21:55 2017-[info] Checking replication filtering settings..

Fri Feb 3 00:21:55 2017-[info] binlog_do_db=, binlog_ignore_db=

Fri Feb 3 00:21:55 2017-[info] Replication filtering check ok.

Fri Feb 3 00:21:55 2017-[info] GTID (with auto-pos) is not supported

Fri Feb 3 00:21:55 2017-[info] Starting SSH connection tests..

Fri Feb 3 00:21:58 2017-[info] All SSH connection tests passed successfully.

Fri Feb 3 00:21:58 2017-[info] Checking MHA Node version..

Fri Feb 3 00:21:58 2017-[info] Version check ok.

Fri Feb 3 00:21:58 2017-[info] Checking SSH publickey authentication settings on the current master..

Fri Feb 3 00:21:59 2017-[info] HealthCheck: SSH to 192.168.222.156 is reachable.

Fri Feb 3 00:21:59 2017-[info] Master MHA Node version is 0.57.

Fri Feb 3 00:21:59 2017-[info] Checking recovery script configurations on 192.168.222.156 (192.168.222.156).

Fri Feb 3 00:21:59 2017-[info] Executing command: save_binary_logs-command=test-start_pos=4-binlog_dir=/app/oracle/oradata2/mysql-5.5.37/data-output_file=

/ tmp/save_binary_logs_test-manager_version=0.57-start_file=mysql-bin.000068

Fri Feb 3 00:21:59 2017-[info] Connecting to root@192.168.222.156 (192.168.222.156 Connecting to root@192.168.222.156)..

Creating / tmp if not exists.. Ok.

Checking output directory is accessible or not..

Ok.

Binlog found at / app/oracle/oradata2/mysql-5.5.37/data, up to mysql-bin.000068

Fri Feb 3 00:21:59 2017-[info] Binlog setting check done.

Fri Feb 3 00:21:59 2017-[info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..

Fri Feb 3 00:21:59 2017-[info] Executing command: apply_diff_relay_logs-command=test-slave_user='root'-slave_host=192.168.222.157-slave_ip=192.168.222.15

7-slave_port=3306-workdir=/tmp-target_version=5.6.27-log-manager_version=0.57-relay_log_info=/usr/local/mysql/data/relay-log.info-relay_dir=/usr/local/mys

Ql/data/-slave_pass=xxx

Fri Feb 3 00:21:59 2017-[info] Connecting to root@192.168.222.157 (192.168.222.157)..

Checking slave recovery environment settings..

Opening / usr/local/mysql/data/relay-log.info... Ok.

Relay log found at / usr/local/mysql/data, up to relay-bin.000002

Temporary relay log file is / usr/local/mysql/data/relay-bin.000002

Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure.

Done.

Testing mysqlbinlog output.. Done.

Cleaning up test file (s).. Done.

Fri Feb 3 00:22:00 2017-[info] Slaves settings check done.

Fri Feb 3 00:22:00 2017-[info]

192.168.222.156 (192.168.222.156) (current master)

+-192.168.222.157 (192.168.222.157purl 3306)

Fri Feb 3 00:22:00 2017-[info] Checking master_ip_failover_script status:

Fri Feb 3 00:22:00 2017-[info] / usr/local/bin/master_ip_failover-command=status-ssh_user=root-orig_master_host=192.168.222.156-orig_master_ip=192.168.222.

156-orig_master_port=3306

Fri Feb 3 00:22:00 2017-[info] OK.

Fri Feb 3 00:22:00 2017-[warning] shutdown_script is not defined.

Fri Feb 3 00:22:00 2017-[info] Set master ping interval 1 seconds.

Fri Feb 3 00:22:00 2017-[info] Set secondary check script: / usr/local/bin/masterha_secondary_check-s mysqldb2-s oradb-- user=root-- master_host=oradb-- master_ip=

'192.168.222.156'--master_port=3306

Fri Feb 3 00:22:00 2017-[info] Starting ping health check on 192.168.222.156 (192.168.222.156).

Fri Feb 3 00:22:00 2017-[info] Ping (SELECT) succeeded, waiting until MySQL doesn't respond..

Fri Feb 3 00:25:06 2017-[warning] Got error on MySQL select ping: 2013 (Lost connection to MySQL server during query)

Fri Feb 3 00:25:06 2017-[info] Executing secondary network check script: / usr/local/bin/masterha_secondary_check-s mysqldb2-s oradb-- user=root-- master_host=orad

B-master_ip='192.168.222.156'-- master_port=3306-- user=root-- master_host=192.168.222.156-- master_ip=192.168.222.156-- master_port=3306-- master_user=root-- ma

Ster_password=123456-ping_type=SELECT

Fri Feb 3 00:25:06 2017-[info] Executing SSH check script: save_binary_logs-command=test-start_pos=4-binlog_dir=/app/oracle/oradata2/mysql-5.5.37/data-outpu

T_file=/tmp/save_binary_logs_test-manager_version=0.57-binlog_prefix=mysql-bin

Fri Feb 3 00:25:07 2017-[warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.222.156')

Fri Feb 3 00:25:07 2017-[warning] Connection failed 2 time (s)..

Monitoring server mysqldb2 is reachable, Master is not reachable from mysqldb2. OK.

Fri Feb 3 00:25:08 2017-[warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.222.156')

Fri Feb 3 00:25:08 2017-[warning] Connection failed 3 time (s)..

Monitoring server oradb is reachable, Master is not reachable from oradb. OK.

Fri Feb 3 00:25:08 2017-[info] Master is not reachable from all other monitoring servers. Failover should start.

Fri Feb 3 00:25:08 2017-[info] HealthCheck: SSH to 192.168.222.156 is reachable.

Fri Feb 3 00:25:09 2017-[warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.222.156')

Fri Feb 3 00:25:09 2017-[warning] Connection failed 4 time (s)..

Fri Feb 3 00:25:09 2017-[warning] Master is not reachable from health checker!

Fri Feb 3 00:25:09 2017-[warning] Master 192.168.222.156 (192.168.222.156) is not reachable!

Fri Feb 3 00:25:09 2017-[warning] SSH is reachable.

Fri Feb 3 00:25:09 2017-[info] Connecting to a master server failed. Reading configuration file / etc/masterha_default.cnf and / etc/masterha/app1.cnf again, and tryi

Ng to connect to all servers to check server status..

Fri Feb 3 00:25:09 2017-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.

Fri Feb 3 00:25:09 2017-[info] Reading application default configuration from / etc/masterha/app1.cnf..

Fri Feb 3 00:25:09 2017-[info] Reading server configuration from / etc/masterha/app1.cnf..

Fri Feb 3 00:25:10 2017-[warning] SQL Thread is stopped (no error) on 192.168.222.157 (192.168.222.157)

Fri Feb 3 00:25:10 2017-[info] GTID failover mode = 0

Fri Feb 3 00:25:10 2017-[info] Dead Servers:

Fri Feb 3 00:25:10 2017-[info] 192.168.222.156 (192.168.222.156)

Fri Feb 3 00:25:10 2017-[info] Alive Servers:

Fri Feb 3 00:25:10 2017-[info] 192.168.222.157 (192.168.222.157)

Fri Feb 3 00:25:10 2017-[info] Alive Slaves:

Fri Feb 3 00:25:10 2017-[info] 192.168.222.157 (192.168.222.157 Version=5.6.27-log (oldest major version between slaves) log-bin:enabled

Fri Feb 3 00:25:10 2017-[info] Replicating from 192.168.222.156 (192.168.222.156 Replicating from 3306)

Fri Feb 3 00:25:10 2017-[info] Primary candidate for the new Master (candidate_master is set)

Fri Feb 3 00:25:10 2017-[info] Checking slave configurations..

Fri Feb 3 00:25:10 2017-[info] Checking replication filtering settings..

Fri Feb 3 00:25:10 2017-[info] Replication filtering check ok.

Fri Feb 3 00:25:10 2017-[info] Master is down!

Fri Feb 3 00:25:10 2017-[info] Terminating monitoring script.

Fri Feb 3 00:25:10 2017-[info] Got exit code 20 (Master dead).

Fri Feb 3 00:25:10 2017-[info] MHA::MasterFailover version 0.57.

Fri Feb 3 00:25:10 2017-[info] Starting master failover.

Fri Feb 3 00:25:10 2017-[info]

Fri Feb 3 00:25:10 2017-[info] * Phase 1: Configuration Check Phase..

Fri Feb 3 00:25:10 2017-[info]

Fri Feb 3 00:25:11 2017-[warning] SQL Thread is stopped (no error) on 192.168.222.157 (192.168.222.157)

Fri Feb 3 00:25:11 2017-[info] GTID failover mode = 0

Fri Feb 3 00:25:11 2017-[info] Dead Servers:

Fri Feb 3 00:25:11 2017-[info] 192.168.222.156 (192.168.222.156)

Fri Feb 3 00:25:11 2017-[info] Checking master reachability via MySQL (double check).

Fri Feb 3 00:25:11 2017-[info] ok.

Fri Feb 3 00:25:11 2017-[info] Alive Servers:

Fri Feb 3 00:25:11 2017-[info] 192.168.222.157 (192.168.222.157)

Fri Feb 3 00:25:11 2017-[info] Alive Slaves:

Fri Feb 3 00:25:11 2017-[info] 192.168.222.157 (192.168.222.157 Version=5.6.27-log (oldest major version between slaves) log-bin:enabled

Fri Feb 3 00:25:11 2017-[info] Replicating from 192.168.222.156 (192.168.222.156 Replicating from 3306)

Fri Feb 3 00:25:11 2017-[info] Primary candidate for the new Master (candidate_master is set)

Fri Feb 3 00:25:11 2017-[info] Starting SQL thread on 192.168.222.157 (192.168.222.157 Starting SQL thread on 3306)

Fri Feb 3 00:25:11 2017-[info] done.

Fri Feb 3 00:25:11 2017-[info] Starting Non-GTID based failover.

Fri Feb 3 00:25:11 2017-[info]

Fri Feb 3 00:25:11 2017-[info] * * Phase 1: Configuration Check Phase completed.

Fri Feb 3 00:25:11 2017-[info]

Fri Feb 3 00:25:11 2017-[info] * Phase 2: Dead Master Shutdown Phase..

Fri Feb 3 00:25:11 2017-[info]

Fri Feb 3 00:25:11 2017-[info] Forcing shutdown so that applications never connect to the current master..

Fri Feb 3 00:25:11 2017-[info] Executing master IP deactivation script:

Fri Feb 3 00:25:11 2017-[info] / usr/local/bin/master_ip_failover-orig_master_host=192.168.222.156-orig_master_ip=192.168.222.156-orig_master_port=3306-com

Mand=stopssh-ssh_user=root

Fri Feb 3 00:25:11 2017-[info] done.

Fri Feb 3 00:25:11 2017-[warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.

Fri Feb 3 00:25:11 2017-[info] * Phase 2: Dead Master Shutdown Phase completed.

Fri Feb 3 00:25:11 2017-[info]

Fri Feb 3 00:25:11 2017-[info] * Phase 3: Master Recovery Phase..

Fri Feb 3 00:25:11 2017-[info]

Fri Feb 3 00:25:11 2017-[info] * Phase 3.1: Getting Latest Slaves Phase..

Fri Feb 3 00:25:11 2017-[info]

Fri Feb 3 00:25:11 2017-[info] The latest binary log file/position on all slaves is mysql-bin.000068:530

Fri Feb 3 00:25:11 2017-[info] Latest slaves (Slaves that received relay log files to the latest):

Fri Feb 3 00:25:11 2017-[info] 192.168.222.157 (192.168.222.157 Version=5.6.27-log (oldest major version between slaves) log-bin:enabled

Fri Feb 3 00:25:11 2017-[info] Replicating from 192.168.222.156 (192.168.222.156 Replicating from 3306)

Fri Feb 3 00:25:11 2017-[info] Primary candidate for the new Master (candidate_master is set)

Fri Feb 3 00:25:11 2017-[info] The oldest binary log file/position on all slaves is mysql-bin.000068:530

Fri Feb 3 00:25:11 2017-[info] Oldest slaves:

Fri Feb 3 00:25:11 2017-[info] 192.168.222.157 (192.168.222.157 Version=5.6.27-log (oldest major version between slaves) log-bin:enabled

Fri Feb 3 00:25:11 2017-[info] Replicating from 192.168.222.156 (192.168.222.156 Replicating from 3306)

Fri Feb 3 00:25:11 2017-[info] Primary candidate for the new Master (candidate_master is set)

Fri Feb 3 00:25:11 2017-[info]

Fri Feb 3 00:25:11 2017-[info] * Phase 3.2: Saving Dead Master's Binlog Phase..

Fri Feb 3 00:25:11 2017-[info]

Fri Feb 3 00:25:12 2017-[info] Fetching dead master's binary logs..

Fri Feb 3 00:25:12 2017-[info] Executing command on the dead master 192.168.222.156 (192.168.222.156 Executing command on the dead master 3306): save_binary_logs-command=save-start_file=mysql-bin.00

0068-start_pos=530-binlog_dir=/app/oracle/oradata2/mysql-5.5.37/data-output_file=/tmp/saved_master_binlog_from_192.168.222.156_3306_20170203002510.binlog-hand

Le_raw_binlog=1-disable_log_bin=0-manager_version=0.57

Creating / tmp if not exists.. Ok.

Concat binary/relay logs from mysql-bin.000068 pos 530 to mysql-bin.000068 EOF into / tmp/saved_master_binlog_from_192.168.222.156_3306_20170203002510.binlog..

Binlog Checksum enabled

Dumping binlog format description event, from position 0 to 120.. Ok.

Dumping effective binlog data from / app/oracle/oradata2/mysql-5.5.37/data/mysql-bin.000068 position 530to tail (61261921).. Ok.

Binlog Checksum enabled

Concat succeeded.

Fri Feb 300: 25:16 2017-[info] scp from root@192.168.222.156:/tmp/saved_master_binlog_from_192.168.222.156_3306_20170203002510.binlog to local:/masterha/app1/saved_

Master_binlog_from_192.168.222.156_3306_20170203002510.binlog succeeded.

Fri Feb 3 00:25:17 2017-[info] HealthCheck: SSH to 192.168.222.157 is reachable.

Fri Feb 3 00:25:17 2017-[info]

Fri Feb 3 00:25:17 2017-[info] * Phase 3.3: Determining New Master Phase..

Fri Feb 3 00:25:17 2017-[info]

Fri Feb 3 00:25:17 2017-[info] Finding the latest slave that has all relay logs for recovering other slaves..

Fri Feb 3 00:25:17 2017-[info] All slaves received relay logs to the same position. No need to resync each other.

Fri Feb 3 00:25:17 2017-[info] Searching new master from slaves..

Fri Feb 3 00:25:17 2017-[info] Candidate masters from the configuration file:

Fri Feb 3 00:25:17 2017-[info] 192.168.222.157 (192.168.222.157 Version=5.6.27-log (oldest major version between slaves) log-bin:enabled

Fri Feb 3 00:25:17 2017-[info] Replicating from 192.168.222.156 (192.168.222.156 Replicating from 3306)

Fri Feb 3 00:25:17 2017-[info] Primary candidate for the new Master (candidate_master is set)

Fri Feb 3 00:25:17 2017-[info] Non-candidate masters:

Fri Feb 3 00:25:17 2017-[info] Searching from candidate_master slaves which have received the latest relay log events..

Fri Feb 3 00:25:17 2017-[info] New master is 192.168.222.157 (192.168.222.157 New master is 3306)

Fri Feb 3 00:25:17 2017-[info] Starting master failover..

Fri Feb 3 00:25:17 2017-[info]

From:

192.168.222.156 (192.168.222.156) (current master)

+-192.168.222.157 (192.168.222.157purl 3306)

To:

192.168.222.157 (192.168.222.157 purl 3306) (new master)

Fri Feb 3 00:25:17 2017-[info]

Fri Feb 3 00:25:17 2017-[info] * Phase 3.3: New Master Diff Log Generation Phase..

Fri Feb 3 00:25:17 2017-[info]

Fri Feb 3 00:25:17 2017-[info] This server has all relay logs. No need to generate diff files from the latest slave.

Fri Feb 3 00:25:17 2017-[info] Sending binlog..

Fri Feb 300: 25:20 2017-[info] scp from local:/masterha/app1/saved_master_binlog_from_192.168.222.156_3306_20170203002510.binlog to root@192.168.222.157:/tmp/saved_

Master_binlog_from_192.168.222.156_3306_20170203002510.binlog succeeded.

Fri Feb 3 00:25:20 2017-[info]

Fri Feb 3 00:25:20 2017-[info] * Phase 3.4: Master Log Apply Phase..

Fri Feb 3 00:25:20 2017-[info]

Fri Feb 3 00:25:20 2017-[info] * NOTICE: If any error happens from this phase, manual recovery is needed.

Fri Feb 3 00:25:20 2017-[info] Starting recovery on 192.168.222.157 (192.168.222.157 Starting recovery on 3306)

Fri Feb 3 00:25:20 2017-[info] Generating diffs succeeded.

Fri Feb 3 00:25:20 2017-[info] Waiting until all relay logs are applied.

Fri Feb 3 00:25:20 2017-[info] done.

Fri Feb 3 00:25:20 2017-[info] Getting slave status..

Fri Feb 3 00:25:20 2017-[info] This slave (192.168.222.157) 's Exec_Master_Log_Pos equals to Read_Master_Log_Pos (mysql-bin.000068:530) .No need to recover from Exec_M

Aster_Log_Pos.

Fri Feb 3 00:25:20 2017-[info] Connecting to the target slave host 192.168.222.157, running recover script..

Fri Feb 3 00:25:20 2017-[info] Executing command: apply_diff_relay_logs-command=apply-slave_user='root'-slave_host=192.168.222.157-slave_ip=192.168.222.157

-slave_port=3306-- apply_files=/tmp/saved_master_binlog_from_192.168.222.156_3306_20170203002510.binlog-- workdir=/tmp-- target_version=5.6.27-log-- timestamp=201702

03002510-handle_raw_binlog=1-disable_log_bin=0-manager_version=0.57-slave_pass=xxx

Fri Feb 3 00:26:48 2017-[info]

MySQL client version is 5.6.27. Using-- binary-mode.

Applying differential binary/relay log files / tmp/saved_master_binlog_from_192.168.222.156_3306_20170203002510.binlog on 192.168.222.157:3306. This may take long time.

Applying log files succeeded.

Fri Feb 3 00:26:48 2017-[info] All relay logs were successfully applied.

Fri Feb 3 00:26:48 2017-[info] Getting new master's binlog name and position..

Fri Feb 3 00:26:48 2017-[info] mysql-bin.000002:196188901

Fri Feb 3 00:26:48 2017-[info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.222.157', MASTER_POR

Titled 3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=196188901, MASTER_USER='repl', MASTER_PASSWORD='xxx'

Fri Feb 3 00:26:48 2017-[info] Executing master IP activate script:

Fri Feb 3 00:26:48 2017-[info] / usr/local/bin/master_ip_failover-command=start-ssh_user=root-orig_master_host=192.168.222.156-orig_master_ip=192.168.222.1

56-orig_master_port=3306-new_master_host=192.168.222.157-new_master_ip=192.168.222.157-new_master_port=3306-new_master_user='root'-new_master_password=xx

Set read_only=0 on the new master.

Creating app user on the new master..

Sh: sshroot@192.168.222.156./lvs-admin stop: No such file or directory

Fri Feb 3 00:26:49 2017-[info] OK.

Fri Feb 3 00:26:49 2017-[info] * * Finished master recovery successfully.

Fri Feb 3 00:26:49 2017-[info] * Phase 3: Master Recovery Phase completed.

Fri Feb 3 00:26:49 2017-[info]

Fri Feb 3 00:26:49 2017-[info] * Phase 4: Slaves Recovery Phase..

Fri Feb 3 00:26:49 2017-[info]

Fri Feb 3 00:26:49 2017-[info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase..

Fri Feb 3 00:26:49 2017-[info]

Fri Feb 3 00:26:49 2017-[info] Generating relay diff files from the latest slave succeeded.

Fri Feb 3 00:26:49 2017-[info]

Fri Feb 3 00:26:49 2017-[info] * Phase 4.2: Starting Parallel Slave Log Apply Phase..

Fri Feb 3 00:26:49 2017-[info]

Fri Feb 3 00:26:49 2017-[info] All new slave servers recovered successfully.

Fri Feb 3 00:26:49 2017-[info]

Fri Feb 3 00:26:49 2017-[info] * Phase 5: New master cleanup phase..

Fri Feb 3 00:26:49 2017-[info]

Fri Feb 3 00:26:49 2017-[info] Resetting slave info on the new master..

Fri Feb 3 00:26:49 2017-[info] 192.168.222.157: Resetting slave info succeeded.

Fri Feb 3 00:26:49 2017-[info] Master failover to 192.168.222.157 (192.168.222.157) completed successfully.

Fri Feb 3 00:26:49 2017-[info] Deleted server1 entry from / etc/masterha/app1.cnf.

Fri Feb 3 00:26:49 2017-[info]

-Failover Report-

App1: MySQL Master failover 192.168.222.156 (192.168.222.156) to 192.168.222.157 (192.168.222.157) succeeded

Master 192.168.222.156 (192.168.222.156 3306) is down!

Check MHA Manager logs at mysqldb2:/masterha/app1/app1.log for details.

Started automated (non-interactive) failover.

Invalidated master IP address on 192.168.222.156 (192.168.222.156)

The latest slave 192.168.222.157 (192.168.222.157) has all relay logs for recovery.

Selected 192.168.222.157 (192.168.222.157) as a new master.

192.168.222.157 (192.168.222.157): OK: Applying all logs succeeded.

192.168.222.157 (192.168.222.157): OK: Activated master IP address.

Generating relay diff files from the latest slave succeeded.

192.168.222.157 (192.168.222.157): Resetting slave info succeeded.

Master failover to 192.168.222.157 (192.168.222.157) completed successfully.

III. Problems encountered in deploying MHA

Question:

[root@localhost mha] #. / install.sh

-> Working on install

Fetching http://www.cpan.org/authors/id/D/DA/DAGOLDEN/install-0.01.tar.gz... OK

Configuring install-0.01... OK

Building and testing install-0.01... FAIL

! Installing install failed. See / root/.cpanm/work/1485934105.3288/build.log for details. Retry with-- force to force install it.

-> Working on DBD::mysql

Fetching http://www.cpan.org/authors/id/M/MI/MICHIELB/DBD-mysql-4.041.tar.gz... OK

= > Found dependencies: DBI

-> Working on DBI

Fetching http://www.cpan.org/authors/id/T/TI/TIMB/DBI-1.636.tar.gz... OK

Configuring DBI-1.636... N/A

! Configure failed for DBI-1.636. See / root/.cpanm/work/1485934125.3353/build.log for details.

! Installing the dependencies failed: Module 'DBI' is not installed

! Bailing out the installation for DBD-mysql-4.041.

Question:

/ usr/bin/ld: cannot find-lz

Usr/bin/ld: cannot find-lz

Collect2: ld returned 1 exit status

Make: * * [blib/arch/auto/DBD/mysql/mysql.so] Error 1

-> FAIL Installing DBD::mysql failed. See / root/.cpanm/work/1485938925.8246/build.log for details. Retry with-- force to force install it.

2 distributions installed

Deal with:

Yum-y install * zlib-dev*

[root@localhost mha] #. / install.sh

Install is up to date. (0.01)

-> Working on DBD::mysql

Fetching http://www.cpan.org/authors/id/M/MI/MICHIELB/DBD-mysql-4.041.tar.gz... OK

Configuring DBD-mysql-4.041... OK

Building and testing DBD-mysql-4.041... OK

Successfully installed DBD-mysql-4.041 (upgraded from 4.013)

1 distribution installed

[root@localhost mha4mysql-node-0.53] # perl Makefile.PL

* Module::AutoInstall version 1.03

* Checking for Perl dependencies...

[Core Features]

-DBI... loaded. (1.609)

-DBD::mysql... loaded. (4.041)

* Module::AutoInstall configuration finished.

Checking if your kit is complete...

Looks good

Generating a Unix-style Makefile

Writing Makefile for mha4mysql::node

Writing MYMETA.yml and MYMETA.json

[root@localhost mha4mysql-node-0.53] # make

Cp lib/MHA/BinlogManager.pm blib/lib/MHA/BinlogManager.pm

Cp lib/MHA/BinlogPosFindManager.pm blib/lib/MHA/BinlogPosFindManager.pm

Cp lib/MHA/BinlogPosFinderXid.pm blib/lib/MHA/BinlogPosFinderXid.pm

Cp lib/MHA/BinlogHeaderParser.pm blib/lib/MHA/BinlogHeaderParser.pm

Cp lib/MHA/BinlogPosFinder.pm blib/lib/MHA/BinlogPosFinder.pm

Cp lib/MHA/BinlogPosFinderElp.pm blib/lib/MHA/BinlogPosFinderElp.pm

Cp lib/MHA/NodeUtil.pm blib/lib/MHA/NodeUtil.pm

Cp lib/MHA/SlaveUtil.pm blib/lib/MHA/SlaveUtil.pm

Cp lib/MHA/NodeConst.pm blib/lib/MHA/NodeConst.pm

Cp bin/apply_diff_relay_logs blib/script/apply_diff_relay_logs

"/ usr/bin/perl"- Iinc"-MExtUtils::MY-e 'MY- > fixin (shift)'-- blib/script/apply_diff_relay_logs

Cp bin/filter_mysqlbinlog blib/script/filter_mysqlbinlog

"/ usr/bin/perl"- Iinc"-MExtUtils::MY-e 'MY- > fixin (shift)'-- blib/script/filter_mysqlbinlog

Cp bin/purge_relay_logs blib/script/purge_relay_logs

"/ usr/bin/perl"- Iinc"-MExtUtils::MY-e 'MY- > fixin (shift)'-- blib/script/purge_relay_logs

Cp bin/save_binary_logs blib/script/save_binary_logs

"/ usr/bin/perl"- Iinc"-MExtUtils::MY-e 'MY- > fixin (shift)'-- blib/script/save_binary_logs

Manifying 4 pod documents

[root@localhost mha4mysql-node-0.53] # make install

Manifying 4 pod documents

Installing / usr/local/share/perl5/MHA/NodeUtil.pm

Installing / usr/local/share/perl5/MHA/BinlogPosFindManager.pm

Installing / usr/local/share/perl5/MHA/NodeConst.pm

Installing / usr/local/share/perl5/MHA/BinlogPosFinder.pm

Installing / usr/local/share/perl5/MHA/BinlogHeaderParser.pm

Installing / usr/local/share/perl5/MHA/BinlogPosFinderXid.pm

Installing / usr/local/share/perl5/MHA/BinlogPosFinderElp.pm

Installing / usr/local/share/perl5/MHA/BinlogManager.pm

Installing / usr/local/share/perl5/MHA/SlaveUtil.pm

Installing / usr/local/share/man/man1/purge_relay_logs.1

Installing / usr/local/share/man/man1/filter_mysqlbinlog.1

Installing / usr/local/share/man/man1/save_binary_logs.1

Installing / usr/local/share/man/man1/apply_diff_relay_logs.1

Installing / usr/local/bin/apply_diff_relay_logs

Installing / usr/local/bin/save_binary_logs

Installing / usr/local/bin/filter_mysqlbinlog

Installing / usr/local/bin/purge_relay_logs

Appending installation info to / usr/lib64/perl5/perllocal.pod

Question 10: download mha4mysql-node installation from https://github.com/yoshinorim/mha4mysql-node

[root@mysqldb2 mha4mysql-node-master] # perl Makefile.PL

Can't locate inc/Module/Install.pm in @ INC (@ INC contains: / usr/local/lib64/perl5 / usr/local/share/perl5 / usr/lib64/perl5/vendor_perl / usr/share/perl5/vendor_perl / usr/lib64/perl5/ usr/share/perl5.) At Makefile.PL line 1.

BEGIN failed--compilation aborted at Makefile.PL line 1.

Deal with:

-- View the contents of the first line of Makefile.PL

[root@mysqldb2 mha4mysql-node-master] # vi Makefile.PL

Use inc::Module::Install

-- install the module

Cpanm inc::Module::Install

[root@mysqldb2 mha4mysql-node-master] # perl Makefile.PL

Include / root/olm/mha/mha4mysql-node-master/inc/Module/Install.pm

Include inc/Module/Install/Metadata.pm

Include inc/Module/Install/Base.pm

Include inc/Module/Install/Makefile.pm

Include inc/Module/Install/Scripts.pm

Include inc/Module/Install/AutoInstall.pm

Include inc/Module/Install/Include.pm

Include inc/Module/AutoInstall.pm

* Module::AutoInstall version 1.17

* Checking for Perl dependencies...

[Core Features]

-DBI... loaded. (1.609)

-DBD::mysql... loaded. (4.041)

* Module::AutoInstall configuration finished.

Include inc/Module/Install/WriteAll.pm

Include inc/Module/Install/Win32.pm

Include inc/Module/Install/Can.pm

Include inc/Module/Install/Fetch.pm

Checking if your kit is complete...

Warning: the following files are missing in your kit:

META.yml

Please inform the author.

Generating a Unix-style Makefile

Writing Makefile for mha4mysql::node

Writing MYMETA.yml and MYMETA.json

Writing META.yml

[root@mysqldb2 mha4mysql-node-master] # make

Cp lib/MHA/BinlogManager.pm blib/lib/MHA/BinlogManager.pm

Cp lib/MHA/BinlogPosFindManager.pm blib/lib/MHA/BinlogPosFindManager.pm

Cp lib/MHA/BinlogPosFinderXid.pm blib/lib/MHA/BinlogPosFinderXid.pm

Cp lib/MHA/BinlogHeaderParser.pm blib/lib/MHA/BinlogHeaderParser.pm

Cp lib/MHA/BinlogPosFinder.pm blib/lib/MHA/BinlogPosFinder.pm

Cp lib/MHA/BinlogPosFinderElp.pm blib/lib/MHA/BinlogPosFinderElp.pm

Cp lib/MHA/NodeUtil.pm blib/lib/MHA/NodeUtil.pm

Cp lib/MHA/SlaveUtil.pm blib/lib/MHA/SlaveUtil.pm

Cp lib/MHA/NodeConst.pm blib/lib/MHA/NodeConst.pm

Cp bin/apply_diff_relay_logs blib/script/apply_diff_relay_logs

"/ usr/bin/perl"- Iinc"-MExtUtils::MY-e 'MY- > fixin (shift)'-- blib/script/apply_diff_relay_logs

Cp bin/filter_mysqlbinlog blib/script/filter_mysqlbinlog

"/ usr/bin/perl"- Iinc"-MExtUtils::MY-e 'MY- > fixin (shift)'-- blib/script/filter_mysqlbinlog

Cp bin/purge_relay_logs blib/script/purge_relay_logs

"/ usr/bin/perl"- Iinc"-MExtUtils::MY-e 'MY- > fixin (shift)'-- blib/script/purge_relay_logs

Cp bin/save_binary_logs blib/script/save_binary_logs

"/ usr/bin/perl"- Iinc"-MExtUtils::MY-e 'MY- > fixin (shift)'-- blib/script/save_binary_logs

Manifying 4 pod documents

[root@mysqldb2 mha4mysql-node-master] # make install

Manifying 4 pod documents

Installing / usr/local/share/perl5/MHA/NodeUtil.pm

Installing / usr/local/share/perl5/MHA/NodeConst.pm

Installing / usr/local/share/perl5/MHA/BinlogHeaderParser.pm

Installing / usr/local/share/perl5/MHA/BinlogManager.pm

Installing / usr/local/share/perl5/MHA/SlaveUtil.pm

Installing / usr/local/share/man/man1/purge_relay_logs.1

Installing / usr/local/share/man/man1/filter_mysqlbinlog.1

Installing / usr/local/share/man/man1/save_binary_logs.1

Installing / usr/local/share/man/man1/apply_diff_relay_logs.1

Installing / usr/local/bin/apply_diff_relay_logs

Installing / usr/local/bin/save_binary_logs

Installing / usr/local/bin/purge_relay_logs

Appending installation info to / usr/lib64/perl5/perllocal.pod

Question 11: after dealing with problem 10

[root@mysqldb2 debian] # / usr/local/bin/masterha_check_ssh-- conf=/etc/masterha/app1.cnf

"NI_NUMERICHOST" is not exported by the Socket module

"getaddrinfo" is not exported by the Socket module

"getnameinfo" is not exported by the Socket module

Can't continue after import errors at / usr/local/share/perl5/MHA/NodeUtil.pm line 29.

BEGIN failed--compilation aborted at / usr/local/share/perl5/MHA/NodeUtil.pm line 29.

Compilation failed in require at / usr/local/share/perl5/MHA/SlaveUtil.pm line 27.

BEGIN failed--compilation aborted at / usr/local/share/perl5/MHA/SlaveUtil.pm line 27.

Compilation failed in require at / usr/local/share/perl5/MHA/DBHelper.pm line 26.

BEGIN failed--compilation aborted at / usr/local/share/perl5/MHA/DBHelper.pm line 26.

Compilation failed in require at / usr/local/share/perl5/MHA/HealthCheck.pm line 30.

BEGIN failed--compilation aborted at / usr/local/share/perl5/MHA/HealthCheck.pm line 30.

Compilation failed in require at / usr/local/share/perl5/MHA/Server.pm line 28.

BEGIN failed--compilation aborted at / usr/local/share/perl5/MHA/Server.pm line 28.

Compilation failed in require at / usr/local/share/perl5/MHA/Config.pm line 29.

BEGIN failed--compilation aborted at / usr/local/share/perl5/MHA/Config.pm line 29.

Compilation failed in require at / usr/local/share/perl5/MHA/SSHCheck.pm line 32.

BEGIN failed--compilation aborted at / usr/local/share/perl5/MHA/SSHCheck.pm line 32.

Compilation failed in require at / usr/local/bin/masterha_check_ssh line 25.

BEGIN failed--compilation aborted at / usr/local/bin/masterha_check_ssh line 25.

Deal with:

[root@mysqldb2 mha4mysql-node-master] # cpanm Socket

-> Working on Socket

Fetching http://www.cpan.org/authors/id/P/PE/PEVANS/Socket-2.024.tar.gz... OK

= > Found dependencies: ExtUtils::Constant

-> Working on ExtUtils::Constant

Fetching http://www.cpan.org/authors/id/N/NW/NWCLARK/ExtUtils-Constant-0.23.tar.gz... OK

Configuring ExtUtils-Constant-0.16... OK

Building and testing ExtUtils-Constant-0.23... OK

Successfully installed ExtUtils-Constant-0.23 (upgraded from 0.22)

Configuring Socket-2.024... OK

Building and testing Socket-2.024... OK

Successfully installed Socket-2.024 (upgraded from 1.82)

2 distributions installed

[root@mysqldb2 mha4mysql-manager-master] # / usr/local/bin/masterha_check_ssh-- conf=/etc/masterha/app1.cnf

Wed Feb 1 19:41:34 2017-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.

Wed Feb 1 19:41:34 2017-[info] Reading application default configuration from / etc/masterha/app1.cnf..

Wed Feb 1 19:41:34 2017-[info] Reading server configuration from / etc/masterha/app1.cnf..

Wed Feb 1 19:41:34 2017-[info] Starting SSH connection tests..

Wed Feb 1 19:41:51 2017-[debug]

Wed Feb 1 19:41:35 2017-[debug] Connecting via SSH from root@192.168.222.157 (192.168.222.157) to root@192.168.222.156 (192.168.222.156)..

Wed Feb 1 19:41:50 2017-[debug] ok.

Wed Feb 1 19:41:55 2017-[debug]

Wed Feb 1 19:41:34 2017-[debug] Connecting via SSH from root@192.168.222.156 (192.168.222.156) to root@192.168.222.157 (192.168.222.157)..

Wed Feb 1 19:41:55 2017-[debug] ok.

Wed Feb 1 19:41:55 2017-[info] All SSH connection tests passed successfully.

Question 12:

[root@mysqldb2 mha] # masterha_check_repl-- conf=/etc/masterha/app1.cnf

Wed Feb 1 20:04:52 2017-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.

Wed Feb 1 20:04:52 2017-[info] Reading application default configuration from / etc/masterha/app1.cnf..

Wed Feb 1 20:04:52 2017-[info] Reading server configuration from / etc/masterha/app1.cnf..

Wed Feb 1 20:04:52 2017-[info] MHA::MasterMonitor version 0.57.

Creating directory / masterha/app1.. Done.

Wed Feb 1 20:04:57 2017-[info] GTID failover mode = 0

Wed Feb 1 20:04:57 2017-[info] Dead Servers:

Wed Feb 1 20:04:57 2017-[info] Alive Servers:

Wed Feb 1 20:04:57 2017-[info] 192.168.222.156 (192.168.222.156)

Wed Feb 1 20:04:57 2017-[info] 192.168.222.157 (192.168.222.157)

Wed Feb 1 20:04:57 2017-[info] Alive Slaves:

Wed Feb 1 20:04:57 2017-[info] 192.168.222.157 (192.168.222.157 Version=5.6.27 (oldest major version between slaves) log-bin:disabled

Wed Feb 1 20:04:57 2017-[info] Replicating from 192.168.222.156 (192.168.222.156)

Wed Feb 1 20:04:57 2017-[info] Primary candidate for the new Master (candidate_master is set)

Wed Feb 1 20:04:57 2017-[info] Current Alive Master: 192.168.222.156 (192.168.222.156)

Wed Feb 1 20:04:57 2017-[info] Checking slave configurations..

Wed Feb 1 20:04:57 2017-[warning] log-bin is not set on slave 192.168.222.157 (192.168.222.157) This host cannot be a master.

Wed Feb 1 20:04:57 2017-[info] Checking replication filtering settings..

Wed Feb 1 20:04:57 2017-[info] binlog_do_db=, binlog_ignore_db=

Wed Feb 1 20:04:57 2017-[info] Replication filtering check ok.

Wed Feb 1 20:04:57 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln364] None of slaves can be master. Check failover configuration file or log-bin settings in my.cnf

Wed Feb 1 20:04:57 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. At / usr/local/bin/masterha_check_repl line 48.

Wed Feb 1 20:04:57 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.

Wed Feb 1 20:04:57 2017-[info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

Deal with:

Vi / usr/local/mysql/my.cnf

Log_bin=/usr/local/mysql/data/mysql-bin

# relay_log=/usr/local/mysql/data/relay-bin

Skip_slave_start

/ usr/local/mysql/bin/mysqld_safe-defaults-file=/usr/local/mysql/my.cnf-user=mysql &

Mysql > start slave

[root@mysqldb2 mysql] # masterha_check_repl-- conf=/etc/masterha/app1.cnf

Wed Feb 1 20:16:43 2017-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.

Wed Feb 1 20:16:43 2017-[info] Reading application default configuration from / etc/masterha/app1.cnf..

Wed Feb 1 20:16:43 2017-[info] Reading server configuration from / etc/masterha/app1.cnf..

Wed Feb 1 20:16:43 2017-[info] MHA::MasterMonitor version 0.57.

Wed Feb 1 20:16:44 2017-[info] GTID failover mode = 0

Wed Feb 1 20:16:44 2017-[info] Dead Servers:

Wed Feb 1 20:16:44 2017-[info] Alive Servers:

Wed Feb 1 20:16:44 2017-[info] 192.168.222.156 (192.168.222.156)

Wed Feb 1 20:16:44 2017-[info] 192.168.222.157 (192.168.222.157)

Wed Feb 1 20:16:44 2017-[info] Alive Slaves:

Wed Feb 1 20:16:44 2017-[info] 192.168.222.157 (192.168.222.157 Version=5.6.27-log (oldest major version between slaves) log-bin:enabled

Wed Feb 1 20:16:44 2017-[info] Replicating from 192.168.222.156 (192.168.222.156)

Wed Feb 1 20:16:44 2017-[info] Primary candidate for the new Master (candidate_master is set)

Wed Feb 1 20:16:44 2017-[info] Current Alive Master: 192.168.222.156 (192.168.222.156)

Wed Feb 1 20:16:44 2017-[info] Checking slave configurations..

Wed Feb 1 20:16:44 2017-[info] read_only=1 is not set on slave 192.168.222.157 (192.168.222.157)

Wed Feb 1 20:16:44 2017-[warning] relay_log_purge=0 is not set on slave 192.168.222.157 (192.168.222.157)

Wed Feb 1 20:16:44 2017-[info] Checking replication filtering settings..

Wed Feb 1 20:16:44 2017-[info] binlog_do_db=, binlog_ignore_db=

Wed Feb 1 20:16:44 2017-[info] Replication filtering check ok.

Wed Feb 1 20:16:44 2017-[info] GTID (with auto-pos) is not supported

Wed Feb 1 20:16:44 2017-[info] Starting SSH connection tests..

Wed Feb 1 20:16:50 2017-[info] All SSH connection tests passed successfully.

Wed Feb 1 20:16:50 2017-[info] Checking MHA Node version..

Wed Feb 1 20:16:51 2017-[info] Version check ok.

Wed Feb 1 20:16:51 2017-[info] Checking SSH publickey authentication settings on the current master..

Wed Feb 1 20:16:51 2017-[info] HealthCheck: SSH to 192.168.222.156 is reachable.

Wed Feb 1 20:16:51 2017-[info] Master MHA Node version is 0.57.

Wed Feb 1 20:16:51 2017-[info] Checking recovery script configurations on 192.168.222.156 (192.168.222.156).

Wed Feb 1 20:16:51 2017-[info] Executing command: save_binary_logs-command=test-start_pos=4-binlog_dir=/home/binlog-output_file=/tmp/save_binary_logs_test-manager_version=0.57-start_file=mysql-bin.000058

Wed Feb 1 20:16:51 2017-[info] Connecting to root@192.168.222.156 (192.168.222.156 Connecting to root@192.168.222.156)..

Failed to save binary log: Binlog not found from / home/binlog! If you got this error at MHA Manager, please set "master_binlog_dir=/path/to/binlog_directory_of_the_master" correctly in the MHA Manager's configuration file and try again.

At / usr/local/bin/save_binary_logs line 123.

Eval {...} called at / usr/local/bin/save_binary_logs line 70

Main::main () called at / usr/local/bin/save_binary_logs line 66

Wed Feb 1 20:16:51 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln161] Binlog setting check failed!

Wed Feb 1 20:16:51 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln408] Master configuration failed.

Wed Feb 1 20:16:51 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. At / usr/local/bin/masterha_check_repl line 48.

Wed Feb 1 20:16:51 2017-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.

Wed Feb 1 20:16:51 2017-[info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

Question 12 has been dealt with, but a new problem has emerged, named question 13.

Question 13: