MySQL高可用之MHA (转)

发布时间:2019-10-30  栏目:SQL  评论:0 Comments

 

MySQL高可用之MHA 

MHA简介

MHA是由菲律宾人yoshinorim(原就职于DeNA现就职于FaceBook)开荒的可比早熟的MySQL高可用方案。MHA能够在30秒内达成故障切换,并能在故障切换中,最大或者的保障数据豆蔻梢头致性。近日Taobao也正值开荒相似产品TMHA,前段时间已帮助后生可畏主风姿浪漫从。
 
MHA架构
MHA由MHA
Manager和MHA Node组成,如下图所示:

图片 1

 
MHA
Manager:

运维一些工具,举例masterha_manager工具达成全自动监察和控制MySQL
Master和落到实处master故障切换,别的工具达成手动达成master故障切换、在线mater转移、连接检查等等。一个Manager能够管理五个master-slave集群
 
MHA
Node:
布署在颇负运营MySQL的服务器上,无论是master依然slave。重要意义有八个。
 
  Ⅰ、保存二进制日志
        
  如若能够访谈故障master,会拷贝master的二进制日志

    
II、应用差别中继日志
         
从拥有最新数据的slave上转变差别中继日志,然后使用差别日志。

    
III、消灭中继日志
         
在不平息SQL线程的气象下删除中继日志
 
MHA职业原理

图片 2

当master现身故障时,通过对照slave之间I/O线程读取masterbinlog的岗位,选择最相近的slave做为latestslave。其余slave通过与latest
slave比较变化差别中继日志。在latest
slave上采用从master保存的binlog,同不时候将latest
slave进步为master。最终在其余slave上应用相应的反差中继日志并起头从新的master起首复制。
 
在MHA完结Master故障切换进度中,MHA
Node会试图访谈故障的master(通过SSH),如果可以访问(不是硬件故障,比方InnoDB数据文件损坏等),会保留二进制文件,以最大程度保障数据不甩掉。MHA和半联合签字复制一同行使会大大减少数据错失的摇摇欲堕。
 
时下高可用方案
Heartbeat+DRBD:

  • 付出:要求格外增加处于人困马乏状态的master server(并不处理利用流量)

  • 性能:为了兑现DRBD复制景况的高可用,innodb-flush-log-at-trx-commit和sync-binlog必得设置为1,那样会促成写品质降低。

  • 后生可畏致性:在master上必不可缺的binlog时间或然会扬弃,那样slave就无法张开复制,导致产生多少黄金年代致性难点

 
MySQL
Cluster:
MySQL
Cluster真正达成了高可用,不过利用的是NDB存款和储蓄引擎,并且SQL节点有单点故障难题。
 
半联合复制(5.5+)

  • 半同步复制大大收缩了“binlog events只存在故障master上”的题目。

  • 在交付时,保证起码二个slave(并非统筹的)采用到binlog,由此部分slave恐怕未有摄取到binlog。

全局专门的学问ID

  • 在二进制文件中增添全局专业ID(global transaction
    id)需求更正binlog格式,在5.1/5.5本子中不援救。

  • 在动用方面有广大方式能够直线全局工作ID,不过仍防止不了复杂度、性能、数据错失可能后生可畏致性的难点。

 
PXC:
PXC达成了服务高可用,数据同步时是出新复制。然而仅帮助InnoDB引擎,全部的表都要有主键。锁冲突、死锁难点相对比较多等等难题。
 
MHA的优势 1、故障切换快
在主从复制集群中,只要从库在复制上向来不延迟,MHA平时能够在数秒内落成故障切换。9-10秒内检查到master故障,能够选取在7-10秒关闭master以幸免现身裂脑,几秒钟内,将差距中继日志(relay
log)应用到新的master上,由此总的宕机时间一般为10-30秒。苏醒新的master后,MHA并行的东山复起其他的slave。即便在有数万台slave,也不会潜移暗化master的复原时间。

