Configuring MyCat with MySQL Master‑Slave and SpringBoot for Read‑Write Splitting

The article introduces MyCat, an open‑source database clustering solution built on Alibaba's Cobar, and describes how to achieve MySQL master‑slave replication and integrate MyCat with SpringBoot for read‑write splitting.

MySQL master configuration

Enable binary logging in /etc/my.cnf :

log-bin = mysql-bin
server-id = 4
expire-logs-days = 7
binlog-do-db = itools_simple

Create a replication user and restart MySQL, then verify log_bin is ON.

Slave configuration

Edit my.ini (or /etc/my.cnf ) on the Windows slave to set log-bin , server-id = 223 , and other parameters, then restart the slave and run CHANGE MASTER TO with the master’s host, user, password, log file and position.

Confirm replication with SHOW SLAVE STATUS\G – both Slave_IO_Running and Slave_SQL_Running should be Yes .

Storage engine notes

MySQL 5.5+ defaults to InnoDB, which supports transactions, row‑level locking, and foreign keys, while MyISAM lacks transactions and uses table‑level locks. The article also shows how to switch a slave’s engine to MyISAM if needed.

Installing MyCat

Download the tarball from http://dl.mycat.io , extract it to /usr/local/mycat , create a mycat user, set proper permissions, and add MYCAT_HOME to /etc/profile :

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

Source the profile to apply changes.

MyCat configuration

Define a schema in schema.xml linking the logical database itools_simple to a data node, and configure dataHost with write and read hosts, users, and passwords.

<mycat:schema ...>
  <schema name="itools_simple" dataNode="dn1"/>
  <dataNode name="dn1" dataHost="localhost" database="itools_simple"/>
  <dataHost name="localhost" ...>
    <writeHost host="hostM1" url="192.168.0.105:3306" user="user1" password="Root@123"/>
    <readHost host="hostS2" url="127.0.0.1:3306" user="user2" password="Root@123"/>
  </dataHost>
</mycat:schema>

Add two users in server.xml (one read‑write, one read‑only) with the appropriate default schema.

SpringBoot integration

Add dependencies for Spring AOP and Druid, then create two DataSource beans (select and update) using @ConfigurationProperties . Implement a DynamicDataSource extending AbstractRoutingDataSource that selects the target based on a ThreadLocal context.

Provide a DataSourceContextHolder class with setDbType , getDbType , and clearDbType methods.

Define an AOP aspect DataSourceOptionAop that intercepts service methods and sets the context to selectDataSource for read‑oriented method names (get, find, list, query, etc.) and to updateDataSource otherwise.

Running the application shows console output indicating which datasource is used for each method call, confirming successful read‑write separation.

Finally, the article includes promotional notes about related technical columns and recommended reading.