环境中需要使用统一入口访问多数据库,环境下有B2C、B2B、O2O数据库,现针对所有B2C、B2B、O2O中的所有订单进行聚合搭建一个类似京东京享值一样的会员级别系统,针对不同级别会员给予不同的优惠。

数据库 数据库主机 数据库用户名 数据库用密码 备注
b2c 192.168.0.200 root 123456 B2C数据库
b2b 192.168.0.201 root 123456 B2B数据库
o2o 192.168.0.202 root 123456 O2O数据库

1.先安装JDK
tar zxf jdk-8u211-linux-x64.tar.gz
mv jdk1.8.0_211 /usr/local/

2.配置PATH
echo 'export PATH=/usr/local/jdk1.8.0_211/bin:$PATH'>>/etc/profile
source /etc/profile
java -version

3.下载mycat
wget http://dl.mycat.io/1.6.6.1/Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz
mv mycat /usr/local/

4.配置mycat
配置server.xml
cd /usr/local/mycat
cp -rf conf conf_ori
cd conf
vim server.xml

 <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <system>
        <property name="nonePasswordLogin">0</property>
        <property name="useHandshakeV10">1</property>
        <property name="useSqlStat">0</property>
        <property name="useGlobleTableCheck">0</property>
                <property name="sequnceHandlerType">2</property>
        <property name="subqueryRelationshipCheck">false</property>
                <property name="processorBufferPoolType">0</property>
                <property name="handleDistributedTransactions">0</property>
                <property name="useOffHeapForMerge">1</property>
        <property name="memoryPageSize">64k</property>
                <property name="spillsFileBufferSize">1k</property>
                <property name="useStreamOutput">0</property>
                <property name="systemReserveMemorySize">384m</property>
                <property name="useZKSwitch">false</property>
                <property name="strictTxIsolation">false</property>
                <property name="useZKSwitch">true</property>
        </system>
        <user name="root" defaultAccount="true">
                <property name="password">123456</property>
                <property name="schemas">b2c,b2b,o2o</property>
        </user>

        <user name="user">
                <property name="password">user</property>
                <property name="schemas">b2c,b2b,o2o</property>
                <property name="readOnly">false</property>
        </user>

</mycat:server>

vim schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="b2c" checkSQLschema="false" sqlMaxLimit="100" dataNode="b2c" >
        </schema>
        <schema name="b2b" checkSQLschema="false" sqlMaxLimit="100" dataNode="b2b" >
        </schema>
        <schema name="o2o" checkSQLschema="false" sqlMaxLimit="100" dataNode="o2o" >
        </schema>
        <dataNode name="b2c" dataHost="b2chost" database="b2c" />
        <dataNode name="b2b" dataHost="b2bhost" database="b2b" />
        <dataNode name="o2o" dataHost="o2ohost" database="o2o" />
        <dataHost name="b2chost" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="3310" url="192.168.0.200:3306" user="root" password="123456">
                </writeHost>
        </dataHost>
        <dataHost name="b2bhost" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="3311" url="192.168.0.201:3306" user="root" password="123456">
                </writeHost>
        </dataHost>
        <dataHost name="o2ohost" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="3312" url="192.168.0.202:3306" user="root" password="123456">
                </writeHost>
        </dataHost>
</mycat:schema>

可以看到关键的逻辑是这个schema.xml配置文件

5.启动mycat并查看日志
启动mycat
/usr/local/mycat/bin/mycat start
查看日志
vim /usr/local/mycat/logs/wrapper.log
显示

STATUS | wrapper  | 2019/05/26 18:28:10 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2019/05/26 18:28:10 | Launching a JVM...
INFO   | jvm 1    | 2019/05/26 18:28:10 | Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0
INFO   | jvm 1    | 2019/05/26 18:28:11 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2019/05/26 18:28:11 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2019/05/26 18:28:11 |
INFO   | jvm 1    | 2019/05/26 18:28:12 | MyCAT Server startup successfully. see logs in logs/mycat.log

可以看到服务启动了

6.查看结果
mysql -h127.0.0.1 -P8066 -uroot -p123456

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.6.1-release-20181031195535 MyCat Server (OpenCloudDB)

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 |
+----------+
| b2b      |
| o2o      |
| b2c    |
+----------+
3 rows in set (0.00 sec)