mycat

1.原理图

image.png

2.环境

主：172.16.122.131
从：172.16.122.132
# 下载mycat
[root@localhost opt]# wget http://dl.mycat.io/1.6.7.1/Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz
# 解压
[root@localhost opt]# tar -zxvf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz

3.mycat 配置

• server.xml

        <user name="root" defaultAccount="true">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>
        </user>

        <user name="user">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
        </user>

• schema.xml

?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="fulj_mycat">
        </schema>
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                /> -->
        <dataNode name="fulj_mycat" dataHost="localhost1" database="fulj" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="172.16.122.131:3306" user="root"
                                   password="123456">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="172.16.122.132:3306" user="root" password="123456" />
                </writeHost>
        </dataHost>
</mycat:schema>

4.启动

[root@localhost bin]# ./mycat start console

[root@localhost bin]# netstat -ntlp
[root@localhost bin]# ps -ef|grep mycat
#登录管理端口
[root@localhost bin]# mysql -uroot -p123456 -h127.0.0.1 -P9066
mysql> show database;
# 查看读写配置
mysql> show @@datasource;
# 查看心跳信息
mysql> show @@heartbeat;
# 登录mycat数据端口
[root@localhost bin]# mysql -uroot -p123456 -h127.0.0.1 -P8066

mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)

5. 验证
   更改日志的输出模式，需要重启./mycat restart

vi log4j2.xml;
<asyncRoot level="info" includeLocation="true">
# 修改为
<asyncRoot level="debug" includeLocation="true">
# 查看日志
tail -f mycat.log

最后配置分库分表+读写分离

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="fulj" checkSQLschema="false" sqlMaxLimit="100" dataNode="fulj_mycat">
# city 表分成了3个表city_1到3
                <table name="city" primaryKey="id" autoIncrement="true" subTables="city_$1-3" dataNode="fulj_mycat" rule="mod-long" />
        </schema>
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                /> -->
        <dataNode name="fulj_mycat" dataHost="localhost1" database="fulj" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="172.16.122.131:3306" user="root"
                                   password="123456">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="172.16.122.132:3306" user="root" password="123456" />
                </writeHost>
        </dataHost>
</mycat:schema>