Step-by-Step Guide to Installing and Configuring Mycat for Read/Write Splitting

Environment Description

Slave node: 192.168.1.248, Master node: 192.168.1.250.

Database Service Preparation

After master‑slave configuration, both Slave_IO_Running and Slave_SQL_Running are "yes".

Create Databases and Users for Mycat DataNodes

Install Mycat

Configure Environment Variables

echo export PATH=$PATH:/usr/local/mycat/bin >>/etc/profile
echo export MYCAT_HOME=/usr/local/mycat >>/etc/profile
source /etc/profile

Update /etc/my.cnf and Restart MySQL

lower_case_table_names=1

Install JDK7

rpm -ivh jdk-7u79-linux-x64.rpm

Install Mycat Binaries

tar xvf Mycat-server-1.5.1-RELEASE-20160816173057-linux.tar.gz -C /usr/local
groupadd mycat -g 910
useradd mycat -g 910
chown -R mycat:mycat /usr/local/mycat

Modify Configuration and Start

cd /usr/local/mycat/conf
vim schema.xml

Configure tables, data nodes, sharding rules, and key columns.

Edit line 43 of the dataHost tag to set writeHost URL to 10.10.10.250, username mycat, and password MANAGER.

Start Mycat

cd /usr/local/mycat/bin
mycat
# start Starting Mycat-server...

Check wrapper.log for a successful start, then view logs in /usr/local/mycat/log.

Mycat Configuration Details

In schema.xml set balance, switchType, slaveThreshold, and writeType attributes. Balance values:

0 – No read/write separation; all reads go to the current writeHost.

1 – Both readHost and standby writeHost participate in SELECT load balancing.

2 – Reads are randomly distributed between writeHost and readHost.

3 – Reads are randomly sent to the readHost associated with the writeHost; available from version 1.4 onward.

Switch type values:

-1 – No automatic switch.

1 – Automatic switch (default).

2 – Switch based on MySQL master‑slave sync status.

3 – Switch based on Galera cluster heartbeat.

In this example balance=1, switchType=1, slaveThreshold=100, and writeType=0 (writes go only to the primary host).

Validate Read/Write Splitting

Insert operations are directed to the master (192.168.1.250). Use show global status like 'Com_insert'; on both master and slave to confirm that the insert count increases on the master and replicates to the slave.

Read queries are routed to the slave (hostS1) when balance=1, confirming proper read/write separation.

Validate Automatic Failover

Stop the current master MySQL service and observe changes in dnindex.properties. The remaining node (original slave) becomes the writable host.

Insertions now go to the original slave, confirming that Mycat automatically switched the write host without a restart.

To reintegrate the failed node, re‑establish master‑slave replication; Mycat does not need to be restarted, making the failover transparent to front‑end applications.