master_haproxy:

#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
    log         127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon

    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats

#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
    mode                    tcp
    log                     global
    option                  dontlognull
    option                  redispatch
    retries                 3
    timeout queue           1m
    timeout connect         10s
    timeout client          10000m
    timeout server          10000m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 3000
listen db_mycat *:3307
    option  tcpka
    option  tcplog
    balance leastconn
    server  mycat1 192.168.10.220:8066 check inter 3000 rise 1 maxconn 1000 fall 3
    server  mycat2 192.168.10.221:8066 check inter 3000 rise 1 maxconn 1000 fall 3


/////////////////////////////////////////////////////////////////////


slave_haproxy:

global
    # to have these messages end up in /var/log/haproxy.log you will
    # need to:
    #
    # 1) configure syslog to accept network log events.  This is done
    #    by adding the '-r' option to the SYSLOGD_OPTIONS in
    #    /etc/sysconfig/syslog
    #
    # 2) configure local2 events to go to the /var/log/haproxy.log
    #   file. A line like the following can be added to
    #   /etc/sysconfig/syslog
    #
    #    local2.*                       /var/log/haproxy.log
    #
    log         127.0.0.1 local2

    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon

    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats

#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
    mode                    tcp
    log                     global
    option                  dontlognull
    option                  redispatch
    retries                 3
    timeout queue           1m
    timeout connect         10s
    timeout client          10000m
    timeout server          10000m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 3000

#---------------------------------------------------------------------
# main frontend which proxys to the backends
#---------------------------------------------------------------------
#frontend  main *:5000
#
#    stats uri /haproxy
#    acl url_static       path_beg       -i /static /images /javascript /stylesheets
#    acl url_static       path_end       -i .jpg .gif .png .css .js
#
#    use_backend static          if url_static
#    default_backend             app

#---------------------------------------------------------------------
# static backend for serving up images, stylesheets and such
#---------------------------------------------------------------------
#backend static
#    balance     roundrobin
#    server      static 127.0.0.1:4331 check

#---------------------------------------------------------------------
# round robin balancing between the various backends
#---------------------------------------------------------------------
#backend app
#    balance     roundrobin
#    server  app1 127.0.0.1:5001 check
#    server  app2 127.0.0.1:5002 check
#    server  app3 127.0.0.1:5003 check
#    server  app4 127.0.0.1:5004 check

listen db_mycat *:3307
    option  tcpka
    option  tcplog
    balance leastconn
    server  mycat1 192.168.10.220:8066 check inter 3000 rise 1 maxconn 1000 fall 3
    server  mycat2 192.168.10.221:8066 check inter 3000 rise 1 maxconn 1000 fall 3



#####################################################################


master_keepalived:


onfiguration File for keepalived

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 dbvip_1
}

vrrp_instance VI_1 {
    state BACKUP
    nopreempt
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 2
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.10.226/24 brd 192.168.10.255 dev eth0 label eth0:0
    }
    track_script { 
       chk_haproxy weight=0    # +2 if process is present
    }
}


/////////////////////////////////////////////////////////////////////

slave_keepalived:

! Configuration File for keepalived

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 dbvip_2
}

vrrp_instance VI_1 {
    state BACKUP
    nopreempt
    interface eth0
    virtual_router_id 51
    priority 90
    advert_int 2
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.10.226/24 brd 192.168.10.255 dev eth0 label eth0:0
    }
    track_script { 
       chk_haproxy weight=0    # +2 if process is present
    }
}



#####################################################################


master_mycat:

## schema.xml ##

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" >
    </schema>
    <dataNode name="dn1" dataHost="localhost1" database="s6000" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.10.227:3306" user="admin"
                   password="123qqq...A">
            <!-- can have multi read hosts -->
            <readHost host="hostS1" url="192.168.10.220:3306" user="test" password="123qqq...A" />
            <readHost host="hostS2" url="192.168.10.221:3306" user="test" password="123qqq...A" />
        </writeHost>
        <!-- <writeHost host="hostS1" url="localhost:3316" user="root"
                   password="123456" /> -->
        <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
    </dataHost>
