How to Install and Configure MyCat 1.6.5 for a Distributed MySQL Cluster

What is MyCat

MyCat is an open‑source distributed database middleware that implements the MySQL protocol. It acts as a proxy, allowing MySQL clients to connect while routing queries to multiple backend MySQL servers or other databases via JDBC.

Key Concepts

Schema : logical database, corresponds to MySQL’s Database.

Table : logical table that must declare a DataNode and can bind a sharding rule.

DataNode : logical storage node that maps to a physical backend host.

DataSource : defines the physical address of a backend database.

Sharding rule : algorithm that determines how a large table is split into multiple physical tables.

Test Environment

The test uses MyCat 1.6.5 (two nodes) together with MySQL 8.0.11 (five nodes).

MySQL Node Installation

Install MySQL 8.0.11 on five hosts, create a database named szabm on each, and set lower_case_table_names=1 to avoid case‑sensitivity issues.

Java Installation

Download JDK 8 (e.g., jdk-8u152-linux-x64.tar.gz).

Extract and create a symlink:

tar -xzvf jdk-8u152-linux-x64.tar.gz
ln -s jdk1.8.0_152 java

Configure environment variables (adjust JAVA_HOME as needed):

export JAVA_HOME=/gpdb/java
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$CLASSPATH

MySQL Client Installation

Download MySQL client binary (e.g., mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz).

Extract and create a symlink:

tar -xzvf mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-8.0.11-linux-glibc2.12-x86_64 mysql

Add the client bin directory to PATH:

export PATH=$PATH:/gpdb/mysql/bin

MyCat Installation

Download MyCat 1.6.5 (e.g., MyCat-server-1.6.5-release-20180122220033-linux.tar.gz).

Extract:

tar -xzvf MyCat-server-1.6.5-release-20180122220033-linux.tar.gz

Set environment variables:

export MYCAT_HOME=/gpdb/MyCat
export PATH=$PATH:$MYCAT_HOME/bin

Start MyCat: MyCat start If a NumberFormatException occurs for memory size, edit $MYCAT_HOME/conf/wrapper.conf and set:

wrapper.java.initmemory=16
wrapper.java.maxmemory=4096

Configuration Files

All configuration resides in $MYCAT_HOME/conf: server.xml: server parameters and user authentication. schema.xml: logical database, table, and sharding definitions. rule.xml: detailed sharding rule parameters.

After any change, restart MyCat or reload via the manager port (9066).

Port Change Example

Default service port is 8066. To align with MySQL’s default, change to 3306 by adding the following line (remove surrounding comment tags):

<property name="serverPort">3306</property> <property name="managerPort">9066</property>

User and Schema Setup

Add a test user with read/write rights:

<user name="test">
  <property name="password">123456</property>
  <property name="schemas">szabm</property>
  <property name="readOnly">false</property>
</user>

Define a logical schema szabm7 with four tables, each mapped to five data nodes ( ceph3‑ceph7) and a simple modulo sharding rule:

<schema name="szabm7" checkSQLschema="false" sqlMaxLimit="100">
  <table name="abm_acct_balance" dataNode="ceph3,ceph4,ceph5,ceph6,ceph7" rule="abm_acct_balance-rule"/>
  <table name="abm_counter" dataNode="ceph3,ceph4,ceph5,ceph6,ceph7" rule="abm_counter_rule"/>
  <table name="abm_realtime_fee" dataNode="ceph3,ceph4,ceph5,ceph6,ceph7" rule="abm_realtime_fee_rule"/>
  <table name="abm_resource_present" dataNode="ceph3,ceph4,ceph5,ceph6,ceph7" rule="abm_resource_present_rule"/>
</schema>

DataNode Definitions

<dataNode name="ceph3" dataHost="ceph3db" database="szabm"/>
<dataNode name="ceph4" dataHost="ceph4db" database="szabm"/>
<dataNode name="ceph5" dataHost="ceph5db" database="szabm"/>
<dataNode name="ceph6" dataHost="ceph6db" database="szabm"/>
<dataNode name="ceph7" dataHost="ceph7db" database="szabm"/>

Sharding Rules

All tables use a modulo‑long algorithm with five data nodes:

<tableRule name="abm_acct_balance-rule">
  <rule>
    <columns>ACCT_BALANCE_ID</columns>
    <algorithm>mod-long_abm</algorithm>
  </rule>
</tableRule>
... (similar blocks for other tables) ...
<function name="mod-long_abm" class="io.MyCat.route.function.PartitionByMod">
  <property name="count">5</property>
</function>

Service Verification

Check ports:

netstat -an | grep 8066
netstat -an | grep 9066

Connect with MySQL client (use port 8066): mysql -uuser -puser -h127.0.0.1 -P8066 Note: MySQL 8.0 client is incompatible with MyCat 1.6.5; using MySQL 5.7 resolves the issue.

Configuration File Diagram