读写分离部署

一、第一台节点安装MYCAT服务

[root@mycat ~]# yum install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel
[root@mycat ~]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/     //解压mycat安装包至/usr/local目录下
[root@mycat ~]# chmod -R 777 /usr/local/mycat/                         //将mycat下的包赋予所有权限
[root@mycat ~]# echo export MYCAT_HOME=/usr/local/mycat/ >> /etc/profile            //在系统环境下添加Mycat变量
[root@mycat ~]# source /etc/profile

二、编辑Mycat 的逻辑库配置文件

[root@mycat ~]# vi /usr/local/mycat/conf/schema.xml                    //编辑mycat逻辑库（将原来的文件直接删除然后进行覆盖添加即可）  
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
 <mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="USERDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"></schema>
<dataNode name="dn1" dataHost="localhost1" database="test" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" dbType="mysql" dbDriver="native" writeType="0" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="192.168.100.22:3306" user="root" password="123456">
                <readHost host="hostS1" url="192.168.100.33:3306" user="root"  password="123456" />
        </writeHost>
 </dataHost>
</mycat:schema> 
[root@mycat ~]# chown root:root /usr/local/mycat/conf/schema.xml                 //修改逻辑库配置文件权限

三、编辑Mycta访问用户

[root@mycat ~]# vi /usr/local/mycat/conf/server.xml                     //编辑文件 
在配置文件的最后部分 
<user name="root">   
    <property name="password">123456</property>   
    <property name="schemas">USERDB</property>                     //将TESTDB改为USERDB
然后删除以下几行内容
<user name="user">   
    <property name="password">user</property>   
    <property name="schemas">TESTDB</property>   
    <property name="readOnly">true</property> 
</user>

四、启动Mycat服务

[root@mycat ~]# /bin/bash /usr/local/mycat/bin/mycat start 
启动之后使用netstat -ntpl命令查询端口号，寻找暴露端口8066、9066端口，如果还想端口暴露则服务正常
[root@mycat ~]# netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 127.0.0.1:32000         0.0.0.0:*               LISTEN      11495/java          
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      914/sshd            
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1159/master         
tcp6       0      0 :::1984                 :::*                    LISTEN      11495/java          
tcp6       0      0 :::8066                 :::*                    LISTEN      11495/java          
tcp6       0      0 :::43078                :::*                    LISTEN      11495/java          
tcp6       0      0 :::46792                :::*                    LISTEN      11495/java          
tcp6       0      0 :::9066                 :::*                    LISTEN      11495/java          
tcp6       0      0 :::22                   :::*                    LISTEN      914/sshd            
tcp6       0      0 ::1:25                  :::*                    LISTEN      1159/master         
[root@mycat ~]#

五、验证mycat读写分离信息

[root@mycat ~]# yum install -y MariaDB-client         //现在第一台节点上安装数据库插件
如果安装不上就需要配置gpmall yum 源
上传gpmall-repo到root下
[root@mycat ~]# cat /etc/yum.repos.d/local.repo 
[centos]
name=centos
baseurl=file:///opt/centos
gpgcheck=0
enabled=1
[gpmall]
name=gpmall
baseurl=file:///root/gpmall-repo
gpgcheck=0
enabled=1
[root@mycat ~]# 
[root@mycat ~]# mysql -h127.0.0.1 -P8066 -uroot -p123456 
Welcome to the MariaDB monitor.  Commands end with ; or \g. 
Your MySQL connection id is 2 
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB) 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
MySQL [(none)]> show databases; 
+----------+ 
| DATABASE | 
+----------+
 | USERDB   | 
+----------+ 
1 row in set (0.001 sec) 
MySQL [(none)]> use USERDB 
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 [USERDB]> show tables; 
+----------------+
| Tables_in_test | 
+----------------+ 
| company        | 
+----------------+ 
1 row in set (0.003 sec)
MySQL [USERDB]> select * from company; 
+----+----------+------+ 
| id | name     | addr | 
+----+----------+------+ 
|  1 | facebook | usa  | 
+----+----------+------+ 
1 row in set (0.005 sec)

六、使用Mycat服务添加数据表

MySQL [USERDB]> insert into company values(2,"bastetball","usa"); 
Query OK, 1 row affected (0.050 sec) 
MySQL [USERDB]> select * from company; 
+----+------------+------+
| id | name       | addr | 
+----+------------+------+
|  1 | facebook   | usa  | 
|  2 | bastetball | usa  | 
+----+------------+------+ 
2 rows in set (0.002 sec)

七、验证读写分离操作

[root@mycat ~]# mysql -h127.0.0.1 -P9066 -uroot -p123456 -e 'show  @@datasource; 
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST           | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | hostM1 | mysql | 192.168.100.22 | 3306 | W    |      0 |   10 | 1000 |      64 |         0 |          1 |
| dn1      | hostS1 | mysql | 192.168.100.33 | 3306 | R    |      0 |    5 | 1000 |      62 |         4 |          0 |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
在 Mycat 虚拟机节点使用 mysql 命令，通过 9066 端口查询对数据库读写操作的分离信 息。可以看到所有的写入操作 WRITE_LOAD 数都在 db1 主数据库节点上，所有的读取操作 READ_LOAD 数都在 db2 主数据库节点上。