DeNA在当先1四17个MySQL(首要5.0/5.1本子)主从意况下使用了MHA。当mater故障后,MHA在4秒内就完了了故障切换。在价值观的积极向上/被动集群解决方案中,4秒内成功故障切换是十分小概的。
 
2、master故障不会导致数据不等同
当前段时间的master现身故障是,MHA自动识别slave之间对接日志(relay
log)的不一致,并选取到全数的slave中。那样有着的salve能够保持同步,只要持有的slave处于存活状态。和Semi-Synchronous
Replication一齐行使,(差不离)能够保险未有数量遗失。

3、没有供给校正当前的MySQL设置
MHA的宏图的主要条件之生机勃勃正是拼命三郎地质大学致易用。MHA职业在理念的MySQL版本5.0和后来版本的主从复制境遇中。和任何高可用消除方法比,MHA并没有必要更换MySQL的配置境遇。MHA适用于异步和半手拉手的主从复制。
 
初步/截至/晋级/降级/安装/卸载MHA无需改造(包扩运行/截止)MySQL复制。当必要升高MHA到新的版本,无需甘休MySQL,仅仅替换成新本子的MHA,然后重启MHA Manager就好了。
 
MHA运转在MySQL
5.0起首的原生版本上。一些其余的MySQL高可用实施方案须要一定的版本(例如MySQL集群、带全局专门的工作ID的MySQL等等),但并不独有为了master的高可用才迁移应用的。在大好些个气象下,已经布置了比较旧MySQL应用,并且不想单独为了兑现Master的高可用,花太多的时光迁移到差异的仓库储存引擎或更新的火线发行版。MHA职业的不外乎5.0/5.1/5.5的原生版本的MySQL上,所以并不要求迁移。

4、不须要增添大气的服务器
MHA由MHA
Manager和MHA Node组成。MHA
Node运营在须要故障切换/苏醒的MySQL服务器上,由此并不要求额外扩大服务器。MHA
Manager运营在一定的服务器上,因而须要追加生龙活虎台(达成高可用必要2台),可是MHA
Manager能够监督大量(以致上百台)单独的master,由此,并无需扩展大气的服务器。固然在黄金时代台slave上运转MHA
Manager也是能够的。综上,达成MHA并没用额外增添大气的服务。

5、无品质减弱
MHA适用与异步或半手拉手的MySQL复制。监察和控制master时,MHA仅仅是每间距几秒(私下认可是3秒)发送一个ping包,并不发送重查询。能够获取像原生MySQL复制相符快的个性。

6、适用于任何存款和储蓄引擎
MHA能够运行在只要MySQL复制运维的蕴藏引擎上,并不只限定于InnoDB,就算在不利迁移的价值观的MyISAM引擎情况,同样能够动用MHA。

 

源于为知笔记(Wiz)

Preface

 

    MasterHA is a tool which can be used
in MySQL HA architecture.I’m gonna implement it and have some tests
depend on it later.

 

Framework

 

Hostname IP Port Identity OS Version MySQL Version
zlm2 192.168.1.101 3306 master CentOS 7.0 5.7.21
zlm3 192.168.1.102 3306 slave/mha-manager CentOS 7.0 5.7.21
null 192.168.1.200 null vip null null

 

Procedure

 

Downlaod the rpm package of MasterHA using
below web link first:

https://code.google.com/archive/p/mysql-master-ha/downloads

MasterHA’s
wiki document.

https://github.com/yoshinorim/mha4mysql-manager/wiki

 

Configure
ssh authentication between zlm2 and zlm3 from each other.

 1 [root@zlm2 09:00:44 ~]
 2 #ssh-keygen -t rsa
 3 Generating public/private rsa key pair.
 4 Enter file in which to save the key (/root/.ssh/id_rsa): 
 5 Enter passphrase (empty for no passphrase): 
 6 Enter same passphrase again: 
 7 Your identification has been saved in /root/.ssh/id_rsa.
 8 Your public key has been saved in /root/.ssh/id_rsa.pub.
 9 The key fingerprint is:
