Building a Highly Available MySQL Cluster: Best Practice Guide for Master-Slave Replication and Load Balancing

Building a Highly Available MySQL Cluster: Best Practice Guide for Master-Slave Replication and Load Balancing

In recent years, with the rapid development of the Internet, databases have become a large One of the core data storage and processing engines of some web applications. In this scenario, high availability and load balancing have become important considerations in database architecture design. As one of the most popular open source relational databases, MySQL's cluster deployment solution has attracted much attention.

This article will introduce how to implement a highly available database cluster through MySQL master-slave replication and load balancing. We will first introduce the principle and configuration of MySQL master-slave replication, and then discuss how to use a load balancer to balance database access traffic.

1. The principle and configuration of MySQL master-slave replication

MySQL master-slave replication is a database replication technology based on log transmission, which records modification operations on the master database into binary logs , and then modify the slave database by reading these logs to achieve data synchronization between the master database and the slave database.

Configuring MySQL master-slave replication requires the following steps:

1. Configuring the master database

Add in the configuration file my.cnf of the master database The following configuration:

[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=ROW

2. Create a replication account and authorize

Log in to the main database, create a replication account and authorize replication permissions:

CREATE USER 'repl'@'slaveip' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slaveip';
FLUSH PRIVILEGES;

will ## Replace #slaveip with the IP address of the slave database, and password with the password of the copied account.

Start the binary log of the main database

Log in to the mysql command line of the main database and execute the following command to start the binary log:

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

Record The values ​​of

File and Position will be used later when configuring the slave database.

Configuring the slave database

Add the following configuration in the configuration file my.cnf of the slave database:

[mysqld]
server-id=2

Then restart the slave database.

Start replication from the database

Execute the following command in the mysql command line from the database to start replication:

mysql> CHANGE MASTER TO MASTER_HOST='masterip', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='filename', MASTER_LOG_POS=position;
mysql> START SLAVE;

Place

masteripReplace the IP address of the main database, password with the password of the replication account, filename and position with the values ​​recorded in the previous step.

After the configuration is completed, the slave database will start to synchronize with the master database.

2. Selection and configuration of load balancer

In a high-availability database cluster, the load balancer plays a vital role. It can evenly distribute read operation requests among multiple slave databases, thereby improving the overall performance and availability of the system.

Common MySQL load balancers include HAProxy, MaxScale and MySQL Router. Taking HAProxy as an example, the following is an example configuration file:

global
    maxconn 4096

defaults
    mode tcp
    timeout connect 5000ms
    timeout client 50000ms
    timeout server 50000ms

listen mysql-cluster
    bind 0.0.0.0:3306
    mode tcp
    balance roundrobin
    option mysql-check user haproxy_check
    server mysql-1 masterip:3306 check
    server mysql-2 slaveip:3306 check

Replace

masterip and slaveip with the IP addresses of the master and slave databases.

After the configuration is completed, start the HAProxy service and use HAProxy to load balance database access.

3. Code Example

The following is a simple PHP code example to demonstrate how to connect to a MySQL database cluster through a load balancer:

<?php
$host = 'load-balancer-ip';
$user = 'username';
$pass = 'password';
$db = 'database';

$conn = new mysqli($host, $user, $pass, $db);

if($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

Will

Replace load-balancer-ip with the IP address of the load balancer, and username, password, and database with your correct database login credentials.

Through the above configuration and sample code, we can implement a highly available MySQL cluster and achieve load balancing of database access through the load balancer. Such an architecture can not only improve the availability and performance of the system, but also better cope with various faults and high concurrency scenarios.

Summary:

This article introduces how to implement a highly available database cluster through MySQL master-slave replication and load balancing. Through master-slave replication, data synchronization between the master database and the slave database can be achieved; through the load balancer, database access requests can be distributed evenly. At the same time, we also provide corresponding configuration and code examples to facilitate readers to get started quickly. I hope this article can provide some reference and help for everyone to build a highly available MySQL cluster.

The above is the detailed content of Building a Highly Available MySQL Cluster: Best Practice Guide for Master-Slave Replication and Load Balancing. For more information, please follow other related articles on the PHP Chinese website!