4.mycat部署

1.准备工作

mycat依赖Java环境，所以必须安装jdk

yum install java-1.8.0-openjdk-devel.x86_64

配置JAVA_HOME环境变量

ls -lrt /etc/alternatives/java
#可以看到：/etc/alternatives/java -> /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.191.b12-1.el7_6.x86_64/jre/bin/java

vim /etc/profile
#添加

 export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.191.b12-1.el7_6.x86_64
 export JRE_HOME=${JAVA_HOME}/jre
 export CLASSPATH=.:${JAVA_HOME}/lib:${JRE_HOME}/lib
 export PATH=${JAVA_HOME}/bin:$PATH

source /etc/profile

创建两个pxc集群，充当两个分片

创建数据表，用于保存切分数据，两个PXC集群都建立

CREATE TABLE t_user (
id INT ( 10 ) UNSIGNED NOT NULL,
username VARCHAR ( 200 ) NOT NULL,
passwords VARCHAR ( 2000 ) NOT NULL,
tel CHAR ( 11 ) NOT NULL,
locked TINYINT ( 1 ) UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY ( id ),
KEY idx_username ( username ),
UNIQUE KEY unq_username ( username ) 
) ENGINE = INNODB 
DEFAULT charset = utf8;

下载mycat压缩包

wget http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
解压到/usr/local/下即可

编辑mycat配置文件

文件               作用                                          修改内容
rule.xml          切分算法                                       修改mod-long分片数量为2
server.xml        虚拟mysql                                      修改用户名，密码和逻辑库
schema.xml        数据库连接，读写分离，负载均衡，数据表映射           定义连接，读写分离，负载均衡，数据表映射 

 vim server.xml  

 如下：

      #定义用户权限  
      <user name="admin" defaultAccount="true">
                <property name="password">Abc_123456</property>
                <property name="schemas">test1</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">user</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
        </user>
        -->

vim schema.xml

vim rule.xml 

 开放防火墙端口，关闭selinux

端口          作用
8066         数据服务
9066         管理端口

启动和关闭mycat

启动：
chmod -R 777 /bin/*.sh
./bin/mycat start

关闭：

./bin/mycat stop

 2.测试

登陆mycat：

mysql -uadmin -pAbc_123456 -h 127.0.0.1 -P8066

插入数据：

mysql> use test1;

mysql> insert into t_user(id,username,passwords,tel,locked) values(1,"jack","123456","13106061111",0);
Query OK, 1 row affected (0.11 sec)

mysql> insert into t_user(id,username,passwords,tel,locked) values(2,"tom","123456","13106061112",1);      
Query OK, 1 row affected (0.13 sec)

mysql> select * from t_user;
+----+----------+-----------+-------------+--------+
| id | username | passwords | tel         | locked |
+----+----------+-----------+-------------+--------+
|  2 | tom      | 123456    | 13106061112 |      1 |
|  1 | jack     | 123456    | 13106061111 |      0 |
+----+----------+-----------+-------------+--------+
2 rows in set (0.09 sec)

分别登陆两个pxc节点查看：

节点一：
mysql> select * from t_user;
+----+----------+-----------+-------------+--------+
| id | username | passwords | tel         | locked |
+----+----------+-----------+-------------+--------+
|  1 | jack     | 123456    | 13106061111 |      0 |
+----+----------+-----------+-------------+--------+

节点二：
mysql> select * from t_user;
+----+----------+-----------+-------------+--------+
| id | username | passwords | tel         | locked |
+----+----------+-----------+-------------+--------+
|  2 | tom      | 123456    | 13106061112 |      1 |
+----+----------+-----------+-------------+--------+

 3.集群方案（若用tpcc做压力测试，需修改配置文件设置pxc_strict_mode=DISABLED）