MySQL Master-Slave Replication: Splitting Reads and Writes - Innovations in IT, Leadership, and Digital Strategy

MySQL master-slave replication is a common technique to scale database operations by distributing queries. The master server handles write operations, while one or more slave servers handle read operations. This approach reduces load on the master and enhances overall performance.

1. Understanding Master-Slave Replication

In a master-slave replication setup:

Master: Handles all write operations (INSERT, UPDATE, DELETE).
Slaves: Handle read operations (SELECT) by replicating data changes from the master.

This separation allows for better performance and scalability, especially for read-heavy workloads.

2. Setting Up Master-Slave Replication

Follow these steps to configure a basic master-slave replication:

2.1 Configure the Master

Edit the master server’s my.cnf file:

[mysqld]

server-id = 1 log-bin = mysql-bin binlog-do-db = your_database_name

Restart the MySQL service and create a replication user:

CREATE USER 'replica_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;

Note the File and Position values from the output.

2.2 Configure the Slave

Edit the slave server’s my.cnf file:

[mysqld]

server-id = 2 relay-log = relay-log

Restart the MySQL service and set up replication:

CHANGE MASTER TO 
MASTER_HOST='master_ip',
MASTER_USER='replica_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;
START SLAVE;
SHOW SLAVE STATUS\G;

3. Splitting Reads and Writes

To effectively split read and write queries, you need a routing mechanism. Two common approaches are:

3.1 Application-Level Routing

Modify the application to route writes to the master and reads to the slave. Example in PHP:

$master = new mysqli('master_host', 'user', 'password', 'database');
$slave = new mysqli('slave_host', 'user', 'password', 'database');

// Write operation
$master->query("INSERT INTO table_name (column) VALUES ('value')");

// Read operation
$result = $slave->query("SELECT * FROM table_name");

3.2 Proxy-Based Routing

Use a proxy like ProxySQL to automate query routing:

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, 'master_host', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, 'slave_host', 3306);

INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup)
VALUES (1, '^SELECT.*', 2), (2, '.*', 1);
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

4. Best Practices

To ensure optimal performance and consistency:

Monitor replication lag and avoid critical read-after-write queries on slaves.
Use tools like ProxySQL or MySQL Router for advanced routing.
Regularly test and validate replication integrity.
Implement failover mechanisms for high availability.

5. Conclusion

Master-slave replication with read-write splitting is a powerful strategy for scaling MySQL databases. By distributing reads across multiple slaves, you can significantly reduce the load on the master and improve overall performance. With proper configuration and monitoring, this setup can handle demanding workloads efficiently.