MySQL-中间件mycat（三）

🦐博客主页：大虾好吃吗的博客

🦐MySQL专栏：MySQL专栏地址

    在实际项目中， Mycat 服务也需要考虑高可用性，如果 Mycat 所在服务器出现宕机，或 Mycat 服务故障，需要有备机提供服务，需要考虑 Mycat 集群。

高可用方案

    我们可以使用 HAProxy + Keepalived 配合两台 Mycat 搭起 Mycat 集群，实现高可用性。 HAProxy实现了MyCat 多节点的集群高可用和负载均衡， 而 HAProxy 自身的高可用则可以通过 Keepalived 来实现。继续上一章的部署，添加一台mycat做轮询，mycat2部署和mycat1一样，需要提前配置，添加两台haproxy加keepalived。

拓扑图如下：

安装配置 HAProxy

安装 HAProxy

注意：两台haproxy服务器都需要安装。

准备好HAProxy安装包，传到/opt目录下解压到/usr/local/src

[root@haproxy1 ~]# tar zxf haproxy-2.1.2.tar.gz -C /usr/local/src

[root@haproxy1 ~]# cd /usr/local/src/haproxy-2.1.2
[root@haproxy1 haproxy-2.1.2]# uname -r
3.10.0-1160.el7.x86_64
[root@haproxy1 haproxy-2.1.2]# make TARGET=linux310 PREFIX=/usr/local/haproxy ARCH=x86_64

ARGET=linux310，内核版本，使用uname -r查看内核，如： 3.10.0-514.el7，此时该参数就为linux310；

ARCH=x86_64，系统位数；

PREFIX=/usr/local/haprpxy #/usr/local/haprpxy，为haprpxy安装路径。

编译完成后，进行安装，向配置文件中插入以下配置信息,并保存

[root@haproxy1 haproxy-2.1.2]# make install PREFIX=/usr/local/haproxy
[root@haproxy1 haproxy-2.1.2]# vim /usr/local/haproxy/haproxy.conf
global
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
#quiet
defaults
log global
mode tcp
option abortonclose
option redispatch
retries 3
maxconn 2000
timeout connect 5000
timeout client 50000
timeout server 50000
listen proxy_status
bind :48066
mode tcp
balance roundrobin
server mycat_1 192.168.8.10:8066 check inter 10s            #mycat1主机
server mycat_2 192.168.8.11:8066 check inter 10s            #mycat2主机
frontend 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

注意：haproxy2配置和haproxy1相同，这里不在重复。

启动验证

启动HAProxy

[root@haproxy1 haproxy-2.1.2]# /usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf

查看HAProxy进程

[root@haproxy1 haproxy-2.1.2]# ps -ef | grep haproxy
nobody   101066      1  0 17:14 ?        00:00:00 /usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf
root     101086   5343  0 17:15 pts/2    00:00:00 grep --color=auto haproxy

打开浏览器访问 http://192.168.8.1:7777/admin 或者8.2主机，在弹出登录框后输入用户名： admin密码： 123123，注意开启mycat。

验证负载均衡，通过haproxy1和haproxy2访问mycat登录mysql，端口号为48066

[root@master1 ~]# mysql -umycat -p123456 -h192.168.8.1 -P 48066

配置 Keepalived

注意：两台haproxy主机都需要安装keepalived，这里以haproxy1主机为例。

安装 Keepalived

准备好Keepalived安装包，传到/opt目录下解压到/usr/local/src

[root@haproxy1 ~]# tar zxf keepalived-2.2.7.tar.gz -C /usr/local/src

安装依赖插件

[root@haproxy1 ~]# yum install -y gcc openssl-devel popt-devel

[root@haproxy1 ~]# cd /usr/local/src/keepalived-2.2.7/
[root@haproxy1 keepalived-2.2.7]# ./configure --prefix=/usr/local/keepalived

进行编译，完成后进行安装

[root@haproxy1 keepalived-2.2.7]# make && make install

运行前配置

[root@haproxy1 keepalived-2.2.7]# cp /usr/local/src/keepalived-2.2.7/keepalived/etc/init.d/keepalived /etc/init.d/
[root@haproxy1 keepalived-2.2.7]# mkdir /etc/keepalived
[root@haproxy1 keepalived-2.2.7]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf.sample /etc/keepalived/
[root@haproxy1 keepalived-2.2.7]# mv /etc/keepalived/keepalived.conf.sample /etc/keepalived/keepalived.conf
[root@haproxy1 keepalived-2.2.7]# cp /usr/local/src/keepalived-2.2.7/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@haproxy1 keepalived-2.2.7]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

修改配置文件

haproxy1配置文件

[root@haproxy1 ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id ha1
}

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.8.100
    }
}

virtual_server 192.168.8.100 48066 {
    delay_loop 6
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocol TCP

    real_server 192.168.8.1 48066 {
        weight 1
        TCP_CHECK {
            connect_timeout 3
            retry 3
            delay_before_retry 3
        }
    }
    real_server 192.168.8.2 48066 {
        weight 1
        TCP_CHECK {
            connect_timeout 3
            retry 3
            delay_before_retry 3
        }
    } 
}

