Database Shardign Implementation

Mycat Installation and Configuration

Download Mycat from the official repository. For Linux environments, extract the package using:

tar -xvf Mycat-server-1.6.7.1-release-*.tar.gz

The extracted directory includes:

• bin: executable scripts
• conf: configuration files (schema.xml, server.xml, rule.xml)
• lib: required JAR dependencies

Create a logs directory in the root for automatic log generation.

Memory Configuration

Adjust JVM heap settings in conf/wrapper.conf based on available system memory:

wrapper.java.additional.10=-Xmx1G
wrapper.java.additional.11=-Xms256M

Add Mycat to system environment variables:

MYCAT_HOME=/usr/local/mycat
PATH=$MYCAT_HOME/bin:$PATH

Environment Setup

Use two MySQL 5.7 instances:

• Host1: 192.168.12.66
• Host2: 192.168.12.88

Create dtaabases and tables on both hosts:

CREATE DATABASE db_user DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

For db_store, create only on Host1 (Host2 uses replication).

Configuration Files

schema.xml defines logical schemas and data nodes:

<?xml version="1.0"?>
<mycat:schema xmlns:mycat="http://io.mycat/">
  <schema name="db_store" checkSQLschema="false" sqlMaxLimit="100">
    <table name="store" dataNode="db_store_dataNode" primaryKey="storeID"/>
    <table name="employee" dataNode="db_store_dataNode" primaryKey="employeeID"/>
  </schema>

  <schema name="db_user" checkSQLschema="false" sqlMaxLimit="100">
    <table name="data_dictionary" type="global" 
           dataNode="db_user_dataNode1,db_user_dataNode2" 
           primaryKey="dataDictionaryID"/>
    <table name="users" dataNode="db_user_dataNode$1-2"  
           rule="mod-userID-long" primaryKey="userID">
      <childTable name="user_address" joinKey="userID" 
                  parentKey="userID" primaryKey="addressID"/>
    </table>
  </schema>

  <dataNode name="db_store_dataNode" dataHost="db_storeHOST" database="db_store" />
  <dataNode name="db_user_dataNode1" dataHost="db_userHOST1" database="db_user" />
  <dataNode name="db_user_dataNode2" dataHost="db_userHOST2" database="db_user" />

  <dataHost name="db_storeHOST" maxCon="1000" minCon="10" balance="1"
            writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="192.168.12.66:3306" user="root" password="123456">
      <readHost host="hostS1" url="192.168.12.88:3306" user="root" password="123456" />
    </writeHost>
  </dataHost>

  <dataHost name="db_userHOST1" maxCon="1000" minCon="10" balance="0"
            writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="userHost1" url="192.168.12.66:3306" user="root" password="123456" />
  </dataHost>

  <dataHost name="db_userHOST2" maxCon="1000" minCon="10" balance="0"
            writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="userHost2" url="192.168.12.88:3306" user="root" password="123456" />
  </dataHost>
</mycat:schema>

server.xml configures users and system properties:

<user name="root">
  <property name="password">123456</property>
  <property name="schemas">db_store,db_user</property>
</user>

rule.xml defines sharding logic:

<tableRule name="mod-userID-long">
  <rule>
    <columns>userID</columns>
    <algorithm>mod-long</algorithm>
  </rule>
</tableRule>

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
  <property name="count">2</property>
</function>

Testing Data Distribution

After restarting Mycat, connect via:

mysql -uroot -p123456 -P8066 -h192.168.12.66

Insert test data into sharded tables. The users table uses modulo sharding on userID, while data_dictionary is replicated globally across all nodes.

Read-Write Splitting Implementation

Configured through dataHost elements in schema.xml. The balance attribute controls read distribution:

• balance="1": All read hosts and standby write hosts participate in read load balancing
• balance="0": No read-write splitting (all operations go to primary)

Heartbeat queries (select user()) monitor backend database availability.

Common Errors and Solutions

JVM Startup Issues

Ensure JDK 8 is installed to avoid wrapper initialization errors.

Invalid DataSource Error

Error: ERROR 1152 (HY000): java.lang.IllegalArgumentException: Invalid DataSource:0

Cause: MySQL user permissions not properly configured for remote access.

Solution: Update MySQL's user table to ensure consistent passwords across all host entries (localhost, 127.0.0.1, %).

Network Connectivity

no route to host indicates firewall restrictions. Verify network accessibility between Mycat and MySQL hosts.