概述

本来通过研究对比TiDB、MyCat和ShardingSphere这三种分库分表技术，最后选中了ShardingSphere。上报上去后阿里那边强烈要求使用MyCat，胳膊拗不过大腿，从了得了。

部署mariadb

安装并启动mariadb：

yum -y install mariadb mariadb-server.x86_64

systemctl start mariadb.service
systemctl enable mariadb.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

部署MyCat

1. 下载MyCat：

http://dl.mycat.io/1.6.7.1/

2. 解压到/usr/local目录下：

tar -xvf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz -C /usr/local

配置

[/usr/local/mycat/conf]# ll
总用量 120
-rwxr-xr-x 1 root root  3338 7月  15 17:31 dbseq.sql
-rwxr-xr-x 1 root root 87397 7月  15 17:31 globalVariables.json
-rwxr-xr-x 1 root root  3549 7月  15 17:31 jta.properties
-rwxr-xr-x 1 root root   922 7月  15 17:31 log4j.properties
-rwxr-xr-x 1 root root  3693 7月  15 17:31 mycat.yml
-rwxr-xr-x 1 root root   155 7月  15 17:31 saffron.properties
drwxr-xr-x 2 root root   233 8月   6 16:49 sql
-rwxr-xr-x 1 root root   216 7月  16 00:05 version.txt
-rwxr-xr-x 1 root root  4232 7月  16 00:05 wrapper.conf

修改server.xml

在最后面的user标签部分把root改成mycat：

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

                <!-- 表级 DML 权限设置 -->
                <!--
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>
                 -->
        </user>

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

修改schema.xml

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

启动

../mycat console
Running Mycat-server...
wrapper  | --> Wrapper Started as Console
wrapper  | Launching a JVM...
jvm 1    | OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0
jvm 1    | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1    |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
jvm 1    | 
jvm 1    | MyCAT Server startup successfully. see logs in logs/mycat.log

登录后台管理

mysql -umycat -p123456 -P 9066 -h 192.168.201.34
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.1-release-20190627191042 MyCat Server (monitor)

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)]> 
MySQL [(none)]> show databases;
+----------+
| DATABASE 
+----------+
| TESTDB   
+----------+

登录数据管理

mysql -umycat -p123456 -P 8066 -h 192.168.201.34 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29-mycat-1.6.7.1-release-20190627191042 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 |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)