MHA部署记录

一.MHA环境如下

角色                     ip地址          主机名          server_id                    类型
Monitor host            192.168.0.20    server01            -                      监控复制组
Master                  192.168.0.50    server02            1                      写入
Candicate master        192.168.0.60    server03            2                      读
Slave                   192.168.0.70    server04            3                      读

其中master对外提供写服务，备选master（实际的slave，主机名server03）提供读服务，slave也提供相关的读服务，一旦master宕机，将会把备选master提升为新的master，slave指向新的master

1、安装依赖包

yum install -y gcc ntpdate wget lrzsz vim net-tools openssh-clients*

2、安装epel源

yum install -y epel-release

3、安装组件

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

yum install perl-DBD-MySQL -y

2、在所有的节点安装mha node：

下载链接： 

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

wget https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/mysql-master-ha/mha4mysql-node-0.54.tar.gz

tar xf mha4mysql-node-0.54.tar.gz

cd mha4mysql-node-0.54

perl Makefile.PL

make && make install

报错一

解决办法：

root># yum install perl-ExtUtils-MakeMaker

报错二

解决办法：

root># yum install perl-CPAN -y

2.安装MHA Manager

MHA Manager中主要包括了几个管理员的命令行工具，例如master_manger，master_master_switch等。MHA Manger也依赖于perl模块，具体如下：

（1）安装MHA Node软件包之前需要安装依赖。我这里使用yum完成，没有epel源的可以使用上面提到的脚本（epel源安装也简单）。注意：在MHA Manager的主机也是需要安装MHA Node。

rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

yum install perl-DBD-MySQL -y


（2）安装MHA Manager。首先安装MHA Manger依赖的perl模块（我这里使用yum安装）：

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

安装MHA Manager软件包：
wget https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/mysql-master-ha/mha4mysql-manager-0.54.tar.gz

tar xf mha4mysql-manager-0.54.tar.gz

cd mha4mysql-manager-0.54

perl Makefile.PL

make && make install

复制相关脚本到/usr/local/bin目录

root># cp -rp /root/mha4mysql-manager-0.54/samples/scripts/* /usr/local/bin/

免key配置

root># ssh-copy-id 192.168.56.131
root># ssh-copy-id 192.168.56.132
root># ssh-copy-id 192.168.56.133

数据库配置

1）在server02上执行备份（192.168.56.131）

mysqldump --master-data=2 --single-transaction -R --triggers -A > all.sql

在server02上创建复制用户：

mysql> grant replication slave on *.* to 'repl'@'192.168.56.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

#修改root密码
mysql> grant all privileges on *.* to root@"%" identified by '123456';

1、在主库上查看偏移量
root># head -n 30 all.sql | grep 'CHANGE MASTER TO'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=214;



2、在2个从库上执行
CHANGE MASTER TO MASTER_HOST='192.168.56.131',MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=214;


3、检查主从是否一致
root># mysql -e 'show slave status\G' | egrep 'Slave_IO|Slave_SQL'
               Slave_IO_State: Waiting for master to send event
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

4、两台slave服务器设置read_only（从库对外提供读服务，只所以没有写进配置文件，是因为随时slave会提升为master）

server03<2018-08-14 17:25:23> /data
root># mysql -e 'set global read_only=1'

server04<2018-08-14 17:24:42> /data
root># mysql -e 'set global read_only=1'

5、创建监控用户（在master上执行，也就是192.168.56.131）：
mysql> grant all privileges on *.* to 'mha'@'192.168.56.%' identified  by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush  privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>

6、在master主机上查看master的状态。

mysql> SHOW SLAVE HOSTS;  
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         3 |      | 3306 |         1 | b9543701-a052-11e8-b2f6-000c29c77f26 |
|         2 |      | 3306 |         1 | bcdbef2c-a052-11e8-b2f6-000c293612be |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)

到这里整个集群环境已经搭建完毕，剩下的就是配置MHA软件了。

5.配置MHA

1）创建MHA的工作目录，并且创建相关配置文件（在软件包解压后的目录里面有样例配置文件）。

server01<2018-08-14 17:30:36> ~
root># mkdir -p /etc/masterha

root># cp /root/mha4mysql-manager-0.54/samples/conf/app1.cnf /etc/masterha/

###使用proxysql的mha配置文件
root># cat /etc/masterha/proxy_app_default.cnf 
[server default]
manager_workdir=/etc/masterha/app1
manager_log=/etc/masterha/app1/manager_proxy.log
ssh_user=root
ssh_port=22
user=mha    
password=123456
ping_type=connect
repl_user=repl
repl_password=123456
master_binlog_dir=/var/lib/mysql/
ping_interval=3
#master failover时执行,不配置vip时不用配
master_ip_failover_script=/etc/masterha/app1/scripts/master_ip_failover_proxy
#shutdown_script=/etc/masterha/power_manager
report_script=/etc/masterha/app1/scripts/send_report_proxy  
master_ip_online_change_script=/etc/masterha/app1/scripts/master_ip_online_change_proxy
#secondary_check_script=masterha_secondary_check -s 192.168.56.130
[server1]
hostname=192.168.56.131
port=3306
candidate_master=1
#check_repl_delay=0
 
[server2]
hostname=192.168.56.132
port=3306
candidate_master=1  
#check_repl_delay=0
 
[server3]
hostname=192.168.56.133
port=3306
candidate_master=1  
#check_repl_delay=0


###使用vip的mha配置文件
root># cat vip_app_default.cnf 
[server default]
manager_workdir=/etc/masterha/app1
manager_log=/etc/masterha/app1/manager_vip.log
ssh_user=root
ssh_port=22
user=mha    
password=123456
ping_type=connect
repl_user=repl
repl_password=123456
master_binlog_dir=/var/lib/mysql/
ping_interval=3
#master failover时执行,不配置vip时不用配
master_ip_failover_script=/etc/masterha/app1/scripts/master_ip_failover
#shutdown_script=/etc/masterha/power_manager
report_script=/etc/masterha/app1/scripts/send_report
master_ip_online_change_script=/etc/masterha/app1/scripts/master_ip_online_change
#secondary_check_script=masterha_secondary_check -s 192.168.56.130
[server1]
hostname=192.168.56.131
port=3306
candidate_master=1
#check_repl_delay=0
 
[server2]
hostname=192.168.56.132
port=3306
candidate_master=1  
#check_repl_delay=0
 
[server3]
hostname=192.168.56.133
port=3306
candidate_master=1  
#check_repl_delay=0