Implementing MySQL Read/Write Splitting with Spring Boot, MyBatis, and MyCat Middleware

The article begins by introducing the need for read/write separation in a MySQL master‑slave setup and outlines two implementation methods: a code‑level solution using Spring Boot, MyBatis, and a custom DynamicDataSource with an interceptor, and a middleware solution using MyCat.

Code‑Level Implementation includes creating Maven dependencies, configuring application.yml, defining MasterProperties and SlaveProperties, setting up DataSourceConfig, and implementing DynamicDataSource that extends AbstractRoutingDataSource. A DynamicDataSourceHolder thread‑local class stores the current DB type, while DynamicDataSourceInterceptor analyzes SQL statements to decide whether to route to the master or slave data source.

Sample Java classes such as Student, StudentDao, and test cases demonstrate inserting and querying data, confirming that writes go to the master and reads to the slave.

MyCat Middleware Implementation describes configuring server.xml and schema.xml to define a virtual database, data nodes, and read/write hosts. The configuration sets balance="1" for load balancing, specifies heartbeat queries, and enables automatic failover by setting switchType="2". After restarting MyCat, SQL statements are logged to show that INSERTs are routed to the master and SELECTs are distributed across slave nodes.

The article also demonstrates fault‑tolerance: when the master node is stopped, MyCat automatically promotes a slave to master, allowing continued write operations without manual code changes.

Finally, an analysis compares the flexibility of the custom code approach with the ease of configuration, scalability, and automatic failover provided by MyCat, offering guidance on when to choose each method.