iFakeLocation

在实际项目中，Mycat服务也需要考虑高可用性，如果Mycat所在的服务器出现宕机，或者Mycat服务故障，需要有备机提供服务，需要考虑Mycat集群。高可用方案：我们可以使用HAProxy+Keepalived配合两台Mycat搭起Mycat集群，实现高可用性。HAProxy实现了Mycat多节点的集群高可用和负载均衡，而HAProxy自身的高可用则可以通过Keepalived来实现。Mycat及主从可参考MySQL 中间件Mycat部署

一、安装配置HAProxy

[root@haproxy ~]# wget https://src.fedoraproject.org/repo/pkgs/haproxy/haproxy-1.8.23.tar.gz/sha512/bfd65179345285f6f4581a7dce42e638b89e12717d4cb9218afa085759161e04b6c78307d04265a6c97cd484b67949781639da5236edb89137585c625130be4f/haproxy-1.8.23.tar.gz[root@haproxy ~]# tar zxf haproxy-1.8.23.tar.gz [root@haproxy ~]# cd haproxy-1.8.23/#查看内核版本[root@haproxy haproxy-1.8.23]# uname -r3.10.0-514.el7.x86_64#查看位shu[root@haproxy ~]# uname -mx86_64#进行编译[root@haproxy haproxy-1.8.23]# make TARGET=linux310 PREFIX=/usr/local/haproxy ARCH=x86_64#编译完成后安装[root@haproxy haproxy-1.8.23]# make install PREFIX=/usr/local/haproxy[root@haproxy ~]# vim /usr/local/haproxy/haproxy.confglobal        log 127.0.0.1 local0        #log 127.0.0.1 local1 notice        #log loghost local0 info        maxconn 4096        chroot /usr/local/haproxy        pidfile /usr/local/haproxy/haproxy.pid        uid 99        gid 99        daemon        #debug        #quietdefaults        log global        mode tcp        option abortonclose        option redispatch        retries 3        maxconn 2000        timeout connect 5000        timeout client 50000        timeout server 50000listen proxy_status        bind :48066                mode tcp                balance roundrobin                    # 轮询方式访问mycat                server mycat_1 192.168.171.134:8066 check inter 10s                server mycat_2 192.168.171.140:8066 check inter 10sfrontend admin_stats        bind :7777                mode http                stats enable                option httplog                maxconn 10                stats refresh 30s                stats uri /admin                stats auth admin:123123                stats hide-version                stats admin if TRUE[root@haproxy ~]# /usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf            # 启动[root@haproxy ~]# ss -anput | grep haproxy       # 查看是否启动udp    UNCONN     0      0         *:33498                 *:*                   users:(('haproxy',pid=4535,fd=4))tcp    LISTEN     0      10        *:7777                  *:*                   users:(('haproxy',pid=4535,fd=5))tcp    LISTEN     0      128       *:48066                 *:*                   users:(('haproxy',pid=4535,fd=3))

浏览器访问http://192.168.171.132/admin:7777在弹出框输入账户密码验证负载均衡，通过haproxy访问Mycat

[root@haproxy ~]# mysql -umycat -p123456 -h192.168.171.132 -P48066

再次安装配置一个haproxy服务器（配置方法同上）

#将之前的配置文件传到新安装的haproxy[root@haproxy ~]# scp /usr/local/haproxy/haproxy.conf root@192.168.171.136:/usr/local/haproxy/#启动haproxy2[root@hahaproxy2 haproxy-1.8.23]# /usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf

二、配置Keepalived

#在两个haproxy主机上都安装上Keepalived#安装依赖包[root@haproxy ~]# yum -y install  openssl-devel popt-devel kernel-devel [root@haproxy ~]# tar zxf keepalived-2.0.20.tar.gz [root@haproxy ~]# cd keepalived-2.0.20/[root@haproxy keepalived-2.0.20]# ./configure --prefix=/ && make && make install [root@haproxy ~]# vim /etc/keepalived/keepalived.conf ! Configuration File for keepalivedglobal_defs {   router_id LVS_DEVEL}vrrp_instance VI_1 {    state MASTER    interface ens33    virtual_router_id 51    priority 100    advert_int 1    authentication {        auth_type PASS        auth_pass 1111    }    virtual_ipaddress {        192.168.171.250                      # VIP地址    }}virtual_server 192.168.171.250 48066 {    delay_loop 6    lb_algo rr    lb_kind NAT    persistence_timeout 50    protocol TCP    real_server 192.168.171.132 48066 {        weight 1        TCP_CHECK {            connect_timeout 3            retry 3            delay_before_retry 3        }    }    real_server 192.168.171.136 48066 {        weight 1        TCP_CHECK {            connect_timeout 3            retry 3            delay_before_retry 3        }    }}[root@haproxy ~]# systemctl start keepalived      # 启动服务#将配置文件复制到haproxy2上[root@localhost ~]# scp /etc/keepalived/keepalived.conf root@192.168.171.136:/etc/keepalived/#修改haproxy上的Keepalived配置文件! Configuration File for keepalivedglobal_defs {   router_id LVS_2                # 修改id}vrrp_instance VI_1 {    state BACKUP             # 修改状态    interface ens33    virtual_router_id 51    priority 50              # 优先级修改    advert_int 1    authentication {        auth_type PASS        auth_pass 1111    }    virtual_ipaddress {        192.168.171.250    }}[root@haproxy2 ~]# systemctl start keepalived

测试高可用，连接VIP地址进行管理mycat

[root@haproxy ~]# mysql -umycat -p123456 -h192.168.171.250 -P48066mysql> show databases;+----------+| DATABASE |+----------+| TESTDB   |+----------+1 row in set (0.00 sec)

Mycat安全设置1、权限配置1）user标签权限控制目前Mycat对于中间件的连接控制并没有做太复杂的控制，目前只做了中间件逻辑库级别的读写权限控制。

#修改server.xml配置文件user部分<user name='mycat' defaultAccount='true'>                <property name='password'>123456</property>                <property name='schemas'>TESTDB</property>                <property name='defaultSchema'>TESTDB</property>                                </user>user name='user'>                <property name='password'>user</property>                <property name='schemas'>TESTDB</property>                <property name='readOnly'>true</property>                <property name='defaultSchema'>TESTDB</property>        </user>

2）privileges标签权限控制在user标签下的privilege标签可以对逻辑库（schema）、表（table）进行精细化的DML权限控制。privileges标签下的check属性，如为true开启权限检查，为false不开启，默认为false。由于Mycat一个用户的schemas属性可配置多个逻辑库（schema），所以privileges的下级节点schema节点同样可配置多个，对多库多表进行细粒度的DML权限控制

#修改server.xml的privileges部分 <!-- 表级 DML 权限设置 -->                      <privileges check='false'>                        <schema name='TESTDB' dml='0110' >                                <table name='tb01' dml='0000'></table>                                <table name='tb02' dml='1111'></table>                        </schema>                </privileges>           

SQL拦截firewall标签用来定义防火墙；firewall下whitehost标签用来定义IP白名单，blacklist用来定义SQL黑名单。1、设置白名单

#srserver.xml配置文件<firewall>           <whitehost>              <host host='192.168.171.250' user='root'/>           </whitehost></firewall>

2、设置黑名单

<firewall>           <whitehost>              <host host='192.168.171.250' user='root'/>           </whitehost>       <blacklist check='true'>                 <property name='deleteAllow'>false</property>       </blacklist>        </firewall>

可以设置的黑名单SQL拦截列表

原文地址：https://blog.51cto.com/14227204/2506947