时间：2021-07-01 10:21:17 帮助过：12人阅读
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 354
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 500
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 354
Relay_Log_Space: 649
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
3）在master节点上创建具有管理权限的账号
mysql> grant all on *.* to ‘zwj‘@‘192.168.154.%‘ identified by ‘zwj‘;
Query OK, 0 rows affected (0.00 sec)
二 配置集群间的密钥登陆
在node1上：
[root@node1 ~]# ssh-copy-id -i ./.ssh/id_rsa.pub root@192.168.154.156
[root@node1 ~]# ssh-copy-id -i ./.ssh/id_rsa.pub root@192.168.154.130
[root@node1 ~]# ssh-copy-id -i ./.ssh/id_rsa.pub root@192.168.154.154
[root@node1 ~]# ssh 192.168.154.154 ‘ifconfig‘ #验证
eth0 Link encap:Ethernet HWaddr 00:0C:29:67:65:ED
inet addr:192.168.154.154 Bcast:192.168.154.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe67:65ed/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:26253 errors:0 dropped:0 overruns:0 frame:0
TX packets:42416 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:23453164 (22.3 MiB) TX bytes:2514457 (2.3 MiB)
Interrupt:19 Base address:0x2024
在node2上：
[root@node2 ~]# ssh-keygen -t rsa
[root@node2 ~]# ssh-copy-id -i ./.ssh/id_rsa.pub root@192.168.154.128
[root@node2 ~]# ssh-copy-id -i ./.ssh/id_rsa.pub root@192.168.154.130
[root@node2 ~]# ssh-copy-id -i ./.ssh/id_rsa.pub root@192.168.154.154
在node3上：
[root@node3 log]# ssh-keygen -t rsa
[root@node3 ~]# ssh-copy-id -i ./.ssh/id_rsa.pub root@192.168.154.128
[root@node3 ~]# ssh-copy-id -i ./.ssh/id_rsa.pub root@192.168.154.156
[root@node3 ~]# ssh-copy-id -i ./.ssh/id_rsa.pub root@192.168.154.154
在node4上：
[root@node4 ~]# ssh-keygen -t rsa
[root@node4 ~]# ssh-copy-id -i ./.ssh/id_rsa.pub root@192.168.154.128
[root@node4 ~]# ssh-copy-id -i ./.ssh/id_rsa.pub root@192.168.154.156
[root@node4 ~]# ssh-copy-id -i ./.ssh/id_rsa.pub root@192.168.154.130
三 安装MHA Manager，在node1上：
[root@node1 ~]# yum install perl-DBD-MySQL -y
[root@node1 ~]# tar -zxf mha4mysql-node-0.56.tar.gz
[root@node1 ~]# cd mha4mysql-node-0.56
[root@node1 mha4mysql-node-0.56]# perl Makefile.PL
[root@node1 mha4mysql-node-0.56]# make
[root@node1 mha4mysql-node-0.56]# make install
[root@node1 mha4mysql-manager-0.56]# yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y #安装MHA Manger依赖的perl模块
[root@node1 ~]# tar -zxf mha4mysql-manager-0.56.tar.gz
[root@node1 ~]# cd mha4mysql-manager-0.56
[root@node1 mha4mysql-manager-0.56]# perl Makefile.PL
[root@node1 mha4mysql-manager-0.56]# make
[root@node1 mha4mysql-manager-0.56]# make install
四 安装MySQL node（在所有MySQL服务器上）
[root@node2 ~]#yum install perl-DBD-MySQL -y
[root@node2 ~]# cd mha4mysql-node-0.56/
[root@node2 mha4mysql-node-0.56]# perl Makefile.PL
[root@node2 mha4mysql-node-0.56]# make
[root@node2 mha4mysql-node-0.56]# make install
五 创建工作目录，配置MHA:
[root@node1 ~]# mkdir -pv /etc/masterha
[root@node1 ~]# vim /etc/masterha/appl.cnf
[server default]
user=zwj
password=zwj
manager_workdir=/etc/masterha/appl
manager_log=/etc/masterha/appl/manager.log
remote_workdir=/etc/masterha/appl
ssh_user=root
repl_user=slave
repl_password=slave
ping_interval=1
[server1]
hostname=192.168.154.156
[server2]
hostname=192.168.154.130
candidate_master=1 #设置为备选的master
[server3]
hostname=192.168.154.154
六 检查SSH连接状态：
[root@node1 ~]# masterha_check_ssh --conf=/etc/masterha/appl.cnf
Wed May 10 00:12:58 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed May 10 00:12:58 2017 - [info] Reading application default configuration from /etc/masterha/appl.cnf..
Wed May 10 00:12:58 2017 - [info] Reading server configuration from /etc/masterha/appl.cnf..
Wed May 10 00:12:58 2017 - [info] Starting SSH connection tests..
Wed May 10 00:13:15 2017 - [debug]
Wed May 10 00:12:59 2017 - [debug] Connecting via SSH from root@192.168.154.154(192.168.154.154:22) to root@192.168.154.156(192.168.154.156:22)..
Wed May 10 00:13:05 2017 - [debug] ok.
Wed May 10 00:13:05 2017 - [debug] Connecting via SSH from root@192.168.154.154(192.168.154.154:22) to root@192.168.154.130(192.168.154.130:22)..
Wed May 10 00:13:15 2017 - [debug] ok.
Wed May 10 00:13:20 2017 - [debug]
Wed May 10 00:12:58 2017 - [debug] Connecting via SSH from root@192.168.154.130(192.168.154.130:22) to root@192.168.154.156(192.168.154.156:22)..
Wed May 10 00:13:11 2017 - [debug] ok.
Wed May 10 00:13:11 2017 - [debug] Connecting via SSH from root@192.168.154.130(192.168.154.130:22) to root@192.168.154.154(192.168.154.154:22)..
Wed May 10 00:13:20 2017 - [debug] ok.
Wed May 10 00:13:35 2017 - [debug]
Wed May 10 00:12:58 2017 - [debug] Connecting via SSH from root@192.168.154.156(192.168.154.156:22) to root@192.168.154.130(192.168.154.130:22)..
Wed May 10 00:13:15 2017 - [debug] ok.
Wed May 10 00:13:15 2017 - [debug] Connecting via SSH from root@192.168.154.156(192.168.154.156:22) to root@192.168.154.154(192.168.154.154:22)..
Wed May 10 00:13:35 2017 - [debug] ok.
Wed May 10 00:13:35 2017 - [info] All SSH connection tests passed successfully.
七 检查整个复制环境：
[root@node1 ~]# masterha_check_repl --conf=/etc/masterha/appl.cnf
...
192.168.154.156(192.168.154.156:3306) (current master)
+--192.168.154.130(192.168.154.130:3306)
+--192.168.154.154(192.168.154.154:3306)
Wed May 10 00:33:36 2017 - [info] Checking replication health on 192.168.154.130..
Wed May 10 00:33:36 2017 - [info] ok.
Wed May 10 00:33:36 2017 - [info] Checking replication health on 192.168.154.154..
Wed May 10 00:33:36 2017 - [info] ok.
Wed May 10 00:33:36 2017 - [warning] master_ip_failover_script is not defined.
Wed May 10 00:33:36 2017 - [warning] shutdown_script is not defined.
Wed May 10 00:33:36 2017 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
八 开启MHA Manager监控：
[root@node1 ~]# nohup masterha_manager --conf=/etc/masterha/appl.cnf > /etc/masterha/appl/manager.log 2>&1 &
[1] 8300
查看MHA Manager监控：
[root@node1 ~]# masterha_check_status --conf=/etc/masterha/appl.cnf
appl (pid:8300) is running(0:PING_OK), master:192.168.154.156
关闭MHA Manager监控：
[root@node1 ~]# masterha_stop --conf=/etc/masterha/appl.cnf
Stopped appl successfully.
[1]+ Exit 1 nohup masterha_manager --conf=/etc/masterha/appl.cnf > /etc/masterha/appl/manager.log 2>&1
九 模拟主库宕机：
[root@node2 ~]# service mysqld stop
Stopping mysqld: [ OK ]
查看slave（node4），可见master已发生变化，
...
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.154.130
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: slave-bin.000003
Read_Master_Log_Pos: 106
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: slave-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
本文出自 “一万年太久，只争朝夕” 博客，请务必保留此出处http://zengwj1949.blog.51cto.com/10747365/1923915
MySQL之高可用架构—MHA
标签：mysql mha