注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

MySQL高可用之一:主从复制环境 MHA 部署  

2016-07-09 17:35:44|  分类: MySQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
MHA(Master High Availability) 目前在MySQL高可用方面是一个相对成熟的解决方案, MHA 官方宣称在故障切换时通常能在 30 秒内完成,并且最大的优势是:在故障切换过程中能够最大限度保证数据一致性。

今天先记录下 MHA 的部署过程,高可用部分可以结合 MHA 自带的脚本或 keepalived 管理 VIP ,以后再做这方面的测试。

一、安装前准备
--环境信息
 主机名 IP 操作系统 角色
 db1 192.168.2.37 RHEL6.2 MySQL MASTER + MHA NODE
 db2 192.168.2.38 RHEL6.2 MySQL SLAVE + MHA NODE
 db3 192.168.2.39 RHEL6.2 MHA MANAGER
   备注:笔记本三台虚机,db1做主库,db2做备库,db3做 MHA MANAGER 结点。

--前提条件
 db1、db2节点已配置半同步复制,详见 MySQL5.7: 半同步复制(Semisynchronous Replication)配置 
  
--db1、db2 上创建监控用户

root@localhost:mysql>grant all privileges on *.* to 'root'@'db2' identified by '111111';
Query OK, 0 rows affected, 1 warning (0.01 sec)

root@localhost:mysql>grant all privileges on *.* to 'root'@'db3' identified by '111111';
Query OK, 0 rows affected, 1 warning (0.01 sec)


--db2 设置只读

[mysql@db2 ~]$ mysql -uroot -p -e "set global read_only=1"
Enter password: 


--下载

二、安装 MHA NODE
--db1、db2、db3节点上编译安装 MHA NODE

# yum install perl-DBD-MySQL perl-devel perl-CPAN -y
# tar xvf mha4mysql-node-0.56.tar.gz 
# perl Makefile.PL
# make
# make install

备注:安装完 MHA NODE 结点后,在 /usr/local/bin 目录下存在以下文件。

--MHA NODE 生成的脚本

-r-xr-xr-x   1 root root  7525 Jul  8 20:42 save_binary_logs
-r-xr-xr-x   1 root root  8261 Jul  8 20:42 purge_relay_logs
-r-xr-xr-x   1 root root  4807 Jul  8 20:42 filter_mysqlbinlog
-r-xr-xr-x   1 root root 16367 Jul  8 20:42 apply_diff_relay_logs

备注,这几个脚本作用如下:
save_binary_logs: 保存和复制主节点的 binary log 
apply_diff_relay_logs: 识别差异的 relay 日志事件并将差异应用于其它备节点
purge_relay_logs: 清除 relay 日志


三、安装 MHA Manager
1、db3 节点上编译安装 MHA MANAGER

# yum install perl-DBD-MySQL
# yum install perl-Config-Tiny 
# yum install perl-Log-Dispatch 
# yum install perl-Parallel-ForkManager 
# yum install perl-Time-HiRes

# tar xvf mha4mysql-manager-0.56.tar.gz
# perl Makefile.PL
# make
# make install

备注:如果系统yum源找不到某个安装包,网上下载相应包安装即可。

2、配置信任关系

--db1
# ssh-keygen -t rsa
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@db2

--db2
# ssh-keygen -t rsa
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@db1

--db3
# ssh-keygen -t rsa
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@db1
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@db2


3、编辑 MHA MANAGER 配置文件
--创建 MHA MANAGER 目录

# mkdir -p /opt/masterha
# mkdir -p /opt/masterha/app1

--创建文件 /opt/masterha/app1.cnf

[server default]
# mysql user and password
user=root
password=111111
ssh_user=root
master_pid_file=/opt/masterha/app1/master1.pid
manager_log=/opt/masterha/app1/manager.log
master_binlog_dir=/database/mysql/data/binlog
repl_user=rep1
repl_password=rep1abcd1243d
ping_interval=3
master_ip_failover_script= ''

# working directory on the manager
manager_workdir=/opt/masterha/app1

# working directory on MySQL servers
remote_workdir=/opt/masterha/app1

[server2]
hostname=db1
port=3306

[server1]
hostname=db2
port=3306

