MySQL-MHA 搭建文档

MHA软件介绍

• MHA是由日本Mysql专家用Perl写的一套Mysql故障切换方案以保障数据库的高可用性，它的功能是能在0一30s之内实现主Mysql故障转移（failover)，凡MHA故障转移叮以很好的帮我们解决从库数据的一致性问题．同时最大化挽回故障发生后的数据．MHA里有两个角色一个是node节点一个是manager节点，要实现这个MHA，必须最少要二台数据库服务器，一主多备，即一台充当master，一台充当master的备份机，另外一台是从属机，这里实验为了实现更好的效果使用三台机器，需要说明的是一旦主服务器宕机．备份机即开始充当master提供服务，如果上服务器上线也不会再成为master了．因为如果这样数据库的一致性就被改变了。
• MHA监控复制架构的主服务器，一旦检侧到主服务器故障．就会自动进行故障转移。即使有些从服务器没有收到最新的relay log自动从最新的从服务器上识别差异的relay log并把这些日志应用到其他从服务器上，因此所有的从服务器保持一致性了 , 期间通常在几秒内完成故障转移，9一12秒可以检测出主服务器故障，7一10秒内关闭故障的主服务器以避免脑裂，几秒中内应用差异的relay10到新的主上， 整个过程在0-30s 内完成。

搭建环境

mha-master：4核  2G内存  Redhat 6.5  IP:192.168.86.148 主机名:mha-master 安装MySQL 5.6.36

mha-manager ：4核  2G内存  Redhat 6.5  IP:192.168.86.149 主机名:mha-manager 安装MySQL 5.6.36

mha-bak ：4核  2G内存  Redhat 6.5  IP:192.168.86.150 主机名:mha-bak 安装MySQL 5.6.36

搭建步骤

一、搭建主从（一主三从）

主库：mha-master

从库：mha-manager、mha-bak

注意：要在mha-master 端新建复制用户并授予复制权限

1

mysql> grant replication slave,replication client on *.* to 'repl'@'192.168.86.%' identified by '123456';

二、安装MHA及基本环境配置

三台机器都需执行1-2步骤

1、在所有的节点上安装MHA node所需的perl模块（DBD-mysql）以及MHA node节点

1

$ yum install perl-DBD-MySQL -y

2、在所有的节点上安装MHA的node数据节点

1

$ rpm -ivh mha4mysql-node-0.54-0.el6.noarch.rpm

2

Preparing...                ########################################### [100%]

3

   1:mha4mysql-node         ########################################### [100%]

安装完成后，看/usr/bin目录下是否生成以下脚本文件：

1

$ ll /usr/bin/ | grep logs

2

-rwxr-xr-x. 1 root root       15977 Dec  1  2012 apply_diff_relay_logs

3

-rwxr-xr-x. 1 root root        4807 Dec  1  2012 filter_mysqlbinlog

4

-rwxr-xr-x. 1 root root        7401 Dec  1  2012 purge_relay_logs

5

-rwxr-xr-x. 1 root root        7263 Dec  1  2012 save_binary_logs

3、在manager节点（mha-manager）上安装MHA manager以及依赖包

1

$ ls

2

perl-Config-Tiny-2.12-7.1.el6.noarch.rpm

3

perl-Email-Date-Format-1.002-5.el6.noarch.rpm

4

perl-Log-Dispatch-2.27-1.el6.noarch.rpm

5

perl-Mail-Sender-0.8.16-3.el6.noarch.rpm

6

perl-Mail-Sendmail-0.79-12.el6.noarch.rpm

7

perl-MailTools-2.04-4.el6.noarch.rpm

8

perl-MIME-Lite-3.027-2.el6.noarch.rpm

9

perl-MIME-Lite-HTML-1.23-2.el6.noarch.rpm

10

perl-MIME-Types-1.28-2.el6.noarch.rpm

11

perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm

12

perl-Params-Validate-0.92-3.el6.x86_64.rpm

13

perl-TimeDate-1.16-13.el6.noarch.rpm

1

$ yum localinstall perl-* -y

1

$ rpm -ivh mha4mysql-manager-0.55-0.el6.noarch.rpm 

2

Preparing...                ########################################### [100%]

3

   1:mha4mysql-manager      ########################################### [100%]

安装成功后，生成以下脚本文件：

1

$ ls /usr/bin/ | grep "masterha_*"

2

masterha_check_repl

3

masterha_check_ssh

4

masterha_check_status

5

masterha_conf_host

6

masterha_manager

7

masterha_master_monitor

8

masterha_master_switch

9

masterha_secondary_check

10

masterha_stop

4、配置主机名字（三台机器都要配置）

1

$ vim /etc/hosts

2

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

3

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

4

192.168.86.148 mha-master

5

192.168.86.149 mha-manager

6

192.168.86.150 mha-bak

5、配置SSH登录无密码验证

方法一：

在mha-master上：

①生成rsa秘钥

1

$ ssh-keygen -t rsa

2

Generating public/private rsa key pair.

3

Enter file in which to save the key (/root/.ssh/id_rsa): 

4

Created directory '/root/.ssh'.

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

5a:26:2d:d2:63:48:52:d1:0f:54:b0:fc:8c:6e:93:9e root@mha-master

11

The key's randomart image is:

12

+--[ RSA 2048]----+

13

|    o+oo.        |

14

|   . .o.         |

15

|  . . oo         |

16

|   o o =.        |

17

|    o B S        |

18

|     + O         |

19

|      *          |

20

|     o o         |

21

|      E          |

22

+-----------------+

②将生成的公钥追加到认证文件中

1

$ cd /root/.ssh/

2

$ cat id_rsa.pub >> authorized_keys

③修改ssh文件夹和公钥的权限

chmod 700 /root/.ssh/

chmod 600 /root/.ssh/authorized_keys

④将公钥传输到mha-manager和mha-bak上

1

$ ssh-copy-id -i id_rsa.pub root@192.168.86.149

2

The authenticity of host '192.168.86.149 (192.168.84.149)' can't be established.

3

RSA key fingerprint is 22:00:37:a2:be:c4:98:12:72:57:d4:74:bb:07:02:50.

4

Are you sure you want to continue connecting (yes/no)? yes

5

Warning: Permanently added '192.168.86.149' (RSA) to the list of known hosts.

6

root@192.168.86.149's password: 

7

Now try logging into the machine, with "ssh 'root@192.168.86.149'", and check in:

8

9

  .ssh/authorized_keys

10

11

to make sure we haven't added extra keys that you weren't expecting.

1

 ssh-copy-id -i id_rsa.pub root@192.168.86.150

2

The authenticity of host '192.168.86.150 (192.168.86.150)' can't be established.

3

RSA key fingerprint is 22:00:37:a2:be:c4:98:12:72:57:d4:74:bb:07:02:50.

4

Are you sure you want to continue connecting (yes/no)? yes

5

Warning: Permanently added '192.168.86.150' (RSA) to the list of known hosts.

6

root@192.168.86.150's password: 

7

Now try logging into the machine, with "ssh 'root@192.168.86.150'", and check in:

8