使用MyCAT代理MySQL数据库

原创

运维记事 2019-05-26 18:31:48 博主文章分类：LNAMP ©著作权

文章标签 mycat mysql 文章分类 运维

©著作权归作者所有：来自51CTO博客作者运维记事的原创作品，请联系作者获取转载授权，否则将追究法律责任

环境中需要使用统一入口访问多数据库，环境下有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)