您现在的位置是：首页 >技术杂谈 >MySQL-中间件mycat（三）网站首页技术杂谈

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

[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/

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

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

[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

[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.

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

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

[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>

[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...

[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'

[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...

[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> 

[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...

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'

[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)