10 48:36:15:af:d7:97:3c:94:f3:0e:bc:ea:34:f1:bc:43 root@zlm2
11 The key's randomart image is:
12 +--[ RSA 2048]----+
13 |        o.       |
14 |       . .     . |
15 |      +   .   +  |
16 |     o o . . + + |
17 |      . S . o B .|
18 |         .   =E= |
19 |            o.+ .|
20 |           . o.. |
21 |           .o .. |
22 +-----------------+
23 
24 [root@zlm2 09:03:04 ~]
25 #cd .ssh
26 
27 [root@zlm2 09:03:09 ~/.ssh]
28 #ls -l
29 total 12
30 -rw------- 1 root root 1675 Aug  2 09:02 id_rsa
31 -rw-r--r-- 1 root root  391 Aug  2 09:02 id_rsa.pub
32 -rw-r--r-- 1 root root  360 Jun 13 09:51 known_hosts
33 
34 [root@zlm2 09:03:18 ~/.ssh]
35 #cat id_rsa.pub >> authorized_keys
36 
37 [root@zlm2 09:07:51 ~/.ssh]
38 #scp ./* zlm3:~/.ssh/
39 root@zlm3's password: 
40 authorized_keys                                                                                                    100%  391     0.4KB/s   00:00    
41 id_rsa                                                                                                             100% 1675     1.6KB/s   00:00    
42 id_rsa.pub                                                                                                         100%  391     0.4KB/s   00:00    
43 known_hosts                                                                                                        100%  360     0.4KB/s   00:00
44 
45 [root@zlm2 09:08:22 ~/.ssh]
46 #ssh zlm3
47 Last login: Thu Aug  2 08:47:54 2018 from 192.168.1.1
48 Welcome to your Vagrant-built virtual machine.
49 
50 [root@zlm3 09:10:39 ~]
51 #
52 
53 [root@zlm3 09:11:13 ~/.ssh]
54 #ssh zlm2
55 The authenticity of host 'zlm2 (192.168.1.101)' can't be established.
56 ECDSA key fingerprint is 75:06:22:b7:e2:3b:46:88:51:97:c4:4f:27:20:21:26.
57 Are you sure you want to continue connecting (yes/no)? yes
58 Warning: Permanently added 'zlm2,192.168.1.101' (ECDSA) to the list of known hosts.
59 Last login: Thu Aug  2 03:29:04 2018 from 192.168.1.1
60 Welcome to your Vagrant-built virtual machine.
61 
62 [root@zlm2 09:11:22 ~]
63 #

 

