How to Set Up MySQL Master‑Slave Replication and Read/Write Splitting with Mycat

Master‑Slave Replication Overview

Replication creates an exact copy of a primary MySQL instance (the master) on one or more secondary instances (the slaves). The master records every data‑changing operation in a binary log. Each slave runs an I/O thread that reads the binary log, writes the events to a relay log, and then an SQL thread replays the events to keep the slave synchronized.

Why Use Replication

Provides a hot standby that can take over if the master fails, preventing data loss.

Distributes read traffic to improve I/O performance on the master.

Enables read/write splitting so heavy reporting queries run on slaves without blocking front‑end services.

Replication Workflow

The master writes updates to the binary log.

The slave connects to the master and starts a replication I/O thread.

The I/O thread copies the binary log to the slave’s relay log.

The SQL thread reads the relay log and applies the events to the slave database.

Required Configuration

Master server

Enable binary logging (log-bin).

Set a unique server-id.

Obtain the current binary‑log file name and position (via SHOW MASTER STATUS).

Create a user account that the slave can use for replication.

Slave server

Set a unique server-id.

Use the master‑provided account to read the binary log.

Enable the slave service (START SLAVE).

Practical Setup Example

Two CentOS VMs are used: centos1 (192.168.1.109) – master (also runs Mycat). centos2 (192.168.1.100) – slave.

Master Configuration

[mysqld]
# Enable binary logging
log-bin=mysql-bin
# Unique server ID (last octet of IP)
server-id=109

service mysqld restart

GRANT FILE ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;

Slave Configuration

[mysqld]
# Unique server ID
server-id=110

# Remove auto‑generated UUID file
rm /var/lib/mysql/auto.cnf
service mysqld restart

CHANGE MASTER TO MASTER_HOST='192.168.1.109', MASTER_PORT=3306, MASTER_USER='root', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=2678;
START SLAVE;
SHOW SLAVE STATUS;

After these commands the slave continuously replicates the master’s data and can serve read‑only queries.

Read/Write Splitting with Mycat

Mycat is a middleware that routes writes to the master and distributes reads to one or more slaves based on MySQL replication status. Only two XML files need to be edited: schema.xml and server.xml.

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- Logical database definition -->
    <schema name="USER_DB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn"/>
    <!-- Storage node definition -->
    <dataNode name="dn" dataHost="node" database="user_db"/>
    <!-- Host definition with balancing and write‑type settings -->
    <dataHost name="node" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="192.168.1.109" url="192.168.1.109:3306" user="root" password="123456">
            <readHost host="192.168.1.110" url="192.168.1.110:3306" user="root" password="123456"/>
        </writeHost>
        <!-- Backup write host for failover -->
        <writeHost host="192.168.1.110" url="192.168.1.110:3306" user="root" password="123456"/>
    </dataHost>
</mycat:schema>

Key parameters: balance="1" – Randomly distributes read queries to the defined readHost. writeType="0" – Sends all write queries to an available writeHost. switchType="2" with slaveThreshold="100" – Enables automatic failover based on MySQL replication health. Mycat checks Seconds_Behind_Master, Slave_IO_Running, and Slave_SQL_Running from SHOW SLAVE STATUS.

server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
    <system>
        <property name="nonePasswordLogin">0</property>
        <property name="useHandshakeV10">1</property>
        <property name="useSqlStat">0</property>
        <property name="useGlobleTableCheck">0</property>
        <property name="sequnceHandlerType">2</property>
        <property name="subqueryRelationshipCheck">false</property>
        <property name="processorBufferPoolType">0</property>
        <property name="handleDistributedTransactions">0</property>
        <property name="useOffHeapForMerge">1</property>
        <property name="memoryPageSize">64k</property>
        <property name="spillsFileBufferSize">1k</property>
        <property name="useStreamOutput">0</property>
        <property name="systemReserveMemorySize">384m</property>
        <property name="useZKSwitch">false</property>
    </system>
    <!-- Client connection credentials -->
    <user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">USER_DB</property>
    </user>
</mycat:server>

With these configurations Mycat routes write operations to the master MySQL instance, distributes read operations to the slave, and automatically switches to a backup host when replication health degrades.