备注:主要参数解释:
user: MySQL 数据库超级用户,这里配置成 root
pasword: MySQL 数据库 root 用户密码
ssh_user:MHA MANAGER 和 MHA NODE 节点SSH通信时的操作系统用户
manager_log: MHA MANAGER 日志文件
master_binlog_dir: Master 主节点 binlog 目录位置
repl_user:复制环境的用户名
ping_interval:MHA MANAGER 节点 ping master 节点的时间间隔,默认三秒,如果三次 PING 不通,则判断主节点宕机。

4、Checking SSH connections

[root@db3 ~]# masterha_check_ssh --conf /opt/masterha/app1/app1.cnf 
Fri Jul  8 22:21:30 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Jul  8 22:21:30 2016 - [info] Reading application default configuration from /opt/masterha/app1/app1.cnf..
Fri Jul  8 22:21:30 2016 - [info] Reading server configuration from /opt/masterha/app1/app1.cnf..
Fri Jul  8 22:21:30 2016 - [info] Starting SSH connection tests..
Fri Jul  8 22:21:31 2016 - [debug] 
Fri Jul  8 22:21:30 2016 - [debug]  Connecting via SSH from root@db1(192.168.2.37:22) to root@db2(192.168.2.38:22)..
Fri Jul  8 22:21:31 2016 - [debug]   ok.
Fri Jul  8 22:21:31 2016 - [debug] 
Fri Jul  8 22:21:31 2016 - [debug]  Connecting via SSH from root@db2(192.168.2.38:22) to root@db1(192.168.2.37:22)..
Fri Jul  8 22:21:31 2016 - [debug]   ok.
Fri Jul  8 22:21:31 2016 - [info] All SSH connection tests passed successfully.


5、Checking Replication Configuration
--masterha_check_repl 报错

[root@db3 app1]# masterha_check_repl --conf=/opt/masterha/app1/app1.cnf 
Sat Jul  9 07:25:03 2016 - [info]   Connecting to root@192.168.2.38(db2:22).. 
Can not exec "mysqlbinlog": No such file or directory at /usr/local/share/perl5/MHA/BinlogManager.pm line 106.
mysqlbinlog version command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client options
 at /usr/local/bin/apply_diff_relay_logs line 493

 
 --解决方法:db1、db2 添加软链接

ln -s /opt/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
ln -s /opt/mysql/bin/mysql /usr/local/bin/mysql

备注:之后看到如下信息:

....省略 
Sat Jul  9 10:37:19 2016 - [info] Checking replication health on db2..
Sat Jul  9 10:37:19 2016 - [info]  ok.
Sat Jul  9 10:37:19 2016 - [warning] master_ip_failover_script is not defined.
Sat Jul  9 10:37:19 2016 - [warning] shutdown_script is not defined.
Sat Jul  9 10:37:19 2016 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.

备注:说明 Replication 检测通过。

6、启动 MHA MANAGER

[root@db3 app1]# masterha_manager --conf=/opt/masterha/app1/app1.cnf > /tmp/mha_manager.log 2>&1 &
[1] 4449


7、查看 MHA MANAGER状态

[root@db3 app1]# masterha_check_status --conf=/opt/masterha/app1/app1.cnf 
app1 (pid:4449) is running(0:PING_OK), master:db1

四、主从切换测试:自动方式
1、db1 上关闭主库

[mysql@db1 bin]$ mysqladmin -uroot -p shutdown
Enter password: 


2、db3 上查看 MHA MANAGER 日志 /opt/masterha/app1/manager.log

