mha4mysql-manager
mha4mysql-manager copied to clipboard
Published
20 hours ago •
yoshinorim
yoshinorim
Got "MySQL server has gone away" error on checking slave status
Hi, my mha failover failed due to "MySQL server has gone away".
my config:
[server default]
# mysql user and password
user=root
password=root
ssh_user=root
# working directory on the manager
manager_workdir=/home/worker/dbtest/mha4mysql-manager/test
# working directory on MySQL servers
remote_workdir=/home/worker/dbtest/mha4mysql-node/test
master_binlog_dir=/home/worker/dbtest/percona/log
manager_log=/home/worker/dbtest/mha4mysql-manager/logs/manager.log
[server1]
hostname=10.32.64.13
port=3302
[server2]
hostname=10.32.64.20
port=3302
the output of manager log:
Fri Feb 10 20:27:11 2017 - [info] MHA::MasterMonitor version 0.56.
Fri Feb 10 20:27:12 2017 - [info] GTID failover mode = 0
Fri Feb 10 20:27:12 2017 - [info] Dead Servers:
Fri Feb 10 20:27:12 2017 - [info] 10.32.64.13(10.32.64.13:3302)
Fri Feb 10 20:27:12 2017 - [info] Alive Servers:
Fri Feb 10 20:27:12 2017 - [info] 10.32.64.20(10.32.64.20:3302)
Fri Feb 10 20:27:12 2017 - [info] Alive Slaves:
Fri Feb 10 20:27:12 2017 - [info] 10.32.64.20(10.32.64.20:3302) Version=5.7.17-11-log (oldest major version between slaves) log-bin:enabled
Fri Feb 10 20:27:12 2017 - [info] Replicating from 10.32.64.13(10.32.64.13:3302)
Fri Feb 10 20:27:12 2017 - [warning] MySQL master is not currently alive!
Fri Feb 10 20:27:12 2017 - [info] Checking slave configurations..
Fri Feb 10 20:27:12 2017 - [info] read_only=1 is not set on slave 10.32.64.20(10.32.64.20:3302).
Fri Feb 10 20:27:12 2017 - [info] Checking replication filtering settings..
Fri Feb 10 20:27:12 2017 - [info] Replication filtering check ok.
Fri Feb 10 20:27:12 2017 - [info] GTID (with auto-pos) is not supported
Fri Feb 10 20:27:12 2017 - [info] Starting SSH connection tests..
Fri Feb 10 20:27:12 2017 - [info] All SSH connection tests passed successfully.
Fri Feb 10 20:27:12 2017 - [info] Checking MHA Node version..
Fri Feb 10 20:27:13 2017 - [info] Version check ok.
Fri Feb 10 20:27:13 2017 - [info] Getting current master (maybe dead) info ..
Fri Feb 10 20:27:13 2017 - [info] Identified master is 10.32.64.13(10.32.64.13:3302).
Fri Feb 10 20:27:13 2017 - [info] Checking SSH publickey authentication settings on the current master..
Fri Feb 10 20:27:13 2017 - [info] HealthCheck: SSH to 10.32.64.13 is reachable.
Fri Feb 10 20:27:13 2017 - [info] Master MHA Node version is 0.56.
Fri Feb 10 20:27:13 2017 - [info] Checking recovery script configurations on 10.32.64.13(10.32.64.13:3302)..
Fri Feb 10 20:27:13 2017 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/home/worker/dbtest/percona/log --output_file=/home/worker/dbtest/mha4mysql-node/test/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000012
Fri Feb 10 20:27:13 2017 - [info] Connecting to [email protected](10.32.64.13:22)..
###############################################################################
## !!! ALERT !!! #
## You are entering into a secured area! #
## #
## Your IP, login time and username has been noted and #
## has been sent to the server administrator! #
## #
## This service is restricted to authorized users only. #
## All activities on this system are logged. #
## #
## Unauthorized access will be fully investigated and #
## reported to the appropriate law enforcement agencies. #
################################################################################
Creating /home/worker/dbtest/mha4mysql-node/test if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /home/worker/dbtest/percona/log, up to mysql-bin.000014
Fri Feb 10 20:27:13 2017 - [info] Binlog setting check done.
Fri Feb 10 20:27:13 2017 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Fri Feb 10 20:27:13 2017 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=10.32.64.20 --slave_ip=10.32.64.20 --slave_port=3302 --workdir=/home/worker/dbtest/mha4mysql-node/test --target_version=5.7.17-11-log --manager_version=0.56 --relay_log_info=/home/worker/dbtest/percona/data/relay-log.info --relay_dir=/home/worker/dbtest/percona/data/ --slave_pass=xxx
Fri Feb 10 20:27:13 2017 - [info] Connecting to [email protected](10.32.64.20:22)..
###############################################################################
## !!! ALERT !!! #
## You are entering into a secured area! #
## #
## Your IP, login time and username has been noted and #
## has been sent to the server administrator! #
## #
## This service is restricted to authorized users only. #
## All activities on this system are logged. #
## #
## Unauthorized access will be fully investigated and #
## reported to the appropriate law enforcement agencies. #
################################################################################
Checking slave recovery environment settings..
Opening /home/worker/dbtest/percona/data/relay-log.info ... ok.
Relay log found at /home/worker/dbtest/percona/log, up to mysql-relay-bin.000016
Temporary relay log file is /home/worker/dbtest/percona/log/mysql-relay-bin.000016
Testing mysql connection and privileges..mysql: [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 10 20:27:14 2017 - [info] Slaves settings check done.
Fri Feb 10 20:27:14 2017 - [info]
10.32.64.13(10.32.64.13:3302) (current master)
+--10.32.64.20(10.32.64.20:3302)
Fri Feb 10 20:27:14 2017 - [warning] master_ip_failover_script is not defined.
Fri Feb 10 20:27:14 2017 - [warning] shutdown_script is not defined.
Fri Feb 10 20:27:14 2017 - [error][/root/perl5/lib/perl5/MHA/Server.pm, ln457] Checking slave status failed on 10.32.64.20(10.32.64.20:3302). err=Got error when executing SHOW SLAVE STATUS. MySQL server has gone away
Fri Feb 10 20:27:14 2017 - [info] Set master ping interval 3 seconds.
Fri Feb 10 20:27:14 2017 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Fri Feb 10 20:27:14 2017 - [info] Starting ping health check on 10.32.64.13(10.32.64.13:3302)..
Fri Feb 10 20:27:14 2017 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.32.64.13' (111))
Fri Feb 10 20:27:14 2017 - [warning] Connection failed 1 time(s)..
Fri Feb 10 20:27:14 2017 - [info] Executing SSH check script: save_binary_logs --command=test --start_pos=4 --binlog_dir=/home/worker/dbtest/percona/log --output_file=/home/worker/dbtest/mha4mysql-node/test/save_binary_logs_test --manager_version=0.56 --binlog_prefix=mysql-bin
Fri Feb 10 20:27:14 2017 - [info] HealthCheck: SSH to 10.32.64.13 is reachable.
Fri Feb 10 20:27:17 2017 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.32.64.13' (111))
Fri Feb 10 20:27:17 2017 - [warning] Connection failed 2 time(s)..
Fri Feb 10 20:27:20 2017 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.32.64.13' (111))
Fri Feb 10 20:27:20 2017 - [warning] Connection failed 3 time(s)..
Fri Feb 10 20:27:23 2017 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.32.64.13' (111))
Fri Feb 10 20:27:23 2017 - [warning] Connection failed 4 time(s)..
Fri Feb 10 20:27:23 2017 - [warning] Master is not reachable from health checker!
Fri Feb 10 20:27:23 2017 - [warning] Master 10.32.64.13(10.32.64.13:3302) is not reachable!
Fri Feb 10 20:27:23 2017 - [warning] SSH is reachable.
Fri Feb 10 20:27:23 2017 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /home/worker/dbtest/mha4mysql-manager/manager.cnf again, and trying to connect to all servers to check server status..
Fri Feb 10 20:27:23 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Feb 10 20:27:23 2017 - [info] Reading application default configuration from /home/worker/dbtest/mha4mysql-manager/manager.cnf..
Fri Feb 10 20:27:23 2017 - [info] Reading server configuration from /home/worker/dbtest/mha4mysql-manager/manager.cnf..
Fri Feb 10 20:27:24 2017 - [info] GTID failover mode = 0
Fri Feb 10 20:27:24 2017 - [info] Dead Servers:
Fri Feb 10 20:27:24 2017 - [info] 10.32.64.13(10.32.64.13:3302)
Fri Feb 10 20:27:24 2017 - [info] Alive Servers:
Fri Feb 10 20:27:24 2017 - [info] 10.32.64.20(10.32.64.20:3302)
Fri Feb 10 20:27:24 2017 - [info] Alive Slaves:
Fri Feb 10 20:27:24 2017 - [info] 10.32.64.20(10.32.64.20:3302) Version=5.7.17-11-log (oldest major version between slaves) log-bin:enabled
Fri Feb 10 20:27:24 2017 - [info] Replicating from 10.32.64.13(10.32.64.13:3302)
Fri Feb 10 20:27:24 2017 - [info] Checking slave configurations..
Fri Feb 10 20:27:24 2017 - [info] read_only=1 is not set on slave 10.32.64.20(10.32.64.20:3302).
Fri Feb 10 20:27:24 2017 - [info] Checking replication filtering settings..
Fri Feb 10 20:27:24 2017 - [info] Replication filtering check ok.
Fri Feb 10 20:27:24 2017 - [info] Master is down!
Fri Feb 10 20:27:24 2017 - [info] Terminating monitoring script.
Fri Feb 10 20:27:24 2017 - [info] Got exit code 20 (Master dead).
Fri Feb 10 20:27:24 2017 - [info] MHA::MasterFailover version 0.56.
Fri Feb 10 20:27:24 2017 - [info] Starting master failover.
Fri Feb 10 20:27:24 2017 - [info]
Fri Feb 10 20:27:24 2017 - [info] * Phase 1: Configuration Check Phase..
Fri Feb 10 20:27:24 2017 - [info]
Fri Feb 10 20:27:25 2017 - [info] GTID failover mode = 0
Fri Feb 10 20:27:25 2017 - [info] Dead Servers:
Fri Feb 10 20:27:25 2017 - [info] 10.32.64.13(10.32.64.13:3302)
Fri Feb 10 20:27:25 2017 - [info] Checking master reachability via MySQL(double check)...
Fri Feb 10 20:27:25 2017 - [info] ok.
Fri Feb 10 20:27:25 2017 - [info] Alive Servers:
Fri Feb 10 20:27:25 2017 - [info] 10.32.64.20(10.32.64.20:3302)
Fri Feb 10 20:27:25 2017 - [info] Alive Slaves:
Fri Feb 10 20:27:25 2017 - [info] 10.32.64.20(10.32.64.20:3302) Version=5.7.17-11-log (oldest major version between slaves) log-bin:enabled
Fri Feb 10 20:27:25 2017 - [info] Replicating from 10.32.64.13(10.32.64.13:3302)
Fri Feb 10 20:27:25 2017 - [info] Starting Non-GTID based failover.
Fri Feb 10 20:27:25 2017 - [info]
Fri Feb 10 20:27:25 2017 - [info] ** Phase 1: Configuration Check Phase completed.
Fri Feb 10 20:27:25 2017 - [info]
Fri Feb 10 20:27:25 2017 - [info] * Phase 2: Dead Master Shutdown Phase..
Fri Feb 10 20:27:25 2017 - [info]
Fri Feb 10 20:27:25 2017 - [info] Forcing shutdown so that applications never connect to the current master..
Fri Feb 10 20:27:25 2017 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master IP address.
Fri Feb 10 20:27:25 2017 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Fri Feb 10 20:27:26 2017 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Fri Feb 10 20:27:26 2017 - [info]
Fri Feb 10 20:27:26 2017 - [info] * Phase 3: Master Recovery Phase..
Fri Feb 10 20:27:26 2017 - [info]
Fri Feb 10 20:27:26 2017 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Fri Feb 10 20:27:26 2017 - [info]
Fri Feb 10 20:27:26 2017 - [error][/root/perl5/lib/perl5/MHA/ServerManager.pm, ln937] Checking slave status failed. err=Got error when executing SHOW SLAVE STATUS. Lost connection to MySQL server during query
Fri Feb 10 20:27:26 2017 - [error][/root/perl5/lib/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR: at /root/perl5/lib/perl5/MHA/MasterFailover.pm line 515.
Fri Feb 10 20:27:26 2017 - [info]
----- Failover Report -----
manager: MySQL Master failover 10.32.64.13(10.32.64.13:3302)
Master 10.32.64.13(10.32.64.13:3302) is down!
Check MHA Manager logs at app03.hp.sp.tst.bmsre.com:/home/worker/dbtest/mha4mysql-manager/logs/manager.log for details.
Started automated(non-interactive) failover.
Got Error so couldn't continue failover from here.
log in slave:
2017-02-10T12:27:11.310017Z 156 [Note] Aborted connection 156 to db: 'unconnected' user: 'root' host: 'app03.hp.sp.tst.bmsre.com' (Got an error reading communication packets)
2017-02-10T12:27:23.164972Z 159 [Note] Aborted connection 159 to db: 'unconnected' user: 'root' host: 'app03.hp.sp.tst.bmsre.com' (Got an error reading communication packets)
2017-02-10T12:27:24.252659Z 161 [Note] Aborted connection 161 to db: 'unconnected' user: 'root' host: 'app03.hp.sp.tst.bmsre.com' (Got an error reading communication packets)
2017-02-10T12:27:26.263051Z 162 [Note] Aborted connection 162 to db: 'unconnected' user: 'root' host: 'app03.hp.sp.tst.bmsre.com' (Got an error reading communication packets)
2017-02-10T12:27:26.275693Z 160 [Note] Aborted connection 160 to db: 'unconnected' user: 'root' host: 'app03.hp.sp.tst.bmsre.com' (Got an error reading communication packets)
Hello cngddflzw , i am also facing same issue in MHA . is it resolved for you ?
I occurred same issue in MHA . is it resolved for you ? Thank you !
I resloved it , it is perl version issue.
Hello @linyue515 I facing the same issue too. how do you resolved it ? Could you please help me? My environment like this: ubuntu16.0.4 mysql5.7.26 MHA0.58 How can I get in touch with you? My QQ NO. is 792519826 Thank you very very very much!
Hello @linyue515 I facing the same issue too. how do you resolved it ? Could you please help me? My environment like this: ubuntu16.0.4 mysql5.7.26 MHA0.58 How can I get in touch with you? My QQ NO. is 792519826 Thank you very very very much!
I occurred same issue in MHA . is it resolved for you ? Thank you !
I resloved it , it is perl version issue.
请问一下 perl要用哪个版本？ 我也遇到这个问题了 qq 276821529
