How to use MySQL's master-slave replication to implement a read-write separation architecture

How to use MySQL's master-slave replication to implement read-write separation architecture

In traditional web applications, read and write operations are usually connected to the same database server. As applications develop and access volumes increase, this architecture can easily lead to database performance bottlenecks. In order to solve this problem, MySQL's master-slave replication can be used to implement a read-write separation architecture. This article will introduce how to use MySQL's master-slave replication to achieve read-write separation, and provide corresponding code examples.

1. Environment preparation
   First, make sure you have installed MySQL on the server and ensure that the master-slave database can communicate normally. If MySQL is not installed yet, please follow the official documentation to install and set it up.
2. Configure the main database
   Configure the following on the main database:

(1) Open the configuration file my.cnf of the main database and add the following configuration at the end:

# 设置为主数据库
server-id=1
log-bin=mysql-bin

(2) Restart the main database:

$ sudo service mysql restart

3. Configure the slave database
   Configure the following on the slave database:

(1) Open the slave database In the database configuration file my.cnf, add the following configuration at the end:

# 设置为从数据库
server-id=2
relay-log=mysql-relay-bin

(2) Restart the slave database:

$ sudo service mysql restart

4. Create a replication user
   in the master database Create a user for copying and grant corresponding permissions. Execute the following command:

   mysql> CREATE USER 'replication'@'%' IDENTIFIED BY 'password';
   mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
   mysql> FLUSH PRIVILEGES;

   Copy after login

5. Start master-slave replication
   Execute the following command on the slave database to start master-slave replication:

   mysql> CHANGE MASTER TO MASTER_HOST='主数据库IP', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0;
   mysql> START SLAVE;

   Copy after login

6. Verify master-slave replication
   Execute the following command on the slave database to view the status of master-slave replication:

   mysql> SHOW SLAVE STATUS G

   Copy after login

   Ensure that the values ​​of the following two parameters are "YES":

   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes

   Copy after login

   If If an error occurs, you need to check whether the replication configuration and database connection are set up correctly.

7. Achieve read-write separation
   Once the master-slave replication is configured successfully, read-write separation can be achieved. In the application, the slave database is connected for read operations and the master database is connected for write operations. This can effectively utilize database resources and improve system performance and stability.

The following is a sample code using Java language to demonstrate how to connect the master-slave database to complete the read-write separation operation:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ReadWriteSeparationExample {
    public static void main(String[] args) {
        // 读操作连接从数据库
        Connection readConn = null;
        String readUrl = "jdbc:mysql://从数据库IP:端口/数据库名";
        String readUser = "用户名";
        String readPassword = "密码";
        
        try {
            readConn = DriverManager.getConnection(readUrl, readUser, readPassword);
            Statement readStmt = readConn.createStatement();
            ResultSet readResult = readStmt.executeQuery("SELECT * FROM 表名");
            
            while (readResult.next()) {
                // 处理查询结果
            }
            
            readResult.close();
            readStmt.close();
            readConn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        // 写操作连接主数据库
        Connection writeConn = null;
        String writeUrl = "jdbc:mysql://主数据库IP:端口/数据库名";
        String writeUser = "用户名";
        String writePassword = "密码";
        
        try {
            writeConn = DriverManager.getConnection(writeUrl, writeUser, writePassword);
            Statement writeStmt = writeConn.createStatement();
            writeStmt.execute("INSERT INTO 表名 VALUES(1, '数据')");
            
            writeStmt.close();
            writeConn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The above is the use of MySQL's master-slave replication to achieve read-write separation Architecture steps and code examples. Through this architecture, read operations can be allocated to the slave database, effectively improving the performance and stability of the system. Hope this helps!

The above is the detailed content of How to use MySQL's master-slave replication to implement a read-write separation architecture. For more information, please follow other related articles on the PHP Chinese website!