mysql应用互联网金融_互联网金融MySQL高可用架构之-MHA故障切换

互联网金融MySQL高可用架构之-MHA

在线平滑切换过程

--切换命令如下：

[root@MHA bin]# masterha_master_switch --conf=/etc/app1.cnf --master_state=alive --orig_master_is_new_slave

--MHA 版本0.56

Mon Sep 10 10:39:03 2018 - [info] MHA::MasterRotate version 0.56.

--启动在线切换

Mon Sep 10 10:39:03 2018 - [info] Starting online master switch..

Mon Sep 10 10:39:03 2018 - [info]

--第一阶段配置检查

Mon Sep 10 10:39:03 2018 - [info] * Phase 1: Configuration Check Phase..

Mon Sep 10 10:39:03 2018 - [info]

--告警不用管，全局配置文件没有找到

Mon Sep 10 10:39:03 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

--读取应用默认配置文件/etc/app1.cnf

Mon Sep 10 10:39:03 2018 - [info] Reading application default configuration from /etc/app1.cnf..

--读取服务器配置

Mon Sep 10 10:39:03 2018 - [info] Reading server configuration from /etc/app1.cnf..

--没有采用GTID

Mon Sep 10 10:39:04 2018 - [info] GTID failover mode = 0

--当前master alive

Mon Sep 10 10:39:04 2018 - [info] Current Alive Master: master(192.168.56.101:3306)

--slave alive

Mon Sep 10 10:39:04 2018 - [info] Alive Slaves:

Mon Sep 10 10:39:04 2018 - [info] slave1(192.168.56.102:3306)

--MariaDB 版本10.1.35

Version=10.1.35-MariaDB (oldest major version between slaves) log-bin:enabled

Mon Sep 10 10:39:04 2018 - [info] Replicating from master(192.168.56.101:3306)

Mon Sep 10 10:39:04 2018 - [info] slave2(192.168.56.103:3306) Version=10.1.35-MariaDB (oldest major version between slaves) log-bin:enabled

Mon Sep 10 10:39:04 2018 - [info] Replicating from 192.168.56.101(192.168.56.101:3306)

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on master(192.168.56.101:3306)?

--输入YES

(YES/no):** yes**

Mon Sep 10 10:42:00 2018 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..

Mon Sep 10 10:42:00 2018 - [info] ok.

Mon Sep 10 10:42:00 2018 - [info] Checking MHA is not monitoring or doing failover..

--检查slave1、slave2复制健康情况

Mon Sep 10 10:42:00 2018 - [info] Checking replication health on slave1..

Mon Sep 10 10:42:00 2018 - [info] ok.

Mon Sep 10 10:42:00 2018 - [info] Checking replication health on slave2..

Mon Sep 10 10:42:00 2018 - [info] ok.

Mon Sep 10 10:42:00 2018 - [info] Searching new master from slaves..

Mon Sep 10 10:42:00 2018 - [info] Candidate masters from the configuration file:

--没有发现candidate masters

Mon Sep 10 10:42:00 2018 - [info] Non-candidate masters:

Mon Sep 10 10:42:00 2018 - [info]

From:

--主从结构如下，一主两从架构

master(192.168.56.101:3306) (current master)

+--slave1(192.168.56.102:3306)

+--slave2(192.168.56.103:3306)

--主从关系发生变化

To:

slave1(192.168.56.102:3306) (new master)

+--slave2(192.168.56.103:3306)

+--master(192.168.56.101:3306)

Starting master switch from master(192.168.56.101:3306) to

--输入YES，主从关系发生改变

slave1(192.168.56.102:3306)? (yes/NO): yes

Mon Sep 10 10:42:34 2018 - [info] Checking whether slave1(192.168.56.102:3306) is ok for the new master..

Mon Sep 10 10:42:34 2018 - [info] ok.

