Database Archives - Page 6 of 33 - Innovations in IT, Leadership, and Digital Strategy

MySQL Master-Slave Replication: Splitting Reads and Writes

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.

Introduction to ProxySQL: Enhancing MySQL Performance

ProxySQL is a robust, high-performance proxy for MySQL designed to enhance the scalability and reliability of database infrastructures. It serves as an intermediary between the application and MySQL servers, providing features like query routing, connection pooling, and query caching to optimize database performance.

1. Key Features of ProxySQL

ProxySQL offers several features that make it a powerful tool for MySQL optimization:

Query Routing: Dynamically routes queries to different backends based on user-defined rules.
Query Caching: Caches frequently executed queries, reducing load on MySQL servers.
Connection Pooling: Maintains persistent connections, improving application performance by minimizing connection overhead.
Load Balancing: Distributes traffic across multiple servers to prevent bottlenecks and ensure high availability.
Monitoring and Statistics: Provides detailed insights into query performance and server health.

2. Why Use ProxySQL?

ProxySQL is ideal for applications with high traffic or complex database architectures. Benefits include:

Improved Performance: Offloads repetitive tasks like query parsing and caching from the database server.
Simplified Scaling: Supports sharding and load balancing for horizontal scaling.
High Availability: Automatically redirects traffic to healthy servers during outages.

3. Setting Up ProxySQL

Follow these steps to set up ProxySQL:

Install ProxySQL: Download and install ProxySQL on a dedicated server or alongside your application server.
Configure Backends: Add your MySQL servers as backends in ProxySQL:
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, ‘mysql1.example.com’, 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, ‘mysql2.example.com’, 3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
Set Query Rules: Define rules for routing queries to specific backends:
INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup)
VALUES (1, ‘^SELECT .* FROM users’, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
Point Applications to ProxySQL: Update the application’s database connection string to point to ProxySQL’s IP and port (default: 6033).

4. Monitoring ProxySQL

Use ProxySQL’s built-in monitoring tools to track query performance and server health. Example:

SELECT * FROM stats_mysql_query_digest;
SELECT * FROM stats_mysql_connection_pool;

5. Advanced Features

ProxySQL also supports advanced features like:

Query Rewrite: Modify queries dynamically based on patterns.
Replication Support: Direct read and write queries to appropriate servers in a master-slave setup.
TLS/SSL Support: Ensures secure communication between the proxy and MySQL servers.

6. Conclusion

ProxySQL is an essential tool for managing MySQL in high-traffic environments. Its ability to optimize query execution, balance loads, and enhance reliability makes it a valuable addition to any MySQL-based infrastructure. With its extensive feature set, ProxySQL ensures that your database scales effectively while maintaining performance and availability.