Environment Prerequisites

• Java Runtime: Verify that JDK 8 or newer is installed and correctly mapped in the system $PATH.
• Network Topology: Provision a dedicated server to act as the middleware proxy. This node intercepts client traffic and distributes queries to backend MySQL instances.
• Host Resolution: Synchronize /etc/hosts across all participating machines to map logical hostnames to static IPs, ensuring stable internal DNS resolution.

  10.10.10.20   db-primary
  10.10.10.21   db-replica
  10.10.10.22   proxy-node
  

Middleware Installation

Extract the release archive into a standardized directory structure:

tar -xzf mycat-server-1.6.7-release.tar.gz -C /opt/db-proxy/
cd /opt/db-proxy/mycat

The extracted directory contains runtime binaries in bin/, configuration templates in conf/, and log storage in logs/.

Access Control Configuration

Modify conf/server.xml to define client authentication, default schemas, and permission profiles:

<mycat:server xmlns:mycat="http://io.mycat/">
    <system>
        <property name="serverPort">8066</property>
        <property name="managerPort">9066</property>
    </system>
    <user name="app_admin" defaultAccount="true">
        <property name="password">Secure#Mgmt_2024</property>
        <property name="schemas">enterprise_data</property>
        <!-- Optional: Fine-grain DML restrictions per table -->
    </user>
    <user name="report_reader">
        <property name="password">Read_Only!Pass</property>
        <property name="schemas">enterprise_data</property>
        <property name="readOnly">true</property>
    </user>
</mycat:server>

This configuration establishes two distinct connection pools: one for administrative write operations and another restricted to read-only traffic.

Data Routing Definition

Configure conf/schema.xml to map logical database schemas to physical backend nodes:

<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="enterprise_data" checkSQLschema="false" sqlMaxLimit="500" dataNode="cluster_dn1"></schema>
    
    <dataNode name="cluster_dn1" dataHost="main_cluster" database="enterprise_data"></dataNode>

    <dataHost name="main_cluster" maxCon="500" minCon="20" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
        <heartbeat>select 1</heartbeat>
        
        <writeHost host="primary_node" url="10.10.10.20:3306" user="proxy_svc" password="Secure#Mgmt_2024">
            <readHost host="secondary_node" url="10.10.10.21:3306" user="proxy_svc" password="Secure#Mgmt_2024" />
        </writeHost>
    </dataHost>
</mycat:schema>

Critical routing parameters include balance="3" for read-heavy load distribution, writeType="0 to enforce a single active writer, and switchType="2" for automated master-slave failover detection. Maintain strict XML tag closure to prevent silent startup failures.

Backend Database Initialization

Provision the target schema and grant necessary privileges on the primary MySQL instance:

CREATE DATABASE IF NOT EXISTS enterprise_data;
USE enterprise_data;
CREATE TABLE operations_log (log_id INT PRIMARY KEY, event_desc VARCHAR(255));
INSERT INTO operations_log VALUES (1, 'System initialized');

GRANT ALL PRIVILEGES ON enterprise_data.* TO 'proxy_svc'@'%' IDENTIFIED BY 'Secure#Mgmt_2024';
FLUSH PRIVILEGES;

Validate cross-node connectivity from the proxy host before proceeding:

mysql -u proxy_svc -p'Secure#Mgmt_2024' -h 10.10.10.20 -e 'SELECT 1;'

Runtime Tuning

Adjust conf/wrapper.conf to extend the Java service manager startup threshold, preventing premature termination during initial pool initialization:

wrapper.startup.timeout=300

Confirm the runtime environment aligns with middleware dependencies. Incompatible JVM builds frequently cause the WrapperSimpleApp process to exit immediately. Validate with java -version and ensure JAVA_HOME references a supported JDK distribution.

Service Initialization

Launch the middleware daemon and monitor process stability:

/opt/db-proxy/mycat/bin/mycat start
jps

Expected output should list WrapperSimpleApp. Validate network bindings:

netstat -tulnp | grep java

Port 8066 routes standard SQL traffic, while 9066 exposes management commands. Test end-to-end routing via the MySQL client:

mysql -u app_admin -p'Secure#Mgmt_2024' -h 10.10.10.22 -P 8066

Troubleshooting Runtime Anomalies

If connection attempts stall, verify daemon status via bin/mycat status and inspect logs/wrapper.log for JVM stack traces. Always execute data mutations through the proxy endpoint rather than direct backend access to preserve routing consistency. Use <![CDATA[ ... ]]> blocks for multi-line XML comments to avoid parser conflicts.

A frequent initialization error manifests as ERROR 3009 (HY000): java.lang.IllegalArgumentException: Invalid DataSource:0. This indicates a privilege mismatch between the middleware configuration and the backend MySQL user table. Resolve by updating the backend user host definition:

UPDATE mysql.user SET Host = '%' WHERE User = 'proxy_svc';
FLUSH PRIVILEGES;

Alternatively, grant the proxy user broad access using *.* to bypass strict schema-level restrictions during routing validation. Restart the middleware after applying backend permission changes to flush the active connection pool.