**Configure a master-slave
replication.**

 

  1 [root@zlm2 09:18:30 ~]
  2 #scp db3306_20180802.sql zlm3:~/
  3 db3306_20180802.sql                                                                                                100% 8429KB   8.2MB/s   00:00
  4 
  5 [root@zlm3 09:19:55 ~]
  6 #mysql < db3306_20180802.sql 
  7 ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
  8 
  9 [root@zlm3 09:20:00 ~]
 10 #mysql
 11 Welcome to the MySQL monitor.  Commands end with ; or \g.
 12 Your MySQL connection id is 3
 13 Server version: 5.7.21-log MySQL Community Server (GPL)
 14 
 15 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 16 
 17 Oracle is a registered trademark of Oracle Corporation and/or its
 18 affiliates. Other names may be trademarks of their respective
 19 owners.
 20 
 21 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 22 
 23 (zlm@192.168.1.102 3306)[(none)]>reset master;
 24 Query OK, 0 rows affected (0.03 sec)
 25 
 26 (zlm@192.168.1.102 3306)[(none)]>exit
 27 Bye
 28 
 29 [root@zlm3 09:20:28 ~]
 30 #mysql < db3306_20180802.sql
 31 
 32 [root@zlm3 09:20:34 ~]
 33 #mysql
 34 Welcome to the MySQL monitor.  Commands end with ; or \g.
 35 Your MySQL connection id is 5
 36 Server version: 5.7.21-log MySQL Community Server (GPL)
 37 
 38 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 39 
 40 Oracle is a registered trademark of Oracle Corporation and/or its
 41 affiliates. Other names may be trademarks of their respective
 42 owners.
 43 
 44 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 45 
 46 (zlm@192.168.1.102 3306)[(none)]>change master to \
 47     -> master_host='192.168.1.101',\
 48     -> master_port=3306,\
 49     -> master_user='repl',\
 50     -> master_password='repl4slave',\
 51     -> master_auto_position=1;
 52 Query OK, 0 rows affected, 2 warnings (0.02 sec)
 53 
 54 (zlm@192.168.1.102 3306)[(none)]>start slave;
 55 Query OK, 0 rows affected (0.00 sec)
 56 
 57 (zlm@192.168.1.102 3306)[(none)]>show slave status\G
 58 *************************** 1. row ***************************
 59                Slave_IO_State: Waiting for master to send event
 60                   Master_Host: 192.168.1.101
 61                   Master_User: repl
 62                   Master_Port: 3306
 63                 Connect_Retry: 60
 64               Master_Log_File: mysql-bin.000048
 65           Read_Master_Log_Pos: 736
 66                Relay_Log_File: relay-bin.000002
 67                 Relay_Log_Pos: 398
 68         Relay_Master_Log_File: mysql-bin.000048
 69              Slave_IO_Running: Yes
 70             Slave_SQL_Running: Yes
 71               Replicate_Do_DB: 
 72           Replicate_Ignore_DB: 
 73            Replicate_Do_Table: 
 74        Replicate_Ignore_Table: 
 75       Replicate_Wild_Do_Table: 
 76   Replicate_Wild_Ignore_Table: 
 77                    Last_Errno: 0
 78                    Last_Error: 
 79                  Skip_Counter: 0
 80           Exec_Master_Log_Pos: 736
 81               Relay_Log_Space: 591
 82               Until_Condition: None
 83                Until_Log_File: 
 84                 Until_Log_Pos: 0
 85            Master_SSL_Allowed: No
 86            Master_SSL_CA_File: 
 87            Master_SSL_CA_Path: 
 88               Master_SSL_Cert: 
 89             Master_SSL_Cipher: 
 90                Master_SSL_Key: 
 91         Seconds_Behind_Master: 0
 92 Master_SSL_Verify_Server_Cert: No
 93                 Last_IO_Errno: 0
 94                 Last_IO_Error: 
 95                Last_SQL_Errno: 0
 96                Last_SQL_Error: 
 97   Replicate_Ignore_Server_Ids: 
 98              Master_Server_Id: 1013306
 99                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
100              Master_Info_File: mysql.slave_master_info
101                     SQL_Delay: 0
102           SQL_Remaining_Delay: NULL
103       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
104            Master_Retry_Count: 86400
105                   Master_Bind: 
106       Last_IO_Error_Timestamp: 
107      Last_SQL_Error_Timestamp: 
108                Master_SSL_Crl: 
109            Master_SSL_Crlpath: 
110            Retrieved_Gtid_Set: 
111             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730247
112                 Auto_Position: 1
113          Replicate_Rewrite_DB: 
114                  Channel_Name: 
115            Master_TLS_Version: 
116 1 row in set (0.00 sec)

 

Install dependency packge
first.

 1 [root@zlm2 09:32:28 ~]
 2 #yum install -y perl-Time-HiRes perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager
 3 
 4 [root@zlm2 09:33:56 ~]
 5 #ssh zlm3
 6 Last login: Thu Aug  2 09:10:39 2018 from 192.168.1.101
 7 Welcome to your Vagrant-built virtual machine.
 8 
 9 [root@zlm3 09:34:03 ~]
