mysql-mha
来源：互联网 发布：flyme 彻底清除数据 编辑：程序博客网 时间：2024/04/24 07:53
mha 适合一主多从
主从同步,注意数据一致性。5.6 以上版本 Second behind master 这个数值不靠谱,忽略了 i/o 延迟。MHA 原理:
该软件由两部分组成:MHA Manager(管理节点)和 MHA Node(数据 节 点 ) 。 MHA Manager 可 以 单 独 部 署 在 一 台 独 立 的 机 器 上 管 理 多 个master-slave 集群,也可以部署在一台 slave 节点上。MHA Node 运行在每台 MySQL 服务器上,MHA Manager 会定时探测集群中的 master 节点,当master 出现故障时,它可以自动将最新数据的 slave 提升为新的 master,然后将所有其他的 slave 重新指向新的master。整个故障转移过程对应用程序完全透明。MHA 的搭建:
准备: 在主从复制的基础上面去做一主两从Server7:172.25.19.7(master)
Server8:172.25.19.8(slave)
Server9:172.25.19.9(slave)
Server10:172.25.19.10(mha)
1.安装
server7:
[root@server7~]#yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y ##安装 mha node
[root@server7 ~]# ssh-keygen ##生成密钥
[root@server7 ~]# ssh-copy-id 172.25.19.8 ##给其他 slave 分发密钥
[root@server7 ~]# ssh-copy-id 172.25.19.9
Server8:
[root@server8~]#yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y ##安装 node 节点[root@server8 ~]# ssh-key ##分发公钥和私钥
[root@server8 ~]# ssh-copy-id 172.25.19.7 ## 给 另 外 两 台 mysql 主机分发,这样可以免密连接
[root@server8 ~]# ssh-copy-id 172.25.19.9
[root@server8 ~]# mysql -p ##登陆数据库
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.17 MySQL Community Server (GPL)
mysql> grant replication slave on *.* to czb@'172.25.19.%' identified by 'Redhat+001'; ##创建复制用户
Query OK, 0 rows affected, 1 warning (0.00 sec)
Server9:
[root@server9 ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y ##按转 node 节点[root@server9 ~]# ssh-keygen ##分发公钥和私钥
[root@server9 ~]# ssh-copy-id 172.25.19.7 ##给另外两台分发,这样可以免密连接
[root@server9 ~]# ssh-copy-id 172.25.19.8
[root@server9 ~]# mysql -p ##登陆数据库
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.17 MySQL Community Server (GPL)
mysql> grant replication slave on *.* to czb@'172.25.19.%' identified
by 'Redhat+001'; ##创建复制用户
Query OK, 0 rows affected, 1 warning (0.00 sec)
Server10:(mha 主机)
[root@server10 ~]# ls ##显示内容
anaconda-ks.cfg
install.log
install.log.syslog
master_ip_failover
master_ip_online_change
mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm
perl-Config-Tiny-2.12-7.1.el6.noarch.rpm
perl-Email-Date-Format-1.002-5.el6.noarch.rpm
perl-Log-Dispatch-2.27-1.el6.noarch.rpm
perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
perl-MIME-Lite-3.027-2.el6.noarch.rpm
perl-MIME-Types-1.28-2.el6.noarch.rpm
perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
[root@server10 ~]# yum install *.rpm -y ##安装 mha
[root@server10 ~]# mkdir /usr/local/mha ##创建工作目录 workdir
[root@server10 ~]# mkdir /etc/mha ##创建配置文件所在目录
[root@server10 ~]# cd /etc/mha/ ##进入目录
[root@server10 mha]# vim mha.conf ##编辑配置文件
[root@server10 ~]#ssh-keygen ##mha 分发公钥和密钥进行免密连接
[root@server10 ~]#ssh-copy-id 172.25.19.8
[root@server10 ~]#ssh-copy-id 172.25.19.9
masterha_check_ssh --conf=/etc/mha/mha.conf
[root@server10 mha]# masterha_check_ssh
--conf=/etc/mha/mha.conf
Sat Aug 5 12:58:32 2017 - [warning] Global configuration file
/etc/masterha_default.cnf not found. Skipping.
Sat Aug 5 12:58:32 2017 - [info] Reading application default
configuration from /etc/mha/mha.cnf..Sat Aug 5 12:58:32 2017 - [info] Reading server configuration from
/etc/mha/mha.cnf..
Sat Aug 5 12:58:32 2017 - [info] Starting SSH connection tests..
Sat Aug 5 12:58:32 2017 - [debug]
Sat Aug 5 12:58:32 2017 - [debug] Connecting via SSH from
root@172.25.19.7(172.25.19.7:22) to
root@172.25.19.8(172.25.19.8:22)..
Sat Aug 5 12:58:32 2017 - [debug] ok.
Sat Aug 5 12:58:32 2017 - [debug] Connecting via SSH from
root@172.25.19.7(172.25.19.7:22) to
root@172.25.19.9(172.25.19.9:22)..
Sat Aug 5 12:58:32 2017 - [debug] ok.
Sat Aug 5 12:58:33 2017 - [debug]
Sat Aug 24 12:58:32 2017 - [debug] Connecting via SSH from
root@172.25.19.8(172.25.19.8:22) to
root@172.25.19.7(172.25.19.7:22)..
Sat Aug 5 12:58:33 2017 - [debug] ok.
Sat Aug 5 12:58:33 2017 - [debug] Connecting via SSH from
root@172.25.19.8(172.25.19.8:22) to
root@172.25.19.9(172.25.19.9:22)..
Sat Aug 5 12:58:33 2017 - [debug] ok.
Sat Aug 5 12:58:33 2017 - [debug]
Sat Aug 24 12:58:33 2017 - [debug] Connecting via SSH from
root@172.25.19.9(172.25.19.9:22) to
root@172.25.19.7(172.25.19.7:22)..
Sat Aug 5 12:58:33 2017 - [debug] ok.
Sat Aug 5 12:58:33 2017 - [debug] Connecting via SSH from
root@172.25.19.9(172.25.19.9:22) to
root@172.25.19.8(172.25.19.8:22)..
Sat Aug 5 12:58:33 2017 - [debug] ok.
Sat Aug 5 12:58:33 2017 - [info] All SSH connection tests passed
successfully.
三台 mysql 主机上面添加半同步插件 master 和 slave 都需要安装,三台mysql 主机上面的/etc/my.cnf 配置一样,所有节点配置一样,因为每一台主机可能为主也可能为 slave
vim /etc/my.cnf
28 server-id=7 ##三台机器的 server-id 不同
29 log-bin=mysql-bin
30 binlog-do-db=test31 binlog-ignore-db=mysql
32 gtid-mode=ON
33 enforce-gtid-consistency=ON34 slave-parallel-type=LOGICAL_CLOCK
35 slave-parallel-workers=16
36 relay_log_info_repository=TABLE
37 relay_log_recovery=ON
38 rpl_semi_sync_master_enabled=ON ##设置半同步master和salve开启
39 rpl_semi_sync_slave_enabled=ON
Server7 server8 server9 上面 mysql 都需要安装 semi_master
semi_slave 插件mysql> install plugin rpl_semi_sync_master soname
‘semisync_master.so’;
Query OK, 0 rows affected (0.03 sec)
mysql> install plugin rpl_semi_sync_slave soname
‘semisync_slave.so’;Query OK, 0 rows affected (0.02 sec)
然后两个 slave server8,server9 开启 read_only
mysql> set global read_only=1;Query OK, 0 rows affected (0.00 sec)
然后三台 mysql 主机给复制用户和 root 用户授权:
mysql> GRANT REPLICATION SLAVE ON . TO czb@’172.25.19.%’
identified by ‘Redhat+001’; ##这个密码是 manager 主机配置文件里面所写的密码Query OK, 0 rows affected, 1 warning (10.01 sec)
mysql> grant all on . to root@’172.25.19.%’ identified by
‘’Redhat+001’; ##这个密码是 manager 主机配置文件里面所写的密码Query OK, 0 rows affected, 1 warning (0.01 sec)
Server10(manager 主机):
[root@server10 mha]# masterha_check_repl –conf=/etc/mha/mha.cnfSat Aug 5 14:46:40 2017 - [warning] Global configuration file
/etc/masterha_default.cnf not found. Skipping.
Sat Aug 5 14:46:40 2017 - [info] Reading application default
configuration from /etc/mha/mha.cnf..
Sat Aug 5 14:46:40 2017 - [info] Reading server configuration from/etc/mha/mha.cnf..
Sat Aug 5 14:46:40 2017 - [info] MHA::MasterMonitor version 0.56.
Sat Aug 5 14:46:40 2017 - [info] GTID failover mode = 1
Sat Aug 5 14:46:40 2017 - [info] Dead Servers:
Sat Aug 5 14:46:40 2017 - [info] Alive Servers:
Sat Aug 5 14:46:40 2017 - [info] 172.25.19.7(172.25.19.7:3306)
Sat Aug 5 14:46:40 2017 - [info] 172.25.19.8(172.25.19.8:3306)
Sat Aug 5 14:46:40 2017 - [info] 172.25.19.9(172.25.19.9:3306)
Sat Aug 5 14:46:40 2017 - [info] Alive Slaves:
Sat Aug 5 14:46:40 2017 - [info] 172.25.19.8(172.25.19.8:3306)
Version=5.7.17-log (oldest major version between slaves)
log-bin:enabled
Sat Aug 5 14:46:40 2017 - [info] GTID ON
Sat Aug 5 14:46:40 2017 - [info] Replicating from
172.25.19.7(172.25.19.7:3306)
Sat Aug 5 14:46:40 2017 - [info] Primary candidate for the new Master
(candidate_master is set)
Sat Aug 5 14:46:40 2017 - [info] 172.25.19.9(172.25.19.9:3306)
Version=5.7.17-log (oldest major version between slaves)
log-bin:enabled
Sat Aug 5 14:46:40 2017 - [info] GTID ON
Sat Aug 5 14:46:40 2017 - [info] Replicating from
172.25.19.7(172.25.19.7:3306)
Sat Aug 5 14:46:40 2017 - [info] Current Alive Master:
172.25.14.7(172.25.19.7:3306)
Sat Aug 5 14:46:40 2017 - [info] Checking slave configurations..
Sat Aug 5 14:46:40 2017 - [info] Checking replication filtering
settings..
Sat Aug 5 14:46:40 2017 - [info] binlog_do_db= test,
binlog_ignore_db= mysql
Sat Aug 5 14:46:40 2017 - [info] Replication filtering check ok.
Sat Aug 5 14:46:40 2017 - [info] GTID (with auto-pos) is supported.
Skipping all SSH and Node package checking.
Sat Aug 5 14:46:40 2017 - [info] Checking SSH publickey
authentication settings on the current master..
Sat Aug 5 14:46:40 2017 - [info] HealthCheck: SSH to 172.25.19.7 is
reachable.
Sat Aug 5 14:46:40 2017 - [info]
172.25.19.7(172.25.19.7:3306) (current master)
+–172.25.19.8(172.25.19.8:3306)
+–172.25.19.9(172.25.19.9:3306)
Sat Aug 5 14:46:40 2017 - [info] Checking replication health on
172.25.19.8..Sat Aug 5 14:46:40
Sat Aug 5 14:46:40
172.25.19.9..
Sat Aug 5 14:46:40
Sat Aug 5 14:46:40
defined.
Sat Aug 5 14:46:40
Sat Aug 5 14:46:40
2017 - [info] ok.
2017 - [info] Checking replication health on
2017 - [info] ok.
2017 - [warning] master_ip_failover_script is not
2017 - [warning] shutdown_script is not defined.
2017 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
[root@server10 mha]# nohup masterha_manager –conf=/etc/mha/app.cnf & ##开启 manager
1589 0:PING_OK master:172.25.19.7
[root@server4 mha]#
[root@server10 mha]# masterha_check_status–conf=/etc/mha/app.cnf ##查看状态
app (pid:1589) is running(0:PING_OK), master:172.25.19.7
测试:
在 master 主机上面
[root@server7 ~]# killall -9 mysqld_safe ##这样就会直接跳过去[root@server7 ~]# killall -9 mysqld
在 server8 上面查看,会有 master 状态,没有 slave 状态。
Server9 上面查看,master 和 slave 状态都有
###手动切换
用脚本在线切换(热切)和切换 dead 掉(冷切)的主机
/etc/init.d/mysql start
mysql> change master to
master_host=’172.25.19.8’,master_user=’czb’,master_password=’R
edhat+001’,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.23 sec) ##重新change 然后加入到 slave 中,开启 slave
mysql> start slave;
Query OK, 0 rows affected (0.34 sec)
Server10 上面把切换脚本放到/etc/mha 目录下面
[root@foundation19 ~]# scp master_ip_failovermaster_ip_online_change root@172.25.19.10:/etc/mha
root@172.25.19.10’s password:
master_ip_failover 100% 2167 2.1KB/s 00:00master_ip_online_change 100% 3842 3.8KB/s 00:00
vim /etc/mha/app.cnf
5 master_ip_failover_script=/etc/mha/master_ip_failover
6master_ip_online_change_script=/etc/mha/master_ip_online_change
[root@server10 mha]# chmod +x master_ip_failovermaster_ip_online_change ##给执行权限
[root@server10 mha]# masterha_check_repl –conf=/etc/mha/app.cnf
Sat Aug 5 15:41:19 2017 - [warning] Global configuration file
/etc/masterha_default.cnf not found. Skipping.
Sat Aug 5 15:41:19 2017 - [info] Reading application default
configuration from /etc/mha/mha.cnf..
Sat Aug 5 15:41:19 2017 - [info] Reading server configuration from
/etc/mha/mha.cnf..
Sat Aug 5 15:41:19 2017 - [info] MHA::MasterMonitor version 0.56.
Sat Aug 5 15:41:20 2017 - [info] GTID failover mode = 1
Sat Aug 5 15:41:20 2017 - [info] Dead Servers:
Sat Aug 5 15:41:20 2017 - [info] Alive Servers:
Sat Aug 5 15:41:20 2017 - [info] 172.25.19.7(172.25.19.7:3306)
Sat Aug 5 15:41:20 2017 - [info] 172.25.14.2(172.25.19.8:3306)
Sat Aug 5 15:41:20 2017 - [info] 172.25.19.9(172.25.19.9:3306)
Sat Aug 5 15:41:20 2017 - [info] Alive Slaves:
Sat Aug 5 15:41:20 2017 - [info] 172.25.19.7(172.25.19.7:3306)
Version=5.7.17-log (oldest major version between slaves)
log-bin:enabled
Sat Aug 5 15:41:20 2017 - [info] GTID ON
Sat Aug 5 15:41:20 2017 - [info] Replicating from
172.25.19.8(172.25.19.8:3306)
Sat Aug 5 15:41:20 2017 - [info] 172.25.19.9(172.25.19.9:3306)
Version=5.7.17-log (oldest major version between slaves)
log-bin:enabled
Sat Aug 5 15:41:20 2017 - [info] GTID ON
Sat Aug 5 15:41:20 2017 - [info] Replicating from
172.25.19.8(172.25.19.8:3306)
Sat Aug 5 15:41:20 2017 - [info] Current Alive Master:
172.25.19.8(172.25.19.8:3306)
Sat Aug 5 15:41:20 2017 - [info] Checking slave configurations..
Sat Aug 5 15:41:20 2017 - [info] Checking replication filteringsettings..
Sat Aug 5 15:41:20 2017 - [info] binlog_do_db= test,
binlog_ignore_db= mysql
Sat Aug 5 15:41:20 2017 - [info] Replication filtering check ok.
Sat Aug 5 15:41:20 2017 - [info] GTID (with auto-pos) is supported.
Skipping all SSH and Node package checking.
Sat Aug 5 15:41:20 2017 - [info] Checking SSH publickey
authentication settings on the current master..
Sat Aug 5 15:41:20 2017 - [info] HealthCheck: SSH to 172.25.19.8 is
reachable.
Sat Aug 5 15:41:20 2017 - [info]
172.25.19.8(172.25.19.8:3306) (current master)
+–172.25.19.7(172.25.19.7:3306)
+–172.25.19.9(172.25.19.9:3306)
Sat Aug 5 15:41:20 2017 - [info] Checking replication health on
172.25.19.7..
Sat Aug 5 15:41:20 2017 - [info] ok.
Sat Aug 5 15:41:20 2017 - [info] Checking replication health on
172.25.19.9..
Sat Aug 5 15:41:20 2017 - [info] ok.
Sat Aug 5 15:41:20 2017 - [info] Checking master_ip_failover_script
status:
Sat Aug 5 15:41:20 2017 - [info] /etc/mha/master_ip_failover
–command=status –ssh_user=root –orig_master_host=172.25.19.8
–orig_master_ip=172.25.19.8 –orig_master_port=3306
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig
eth0:1 172.25.19.100/24===
Checking the Status of the script.. OK
Sat Aug 5 15:41:20 2017 - [info] OK.
Sat Aug 5 15:41:20 2017 - [warning] shutdown_script is not defined.
Sat Aug 5 15:41:20 2017 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
然后在 manager 上面进行在线切换:
[root@server10 mha]# masterha_stop –conf=/etc/mha/mha.cnf[root@server10 mha]# masterha_master_switch –conf=/etc/mha/mha.cnf –master_state=alive
–new_master_host=172.25.19.7 –new_master_port=3306
–orig_master_is_new_slave –running_updates_limit=10000
Sat Aug 5 15:54:53 2017 - [info] MHA::MasterRotate version 0.56.Sat Aug 5 15:54:53 2017 - [info] Starting online master switch..
Sat Aug 5 15:54:53 2017 - [info]
Sat Aug 5 15:54:53 2017 - [info] * Phase 1: Configuration Check
Phase..
Sat Aug 5 15:54:53 2017 - [info]
Sat Aug 5 15:54:53 2017 - [warning] Global configuration file
/etc/masterha_default.cnf not found. Skipping.
Sat Aug 5 15:54:53 2017 - [info] Reading application default
configuration from /etc/mha/mha.cnf..
Sat Aug 5 15:54:53 2017 - [info] Reading server configuration from
/etc/mha/mha.cnf..
Sat Aug 5 15:54:53 2017 - [info] GTID failover mode = 1
Sat Aug 5 15:54:53 2017 - [info] Current Alive Master:
172.25.19.8(172.25.19.8:3306)
Sat Aug 5 15:54:53 2017 - [info] Alive Slaves:
Sat Aug 5 15:54:53 2017 - [info] 172.25.19.7(172.25.19.7:3306)
Version=5.7.17-log (oldest major version between slaves)
log-bin:enabled
Sat Aug 5 15:54:53 2017 - [info] GTID ON
Sat Aug 5 15:54:53 2017 - [info] Replicating from
172.25.19.8(172.25.19.8:3306)
Sat Aug 5 15:54:53 2017 - [info] 172.25.19.9(172.25.19.9:3306)
Version=5.7.17-log (oldest major version between slaves)
log-bin:enabled
Sat Aug 5 15:54:53 2017 - [info] GTID ON
Sat Aug 5 15:54:53 2017 - [info] Replicating from
172.25.19.8(172.25.19.8:3306)
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the
master before switching. Is it ok to execute on
172.25.14.2(172.25.19.8:3306)? (YES/no): YES
Sat Aug 5 15:54:56 2017 - [info] Executing FLUSH
NO_WRITE_TO_BINLOG TABLES. This may take long time..
Sat Aug 5 15:54:56 2017 - [info] ok.
Sat Aug 5 15:54:56 2017 - [info] Checking MHA is not monitoring or
doing failover..
Sat Aug 5 15:54:56 2017 - [info] Checking replication health on
172.25.19.7..
Sat Aug 5 15:54:56 2017 - [info] ok.
Sat Aug 5 15:54:56 2017 - [info] Checking replication health on
172.25.19.9..
Sat Aug 5 15:54:56 2017 - [info] ok.
Sat Aug 5 15:54:56 2017 - [info] 172.25.19.7 can be new master.
Sat Aug 5 15:54:56 2017 - [info]From:
172.25.19.8(172.25.19.8:3306) (current master)
+–172.25.19.7(172.25.19.7:3306)
+–172.25.19.9(172.25.19.9:3306)
To:
172.25.19.7(172.25.19.7:3306) (new master)
+–172.25.19.9(172.25.19.9:3306)
+–172.25.19.8(172.25.19.8:3306)
Starting master switch from 172.25.19.8(172.25.19.8:3306) to
172.25.19.7(172.25.19.7:3306)? (yes/NO): yes
Sat Aug 5 15:54:59 2017 - [info] Checking whether
172.25.19.7(172.25.19.7:3306) is ok for the new master..
Sat Aug 5 15:54:59 2017 - [info] ok.
Sat Aug 5 15:54:59 2017 - [info] 172.25.19.8(172.25.19.8:3306):
SHOW SLAVE STATUS returned empty result. To check replication
filtering rules, temporarily executing CHANGE MASTER to a dummy
host.
Sat Aug 5 15:54:59 2017 - [info] 172.25.19.8(172.25.19.8:3306):
Resetting slave pointing to the dummy host.
Sat Aug 5 15:54:59 2017 - [info] ** Phase 1: Configuration Check
Phase completed.
Sat Aug 5 15:54:59 2017 - [info]
Sat Aug 5 15:54:59 2017 - [info] * Phase 2: Rejecting updates Phase..
Sat Aug 5 15:54:59 2017 - [info]
Sat Aug 5 15:54:59 2017 - [info] Executing master ip online change
script to disable write on the current master:
Sat Aug 5 15:54:59 2017 - [info] /etc/mha/master_ip_online_change
–command=stop –orig_master_host=172.25.19.8
–orig_master_ip=172.25.19.8 –orig_master_port=3306
–orig_master_user=’root’ –orig_master_password=’Redhat+001’
–new_master_host=172.25.19.7 –new_master_ip=172.25.19.7
–new_master_port=3306 –new_master_user=’czb’
–new_master_password=’Redhat+001’ –orig_master_ssh_user=root
–new_master_ssh_user=root –orig_master_is_new_slave
Disabling the VIP - 172.25.19.100/24 on old master: 172.25.19.8
Sat Aug 5 15:54:59 2017 - [info] ok.
Sat Aug 5 15:54:59 2017 - [info] Locking all tables on the orig master
to reject updates from everybody (including root):
Sat Aug 5 15:54:59 2017 - [info] Executing FLUSH TABLES WITH READ
LOCK..Sat Aug 5 15:54:59 2017 - [info] ok.
Sat Aug 5 15:54:59 2017 - [info] Orig master binlog:pos is
mysql-bin.000001:737.
Sat Aug 5 15:54:59 2017 - [info] Waiting to execute all relay logs on
172.25.19.7(172.25.19.7:3306)..
Sat Aug 5 15:54:59 2017 - [info]
master_pos_wait(mysql-bin.000001:737) completed on
172.25.19.7(172.25.19.7:3306). Executed 0 events.
Sat Aug 5 15:54:59 2017 - [info] done.
Sat Aug 5 15:54:59 2017 - [info] Getting new master’s binlog name
and position..
Sat Aug 5 15:54:59 2017 - [info] mysql-bin.000004:194
Sat Aug 5 15:54:59 2017 - [info] All other slaves should start
replication from here. Statement should be: CHANGE MASTER TO
MASTER_HOST=’172.25.19.7’, MASTER_PORT=3306,
MASTER_AUTO_POSITION=1, MASTER_USER=’czb’,
MASTER_PASSWORD=’xxx’;
Sat Aug 5 15:54:59 2017 - [info] Executing master ip online change
script to allow write on the new master:
Sat Aug 5 15:54:59 2017 - [info] /etc/mha/master_ip_online_change
–command=start –orig_master_host=172.25.19.8
–orig_master_ip=172.25.19.8 –orig_master_port=3306
–orig_master_user=’root’ –orig_master_password=’Redhat+001’
–new_master_host=172.25.19.7 –new_master_ip=172.25.19.7
–new_master_port=3306 –new_master_user=’czb’
–new_master_password=’Redhat+001’ –orig_master_ssh_user=root
–new_master_ssh_user=root –orig_master_is_new_slave
Enabling the VIP - 172.25.19.100/24 on new master: 172.25.19.7
Sat Aug 5 15:55:00 2017 - [info] ok.
Sat Aug 5 15:55:00 2017 - [info] Setting read_only=0 on
172.25.19.7(172.25.19.7:3306)..
Sat Aug 5 15:55:00 2017 - [info] ok.
Sat Aug 5 15:55:00 2017 - [info]
Sat Aug 5 15:55:00 2017 - [info] * Switching slaves in parallel..
Sat Aug 5 15:55:00 2017 - [info]
Sat Aug 5 15:55:00 2017 - [info] – Slave switch on host
172.25.19.9(172.25.19.9:3306) started, pid: 2653
Sat Aug 5 15:55:00 2017 - [info]
Sat Aug 5 15:55:00 2017 - [info] Log messages from 172.25.19.9 ...
Sat Aug 5 15:55:00 2017 - [info]
Sat Aug 5 15:55:00 2017 - [info] Waiting to execute all relay logs on
172.25.19.9(172.25.19.9:3306)..Sat Aug 5 15:55:00 2017 - [info]
master_pos_wait(mysql-bin.000001:737) completed on
172.25.19.9(172.25.19.9:3306). Executed 0 events.
Sat Aug 5 15:55:00 2017 - [info] done.
Sat Aug 5 15:55:00 2017 - [info] Resetting slave
172.25.19.9(172.25.19.9:3306) and starting replication from the new
master 172.25.19.7(172.25.19.7:3306)..
Sat Aug 5 15:55:00 2017 - [info] Executed CHANGE MASTER.
Sat Aug 5 15:55:00 2017 - [info] Slave started.
Sat Aug 5 15:55:00 2017 - [info] End of log messages from
172.25.149.9 ...
Sat Aug 5 15:55:00 2017 - [info]
Sat Aug 5 15:55:00 2017 - [info] – Slave switch on host
172.25.19.9(172.25.19.9:3306) succeeded.
Sat Aug 5 15:55:00 2017 - [info] Unlocking all tables on the orig
master:
Sat Aug 5 15:55:00 2017 - [info] Executing UNLOCK TABLES..
Sat Aug 5 15:55:00 2017 - [info] ok.
Sat Aug 5 15:55:00 2017 - [info] Starting orig master as a new slave..
Sat Aug 5 15:55:00 2017 - [info] Resetting slave
172.25.19.8(172.25.19.8:3306) and starting replication from the new
master 172.25.19.7(172.25.19.7:3306)..
Sat Aug 5 15:55:01 2017 - [info] Executed CHANGE MASTER.
Sat Aug 5 15:55:01 2017 - [info] Slave started.
Sat Aug 5 15:55:01 2017 - [info] All new slave servers switched
successfully.
Sat Aug 5 15:55:01 2017 - [info]
Sat Aug 5 15:55:01 2017 - [info] * Phase 5: New master cleanup
phase..
Sat Aug 5 15:55:01 2017 - [info]
Sat Aug 5 15:55:01 2017 - [info] 172.25.19.7: Resetting slave info
succeeded.
Sat Aug 5 15:55:01 2017 - [info] Switching master to
172.25.19.7(172.25.19.7:3306) completed successfully.
在线切换成功,可以看到 vip 到了 server7 上面,然后查看 server8 和server7 的 slave 状态,显示 master 为 server7冷切:切换 dead 掉的主机:
25 #candidate_master=1
26 #check_repl_delay=0
[root@server10 mha]# masterha_master_switch –conf=/etc/mha/mha.cnf –master_state=dead
–new_master_host=172.25.19.9 –new_master_port=3306
–ignore_last_failover
–dead_master_ip= is not set. Using 172.25.19.7.
–dead_master_port= is not set. Using 3306.
Sat Aug 5 17:11:39 2017 - [warning] Global configuration file
/etc/masterha_default.cnf not found. Skipping.
Sat Aug 5 17:11:39 2017 - [info] Reading application default
configuration from /etc/mha/mha.cnf..
Sat Aug 5 17:11:39 2017 - [info] Reading server configuration from
/etc/mha/mha.cnf..
Sat Aug 5 17:11:39 2017 - [info] MHA::MasterFailover version 0.56.
Sat Aug 5 17:11:39 2017 - [info] Starting master failover.
Sat Aug 5 17:11:39 2017 - [info]
Sat Aug 5 17:11:39 2017 - [info] * Phase 1: Configuration Check
Phase..
Sat Aug 5 17:11:39 2017 - [info]
Sat Aug 5 17:11:39 2017 - [info] GTID failover mode = 1
Sat Aug 5 17:11:39 2017 - [info] Dead Servers:
Sat Aug 5 17:11:39 2017 - [info] 172.25.19.7(172.25.19.7:3306)
Sat Aug 5 17:11:39 2017 - [info] Checking master reachability via
MySQL(double check)...
Sat Aug 5 17:11:39 2017 - [info] ok.
Sat Aug 5 17:11:39 2017 - [info] Alive Servers:
Sat Aug 5 17:11:39 2017 - [info] 172.25.19.8(172.25.19.8:3306)
Sat Aug 5 17:11:39 2017 - [info] 172.25.19.9(172.25.19.9:3306)
Sat Aug 5 17:11:39 2017 - [info] Alive Slaves:
Sat Aug 5 17:11:39 2017 - [info] 172.25.19.8(172.25.19.8:3306)
Version=5.7.17-log (oldest major version between slaves)
log-bin:enabled
Sat Aug 5 17:11:39 2017 - [info] GTID ON
Sat Aug 5 17:11:39 2017 - [info] Replicating from
172.25.19.7(172.25.19.7:3306)
Sat Aug 5 17:11:39 2017 - [info] 172.25.19.9(172.25.19.9:3306)
Version=5.7.17-log (oldest major version between slaves)
log-bin:enabled
Sat Aug 5 17:11:39 2017 - [info] GTID ON
Sat Aug 5 17:11:39 2017 - [info] Replicating from
172.25.19.7(172.25.19.7:3306)
Master 172.25.19.7(172.25.19.7:3306) is dead. Proceed? (yes/NO):
yesSat Aug 5 17:11:41 2017 - [info] Starting GTID based failover.
Sat Aug 5 17:11:41 2017 - [info]
Sat Aug 5 17:11:41 2017 - [info] ** Phase 1: Configuration Check
Phase completed.
Sat Aug 5 17:11:41 2017 - [info]
Sat Aug 5 17:11:41 2017 - [info] * Phase 2: Dead Master Shutdown
Phase..
Sat Aug 5 17:11:41 2017 - [info]
Sat Aug 5 17:11:41 2017 - [info] HealthCheck: SSH to 172.25.19.7 is
reachable.
Sat Aug 5 17:11:41 2017 - [info] Forcing shutdown so that
applications never connect to the current master..
Sat Aug 5 17:11:41 2017 - [info] Executing master IP deactivation
script:
Sat Aug 5 17:11:41 2017 - [info] /etc/mha/master_ip_failover
–orig_master_host=172.25.19.7 –orig_master_ip=172.25.19.7
–orig_master_port=3306 –command=stopssh –ssh_user=root
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig
eth0:1 172.25.19.100/24===
Disabling the VIP on old master: 172.25.19.7
Sat Aug 5 17:11:41 2017 - [info] done.
Sat Aug 5 17:11:41 2017 - [warning] shutdown_script is not set.
Skipping explicit shutting down of the dead master.
Sat Aug 5 17:11:41 2017 - [info] * Phase 2: Dead Master Shutdown
Phase completed.
Sat Aug 5 17:11:41 2017 - [info]
Sat Aug 5 17:11:41 2017 - [info] * Phase 3: Master Recovery Phase..
Sat Aug 5 17:11:41 2017 - [info]
Sat Aug 5 17:11:41 2017 - [info] * Phase 3.1: Getting Latest Slaves
Phase..
Sat Aug 24 17:11:41 2017 - [info]
Sat Aug 5 17:11:41 2017 - [info] The latest binary log file/position on
all slaves is mysql-bin.000004:194
Sat Aug 5 17:11:41 2017 - [info] Latest slaves (Slaves that received
relay log files to the latest):
Sat Aug 5 17:11:41 2017 - [info] 172.25.19.8(172.25.19.8:3306)
Version=5.7.17-log (oldest major version between slaves)
log-bin:enabled
Sat
Aug 5 17:11:41 2017 - [info] GTID ON
Sat Aug 5 17:11:41 2017 - [info] Replicating from
172.25.19.7(172.25.19.7:3306)
Sat Aug 5 17:11:41 2017 - [info] 172.25.19.9(172.25.19.9:3306)Version=5.7.17-log (oldest major version between slaves)
log-bin:enabled
Sat Aug 5 17:11:41 2017 - [info] GTID ON
Sat Aug 5 17:11:41 2017 - [info] Replicating from
172.25.14.1(172.25.19.7:3306)
Sat Aug 5 17:11:41 2017 - [info] The oldest binary log file/position on
all slaves is mysql-bin.000004:194
Sat Aug 5 17:11:41 2017 - [info] Oldest slaves:
Sat Aug 5 17:11:41 2017 - [info] 172.25.19.8(172.25.19.8:3306)
Version=5.7.17-log (oldest major version between slaves)
log-bin:enabled
Sat Aug 5 17:11:41 2017 - [info] GTID ON
Sat Aug 5 17:11:41 2017 - [info] Replicating from
172.25.19.7(172.25.19.7:3306)
Sat Aug 5 17:11:41 2017 - [info] 172.25.19.9(172.25.19.9:3306)
Version=5.7.17-log (oldest major version between slaves)
log-bin:enabled
Sat Aug 5 17:11:41 2017 - [info] GTID ON
Sat Aug 5 17:11:41 2017 - [info] Replicating from
172.25.19.7(172.25.19.7:3306)
Sat Aug 5 17:11:41 2017 - [info]
Sat Aug 5 17:11:41 2017 - [info] * Phase 3.3: Determining New Master
Phase..
Sat Aug 5 17:11:41 2017 - [info]
Sat Aug 5 17:11:41 2017 - [info] 172.25.19.9 can be new master.
Sat Aug 5 17:11:41 2017 - [info] New master is
172.25.19.9(172.25.19.9:3306)
Sat Aug 5 17:11:41 2017 - [info] Starting master failover..
Sat Aug 5 17:11:41 2017 - [info]
From:
172.25.19.7(172.25.19.7:3306) (current master)
+–172.25.19.8(172.25.19.8:3306)
+–172.25.19.9(172.25.19.9:3306)
To:
172.25.19.9(172.25.19.9:3306) (new master)
+–172.25.19.8(172.25.19.8:3306)
Starting master switch from 172.25.19.7(172.25.19.7:3306) to
172.25.19.9(172.25.19.9:3306)? (yes/NO): yes
Sat Aug 5 17:11:43 2017 - [info] New master decided manually is
172.25.19.9(172.25.19.9:3306)
Sat Aug 5 17:11:43 2017 - [info]
Sat Aug 5 17:11:43 2017 - [info] * Phase 3.3: New Master RecoveryPhase..
Sat Aug 5 17:11:43 2017 - [info]
Sat Aug 5 17:11:43 2017 - [info] Waiting all logs to be applied..
Sat Aug 5 17:11:43 2017 - [info] done.
Sat Aug 5 17:11:43 2017 - [info] Replicating from the latest slave
172.25.19.8(172.25.19.8:3306) and waiting to apply..
Sat Aug 5 17:11:43 2017 - [info] Waiting all logs to be applied on the
latest slave..
Sat Aug 5 17:11:43 2017 - [info] Resetting slave
172.25.19.9(172.25.19.9:3306) and starting replication from the new
master 172.25.19.8(172.25.19.8:3306)..
Sat Aug 5 17:11:44 2017 - [info] Executed CHANGE MASTER.
Sat Aug 5 17:11:44 2017 - [info] Slave started.
Sat Aug 5 17:11:44 2017 - [info] Waiting to execute all relay logs on
172.25.19.9(172.25.19.9:3306)..
Sat
Aug 5 17:11:44 2017 - [info]
master_pos_wait(mysql-bin.000001:737) completed on
172.25.19.9(172.25.19.9:3306). Executed 4 events.
Sat Aug 5 17:11:44 2017 - [info] done.
Sat Aug 5 17:11:44 2017 - [info] done.
Sat Aug 5 17:11:44 2017 - [info] Getting new master’s binlog name
and position..
Sat Aug 5 17:11:44 2017 - [info] mysql-bin.000001:737
Sat Aug 5 17:11:44 2017 - [info] All other slaves should start
replication from here. Statement should be: CHANGE MASTER TO
MASTER_HOST=’172.25.19.9’, MASTER_PORT=3306,
MASTER_AUTO_POSITION=1, MASTER_USER=’czb’,
MASTER_PASSWORD=’xxx’;
Sat Aug 5 17:11:44 2017 - [info] Master Recovery succeeded.
File:Pos:Exec_Gtid_Set: mysql-bin.000001, 737,
e4b10c52-5885-11e7-972d-525400479867:1-2,
ea55b3de-5885-11e7-a3d7-525400a482be:1-2,
f42e88b9-5885-11e7-a45a-5254008995d3:1-2
Sat Aug 5 17:11:44 2017 - [info] Executing master IP activate script:
Sat Aug 5 17:11:44 2017 - [info] /etc/mha/master_ip_failover
–command=start –ssh_user=root –orig_master_host=172.25.19.7
–orig_master_ip=172.25.19.7 –orig_master_port=3306
–new_master_host=172.25.19.9 –new_master_ip=172.25.19.9
–new_master_port=3306 –new_master_user=’root’
–new_master_password=’Redhat+001’
Unknown option: new_master_user
Unknown option: new_master_password
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfigeth0:1 172.25.19.100/24===
Enabling the VIP - 172.25.19.100/24 on the new master - 172.25.19.9
Sat Aug 5 17:11:44 2017 - [info] OK.
Sat Aug 5 17:11:44 2017 - [info] Setting read_only=0 on
172.25.19.9(172.25.19.9:3306)..
Sat Aug 5 17:11:44 2017 - [info] ok.
Sat Aug 5 17:11:44 2017 - [info] ** Finished master recovery
successfully.
Sat Aug 5 17:11:44 2017 - [info] * Phase 3: Master Recovery Phase
completed.
Sat Aug 5 17:11:44 2017 - [info]
Sat Aug 5 17:11:44 2017 - [info] * Phase 4: Slaves Recovery Phase..
Sat Aug 5 17:11:44 2017 - [info]
Sat Aug 5 17:11:44 2017 - [info]
Sat Au 5 17:11:44 2017 - [info] * Phase 4.1: Starting Slaves in
parallel..
Sat Aug 5 17:11:44 2017 - [info]
Sat Aug 5 17:11:44 2017 - [info] – Slave recovery on host
172.25.19.8(172.25.19.8:3306) started, pid: 2693. Check tmp log
/usr/local/mha/172.25.19.8_3306_20170624171139.log if it takes
time..
Sat Aug 5 17:11:45 2017 - [info]
Sat Aug 5 17:11:45 2017 - [info] Log messages from 172.25.19.8 ...
Sat Aug 5 17:11:45 2017 - [info]
Sat Aug 5 17:11:44 2017 - [info] Resetting slave
172.25.19.8(172.25.19.8:3306) and starting replication from the new
master 172.25.19.9(172.25.19.9:3306)..
Sat Aug 5 17:11:45 2017 - [info] Executed CHANGE MASTER.
Sat Aug 5 17:11:45 2017 - [info] Slave started.
Sat Aug 5 17:11:45 2017 - [info]
gtid_wait(e4b10c52-5885-11e7-972d-525400479867:1-2,
ea55b3de-5885-11e7-a3d7-525400a482be:1-2,
f42e88b9-5885-11e7-a45a-5254008995d3:1-2) completed on
172.25.19.8(172.25.19.8:3306). Executed 4 events.
Sat Aug 5 17:11:45 2017 - [info] End of log messages from
172.25.19.8.
Sat Aug 5 17:11:45 2017 - [info] – Slave on host
172.25.19.8(172.25.19.8:3306) started.
Sat Aug 5 17:11:45 2017 - [info] All new slave servers recovered
successfully.
Sat Aug 5 17:11:45 2017 - [info]
Sat Aug 5 17:11:45 2017 - [info] * Phase 5: New master cleanup
phase..Sat Aug 5 17:11:45 2017 - [info]
Sat Aug 5 17:11:45 2017 - [info] Resetting slave info on the new
master..
Sat Aug 5 17:11:46 2017 - [info] 172.25.19.9: Resetting slave info
succeeded.
Sat Aug 5 17:11:46 2017 - [info] Master failover to
172.25.19.9(172.25.19.9:3306) completed successfully.
Sat Aug 5 17:11:46 2017 - [info]
—– Failover Report —–
app: MySQL Master failover 172.25.19.7(172.25.19.7:3306) to
172.25.19.9(172.25.19.9:3306) succeeded
Master 172.25.19.7(172.25.19.7:3306) is down!
Check MHA Manager logs at server10 for details.
Started manual(interactive) failover.
Invalidated master IP address on 172.25.19.7(172.25.19.7:3306)
Selected 172.25.19.9(172.25.19.9:3306) as a new master.
172.25.19.9(172.25.19.9:3306): OK: Applying all logs succeeded.
172.25.19.9(172.25.19.9:3306): OK: Activated master IP address.
172.25.19.8(172.25.19.8:3306): OK: Slave started, replicating from
172.25.19.9(172.25.19.9:3306)
172.25.19.9(172.25.19.9:3306): Resetting slave info succeeded.
Master failover to 172.25.19.9(172.25.19.9:3306) completed
successfully.
[root@server10 mha]#
server7:
/etc/init.d/mysql startmysql> change master to
master_host=’172.25.19.9’,master_user=’czb’,master_password=’Redhat+001’,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.16 sec)
mysql> start slave;
Query OK, 0 rows affected (0.34 sec)
阅读全文
0 0
- mysql MHA
- mysql-mha
- mysql-mha
- mysql+mha+keepalived实现MHA架构
- mysql HA方案： MHA
- MySQL MHA+Keepalived
- MySQL MHA相关测试
- MySQL MHA配置常见问题
- mha+mysql自动切换
- 再谈Mysql MHA
- 再谈Mysql MHA
- Mysql GTID Mha配置
- mysql高可用--mha
- MySQL:MHA安装配置
- MySQL:MHA测试
- mysql-mha架构
- MySQL 5.6 GTID+MHA
- mysql mha+keepalived
- 引用和指针的区别
- 笔记(機器學習技法)：Random Forest
- servlet 读取resource的文件
- 设计模式课堂小笔记
- Unity3D之Input输入事件总结
- mysql-mha
- strlen sizeof详尽分析
- 经典DP——数字三角形
- 析构函数
- AlertDialog学习篇
- 二叉树前序、中序、后序遍历非递归写法的透彻解析
- Csting Left Mid Right
- Ajax获取图片的两种方式
- <job_demo><Qt>自绘仪表类控件