</mycat:schema>


## server.xml ##

...
...
    </system>

    <!-- 全局SQL防火墙设置 -->
    <!-- 
    <firewall> 
       <whitehost>
          <host host="127.0.0.1" user="mycat"/>
          <host host="127.0.0.2" user="mycat"/>
       </whitehost>
       <blacklist check="false">
       </blacklist>
    </firewall>
    -->

    <user name="admin">
        <property name="password">123qqq...A</property>
        <property name="schemas">TESTDB</property>

        <!-- 表级 DML 权限设置 -->
        <!--        
        <privileges check="false">
            <schema name="TESTDB" dml="0110" >
                <table name="tb01" dml="0000"></table>
                <table name="tb02" dml="1111"></table>
            </schema>
        </privileges>       
         -->
    </user>

    <user name="user">
        <property name="password">123qqq...A</property>
        <property name="schemas">TESTDB</property>
        <property name="readOnly">true</property>
    </user>

</mycat:server>


/////////////////////////////////////////////////////////////////////

slave_mycat:

## schema.xml ##

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" >
    </schema>
    <dataNode name="dn1" dataHost="localhost1" database="s6000" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.10.227:3306" user="admin"
                   password="123qqq...A">
            <!-- can have multi read hosts -->
            <readHost host="hostS1" url="192.168.10.220:3306" user="test" password="123qqq...A" />
            <readHost host="hostS2" url="192.168.10.221:3306" user="test" password="123qqq...A" />
        </writeHost>
        <!-- <writeHost host="hostS1" url="localhost:3316" user="root"
                   password="123456" /> -->
        <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
    </dataHost>
</mycat:schema>


## server.xml ##

...
...
    </system>

    <!-- 全局SQL防火墙设置 -->
    <!-- 
    <firewall> 
       <whitehost>
          <host host="127.0.0.1" user="mycat"/>
          <host host="127.0.0.2" user="mycat"/>
       </whitehost>
       <blacklist check="false">
       </blacklist>
    </firewall>
    -->

    <user name="admin">
        <property name="password">123qqq...A</property>
        <property name="schemas">TESTDB</property>

        <!-- 表级 DML 权限设置 -->
        <!--        
        <privileges check="false">
            <schema name="TESTDB" dml="0110" >
                <table name="tb01" dml="0000"></table>
                <table name="tb02" dml="1111"></table>
            </schema>
        </privileges>       
         -->
    </user>

    <user name="user">
        <property name="password">123qqq...A</property>
        <property name="schemas">TESTDB</property>
        <property name="readOnly">true</property>
    </user>

</mycat:server>


#####################################################################


master_mha_node:

## shell command ##
yum -y perl-DBD-mysql   perl-DBI   perl-JSON \
perl-Config-Tiny-2.14-7.el7.noarch.rpm \
perl-Email-Date-Format-1.002-15.el7.noarch.rpm \
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm \
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm \
perl-Mail-Sendmail-0.79-21.el7.art.noarch.rpm \
perl-MIME-Lite-3.030-1.el7.noarch.rpm  \
perl-MIME-Types-1.38-2.el7.noarch.rpm  \
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm 

or # yum -y install  perl-Compress-Raw-Bzip2.x86_64 perl-Compress-Raw-Zlib.x86_64 perl-DBD-MySQL.x86_64  perl-DBI.x86_64  perl-Data-Dumper.x86_64   perl-IO-Compress.noarch  perl-Net-Daemon.noarch  perl-PlRPC.noarch

rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm


master_mha_manager:


## shell command ##
 yum -y  install perl-ExtUtils-*   perl-CPAN-*
###yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
tar -zxf mha4mysql-manager-0.56.tar.gz
cd mha4mysql-manager-0.56/
perl  Makefile.PL
make
make install

#生成秘钥,主_备互相免密登陆(注:主节点还需要将自己的秘钥考到本机,否则测试失败)