10 #yum install -y perl-Time-HiRes perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager

 

Install both manager and node rpm on
master and slave.

 1 [root@zlm2 09:39:08 ~]
 2 #rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm 
 3 error: Failed dependencies:
 4     mha4mysql-node >= 0.54 is needed by mha4mysql-manager-0.56-0.el6.noarch
 5     perl(MHA::BinlogManager) is needed by mha4mysql-manager-0.56-0.el6.noarch
 6     perl(MHA::NodeConst) is needed by mha4mysql-manager-0.56-0.el6.noarch
 7     perl(MHA::NodeUtil) is needed by mha4mysql-manager-0.56-0.el6.noarch
 8     perl(MHA::SlaveUtil) is needed by mha4mysql-manager-0.56-0.el6.noarch
 9 
10 [root@zlm2 09:39:16 ~]
11 #rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm 
12 Preparing...                          ################################# [100%]
13 Updating / installing...
14    1:mha4mysql-node-0.56-0.el6        ################################# [100%]
15 
16 [root@zlm2 09:39:48 ~]
17 #rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm 
18 Preparing...                          ################################# [100%]
19 Updating / installing...
20    1:mha4mysql-manager-0.56-0.el6     ################################# [100%]
21    
22 [root@zlm2 09:39:53 ~]
23 #ssh zlm3
24 Last login: Thu Aug  2 09:34:03 2018 from 192.168.1.101
25 Welcome to your Vagrant-built virtual machine.
26 
27 [root@zlm3 09:41:46 ~]
28 #rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
29 Preparing...                          ################################# [100%]
30 Updating / installing...
31    1:mha4mysql-node-0.56-0.el6        ################################# [100%]
32 
33 [root@zlm3 09:42:03 ~]
34 #rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
35 Preparing...                          ################################# [100%]
36 Updating / installing...
37    1:mha4mysql-manager-0.56-0.el6     ################################# [100%]   

 

Configure the conf files on each manager
node.

 1 [root@zlm2 09:48:22 ~]
 2 #mkdir -p /var/log/masterha/app1
 3 
 4 [root@zlm2 09:48:28 ~]
 5 #mkdir /etc/masterha
 6 
 7 [root@zlm2 09:48:35 ~]
 8 #cp /vagrant/masterha/* /etc/masterha/
 9 
