• 环境

• 部署主从复制环境
  https://www.studyxin.com/MySQL/63.html

• 安装mycat节点的mycat代理服务

[root@mycat ~]# tar xf jdk-8u171-linux-x64.tar.gz
[root@mycat ~]#  mv jdk1.8.0_171/ /usr/local/java
[root@mycat ~]#  vi /etc/profile
export JAVA_HOME=/usr/local/java
export PATH=$PATH:$JAVA_HOME/bin
[root@mycat ~]# source /etc/profile
[root@mycat ~]# tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@mycat ~]# mv mycat/ /usr/local/
[root@mycat ~]# ls /usr/local/mycat/
bin  catlet  conf  lib  logs  version.txt

• 在master节点进行授权允许mycat程序能够连接MySQL集群，slave节点会同步权限

[root@master ~]# mysql -uroot -p123123
mysql> grant all on *.* to 'linux'@'192.168.100.107' identified by '123123';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

• 配置mycat节点的代理服务并启动

[root@mycat ~]# vi /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="VIR" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" ></schema>

        <dataNode name="dn1" dataHost="node1" database="cloud" />

        <dataHost name="node1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="192.168.100.105" url="192.168.100.105:3306" user="linux" password="123123">
                        <readHost host="192.168.100.106" url="192.168.100.106:3306" user="linux" password="123123" />
                </writeHost>
        </dataHost>
</mycat:schema>

[root@mycat ~]# vi /usr/local/mycat/conf/server.xml
     80         <user name="admin">                 ##管理员账户
     81                 <property name="password">admin</property>
     82                 <property name="schemas">VIR</property>
     95         <user name="user">                  ##普通用户，只读权限
     96                 <property name="password">user</property>
     97                 <property name="schemas">VIR</property>
     98                 <property name="readOnly">true</property>
     99         </user>

[root@mycat ~]# vi /usr/local/mycat/conf/log4j2.xml
     25         <asyncRoot level="debug" includeLocation="true">

[root@mycat ~]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@mycat ~]# netstat -utpln |grep 8066
tcp        0      0 0.0.0.0:8066            0.0.0.0:*               LISTEN      13090/java
[root@mycat ~]# netstat -utpln |grep 9066
tcp        0      0 0.0.0.0:9066            0.0.0.0:*               LISTEN      13090/java
[root@mycat ~]# tail /usr/local/mycat/logs/wrapper.log              ##服务日志
[root@mycat ~]# tail /usr/local/mycat/logs/mycat.log                    ##读写分离日志

• 在master节点创建测试数据库及表

[root@master ~]# mysql -uroot -p123123
mysql> create database cloud;
Query OK, 1 row affected (0.00 sec)
mysql> create table cloud.it (id int,name char(16));
Query OK, 0 rows affected (0.02 sec)
mysql> exit

• lamp节点客户端测试数据读写分离

[root@lamp ~]# mysql -uadmin -padmin -h 192.168.100.107 -P 8066
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| VIR      |
+----------+
1 row in set (0.00 sec)

MySQL [(none)]> use VIR;
MySQL [VIR]> show tables;
+-----------------+
| Tables_in_cloud |
+-----------------+
| it              |
| it1             |
+-----------------+
2 rows in set (0.01 sec)