Sat Jul  9 10:39:24 2016 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Sat Jul  9 10:39:24 2016 - [info] Executing SSH check script: save_binary_logs --command=test --start_pos=4 --binlog_dir=/database/mysql/data/binlog --output_file=/opt/masterha/app1/save_binary_logs_test --manager_version=0.56 --binlog_prefix=bin-log
Sat Jul  9 10:39:24 2016 - [info] HealthCheck: SSH to db1 is reachable.
Sat Jul  9 10:39:27 2016 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Sat Jul  9 10:39:27 2016 - [warning] Connection failed 2 time(s)..
Sat Jul  9 10:39:30 2016 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Sat Jul  9 10:39:30 2016 - [warning] Connection failed 3 time(s)..
Sat Jul  9 10:39:33 2016 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Sat Jul  9 10:39:33 2016 - [warning] Connection failed 4 time(s)..
Sat Jul  9 10:39:33 2016 - [warning] Master is not reachable from health checker!
Sat Jul  9 10:39:33 2016 - [warning] Master db1(192.168.2.37:3306) is not reachable!
Sat Jul  9 10:39:33 2016 - [warning] SSH is reachable.
Sat Jul  9 10:39:33 2016 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /opt/masterha/app1/app1.cnf again, and trying to connect to all servers to check server status..
Sat Jul  9 10:39:33 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Jul  9 10:39:33 2016 - [info] Reading application default configuration from /opt/masterha/app1/app1.cnf..
Sat Jul  9 10:39:33 2016 - [info] Reading server configuration from /opt/masterha/app1/app1.cnf..
Sat Jul  9 10:39:34 2016 - [info] GTID failover mode = 0
Sat Jul  9 10:39:34 2016 - [info] Dead Servers:
Sat Jul  9 10:39:34 2016 - [info]   db1(192.168.2.37:3306)
Sat Jul  9 10:39:34 2016 - [info] Alive Servers:
Sat Jul  9 10:39:34 2016 - [info]   db2(192.168.2.38:3306)
Sat Jul  9 10:39:34 2016 - [info] Alive Slaves:
Sat Jul  9 10:39:34 2016 - [info]   db2(192.168.2.38:3306)  Version=5.7.13-debug-log (oldest major version between slaves) log-bin:enabled
Sat Jul  9 10:39:34 2016 - [info]     Replicating from 192.168.2.37(192.168.2.37:3306)
Sat Jul  9 10:39:34 2016 - [info] Checking slave configurations..
Sat Jul  9 10:39:34 2016 - [warning]  relay_log_purge=0 is not set on slave db2(192.168.2.38:3306).
Sat Jul  9 10:39:34 2016 - [info] Checking replication filtering settings..
Sat Jul  9 10:39:34 2016 - [info]  Replication filtering check ok.
Sat Jul  9 10:39:34 2016 - [info] Master is down!
Sat Jul  9 10:39:34 2016 - [info] Terminating monitoring script.
Sat Jul  9 10:39:34 2016 - [info] Got exit code 20 (Master dead).
Sat Jul  9 10:39:34 2016 - [info] MHA::MasterFailover version 0.56.
Sat Jul  9 10:39:34 2016 - [info] Starting master failover.
Sat Jul  9 10:39:34 2016 - [info] 
Sat Jul  9 10:39:34 2016 - [info] * Phase 1: Configuration Check Phase..
Sat Jul  9 10:39:34 2016 - [info] 
Sat Jul  9 10:39:35 2016 - [info] GTID failover mode = 0
Sat Jul  9 10:39:35 2016 - [info] Dead Servers:
Sat Jul  9 10:39:35 2016 - [info]   db1(192.168.2.37:3306)
Sat Jul  9 10:39:35 2016 - [info] Checking master reachability via MySQL(double check)...
Sat Jul  9 10:39:35 2016 - [info]  ok.
Sat Jul  9 10:39:35 2016 - [info] Alive Servers:
Sat Jul  9 10:39:35 2016 - [info]   db2(192.168.2.38:3306)
Sat Jul  9 10:39:35 2016 - [info] Alive Slaves:
Sat Jul  9 10:39:35 2016 - [info]   db2(192.168.2.38:3306)  Version=5.7.13-debug-log (oldest major version between slaves) log-bin:enabled
Sat Jul  9 10:39:35 2016 - [info]     Replicating from 192.168.2.37(192.168.2.37:3306)
Sat Jul  9 10:39:35 2016 - [info] Starting Non-GTID based failover.
Sat Jul  9 10:39:35 2016 - [info] 
Sat Jul  9 10:39:35 2016 - [info] ** Phase 1: Configuration Check Phase completed.
Sat Jul  9 10:39:35 2016 - [info] 
Sat Jul  9 10:39:35 2016 - [info] * Phase 2: Dead Master Shutdown Phase..
Sat Jul  9 10:39:35 2016 - [info] 
Sat Jul  9 10:39:35 2016 - [info] Forcing shutdown so that applications never connect to the current master..
Sat Jul  9 10:39:35 2016 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master IP address.
Sat Jul  9 10:39:35 2016 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Sat Jul  9 10:39:36 2016 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Sat Jul  9 10:39:36 2016 - [info] 
Sat Jul  9 10:39:36 2016 - [info] * Phase 3: Master Recovery Phase..
Sat Jul  9 10:39:36 2016 - [info] 
Sat Jul  9 10:39:36 2016 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Sat Jul  9 10:39:36 2016 - [info] 
Sat Jul  9 10:39:36 2016 - [info] The latest binary log file/position on all slaves is bin-log.000018:2659
Sat Jul  9 10:39:36 2016 - [info] Latest slaves (Slaves that received relay log files to the latest):
Sat Jul  9 10:39:36 2016 - [info]   db2(192.168.2.38:3306)  Version=5.7.13-debug-log (oldest major version between slaves) log-bin:enabled
Sat Jul  9 10:39:36 2016 - [info]     Replicating from 192.168.2.37(192.168.2.37:3306)
Sat Jul  9 10:39:36 2016 - [info] The oldest binary log file/position on all slaves is bin-log.000018:2659
Sat Jul  9 10:39:36 2016 - [info] Oldest slaves:
Sat Jul  9 10:39:36 2016 - [info]   db2(192.168.2.38:3306)  Version=5.7.13-debug-log (oldest major version between slaves) log-bin:enabled
Sat Jul  9 10:39:36 2016 - [info]     Replicating from 192.168.2.37(192.168.2.37:3306)
Sat Jul  9 10:39:36 2016 - [info] 
Sat Jul  9 10:39:36 2016 - [info] * Phase 3.2: Saving Dead Master's Binlog Phase..
Sat Jul  9 10:39:36 2016 - [info] 
Sat Jul  9 10:39:36 2016 - [info] Fetching dead master's binary logs..
Sat Jul  9 10:39:36 2016 - [info] Executing command on the dead master db1(192.168.2.37:3306): save_binary_logs --command=save --start_file=bin-log.000018  --start_pos=2659 --binlog_dir=/database/mysql/data/binlog --output_file=/opt/masterha/app1/saved_master_binlog_from_db1_3306_20160709103934.binlog --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.56
  Creating /opt/masterha/app1 if not exists..    ok.
 Concat binary/relay logs from bin-log.000018 pos 2659 to bin-log.000018 EOF into /opt/masterha/app1/saved_master_binlog_from_db1_3306_20160709103934.binlog ..
 Binlog Checksum enabled
  Dumping binlog format description event, from position 0 to 154.. ok.
  Dumping effective binlog data from /database/mysql/data/binlog/bin-log.000018 position 2659 to tail(2682).. ok.
 Binlog Checksum enabled
 Concat succeeded.
