搭建MYSQL-MHA环境

准备工作

1. 4台机器（可以是虚拟机）

一台安装centos6.X（管理机192.168.2.5），其余三台安装centos7.X（一台主master192.168.2.6，一台主备backup192.168.2.7，一台从slave192.168.2.8）

2. 配置epel源（配置该源时检测是否可连接）：

[root@localhost ~]# rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

3. 建立ssh无密码登录环境（4台机器均需要）

[root@localhost ~]# ssh-keygen -t rsa
[root@localhost ~]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.2.5
[root@localhost ~]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.2.6
[root@localhost ~]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.2.7
[root@localhost ~]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.2.8

安装mysql

1. 安装mysql（除了管理机）

这里安装的是MariaDB

2. 修改mysql配置

• master机器

[root@localhost ~]# vi /etc/my.cnf.d/server.cnf
[mysqld]
server_id = 1
character-set-server = utf8
lower_case_table_names = 1
log-bin = binlog
log-slave-updates
[root@localhost ~]# service mysqld restart

• backup机器

[root@localhost ~]# vi /etc/my.cnf.d/server.cnf
[mysqld]
server_id = 2
character-set-server = utf8
lower_case_table_names = 1
log-bin = binlog
log-slave-updates
[root@localhost ~]# service mysqld restart

• slave机器

[root@localhost ~]# vi /etc/my.cnf.d/server.cnf
[mysqld]
server_id = 3
character-set-server = utf8
lower_case_table_names = 1
log-bin = binlog
log-slave-updates
read_only = 1
[root@localhost ~]# service mysqld restart

3. 登陆mysql

• master机器

[root@localhost ~]# mysql -uroot -p123456
MariaDB [mysql]> CREATE USER 'weimo'@'%' IDENTIFIED BY 'weimo';
MariaDB [mysql]> grant all privileges  ON *.* TO 'weimo'@'%' identified by "weimo";
MariaDB [mysql]> flush privileges;
[root@localhost ~]# mysqldump -uroot -p123456 -A -x > /tmp/full.sql
[root@localhost ~]# scp /tmp/full.sql root@192.168.2.7:/tmp/
[root@localhost ~]# scp /tmp/full.sql root@192.168.2.8:/tmp/
MariaDB [mysql]> show master status;

• backup机器、slave机器

[root@localhost ~]# mysql -uroot -p123456
MariaDB [mysql]> change master to master_host='192.168.2.6',master_port=3306,master_user='weimo',master_password='weimo',master_log_file='binlog.000003',master_log_pos=995;
MariaDB [mysql]> start slave;
MariaDB [mysql]> show slave status \G
只要看到Slave_IO_Running Slave_SQL_Running都为yes就可以了。

4. 赋权

• 三台机器

MariaDB [mysql]> grant all privileges  ON *.* TO 'weimo'@'%' identified by "weimo";
MariaDB [mysql]> FLUSH PRIVILEGES;

安装mha

backup机器、slave机器

[root@localhost ~]# yum install perl-DBD-MySQL
[root@localhost ~]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm 
（需要把安装包传上去，如安装时有报错依赖，则需要对应进行yum把依赖装上）

master机器

[root@localhost ~]# yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager
[root@localhost ~]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm 
（需要把安装包传上去，如安装时有报错依赖，则需要对应进行yum把依赖装上）

新建配置文件
[root@localhost ~]# vi /etc/mha/mha.conf
[server default]
user=weimo
password=weimo
ssh_user=root
manager_log=/usr/local/mha/manager.log
# working directory on the manager
manager_workdir=/usr/local/mha
# working directory on MySQL servers
remote_workdir=/usr/local/mha
repl_user=weimo
repl_password=weimo

[server1]
hostname=192.168.2.6

[server2]
hostname=192.168.2.7
candidate_master=1

[server3]
hostname=192.168.2.8
no_master=1

检查下SSH公钥免密码登录，结果都OK就行
[root@localhost ~]# masterha_check_ssh --conf=/etc/mha/mha.conf

再检查下MySQL复制，最后提示MySQL Replication Health is OK.就行
masterha_check_repl --conf=/etc/mha/mha.conf

后台开启HA进程
nohup masterha_manager --conf=/etc/mha/mha.conf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/manager.log 2>&1 &

配置keepalived

1. 通过yum安装

[root@localhost ~]# yum -y install keepalived

2. 修改配置文件

• master机器

[root@localhost ~]# vi /etc/keepalived/keepalived.conf
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state BACKUP
    nopreempt
    interface eno16780032
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.2.10
    }
}

• backup机器

[root@localhost ~]# vi /etc/keepalived/keepalived.conf
global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state BACKUP
    nopreempt
    interface eno16780032
    virtual_router_id 51
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.2.10
    }
}

• 区别与注释

区别：priority（优先级）不同
注释：
    state：
        状态用的都是BACKUP，取值还有MASTER，区别在于重新连上后，MASTER会抢占vip，而BACKUP不会，直到别的VIP宕机后才会变成VIP
    interface：
        网卡名称
    virtual_router_id：
        这个id需要两个keepalived保持一致，并且同一个局域网内，id不能相同。
    virtual_ipaddress：
        这个就是虚拟ip，可以配置多个，需要保持一致

3. 启动keepalived

[root@localhost ~]# service keepalived start
注意：需要在master机器上启动成功后，再去backup机器上启动。否则会造成操作的不是master数据库，从而导致集群无法保证正常工作，数据不一致，主从同步失败。

参考文档

• 技术实战：基于 MHA 方式实现 MySQL 的高可用
• MySQL高可用架构之MHA
• Slave_SQL_Running: No mysql同步故障解决方法