mysql宕机压测_利用sysbench工具测试MHA

# masterha_master_switch --conf=/etc/masterha/app1.cnf --dead_master_host=192.168.56.100 --master_state=dead --new_master_host=192.168.56.200 --ignore_last_failover

--ignore_last_failover：在缺省情况下，如果MHA检测到连续发生宕机，且两次宕机间隔不足8小时的话，

则不会进行Failover，之所以这样限制是为了避免ping-pong效应。

该参数代表忽略上次MHA触发切换产生的文件，默认情况下，MHA发生切换后会在日志目录，

也就是 /var/log/masterha/app1产生app1.failover.complete文件，

下次再次切换的时候如果发现该目录下存在该文件将不允许触发切换，除非在第一次切换后收到删除该文件

# masterha_master_switch --conf=/etc/masterha/app1.cnf --dead_master_host=192.168.56.100 --master_state=dead --new_master_host=192.168.56.200 --ignore_last_failover

--dead_master_ip= is not set. Using 192.168.56.100.

--dead_master_port= is not set. Using 3306.

Mon Oct 29 15:49:04 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Mon Oct 29 15:49:04 2018 - [info] Reading application default configuration from /etc/masterha/app1.cnf..

Mon Oct 29 15:49:04 2018 - [info] Reading server configuration from /etc/masterha/app1.cnf..

Mon Oct 29 15:49:04 2018 - [info] MHA::MasterFailover version 0.57.

Mon Oct 29 15:49:04 2018 - [info] Starting master failover.

Mon Oct 29 15:49:04 2018 - [info]

Mon Oct 29 15:49:04 2018 - [info] * Phase 1: Configuration Check Phase..

Mon Oct 29 15:49:04 2018 - [info]

Mon Oct 29 15:49:05 2018 - [info] GTID failover mode = 1

Mon Oct 29 15:49:05 2018 - [info] Dead Servers:

Mon Oct 29 15:49:05 2018 - [info] 192.168.56.100(192.168.56.100:3306)

Mon Oct 29 15:49:05 2018 - [info] Checking master reachability via MySQL(double check)...

Mon Oct 29 15:49:05 2018 - [info] ok.

Mon Oct 29 15:49:05 2018 - [info] Alive Servers:

Mon Oct 29 15:49:05 2018 - [info] 192.168.56.200(192.168.56.200:3306)

Mon Oct 29 15:49:05 2018 - [info] 192.168.56.210(192.168.56.210:3306)

Mon Oct 29 15:49:05 2018 - [info] Alive Slaves:

