MHA测试搭建

MHA搭建

使用
percona 集群 数据库 bj02-mpxc-01 bj02-mpxc-02 bj02-mpxc-03

                192.168.1.75   192.168.1.76  192.168.1.77

更新为

mha 集群 bj02-mha-01 bj02-mha-02 bj02-mha-03

                192.168.1.75   192.168.1.76  192.168.1.77

实施流程：

相互加秘钥

统一hosts文件：

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.1.75  bj02-mha-01  mha1
192.168.1.76  bj02-mha-02  mha2
192.168.1.77  bj02-mha-03  mha3

添加yum源：

cat /etc/yum.repos.d/MariaDB.repo 
# MariaDB 10.1 CentOS repository list - created 2016-08-29 03:01 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

安装：

yum install MariaDB-server MariaDB-client -y

service mysql start

chkconfig mysql on

主从配置：

主：mha1
从：mha2、mha3

三台执行：

    create database mha;
    grant all privileges on *.* to mysql@'%'  IDENTIFIED BY 'mysql';
    flush privileges;


三台配置：

    [root@bj02-mha-01 ~]# cat /etc/my.cnf

    [mysqld]
    server-id=1
    log-bin=mysql-bin 
    binlog-do-db=mha


    [root@bj02-mha-02 ~]# cat /etc/my.cnf

    [mysqld]
    server-id=2
    log-bin=mysql-bin
    binlog-do-db=mha


    [root@bj02-mha-03 ~]# cat /etc/my.cnf

    [mysqld]
    server-id=3
    log-bin=mysql-bin
    binlog-do-db=mha

三台重启mysql


主：

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 313
    Binlog_Do_DB: mha
Binlog_Ignore_DB: 
1 row in set (0.00 sec)


从：

MariaDB [(none)]> change master to master_host='mha1',master_user='mysql',master_password='mysql',master_log_file='mysql-bin.000001',master_log_pos=313;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mha1
                  Master_User: mysql
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 313
               Relay_Log_File: bj02-mha-02-relay-bin.000002
                Relay_Log_Pos: 537
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes                  #此处必须为yes
            Slave_SQL_Running: Yes                  #此处必须为yes

            ...

测试：
    主库操作：
        MariaDB [(none)]> use mha;
        MariaDB [mysqltest]> create table user(id int(5),name char(10));
        MariaDB [mysqltest]> insert into user values (00001,'zhangsan');
    两个从库查看：
        MariaDB [(none)]> use mha;

        MariaDB [mha]> select * from user;
        +------+----------+
        | id   | name     |
        +------+----------+
        |    1 | zhangsan |
        +------+----------+
        1 row in set (0.00 sec)

部署MHANode

# 在所有运行MySQL服务的服务器上运行MHA Node，无论是master还是slave。
# 由于MHA　Manager需要MHA　Node，因此在运行MHA　Manager的服务器上也需要安装MHA　Node。
# 当然也可以在任意一个slave上运行MHA　Manager。
# 因为部署步骤相同，所以就列出一个安装步骤（在mha-manager服务器上）

在三台机器上安装 MHA Node：

    mkdir /softs

    yum install epel-release
    包 epel-release-6-8.noarch 已安装并且是最新版本

    yum install perl-DBD-MySQL perl-DBI cpan -y

    cd /softs/
    git clone https://github.com/kevin-hao/mha-node.git

    cd mha-node/
    perl Makefile.PL
    make && make install 
    cd

部署MHAManager

# MHA Manager仅运行在作为manager的服务器上。当然也可以部署在其中任意一台slave上。

在slave mha2上安装 MHAManager：

    yum install perl \
    perl-Config-Tiny \
    perl-Email-Date-Forma \
    perl-Log-Dispatch \
    perl-MIME-Lite \
    perl-MIME-Types \
    perl-Mail-Sender \
    perl-Mail-Sendmail \
    perl-MailTools \
    perl-Parallel-ForkManager \
    perl-Params-Validate \
    perl-Time-HiRes \
    perl-TimeDate -y

    #同上：yum install perl perl-Config-Tiny perl-Email-Date-Format perl-Log-Dispatch perl-MIME-Lite perl-MIME-Types perl-Mail-Sender perl-Mail-Sendmail perl-MailTools perl-Parallel-ForkManager perl-Params-Validate perl-Time-HiRes perl-TimeDate -y


    cd /softs/
    git clone https://github.com/kevin-hao/mha-manager.git

    cd mha-manager/
    perl Makefile.PL
    make && make install 
    cd

    规范mha目录：
        mkdir -p /application/mha/conf
        cp /softs/mha-manager/samples/conf/* /application/mha/conf/

    配置app1.cnf：
        mkdir -p /application/mha/workstatus/app1
        cd /application/mha/conf/
        cp app1.cnf app1.cnf.ori

        cat app1.cnf

            [server default]

            port=3306

            user=mysql          # mysql管理用户名
            password=mysql
            repl_user=mysql     # mysql复制帐号
            repl_password=mysql

            remote_workdir=/application/mha/workstatus/app1
            master_binlog_dir=/var/lib/mysql

            manager_workdir=/application/mha/workstatus/app1
            manager_log=/application/mha/logs/app1.log

            [server1]
            hostname=mha1

            [server2]
            hostname=mha2
            candidate_master=1  #master机宕掉后,优先启用这台作为新master

            [server3]
            hostname=mha3
            no_master=1         #设置no_master=1使主机不能成为新master

    配置全局配置文件:

        cat masterha_default.cnf

            [server default]

            log-level=debug
            check_repl_delay=1
            check_repl_filter=1
            ping_interval=3
            ping_type=connect

    检查配置:
        操作在mha-manager上进行

        检查ssh连接性：

            由于Manager创建在mha2上，需要将 mha2 的公钥放在自己的authorized_keys文件中

            masterha_check_ssh --conf=/application/mha/conf/app1.cnf

            看到 All SSH connection tests passed successfully. 即可

        检查主从复制状态：
            masterha_check_repl --conf=/application/mha/conf/app1.cnf

            看到 MySQL Replication Health is OK. 即可

    启动manager：

        mkdir /application/mha/logs/

        masterha_manager --conf=/application/mha/conf/app1.cnf > /dev/null 2>&1 &

    检查manager状态：

        masterha_check_status --conf=/application/mha/conf/app1.cnf

        app1 (pid:24897) is running(0:PING_OK), master:mha1

master故障切换测试

mha1：
    service mysql stop

mha2：
    从原来的：
        MariaDB [(none)]> show slave status\G
        *************************** 1. row ***************************
                       Slave_IO_State: Waiting for master to send event
                          Master_Host: mha1
    变为：
        MariaDB [(none)]> show slave status\G
        Empty set (0.00 sec)

        MariaDB [(none)]> show master status\G
        *************************** 1. row ***************************
                    File: mysql-bin.000001
                Position: 313
            Binlog_Do_DB: mha
        Binlog_Ignore_DB: 
        1 row in set (0.00 sec)

    Manager日志：
        Master failover to mha2(192.168.1.76:3306) completed successfully.

mha3：
    MariaDB [(none)]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: mha2