haproxy2配置文件

[root@haproxy2 ~]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived

global_defs {
   router_id ha2
}

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.8.100
    }
}

virtual_server 192.168.8.100 48066 {
    delay_loop 6
    lb_algo rr
    lb_kind NAT
    persistence_timeout 50
    protocol TCP

    real_server 192.168.8.1 48066 {
        weight 1
        TCP_CHECK {
            connect_timeout 3
            retry 3
            delay_before_retry 3
        }
    }
    real_server 192.168.8.2 48066 {
        weight 1
        TCP_CHECK {
            connect_timeout 3
            retry 3
            delay_before_retry 3
        }
    } 
}

启动验证

两台主机都启动Keepalived

[root@haproxy1 ~]# systemctl start keepalived
[root@haproxy1 ~]# ps -ef | grep keep
root       2864   2487  0 16:21 ?        00:00:00 /usr/libexec/gsd-housekeeping
root     108226      1  0 17:43 ?        00:00:00 /usr/local/keepalived/sbin/keepalived --dont-fork -D
root     108227 108226  0 17:43 ?        00:00:00 /usr/local/keepalived/sbin/keepalived --dont-fork -D
root     108228 108226  0 17:43 ?        00:00:00 /usr/local/keepalived/sbin/keepalived --dont-fork -D
root     108244   5343  0 17:43 pts/2    00:00:00 grep --color=auto keep
[root@haproxy1 ~]# ip a show dev ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:e6:ef:60 brd ff:ff:ff:ff:ff:ff
    inet 192.168.8.1/24 brd 192.168.8.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.8.100/32 scope global ens33
       valid_lft forever preferred_lft forever

使用一台mysql服务器远程登录验证

[root@slave1 ~]# mysql -umycat -p123456 -P48066 -h192.168.8.100
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.6.29-mycat-1.6.7.6-release-20220524173810 MyCat Server (OpenCloudDB)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

测试高可用

关闭一台mycat通过虚拟ip查询数据，通过mysql访问登录mysql

[root@mycat1 ~]# mycat stop
Stopping Mycat-server...
Stopped Mycat-server.[root@mycat1 ~]# mycat stop
Stopping Mycat-server...
Stopped Mycat-server.

登录mycat

[root@slave1 ~]# mysql -umycat -p123456 -P48066 -h192.168.8.100

mycat 安全设置

权限配置

    user 标签权限控制 目前 Mycat 对于中间件的连接控制并没有做太复杂的控制，目前只做了中间件逻辑库级别的读写权限控制。是通过 server.xml 的 user 标签进行配置。

修改server.xml配置文件user部分，通过下面配置文件看到，有mycat用户（这里就不多讲了）和user用户，可以看到user用户密码，逻辑库等信息。

[root@mycat1 ~]# cd /usr/local/mycat/conf
[root@mycat1 conf]# vim server.xml
#省略部分内容
        <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>

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

修改server.xml配置文件privileges部分，orders表没有删除权限，配置tb1表没有增删改查权限。

[root@mycat1 ~]# cd /usr/local/mycat/conf
[root@mycat1 conf]# vim server.xml
#省略部分内容
                <!-- 表级 DML 权限设置 -->
                <privileges check="true">
                        <schema name="TESTDB" dml="1111" >
                                <table name="orders" dml="1110"></table>
                                <table name="tb1" dml="0000"></table>
                        </schema>
                </privileges>
