How to build a highly available MySQL cluster using distributed database architecture

How to build a highly available MySQL cluster using distributed database architecture

With the development of the Internet, the demand for high availability and scalability of the database is getting higher and higher. Distributed database architecture has become one of the effective ways to solve these needs. This article will introduce how to use a distributed database architecture to build a highly available MySQL cluster and provide relevant code examples.

1. Building a MySQL master-slave replication cluster

MySQL master-slave replication is the basic high availability solution provided by MySQL. Through master-slave replication, data backup and read-write separation can be achieved. First, we need to create a master library and multiple slave libraries. Suppose we have 3 servers, namely the master server (192.168.1.100) and two slave servers (192.168.1.101 and 192.168.1.102).

Configure the following on the main server:

1. Add the following content in the my.cnf configuration file:

   server-id=1
   log-bin=mysql-bin

2. Create a user for replication in MySQL and grant replication permissions:

   GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.%' IDENTIFIED BY 'password';
   FLUSH PRIVILEGES;

3. Execute the following command to start binary log recording:

   FLUSH TABLES WITH READ LOCK;
   SHOW MASTER STATUS;

   Record the displayed File and Position, which will be used in later steps.

Configure the following on the slave server:

1. Add the following content in the my.cnf configuration file:

   server-id=2

2. Execute the following command to connect the slave server to the master server:

   CHANGE MASTER TO 
    MASTER_HOST='192.168.1.100',
    MASTER_USER='replication',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='[MASTER_LOG_FILE]',
    MASTER_LOG_POS=[MASTER_LOG_POS];
   START SLAVE;

   Replace [MASTER_LOG_FILE] and [MASTER_LOG_POS] with the File and Position recorded on the master server.

Repeat the above steps to configure all slave servers.

2. Build a MySQL read-write separation cluster

After building a master-slave replication cluster, we can use read-write separation to further improve database performance. Read-write split distributes read operations to slave servers and sends write operations to the master server. This can reduce the load on the main server and improve the concurrency performance of read operations.

First, configure the following on the main server:

1. Add the following content in the my.cnf configuration file:

   log-slave-updates

2. Execute the following command to restart the master server:

   SET @@GLOBAL.read_only=ON;

Configure the following on the slave server:

1. In the my.cnf configuration file Add the following content:

   read-only

2. Execute the following command to restart the slave server:

   SET @@GLOBAL.read_only=OFF;

Next, we need to configure read and write in the application separation. Assuming we use PHP to develop applications, the following is a simplified sample code:

<?php
$readConn = new mysqli('192.168.1.101', 'username', 'password', 'database');
$writeConn = new mysqli('192.168.1.100', 'username', 'password', 'database');

// 读操作
$result = $readConn->query("SELECT * FROM table");

// 写操作
$writeConn->query("INSERT INTO table (column1, column2) VALUES ('value1', 'value2')");
?>

3. Building a MySQL sharding cluster

MySQL sharding is a decentralized storage of data Methods on multiple servers to improve database scalability. A sharded cluster divides data into multiple shards, with each shard storing a portion of the data. Before sharding, you first need to define sharding rules in the application.

The following is a sample code that implements the logic of sharded storage based on user ID:

<?php
$user_id = 1;
$shard_id = $user_id % 3;

$conn = new mysqli('192.168.1.10' . $shard_id, 'username', 'password', 'database');

$result = $conn->query("SELECT * FROM table WHERE user_id = " . $user_id);
?>

When actually building a sharded cluster, multiple database servers need to be created and corresponding configuration. Each database server stores a portion of the data and reads and writes the data through the application's sharding rules.

Summary

By building a MySQL master-slave replication cluster, a read-write separation cluster, and a sharded cluster, we can achieve a highly available MySQL cluster and improve the performance and scalability of the database. In actual applications, issues such as data backup and recovery, failover, etc. also need to be considered, and corresponding configuration and optimization must be performed. I hope the above code examples and configurations can help readers understand and apply distributed database architecture.

The above is the detailed content of How to build a highly available MySQL cluster using distributed database architecture. For more information, please follow other related articles on the PHP Chinese website!