Mon Sep 10 10:42:34 2018 - [info] master(192.168.56.101:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.

Mon Sep 10 10:42:34 2018 - [info] master(192.168.56.101:3306): Resetting slave pointing to the dummy host.

Mon Sep 10 10:42:34 2018 - [info] ** Phase 1: Configuration Check Phase completed.

Mon Sep 10 10:42:34 2018 - [info]

Mon Sep 10 10:42:34 2018 - [info] * Phase 2: Rejecting updates Phase..

Mon Sep 10 10:42:34 2018 - [info]

Mon Sep 10 10:42:34 2018 - [info] Executing master ip online change script to disable write on the current master:

Mon Sep 10 10:42:34 2018 - [info] /usr/local/bin/master_ip_online_change --command=stop --orig_master_host=master --orig_master_ip=192.168.56.101 --orig_master_port=3306 --orig_master_user='admin' --orig_master_password='mhaadmin' --new_master_host=slave1 --new_master_ip=192.168.56.102 --new_master_port=3306 --new_master_user='admin' --new_master_password='mhaadmin' --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave

ARGS: $VAR1 = [

'--command=stop',

'--orig_master_host=master',

'--orig_master_ip=192.168.56.101',

'--orig_master_port=3306',

'--orig_master_user=admin',

'--orig_master_password=mhaadmin',

'--new_master_host=slave1',

'--new_master_ip=192.168.56.102',

'--new_master_port=3306',

'--new_master_user=admin',

'--new_master_password=mhaadmin',

'--orig_master_ssh_user=root',

'--new_master_ssh_user=root',

'--orig_master_is_new_slave'

];

Unknown option: orig_master_ssh_user

Unknown option: new_master_ssh_user

Unknown option: orig_master_is_new_slave

Mon Sep 10 10:42:34 2018 662234 Set read_only on the new master.. ok.

bind: Cannot assign requested address

Mon Sep 10 10:42:34 2018 737217 Set read_only=1 on the orig master.. ok.

Mon Sep 10 10:42:34 2018 739814 Killing all application threads..

Mon Sep 10 10:42:34 2018 739843 done.

STOP ARGS: $VAR1 = [];

Mon Sep 10 10:42:34 2018 - [info] ok.

Mon Sep 10 10:42:34 2018 - [info] Locking all tables on the orig master to reject updates from everybody (including root):

Mon Sep 10 10:42:34 2018 - [info] Executing FLUSH TABLES WITH READ LOCK..

Mon Sep 10 10:42:34 2018 - [info] ok.

Mon Sep 10 10:42:34 2018 - [info] Orig master binlog:pos is mysql-bin.000007:505.

Mon Sep 10 10:42:34 2018 - [info] Waiting to execute all relay logs on slave1(192.168.56.102:3306)..

Mon Sep 10 10:42:34 2018 - [info] master_pos_wait(mysql-bin.000007:505) completed on slave1(192.168.56.102:3306). Executed 0 events.

Mon Sep 10 10:42:34 2018 - [info] done.

Mon Sep 10 10:42:34 2018 - [info] Getting new master's binlog name and position..

Mon Sep 10 10:42:34 2018 - [info] mysql-bin.000007:505

Mon Sep 10 10:42:34 2018 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='slave1 or 192.168.56.102', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=505, MASTER_USER='repl', MASTER_PASSWORD='xxx';

Mon Sep 10 10:42:34 2018 - [info] Executing master ip online change script to allow write on the new master:

Mon Sep 10 10:42:34 2018 - [info] /usr/local/bin/master_ip_online_change --command=start --orig_master_host=master --orig_master_ip=192.168.56.101 --orig_master_port=3306 --orig_master_user='admin' --orig_master_password='mhaadmin' --new_master_host=slave1 --new_master_ip=192.168.56.102 --new_master_port=3306 --new_master_user='admin' --new_master_password='mhaadmin' --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave

ARGS: $VAR1 = [

'--command=start',

'--orig_master_host=master',

'--orig_master_ip=192.168.56.101',

'--orig_master_port=3306',

'--orig_master_user=admin',

'--orig_master_password=mhaadmin',

'--new_master_host=slave1',

'--new_master_ip=192.168.56.102',

'--new_master_port=3306',

'--new_master_user=admin',

'--new_master_password=mhaadmin',

'--orig_master_ssh_user=root',

'--new_master_ssh_user=root',

'--orig_master_is_new_slave'

];

Unknown option: orig_master_ssh_user

Unknown option: new_master_ssh_user

Unknown option: orig_master_is_new_slave

Mon Sep 10 10:42:34 2018 827330 Set read_only=0 on the new master.

Mon Sep 10 10:42:34 2018 - [info] ok.

Mon Sep 10 10:42:34 2018 - [info]

Mon Sep 10 10:42:34 2018 - [info] * Switching slaves in parallel..

Mon Sep 10 10:42:34 2018 - [info]

Mon Sep 10 10:42:34 2018 - [info] -- Slave switch on host slave2(192.168.56.103:3306) started, pid: 4263

Mon Sep 10 10:42:34 2018 - [info]

Mon Sep 10 10:42:35 2018 - [info] Log messages from slave2 ...

Mon Sep 10 10:42:35 2018 - [info]

Mon Sep 10 10:42:34 2018 - [info] Waiting to execute all relay logs on slave2(192.168.56.103:3306)..

Mon Sep 10 10:42:34 2018 - [info] master_pos_wait(mysql-bin.000007:505) completed on slave2(192.168.56.103:3306). Executed 0 events.

Mon Sep 10 10:42:34 2018 - [info] done.

Mon Sep 10 10:42:34 2018 - [info] Resetting slave slave2(192.168.56.103:3306) and starting replication from the new master slave1(192.168.56.102:3306)..

Mon Sep 10 10:42:34 2018 - [info] Executed CHANGE MASTER.

Mon Sep 10 10:42:35 2018 - [info] Slave started.

Mon Sep 10 10:42:35 2018 - [info] End of log messages from slave2 ...

Mon Sep 10 10:42:35 2018 - [info]

Mon Sep 10 10:42:35 2018 - [info] -- Slave switch on host slave2(192.168.56.103:3306) succeeded.

Mon Sep 10 10:42:35 2018 - [info] Unlocking all tables on the orig master:

Mon Sep 10 10:42:35 2018 - [info] Executing UNLOCK TABLES..

Mon Sep 10 10:42:35 2018 - [info] ok.

Mon Sep 10 10:42:35 2018 - [info] Starting orig master as a new slave..

Mon Sep 10 10:42:35 2018 - [info] Resetting slave master(192.168.56.101:3306) and starting replication from the new master slave1(192.168.56.102:3306)..

--执行change master

Mon Sep 10 10:42:35 2018 - [info] Executed CHANGE MASTER.

--启动slave线程

Mon Sep 10 10:42:36 2018 - [info] Slave started.

--所有新的slave服务器切换成功

Mon Sep 10 10:42:36 2018 - [info] All new slave servers switched successfully.

Mon Sep 10 10:42:36 2018 - [info]

Mon Sep 10 10:42:36 2018 - [info] * Phase 5: New master cleanup phase..

Mon Sep 10 10:42:36 2018 - [info]

--reset master;

Mon Sep 10 10:42:36 2018 - [info] slave1: Resetting slave info succeeded.

Mon Sep 10 10:42:36 2018 - [info] Switching master to slave1(192.168.56.102:3306) completed successfully.

[root@MHA bin]#