[root@mycat1 conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...

配置说明
DML 权限增加（insert）更新（update）查询（select）删除（delete）0000禁止禁止禁止禁止0001禁止禁止禁止允许0010禁止禁止允许禁止1111允许允许允许允许
测试查看两个表是否按照要求修改成功，我这里只修改了mycat1主机，就直接登录mycat1测试了。

[root@master2 ~]# mysql -umycat -p123456 -h192.168.8.10 -P8066
#省略部分内容
mysql> use TESTDB
mysql> select * from orders;
+------+------------+-------------+-----------+
| id   | order_type | customer_id | amount    |
+------+------------+-------------+-----------+
|    2 |        101 |         100 | 100300.00 |
|    4 |        101 |         101 | 103000.00 |
|    6 |        102 |         100 | 100020.00 |
|    1 |        101 |         100 | 100100.00 |
|    3 |        101 |         101 | 120000.00 |
|    5 |        102 |         101 | 100400.00 |
+------+------------+-------------+-----------+
6 rows in set (0.34 sec)

mysql> delete from orders where id=1;
ERROR 3012 (HY000): The statement DML privilege check is not passed, reject for user 'mycat'

mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| customer         |
| dict_order_type  |
| orders           |
| orders_detail    |
| tb1              |
+------------------+
5 rows in set (0.01 sec)

mysql> select * from tb1;
ERROR 3012 (HY000): The statement DML privilege check is not passed, reject for user 'mycat'

    通过上面命令行显示，删除orders以及查看tb1无法成功执行，返回结果为DML权限检查未通过而拒绝。

SQL 拦截

    firewall 标签用来定义防火墙； firewall 下 whitehost 标签用来定义 IP 白名单 ， blacklist 用来定义SQL 黑名单。

白名单可以通过设置白名单，实现某主机某用户可以访问 Mycat，而其他主机用户禁止访问。

 修改server.xml配置文件firewall标签，配置只有192.168.8.50主机可以通过mycat用户访问，其他主机拒绝访问。

[root@mycat1 conf]# vim server.xml
        <firewall>
           <whitehost>
              <host host="192.168.8.50" user="mycat"/>
           </whitehost>
        </firewall>
[root@mycat1 conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...

    测试用mysql服务器通过192.168.8.10登录mysql，最终结果为只有192.168.8.50主机成功登录，至此白名单完成。

[root@master2 ~]# mysql -umycat -p123456 -h192.168.8.10 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.6-release-20220524173810 MyCat Server (OpenCloudDB)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

黑名单可以通过设置黑名单，实现 Mycat 对具体 SQL 操作的拦截，如增删改查等操作的拦截。

修改server.xml配置文件firewall标签，配置禁止mycat用户进行插入操作

[root@mycat1 conf]# vim server.xml
        <firewall>
           <whitehost>
              <host host="192.168.8.50" user="mycat"/>
           </whitehost>
        <blacklist check="true">
        <property name="insertAllow"> false</property>
        </blacklist>
        </firewall>
[root@mycat1 conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...

测试使用8.50主机登录，并且对orders表插入数据，最终结果发现无法插入，黑名单成功。

mysql> use TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| customer         |
| dict_order_type  |
| orders           |
| orders_detail    |
+------------------+
4 rows in set (0.02 sec)

mysql> select * from orders;
+------+------------+-------------+-----------+
| id   | order_type | customer_id | amount    |
+------+------------+-------------+-----------+
|    2 |        101 |         100 | 100300.00 |
|    4 |        101 |         101 | 103000.00 |
|    6 |        102 |         100 | 100020.00 |
|    1 |        101 |         100 | 100100.00 |
|    3 |        101 |         101 | 120000.00 |
|    5 |        102 |         101 | 100400.00 |
+------+------------+-------------+-----------+
6 rows in set (0.04 sec)

mysql> insert into orders values(^C
mysql> INSERT INTO orders(id,order_type,customer_id,amount) VALUES(7,102,101,111400);
ERROR 3012 (HY000): The statement is unsafe SQL, reject for user 'mycat'

在修改一次，使其可以插入数据，通过下面语法很好理解，false为拒绝，true为允许。

[root@mycat1 conf]# vim server.xml 
              <host host="192.168.8.50" user="mycat"/>
           </whitehost>
        <blacklist check="true">
        <property name="insertAllow"> true</property>
        </blacklist>
        </firewall>
[root@mycat1 conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...

插入数据成功。

[root@master2 ~]# mysql -umycat -p123456 -P48066 -h192.168.8.100

mysql> INSERT INTO orders(id,order_type,customer_id,amount) VALUES(7,102,101,111400);
Query OK, 1 row affected (0.21 sec)
 OK!

mysql> select * from orders;
+------+------------+-------------+-----------+
| id   | order_type | customer_id | amount    |
+------+------------+-------------+-----------+
|    2 |        101 |         100 | 100300.00 |
|    4 |        101 |         101 | 103000.00 |
|    6 |        102 |         100 | 100020.00 |
|    1 |        101 |         100 | 100100.00 |
|    3 |        101 |         101 | 120000.00 |
|    5 |        102 |         101 | 100400.00 |
|    7 |        102 |         101 | 111400.00 |
+------+------------+-------------+-----------+
7 rows in set (0.06 sec)

可以设置的黑名单 SQL 拦截功能列表
配置项缺省值描述selectAllowtrue是否允许执行 SELECT 语句deleteAllowtrue是否允许执行 DELETE 语句updateAllowtrue是否允许执行 UPDATE 语句insertAllowtrue是否允许执行 INSERT 语句creaetTableAllowtrue是否允许创建表setAllowtrue是否允许使用SET语法alterTableAllowtrue是否允许执行 Alter Table 语句dropTableAllowtrue是否允许修改表commitAllowtrue是否允许执行 commit 操作rollbackAllowtrue是否允许执行 roll back 操作

标签：服务器 linux 运维

本文转载自: https://blog.csdn.net/qq_61116007/article/details/130241809
版权归原作者 大虾好吃吗 所有，如有侵权，请联系我们删除。

MySQL-中间件mycat（三）

高可用方案

安装配置 HAProxy

安装 HAProxy

启动验证

配置 Keepalived

安装 Keepalived

修改配置文件

启动验证

测试高可用

mycat 安全设置

权限配置

SQL 拦截

发表评论

“MySQL-中间件mycat（三）”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航