Sat Jul  9 10:39:37 2016 - [info] scp from root@192.168.2.37:/opt/masterha/app1/saved_master_binlog_from_db1_3306_20160709103934.binlog to local:/opt/masterha/app1/saved_master_binlog_from_db1_3306_20160709103934.binlog succeeded.
Sat Jul  9 10:39:37 2016 - [info] HealthCheck: SSH to db2 is reachable.
Sat Jul  9 10:39:37 2016 - [info] 
Sat Jul  9 10:39:37 2016 - [info] * Phase 3.3: Determining New Master Phase..
Sat Jul  9 10:39:37 2016 - [info] 
Sat Jul  9 10:39:37 2016 - [info] Finding the latest slave that has all relay logs for recovering other slaves..
Sat Jul  9 10:39:37 2016 - [info] All slaves received relay logs to the same position. No need to resync each other.
Sat Jul  9 10:39:37 2016 - [info] Searching new master from slaves..
Sat Jul  9 10:39:37 2016 - [info]  Candidate masters from the configuration file:
Sat Jul  9 10:39:37 2016 - [info]  Non-candidate masters:
Sat Jul  9 10:39:37 2016 - [info] New master is db2(192.168.2.38:3306)
Sat Jul  9 10:39:37 2016 - [info] Starting master failover..
Sat Jul  9 10:39:37 2016 - [info] 
From:
db1(192.168.2.37:3306) (current master)
 +--db2(192.168.2.38:3306)