10 [root@zlm2 09:50:02 ~]
11 #cd /etc/masterha/
12 
13 [root@zlm2 09:50:09 /etc/masterha]
14 #ll
15 total 36
16 -rwxr-xr-x 1 root root   614 Aug  2 09:50 app1.conf
17 -rwxr-xr-x 1 root root    55 Aug  2 09:50 drop_vip.sh
18 -rwxr-xr-x 1 root root    55 Aug  2 09:50 init_vip.sh
19 -rwxr-xr-x 1 root root   384 Aug  2 09:50 masterha_default.conf
20 -rwxr-xr-x 1 root root  4438 Aug  2 09:50 master_ip_failover
21 -rwxr-xr-x 1 root root 10526 Aug  2 09:50 master_ip_online_change
22 
23 [root@zlm2 09:51:46 /etc/masterha]
24 #cat masterha_default.conf 
25 [server default]
26 #log_level=debug
27 user=zlm
28 password=zlmzlm
29 
30 ssh_user=root
31 ssh_port=3306
32 
33 repl_user=repl
34 repl_password=repl4slave
35 
36 ping_interval=1
37 #shutdown_script=""
38 
39 master_ip_failover_script= /etc/masterha/master_ip_failover
40 master_ip_online_change_script= /etc/masterha/master_ip_online_change
41 
42 [root@zlm2 09:53:56 /etc/masterha]
43 #cat app1.conf 
44 [server default]
45 
46 manager_workdir = /var/log/masterha/app1
47 manager_log = /var/log/masterha/app1/app1.log
48 remote_workdir = /var/log/masterha/app1
49 
50 [server1]
51 hostname=192.168.1.101
52 master_binlog_dir = /data/mysql/mysql3306/logs
53 candidate_master = 1
54 check_repl_delay = 0
55 
56 [server2]
57 hostname=192.168.1.102
58 master_binlog_dir=/data/mysql/mysql3306/logs
59 candidate_master=1
60 check_repl_delay=0
61 
62 [root@zlm2 09:56:20 /etc/masterha]
63 #cat init_vip.sh 
64 vip="192.168.1.200/24"
65 /sbin/ip addr add $vip dev enp0s8
66 
67 [root@zlm2 09:56:23 /etc/masterha]
68 #cat drop_vip.sh 
69 vip="192.168.1.200/24"
70 /sbin/ip addr del $vip dev enp0s8
71 
72 [root@zlm2 09:57:27 /etc/masterha]
73 #ssh zlm3
74 Last login: Thu Aug  2 09:40:20 2018 from 192.168.1.1
75 Welcome to your Vagrant-built virtual machine.
76 
77 [root@zlm3 09:58:04 ~]
78 #mkdir -p /var/log/masterha/app1
79 
80 [root@zlm3 09:58:16 ~]
81 #mkdir /etc/masterha
82 
83 [root@zlm3 09:58:30 ~]
84 #scp zlm2:/etc/masterha/* /etc/masterha
85 app1.conf                                                                                                          100%  498     0.5KB/s   00:00    
86 drop_vip.sh                                                                                                        100%   57     0.1KB/s   00:00    
87 init_vip.sh                                                                                                        100%   57     0.1KB/s   00:00    
88 masterha_default.conf                                                                                              100%  387     0.4KB/s   00:00    
89 master_ip_failover                                                                                                 100% 4438     4.3KB/s   00:00    
90 master_ip_online_change                                                                                            100%   10KB  10.3KB/s   00:00

 

Check the masterha status on both
nodes..

 1 [root@zlm2 10:00:26 /etc/masterha]
 2 #masterha_
 3 masterha_check_repl       masterha_check_status     masterha_manager          masterha_master_switch    masterha_stop
 4 masterha_check_ssh        masterha_conf_host        masterha_master_monitor   masterha_secondary_check
 5 
 6 [root@zlm2 10:00:26 /etc/masterha]
 7 #masterha_check_status --conf=./app1.conf
 8 app1 is stopped(2:NOT_RUNNING).
 9 
