Implementing a robust read-write splitting architecture can significantly enhance database performance and availability by distributing query loads across multiple servers. This setup ensures that write operations are hendled by a dedicated primary server, while read requests are served by one or more secondary servers. This document outlines the process of configuring MySQL master-slave replication and integrating it with MyCAT for effective read-write separation on a Windows environment.

MySQL Master-Slave Replication Setup

MySQL master-slave replication enables data synchronization from a primary MySQL instance (master) to one or more secondary instances (slaves). This commonly employs asynchronous replication, allowing slaves to update their data without continuously blocking the master. This mechanism is crucial for isolating read workloads, thereby preventing read queries from impacting the performance of write-intensive operations on the master.

This guide assumes a one-master, one-slave configuration.

Master Server Configuration

To establish the master role, modify the my.ini configuration file on your primary MySQL server. Essential parameters include a unique server ID, binary logging enablement, and specifying which databases to include in the binary log.

[mysqld]
server-id = 101
log-bin = mysql_master_bin
binlog-do-db = application_database_name
# Optional: enforce transaction safety
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

After modifying my.ini, restart the MySQL service. For example, if your service is named MySQL80:

net stop MySQL80
net start MySQL80

Next, create a dedicated replication user with appropriate permissions on the master server. This user will be utilized by the slave to connect and fetch binary log events.

CREATE USER 'repl_user'@'192.168.1.%' IDENTIFIED BY 'replication_password_123';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.%';
FLUSH PRIVILEGES;

-- Record current master status for slave configuration
SHOW MASTER STATUS;

Note the File (e.g., mysql_master_bin.000001) and Position (e.g., 154) from the SHOW MASTER STATUS output. These values are essential for initializing the slave.

Slave Server Configuration

On the secondary MySQL server, modify its my.ini file. This server requires a distinct server-id and configuration for relay logs, which store events received from the master before applying them.

[mysqld]
server-id = 102
relay-log-index = slave-relay-log.index
relay-log = slave-relay-log
# Optional: If you want to skip specific errors during replication
# slave-skip-errors = 1062,1053

Restart the slave MySQL service after applying these changes.

Now, configure the slave to connect to the master using the credentials and master status information obtained earlier. Replace placeholders with your actual master details.

CHANGE MASTER TO
    MASTER_HOST='192.168.1.100', -- IP address of the master server
    MASTER_USER='repl_user',
    MASTER_PASSWORD='replication_password_123',
    MASTER_PORT=3306, -- Master's MySQL port
    MASTER_LOG_FILE='mysql_master_bin.000001', -- From SHOW MASTER STATUS on master
    MASTER_LOG_POS=154; -- From SHOW MASTER STATUS on master

START SLAVE;

-- Verify replication status
SHOW SLAVE STATUS\G

Ensure that Slave_IO_Running and Slave_SQL_Running both show Yes in the SHOW SLAVE STATUS output, indicating successful replication setup.

MyCAT Read-Write Splitting Configuration

MyCAT acts as a database middleware or proxy, abstracting the underlying database architecture from applications. It allows for advanced features like sharding, read-write splitting, and high availability. For read-write splitting, the primary configuration files are server.xml and schema.xml located in the MyCAT conf directory.

server.xml Configuration

Configure user accounts and their associated logical schemas in server.xml. This defines how applications connect to MyCAT.

<user name="app_user">
    <property name="password">app_password_mycat</property>
    <property name="schemas">mycat_logical_db</property>
</user>

schema.xml Configuration

schema.xml defines logical databases, data nodes, and data hosts. For read-write splitting, you'll define a dataHost that encompasses both your master (writeHost) and slave (readHost) and specify a balance type.

<schema name="mycat_logical_db" checkSQLschema="true" sqlparser="druid" balance="3">
    <dataNode name="dn_mysql_rw" dataHost="dh_mysql_rw"/>
</schema>

<dataHost name="dh_mysql_rw" maxCon="100" minCon="10" balance="3"
          writeType="0" dbType="MySQL" dbDriver="native" switchType="1">
    <heartbeat>select 1</heartbeat>
    <!-- Master for write operations -->
    <writeHost host="192.168.1.100" port="3306" user="app_db_user" password="app_db_password">
        <property name="dataSourceName">master_ds</property>
    </writeHost>
    <!-- Slave for read operations -->
    <readHost host="192.168.1.185" port="3306" user="app_db_user" password="app_db_password">
        <property name="dataSourceName">slave_ds</property>
    </readHost>
</dataHost>

Understanding the balance attribute:

• balance="0": No read-write splitting; all operations (reads and writes) are directed to the primary writeHost within the dataHost.
• balance="1": All readHost nodes and any standby writeHost nodes participate in load balancing for SELECT statements. The active writeHost still handles all writes.
• balance="2": All read operations are randomly distributed among all writeHost and readHost nodes.
• balance="3": All read requests are randomly distributed to the readHost nodes associated with the writeHost. The writeHost itself does not handle read pressure. This is a common choice for dedicated read-write splitting.

In the example above, balance="3" is set on the dataHost, ensuring writes go to 192.168.1.100 (master) and reads are directed to 192.168.1.185 (slave).

Testing Read-Write Splitting

After MyCAT is started (typically on port 8066), applications should connect to MyCAT using the configured user (app_user) and logical database (mycat_logical_db).

1. Verify write operations: Connect to MyCAT (e.g., via mysql -h 127.0.0.1 -P 8066 -u app_user -p) and insert data:

   CREATE TABLE records (id INT AUTO_INCREMENT PRIMARY KEY, value VARCHAR(50));
   INSERT INTO records (value) VALUES ('Data from MyCAT write');
   

   Confirm that this record appears in the master database (192.168.1.100). Due to replication, it should also eventually appear on the slave (192.168.1.185).

2. Verify read operations: Still connected to MyCAT, perform a SELECT query:

   SELECT * FROM records;
   

   With balance="3" configured, this SELECT statement will be routed to the slave database (192.168.1.185). You can verify this by checking the general query log on the slave or by temporarily stopping the slave's MySQL service (reads should fail or MyCAT should switch to the master if configured for failover).

3. Demonstrate replication consistency: Directly insert a new record into the master database (bypassing MyCAT):

   -- Connect directly to master (192.168.1.100)
   INSERT INTO records (value) VALUES ('Direct master insert');
   

   Now, query via MyCAT:

   -- Connect to MyCAT (127.0.0.1:8066)
   SELECT * FROM records;
   

   Both records should be visible, confirming that MyCAT successfully routes reads to the slave, and the slave is replicating data from the master effectively. The second record will be visible after the replication delay. This setup ensures that your primary database is unburdened by read queries, leading to improved write performance and overall system responsiveness.