Mon Oct 29 15:49:05 2018 - [info] 192.168.56.200(192.168.56.200:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled

Mon Oct 29 15:49:05 2018 - [info] GTID ON

Mon Oct 29 15:49:05 2018 - [info] Replicating from 192.168.56.100(192.168.56.100:3306)

Mon Oct 29 15:49:05 2018 - [info] Primary candidate for the new Master (candidate_master is set)

Mon Oct 29 15:49:05 2018 - [info] 192.168.56.210(192.168.56.210:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled

Mon Oct 29 15:49:05 2018 - [info] GTID ON

Mon Oct 29 15:49:05 2018 - [info] Replicating from 192.168.56.100(192.168.56.100:3306)

Mon Oct 29 15:49:05 2018 - [info] Not candidate for the new Master (no_master is set)

Master 192.168.56.100(192.168.56.100:3306) is dead. Proceed? (yes/NO): yes

Mon Oct 29 15:49:06 2018 - [info] Starting GTID based failover.

Mon Oct 29 15:49:06 2018 - [info]

Mon Oct 29 15:49:06 2018 - [info] ** Phase 1: Configuration Check Phase completed.

Mon Oct 29 15:49:06 2018 - [info]

Mon Oct 29 15:49:06 2018 - [info] * Phase 2: Dead Master Shutdown Phase..

Mon Oct 29 15:49:06 2018 - [info]

Mon Oct 29 15:49:07 2018 - [info] HealthCheck: SSH to 192.168.56.100 is reachable.

Mon Oct 29 15:49:07 2018 - [info] Forcing shutdown so that applications never connect to the current master..

Mon Oct 29 15:49:07 2018 - [info] Executing master IP deactivation script:

Mon Oct 29 15:49:07 2018 - [info] /usr/local/bin/master_ip_failover --orig_master_host=192.168.56.100 --orig_master_ip=192.168.56.100 --orig_master_port=3306 --command=stopssh --ssh_user=root

IN SCRIPT TEST====/sbin/ifconfig eth0:0 down==/sbin/ifconfig eth0:0 192.168.56.111/24 up===

Disabling the VIP on old master: 192.168.56.100

Mon Oct 29 15:49:08 2018 - [info] done.

Mon Oct 29 15:49:08 2018 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.

Mon Oct 29 15:49:08 2018 - [info] * Phase 2: Dead Master Shutdown Phase completed.

Mon Oct 29 15:49:08 2018 - [info]

Mon Oct 29 15:49:08 2018 - [info] * Phase 3: Master Recovery Phase..

Mon Oct 29 15:49:08 2018 - [info]

Mon Oct 29 15:49:08 2018 - [info] * Phase 3.1: Getting Latest Slaves Phase..

Mon Oct 29 15:49:08 2018 - [info]

Mon Oct 29 15:49:08 2018 - [info] The latest binary log file/position on all slaves is my3306_binlog.000027:860190114

Mon Oct 29 15:49:08 2018 - [info] Retrieved Gtid Set: 7390a401-b705-11e8-9ed9-080027b0b461:140923-149275

Mon Oct 29 15:49:08 2018 - [info] Latest slaves (Slaves that received relay log files to the latest):

Mon Oct 29 15:49:08 2018 - [info] 192.168.56.200(192.168.56.200:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled

Mon Oct 29 15:49:08 2018 - [info] GTID ON

Mon Oct 29 15:49:08 2018 - [info] Replicating from 192.168.56.100(192.168.56.100:3306)

Mon Oct 29 15:49:08 2018 - [info] Primary candidate for the new Master (candidate_master is set)

Mon Oct 29 15:49:08 2018 - [info] 192.168.56.210(192.168.56.210:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled

Mon Oct 29 15:49:08 2018 - [info] GTID ON

Mon Oct 29 15:49:08 2018 - [info] Replicating from 192.168.56.100(192.168.56.100:3306)

Mon Oct 29 15:49:08 2018 - [info] Not candidate for the new Master (no_master is set)

Mon Oct 29 15:49:08 2018 - [info] The oldest binary log file/position on all slaves is my3306_binlog.000027:860190114

Mon Oct 29 15:49:08 2018 - [info] Retrieved Gtid Set: 7390a401-b705-11e8-9ed9-080027b0b461:140923-149275

Mon Oct 29 15:49:08 2018 - [info] Oldest slaves:

Mon Oct 29 15:49:08 2018 - [info] 192.168.56.200(192.168.56.200:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled

Mon Oct 29 15:49:08 2018 - [info] GTID ON

Mon Oct 29 15:49:08 2018 - [info] Replicating from 192.168.56.100(192.168.56.100:3306)

Mon Oct 29 15:49:08 2018 - [info] Primary candidate for the new Master (candidate_master is set)

Mon Oct 29 15:49:08 2018 - [info] 192.168.56.210(192.168.56.210:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled

Mon Oct 29 15:49:08 2018 - [info] GTID ON

Mon Oct 29 15:49:08 2018 - [info] Replicating from 192.168.56.100(192.168.56.100:3306)

Mon Oct 29 15:49:08 2018 - [info] Not candidate for the new Master (no_master is set)

Mon Oct 29 15:49:08 2018 - [info]

Mon Oct 29 15:49:08 2018 - [info] * Phase 3.3: Determining New Master Phase..

Mon Oct 29 15:49:08 2018 - [info]

Mon Oct 29 15:49:08 2018 - [info] 192.168.56.200 can be new master.

Mon Oct 29 15:49:08 2018 - [info] New master is 192.168.56.200(192.168.56.200:3306)

Mon Oct 29 15:49:08 2018 - [info] Starting master failover..

Mon Oct 29 15:49:08 2018 - [info]

From:

192.168.56.100(192.168.56.100:3306) (current master)

+--192.168.56.200(192.168.56.200:3306)

+--192.168.56.210(192.168.56.210:3306)

To:

192.168.56.200(192.168.56.200:3306) (new master)

+--192.168.56.210(192.168.56.210:3306)

Starting master switch from 192.168.56.100(192.168.56.100:3306) to 192.168.56.200(192.168.56.200:3306)? (yes/NO): yes

Mon Oct 29 15:49:09 2018 - [info] New master decided manually is 192.168.56.200(192.168.56.200:3306)

Mon Oct 29 15:49:09 2018 - [info]

Mon Oct 29 15:49:09 2018 - [info] * Phase 3.3: New Master Recovery Phase..

Mon Oct 29 15:49:09 2018 - [info]

Mon Oct 29 15:49:09 2018 - [info] Waiting all logs to be applied..

Mon Oct 29 15:49:09 2018 - [info] done.

Mon Oct 29 15:49:09 2018 - [info] Getting new master's binlog name and position..

Mon Oct 29 15:49:09 2018 - [info] my3306_binlog.000016:965341200

Mon Oct 29 15:49:09 2018 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.56.200', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';

Mon Oct 29 15:49:09 2018 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: my3306_binlog.000016, 965341200, 7390a401-b705-11e8-9ed9-080027b0b461:1-149275,

a4774e9e-bba9-11e8-bf3e-08002712513f:1-2

Mon Oct 29 15:49:09 2018 - [info] Executing master IP activate script:

Mon Oct 29 15:49:09 2018 - [info] /usr/local/bin/master_ip_failover --command=start --ssh_user=root --orig_master_host=192.168.56.100 --orig_master_ip=192.168.56.100 --orig_master_port=3306 --new_master_host=192.168.56.200 --new_master_ip=192.168.56.200 --new_master_port=3306 --new_master_user='mha_rep' --new_master_password=xxx

Unknown option: new_master_user

Unknown option: new_master_password

IN SCRIPT TEST====/sbin/ifconfig eth0:0 down==/sbin/ifconfig eth0:0 192.168.56.111/24 up===

Enabling the VIP - 192.168.56.111/24 on the new master - 192.168.56.200

bash: /usr/bin/arping: No such file or directory

Mon Oct 29 15:49:10 2018 - [info] OK.

Mon Oct 29 15:49:10 2018 - [info] Setting read_only=0 on 192.168.56.200(192.168.56.200:3306)..

Mon Oct 29 15:49:10 2018 - [info] ok.

Mon Oct 29 15:49:10 2018 - [info] ** Finished master recovery successfully.

Mon Oct 29 15:49:10 2018 - [info] * Phase 3: Master Recovery Phase completed.

Mon Oct 29 15:49:10 2018 - [info]

Mon Oct 29 15:49:10 2018 - [info] * Phase 4: Slaves Recovery Phase..

Mon Oct 29 15:49:10 2018 - [info]

Mon Oct 29 15:49:10 2018 - [info]

Mon Oct 29 15:49:10 2018 - [info] * Phase 4.1: Starting Slaves in parallel..

Mon Oct 29 15:49:10 2018 - [info]

Mon Oct 29 15:49:10 2018 - [info] -- Slave recovery on host 192.168.56.210(192.168.56.210:3306) started, pid: 16740. Check tmp log /var/log/masterha/app1/192.168.56.210_3306_20181029154904.log if it takes time..

Mon Oct 29 15:49:12 2018 - [info]

Mon Oct 29 15:49:12 2018 - [info] Log messages from 192.168.56.210 ...

Mon Oct 29 15:49:12 2018 - [info]

Mon Oct 29 15:49:10 2018 - [info] Resetting slave 192.168.56.210(192.168.56.210:3306) and starting replication from the new master 192.168.56.200(192.168.56.200:3306)..

Mon Oct 29 15:49:10 2018 - [info] Executed CHANGE MASTER.

Mon Oct 29 15:49:11 2018 - [info] Slave started.

Mon Oct 29 15:49:11 2018 - [info] gtid_wait(7390a401-b705-11e8-9ed9-080027b0b461:1-149275,

a4774e9e-bba9-11e8-bf3e-08002712513f:1-2) completed on 192.168.56.210(192.168.56.210:3306). Executed 0 events.

Mon Oct 29 15:49:12 2018 - [info] End of log messages from 192.168.56.210.

Mon Oct 29 15:49:12 2018 - [info] -- Slave on host 192.168.56.210(192.168.56.210:3306) started.

Mon Oct 29 15:49:12 2018 - [info] All new slave servers recovered successfully.

Mon Oct 29 15:49:12 2018 - [info]

Mon Oct 29 15:49:12 2018 - [info] * Phase 5: New master cleanup phase..

Mon Oct 29 15:49:12 2018 - [info]

Mon Oct 29 15:49:12 2018 - [info] Resetting slave info on the new master..

Mon Oct 29 15:49:13 2018 - [info] 192.168.56.200: Resetting slave info succeeded.

Mon Oct 29 15:49:13 2018 - [info] Master failover to 192.168.56.200(192.168.56.200:3306) completed successfully.

Mon Oct 29 15:49:13 2018 - [info]

----- Failover Report -----

app1: MySQL Master failover 192.168.56.100(192.168.56.100:3306) to 192.168.56.200(192.168.56.200:3306) succeeded

Master 192.168.56.100(192.168.56.100:3306) is down!

Check MHA Manager logs at mysqldb2 for details.

Started manual(interactive) failover.

Invalidated master IP address on 192.168.56.100(192.168.56.100:3306)

Selected 192.168.56.200(192.168.56.200:3306) as a new master.

192.168.56.200(192.168.56.200:3306): OK: Applying all logs succeeded.

192.168.56.200(192.168.56.200:3306): OK: Activated master IP address.

192.168.56.210(192.168.56.210:3306): OK: Slave started, replicating from 192.168.56.200(192.168.56.200:3306)

192.168.56.200(192.168.56.200:3306): Resetting slave info succeeded.

Master failover to 192.168.56.200(192.168.56.200:3306) completed successfully.