10 [root@zlm2 10:01:45 /etc/masterha]
11 #masterha_check_ssh --conf=./app1.conf
12 Thu Aug  2 10:02:05 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
13 Thu Aug  2 10:02:05 2018 - [info] Reading application default configuration from ./app1.conf..
14 Thu Aug  2 10:02:05 2018 - [info] Reading server configuration from ./app1.conf..
15 Thu Aug  2 10:02:05 2018 - [info] Starting SSH connection tests..
16 Thu Aug  2 10:02:06 2018 - [debug] 
17 Thu Aug  2 10:02:05 2018 - [debug]  Connecting via SSH from root@192.168.1.101(192.168.1.101:22) to root@192.168.1.102(192.168.1.102:22)..
18 Warning: Permanently added '192.168.1.101' (ECDSA) to the list of known hosts.
19 Thu Aug  2 10:02:06 2018 - [debug]   ok.
20 Thu Aug  2 10:02:06 2018 - [debug] 
21 Thu Aug  2 10:02:06 2018 - [debug]  Connecting via SSH from root@192.168.1.102(192.168.1.102:22) to root@192.168.1.101(192.168.1.101:22)..
22 Thu Aug  2 10:02:06 2018 - [debug]   ok.
23 Thu Aug  2 10:02:06 2018 - [info] All SSH connection tests passed successfully.
24 
25 [root@zlm2 10:02:06 /etc/masterha]
26 #masterha_check_repl --conf=./app1.conf
27 Thu Aug  2 10:02:15 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
28 Thu Aug  2 10:02:15 2018 - [info] Reading application default configuration from ./app1.conf..
29 Thu Aug  2 10:02:15 2018 - [info] Reading server configuration from ./app1.conf..
30 Thu Aug  2 10:02:15 2018 - [info] MHA::MasterMonitor version 0.56.
31 Thu Aug  2 10:02:15 2018 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] Got MySQL error when connecting 192.168.1.101(192.168.1.101:3306) :1045:Access denied for user 'root'@'zlm2' (using password: NO), but this is not a MySQL crash. Check MySQL server settings.
32  at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
33 Thu Aug  2 10:02:16 2018 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] Got MySQL error when connecting 192.168.1.102(192.168.1.102:3306) :1045:Access denied for user 'root'@'zlm2' (using password: NO), but this is not a MySQL crash. Check MySQL server settings.
34  at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
35 Thu Aug  2 10:02:16 2018 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln309] Got fatal error, stopping operations
36 Thu Aug  2 10:02:16 2018 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations.  at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 326.
37 Thu Aug  2 10:02:16 2018 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
38 Thu Aug  2 10:02:16 2018 - [info] Got exit code 1 (Not master dead).
39 
40 MySQL Replication Health is NOT OK!
41 
42 [root@zlm2 10:57:47 /etc/masterha]
43 #masterha_check_repl --conf=./app1.conf --global_conf=./masterha_default.conf //The "--global_conf" option is indispensable.
44 Thu Aug  2 10:57:58 2018 - [info] Reading default configuration from /etc/masterha/masterha_default.conf..
45 Thu Aug  2 10:57:58 2018 - [info] Reading application default configuration from ./app1.conf..
46 Thu Aug  2 10:57:58 2018 - [info] Reading server configuration from ./app1.conf..
47 Thu Aug  2 10:57:58 2018 - [info] MHA::MasterMonitor version 0.56.
48 Thu Aug  2 10:57:59 2018 - [info] GTID failover mode = 1
49 Thu Aug  2 10:57:59 2018 - [info] Dead Servers:
50 Thu Aug  2 10:57:59 2018 - [info] Alive Servers:
51 Thu Aug  2 10:57:59 2018 - [info]   192.168.1.101(192.168.1.101:3306)
52 Thu Aug  2 10:57:59 2018 - [info]   192.168.1.102(192.168.1.102:3306)
53 Thu Aug  2 10:57:59 2018 - [info] Alive Slaves:
54 Thu Aug  2 10:57:59 2018 - [info]   192.168.1.102(192.168.1.102:3306)  Version=5.7.21-log (oldest major version between slaves) log-bin:enabled
55 Thu Aug  2 10:57:59 2018 - [info]     GTID ON
56 Thu Aug  2 10:57:59 2018 - [info]     Replicating from 192.168.1.101(192.168.1.101:3306)
57 Thu Aug  2 10:57:59 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
58 Thu Aug  2 10:57:59 2018 - [info] Current Alive Master: 192.168.1.101(192.168.1.101:3306)
59 Thu Aug  2 10:57:59 2018 - [info] Checking slave configurations..
60 Thu Aug  2 10:57:59 2018 - [info]  read_only=1 is not set on slave 192.168.1.102(192.168.1.102:3306).
61 Thu Aug  2 10:57:59 2018 - [info] Checking replication filtering settings..
62 Thu Aug  2 10:57:59 2018 - [info]  binlog_do_db= , binlog_ignore_db= 
63 Thu Aug  2 10:57:59 2018 - [info]  Replication filtering check ok.
64 Thu Aug  2 10:57:59 2018 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
65 Thu Aug  2 10:57:59 2018 - [info] Checking SSH publickey authentication settings on the current master..
66 ssh_exchange_identification: Connection closed by remote host
67 Thu Aug  2 10:57:59 2018 - [warning] HealthCheck: SSH to 192.168.1.101 is NOT reachable.
68 Thu Aug  2 10:57:59 2018 - [info] 
69 192.168.1.101(192.168.1.101:3306) (current master)
70  +--192.168.1.102(192.168.1.102:3306)
71 
72 Thu Aug  2 10:57:59 2018 - [info] Checking replication health on 192.168.1.102..
73 Thu Aug  2 10:57:59 2018 - [info]  ok.
74 Thu Aug  2 10:57:59 2018 - [info] Checking master_ip_failover_script status:
75 Thu Aug  2 10:57:59 2018 - [info]   /etc/masterha/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.1.101 --orig_master_ip=192.168.1.101 --orig_master_port=3306  --orig_master_ssh_port=3306
76 Thu Aug  2 10:57:59 2018 - [info]  OK.
77 Thu Aug  2 10:57:59 2018 - [warning] shutdown_script is not defined.
78 Thu Aug  2 10:57:59 2018 - [info] Got exit code 0 (Not master dead).
79 
80 MySQL Replication Health is OK.
81 
82 //Do these above checking operations on zlm3,too.

 