To:
db2(192.168.2.38:3306) (new master)
Sat Jul  9 10:39:37 2016 - [info] 
Sat Jul  9 10:39:37 2016 - [info] * Phase 3.3: New Master Diff Log Generation Phase..
Sat Jul  9 10:39:37 2016 - [info] 
Sat Jul  9 10:39:37 2016 - [info]  This server has all relay logs. No need to generate diff files from the latest slave.
Sat Jul  9 10:39:37 2016 - [info] Sending binlog..
Sat Jul  9 10:39:38 2016 - [info] scp from local:/opt/masterha/app1/saved_master_binlog_from_db1_3306_20160709103934.binlog to root@db2:/opt/masterha/app1/saved_master_binlog_from_db1_3306_20160709103934.binlog succeeded.
Sat Jul  9 10:39:38 2016 - [info] 
Sat Jul  9 10:39:38 2016 - [info] * Phase 3.4: Master Log Apply Phase..
Sat Jul  9 10:39:38 2016 - [info] 
Sat Jul  9 10:39:38 2016 - [info] *NOTICE: If any error happens from this phase, manual recovery is needed.
Sat Jul  9 10:39:38 2016 - [info] Starting recovery on db2(192.168.2.38:3306)..
Sat Jul  9 10:39:38 2016 - [info]  Generating diffs succeeded.
Sat Jul  9 10:39:38 2016 - [info] Waiting until all relay logs are applied.
Sat Jul  9 10:39:38 2016 - [info]  done.
Sat Jul  9 10:39:38 2016 - [info] Getting slave status..
Sat Jul  9 10:39:38 2016 - [info] This slave(db2)'s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(bin-log.000018:2659). No need to recover from Exec_Master_Log_Pos.
Sat Jul  9 10:39:38 2016 - [info] Connecting to the target slave host db2, running recover script..
Sat Jul  9 10:39:38 2016 - [info] Executing command: apply_diff_relay_logs --command=apply --slave_user='root' --slave_host=db2 --slave_ip=192.168.2.38  --slave_port=3306 --apply_files=/opt/masterha/app1/saved_master_binlog_from_db1_3306_20160709103934.binlog --workdir=/opt/masterha/app1 --target_version=5.7.13-debug-log --timestamp=20160709103934 --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.56 --slave_pass=xxx
Sat Jul  9 10:39:38 2016 - [info] 
MySQL client version is 5.7.13. Using --binary-mode.
Applying differential binary/relay log files /opt/masterha/app1/saved_master_binlog_from_db1_3306_20160709103934.binlog on db2:3306. This may take long time...
Applying log files succeeded.
Sat Jul  9 10:39:38 2016 - [info]  All relay logs were successfully applied.
Sat Jul  9 10:39:38 2016 - [info] Getting new master's binlog name and position..
Sat Jul  9 10:39:38 2016 - [info]  bin-log.000021:1169
Sat Jul  9 10:39:38 2016 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='db2 or 192.168.2.38', MASTER_PORT=3306, MASTER_LOG_FILE='bin-log.000021', MASTER_LOG_POS=1169, MASTER_USER='rep1', MASTER_PASSWORD='xxx';
Sat Jul  9 10:39:38 2016 - [warning] master_ip_failover_script is not set. Skipping taking over new master IP address.
Sat Jul  9 10:39:38 2016 - [info] Setting read_only=0 on db2(192.168.2.38:3306)..
Sat Jul  9 10:39:38 2016 - [info]  ok.
Sat Jul  9 10:39:38 2016 - [info] ** Finished master recovery successfully.
Sat Jul  9 10:39:38 2016 - [info] * Phase 3: Master Recovery Phase completed.
Sat Jul  9 10:39:38 2016 - [info] 
Sat Jul  9 10:39:38 2016 - [info] * Phase 4: Slaves Recovery Phase..
Sat Jul  9 10:39:38 2016 - [info] 
Sat Jul  9 10:39:38 2016 - [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase..
Sat Jul  9 10:39:38 2016 - [info] 
Sat Jul  9 10:39:38 2016 - [info] Generating relay diff files from the latest slave succeeded.
Sat Jul  9 10:39:38 2016 - [info] 
Sat Jul  9 10:39:38 2016 - [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase..
Sat Jul  9 10:39:38 2016 - [info] 
Sat Jul  9 10:39:38 2016 - [info] All new slave servers recovered successfully.
Sat Jul  9 10:39:38 2016 - [info] 
Sat Jul  9 10:39:38 2016 - [info] * Phase 5: New master cleanup phase..
Sat Jul  9 10:39:38 2016 - [info] 
Sat Jul  9 10:39:38 2016 - [info] Resetting slave info on the new master..
Sat Jul  9 10:39:38 2016 - [info]  db2: Resetting slave info succeeded.
Sat Jul  9 10:39:38 2016 - [info] Master failover to db2(192.168.2.38:3306) completed successfully.
Sat Jul  9 10:39:38 2016 - [info] 

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

app1: MySQL Master failover db1(192.168.2.37:3306) to db2(192.168.2.38:3306) succeeded

Master db1(192.168.2.37:3306) is down!

Check MHA Manager logs at db3:/opt/masterha/app1/manager.log for details.

Started automated(non-interactive) failover.
The latest slave db2(192.168.2.38:3306) has all relay logs for recovery.
Selected db2(192.168.2.38:3306) as a new master.
db2(192.168.2.38:3306): OK: Applying all logs succeeded.
Generating relay diff files from the latest slave succeeded.
db2(192.168.2.38:3306): Resetting slave info succeeded.
Master failover to db2(192.168.2.38:3306) completed successfully.

备注: 日志中有一段“Failover Report” 看到由 db1 切换到 db2 成功,这个日志显示了MHA 切换过程中的详细步骤,应该仔细查看,切换完后发现 db3 节点的 masterha_manager 进程消失了,主从切换后需要将原主库修复成从库。

3、db2 上执行

root@localhost:mysql>show slave status\G
Empty set (0.00 sec)

备注:可见 db2 上没有创建到 db1 的复制,此时 db2 已切换成主库。

4、启动原库库将操作以下
--db1 启动数据库

mysqld_safe --user=mysql --datadir=/database/mysql/data &


--db3 节点上查看 manager.log 中最近的 CHANGE MASTER 日志 

Sat Jul  9 10:39:38 2016 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='db2 or 192.168.2.38', MASTER_PORT=3306, MASTER_LOG_FILE='bin-log.000021', MASTER_LOG_POS=1169, MASTER_USER='rep1', MASTER_PASSWORD='xxx';


--db1节点执行主从复制: db2 上执行

CHANGE MASTER TO 
 MASTER_HOST='192.168.2.38', 
 MASTER_PORT=3306,
 MASTER_LOG_FILE='bin-log.000026', 
 MASTER_LOG_POS=1169, 
 MASTER_USER='rep1', 
 MASTER_PASSWORD='rep1abcd1243d';

root@localhost:mysql>start slave;
Query OK, 0 rows affected (0.01 sec)

备注: 这时新主库上的增量数据也会在 db1 上,从而保证从库和主库数据一致。

5、重新启动 MHA MANAGER 进程
--db3节点删除 .complete 文件

[root@db3 app1]# rm -f /opt/masterha/app1/app1.failover.complete 

备注: MHA MANAGER 发生切换后,会在 manager_workdir 目录下产生 app1.failover.complete  文件,若要启动 MHA MANAGER,需要将此文件删除,否则会影响以后的切换。

--masterha_check_repl

masterha_check_repl  --conf=/opt/masterha/app1/app1.cnf 


--再次启动 MHA MANAGER

masterha_manager --conf=/opt/masterha/app1/app1.cnf > /tmp/mha_manager.log 2>&1 &


五、手动方式主从切换:使用 masterha_master_switch 脚本  
--手工切换前需要关闭 manager 进程

[root@db3 app1]# masterha_stop --conf=/opt/masterha/app1/app1.cnf 
Stopped app1 successfully.


--手工切换: db3 上操作

[root@db3 app1]# masterha_master_switch --conf=/opt/masterha/app1/app1.cnf --master_state=alive --new_master_host=db1
Sat Jul  9 11:52:51 2016 - [info] MHA::MasterRotate version 0.56.
Sat Jul  9 11:52:51 2016 - [info] Starting online master switch..
Sat Jul  9 11:52:51 2016 - [info] 
Sat Jul  9 11:52:51 2016 - [info] * Phase 1: Configuration Check Phase..
Sat Jul  9 11:52:51 2016 - [info] 
Sat Jul  9 11:52:51 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Jul  9 11:52:51 2016 - [info] Reading application default configuration from /opt/masterha/app1/app1.cnf..
Sat Jul  9 11:52:51 2016 - [info] Reading server configuration from /opt/masterha/app1/app1.cnf..
Sat Jul  9 11:52:52 2016 - [info] GTID failover mode = 0
Sat Jul  9 11:52:52 2016 - [info] Current Alive Master: db2(192.168.2.38:3306)
Sat Jul  9 11:52:52 2016 - [info] Alive Slaves:
Sat Jul  9 11:52:52 2016 - [info]   db1(192.168.2.37:3306)  Version=5.7.13-debug-log (oldest major version between slaves) log-bin:enabled
Sat Jul  9 11:52:52 2016 - [info]     Replicating from 192.168.2.38(192.168.2.38:3306)

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on db2(192.168.2.38:3306)? (YES/no): yes
Sat Jul  9 11:52:55 2016 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Sat Jul  9 11:52:55 2016 - [info]  ok.
Sat Jul  9 11:52:55 2016 - [info] Checking MHA is not monitoring or doing failover..
Sat Jul  9 11:52:55 2016 - [info] Checking replication health on db1..
Sat Jul  9 11:52:55 2016 - [info]  ok.
Sat Jul  9 11:52:55 2016 - [info] db1 can be new master.
Sat Jul  9 11:52:55 2016 - [info] 
From:
db2(192.168.2.38:3306) (current master)
 +--db1(192.168.2.37:3306)

To:
db1(192.168.2.37:3306) (new master)

Starting master switch from db2(192.168.2.38:3306) to db1(192.168.2.37:3306)? (yes/NO): yes
Sat Jul  9 11:53:03 2016 - [info] Checking whether db1(192.168.2.37:3306) is ok for the new master..
Sat Jul  9 11:53:03 2016 - [info]  ok.
Sat Jul  9 11:53:03 2016 - [info] ** Phase 1: Configuration Check Phase completed.
Sat Jul  9 11:53:03 2016 - [info] 
Sat Jul  9 11:53:03 2016 - [info] * Phase 2: Rejecting updates Phase..
Sat Jul  9 11:53:03 2016 - [info] 
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes
Sat Jul  9 11:53:49 2016 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Sat Jul  9 11:53:49 2016 - [info] Executing FLUSH TABLES WITH READ LOCK..
Sat Jul  9 11:53:49 2016 - [info]  ok.
Sat Jul  9 11:53:49 2016 - [info] Orig master binlog:pos is bin-log.000021:2549.
Sat Jul  9 11:53:49 2016 - [info]  Waiting to execute all relay logs on db1(192.168.2.37:3306)..
Sat Jul  9 11:53:49 2016 - [info]  master_pos_wait(bin-log.000021:2549) completed on db1(192.168.2.37:3306). Executed 0 events.
Sat Jul  9 11:53:49 2016 - [info]   done.
Sat Jul  9 11:53:49 2016 - [info] Getting new master s binlog name and position..
Sat Jul  9 11:53:49 2016 - [info]  bin-log.000022:154
Sat Jul  9 11:53:49 2016 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='db1 or 192.168.2.37', MASTER_PORT=3306, MASTER_LOG_FILE='bin-log.000022', MASTER_LOG_POS=154, MASTER_USER='rep1', MASTER_PASSWORD='xxx';
Sat Jul  9 11:53:49 2016 - [info] 
Sat Jul  9 11:53:49 2016 - [info] * Switching slaves in parallel..
Sat Jul  9 11:53:49 2016 - [info] 
Sat Jul  9 11:53:49 2016 - [info] Unlocking all tables on the orig master:
Sat Jul  9 11:53:49 2016 - [info] Executing UNLOCK TABLES..
Sat Jul  9 11:53:49 2016 - [info]  ok.
Sat Jul  9 11:53:49 2016 - [info] All new slave servers switched successfully.
Sat Jul  9 11:53:49 2016 - [info] 
Sat Jul  9 11:53:49 2016 - [info] * Phase 5: New master cleanup phase..
Sat Jul  9 11:53:49 2016 - [info] 
Sat Jul  9 11:53:49 2016 - [info]  db1: Resetting slave info succeeded.
Sat Jul  9 11:53:49 2016 - [info] Switching master to db1(192.168.2.37:3306) completed successfully.

备注:执行过程中会提示一些问题,可以加上参数"--interactive=0" 非交互形式进行手工切换。

六、参考


  评论这张
 
阅读(266)| 评论(2)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018