cp bin/* /usr/local/bin/

mkdir /etc/mha_manager
cp samples/conf/app1.cnf  /etc/mha_manager

## /etc/mha_manager/app1.cnf ##

[server default]
manager_workdir=/etc/mha_manager
manager_log=/etc/mha_manager/manager.log
master_ip_failover_script=/usr/local/bin/master_ip_failover


ssh_user=root
ssh_port=22
repl_user=repluser
repl_password=123qqq...A
user=admin
password=123qqq...A


[server1]
hostname=192.168.10.220
port=3306
#candidate_master=1


[server2]
hostname=192.168.10.221
port=3306
candidate_master=1


## mysql主从做好后测试 ##

cd /usr/local/bin/

## 验证ssh 免密登陆数据节点主机 ##
masterha_check_ssh  --conf=/etc/mha_manager/app1.cnf

## 验证数据节点的主从同步配置(先把自动failover时候的切换脚本注释掉) ##
masterha_check_repl --conf=/etc/mha_manager/app1.cnf

## 启动管理服务MHA_Manager  ##

masterha_manager --conf=/etc/mha_manager/app1.cnf \  
 --remove_dead_master_conf --ignore_last_failover

注:
--remove_dead_master_conf //删除宕机主库配置
--ignore_last_failover //忽略xxx.health文件


## 查看状态(另开一个终端) ##
masterha_check_status  --conf=/etc/mha_manager/app1.cnf

## 停止服务 ##
masterha_stop  --conf=/etc/mha_manager/app1.cnf


##_测试故障转移_##

ifconfig eth0:1 192.168.4.100/24

## 修改 master_ip_failover 脚本 ##


 ...
 28 my (
 29   $command,        $ssh_user,         $orig_master_host,
 30   $orig_master_ip, $orig_master_port, $new_master_host,
 31   $new_master_ip,  $new_master_port,  $new_master_user,
 32   $new_master_password
 33 );
 34 
 35 my $vip = '192.168.1.150/24';  # Virtual IP 
 36 my $key = "1";
 37 my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
 38 my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
 39 
 ...
 65      &stop_vip();                                      //添加
 ...
 95 FIXME_xxx_create_user( $new_master_handler->{dbh} );   //删除此行
 ...
 99       FIXME_xxx;                                       //删除行
 99       &start_vip();                                    //添加
120 sub start_vip() {
121     `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
122 }
123 sub stop_vip() {
124     return 0 unless ($ssh_user);
125     `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
126 }
 ...


## /etc/mha_manager/app1.cnf ##
...
master_ip_failover_script=/usr/local/bin/master_ip_failover     //去掉注释，添加自动failover脚本


## 再次启动mha集群 ##
masterha_manager --conf=/etc/mha_manager/app1.cnf  --remove_dead_master_conf  --ignore_last_failover 


/////////////////////////////////////////////////////////////////////


slave_mha_node:


## shell command ##
yum -y perl-DBD-mysql   perl-DBI  perl-JSON \
perl-Config-Tiny-2.14-7.el7.noarch.rpm \
perl-Email-Date-Format-1.002-15.el7.noarch.rpm \
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm \
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm \
perl-Mail-Sendmail-0.79-21.el7.art.noarch.rpm \
perl-MIME-Lite-3.030-1.el7.noarch.rpm  \
perl-MIME-Types-1.38-2.el7.noarch.rpm  \
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm 


rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm


#####################################################################


master_mysql:

## /etc/my.cnf ##

...
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server_id = 220
log-bin = mysql-bin

binlog-do-db=s6000
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1

sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2    
auto-increment-offset = 1   
slave-skip-errors = all
max_connect_errors = 1000


slave_mysql:

## /etc/my.cnf ##

...
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server_id = 221
log-bin = mysql-bin

binlog-do-db=s6000
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1

sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2
auto-increment-offset = 2
max_connect_errors = 1000
slave-skip-errors = all