Start MasterHA-manager and check the
status.

 1 [root@zlm2 11:13:24 /etc/masterha]
 2 #masterha_manager --conf=./app1.conf --global_conf=./masterha_default.conf &
 3 [1] 5091
 4 
 5 [root@zlm2 11:13:55 /etc/masterha]
 6 #Thu Aug  2 11:13:56 2018 - [info] Reading default configuration from ./masterha_default.conf..
 7 Thu Aug  2 11:13:56 2018 - [info] Reading application default configuration from ./app1.conf..
 8 Thu Aug  2 11:13:56 2018 - [info] Reading server configuration from ./app1.conf..
 9 ssh_exchange_identification: Connection closed by remote host
10 ^C
11 
12 [root@zlm2 11:14:09 /etc/masterha]
13 #masterha_check_status --conf=./app1.conf
14 app1 (pid:5091) is running(0:PING_OK), master:192.168.1.101
15 
16 [root@zlm2 10:57:15 /etc/masterha]
17 #cd /var/log/masterha/app1
18 
19 [root@zlm2 11:16:17 /var/log/masterha/app1]
20 #ls -l
21 total 8
22 -rw-r--r-- 1 root root 3821 Aug  2 11:13 app1.log
23 -rw-r--r-- 1 root root   35 Aug  2 11:16 app1.master_status.health
24 
25 [root@zlm2 11:16:19 /var/log/masterha/app1]
26 #tail app1.log 
27  +--192.168.1.102(192.168.1.102:3306)
28 
29 Thu Aug  2 11:13:57 2018 - [info] Checking master_ip_failover_script status:
30 Thu Aug  2 11:13:57 2018 - [info]   /etc/masterha/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.1.101 --orig_master_ip=192.168.1.101 --orig_master_port=3306  --orig_master_ssh_port=3306
31 Thu Aug  2 11:13:57 2018 - [info]  OK.
32 Thu Aug  2 11:13:57 2018 - [warning] shutdown_script is not defined.
33 Thu Aug  2 11:13:57 2018 - [info] Set master ping interval 1 seconds.
34 Thu Aug  2 11:13:57 2018 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
35 Thu Aug  2 11:13:57 2018 - [info] Starting ping health check on 192.168.1.101(192.168.1.101:3306)..
36 Thu Aug  2 11:13:57 2018 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
37 
38 [root@zlm2 11:16:35 /var/log/masterha/app1]
39 #tail app1.master_status.health 
40 5091    0:PING_OK    master:192.168.1.101

 

留下评论

网站地图xml地图