Achieving data redundancy and expansion: application cases of MySQL master-slave replication technology in cluster environments

Realizing data redundancy and expansion: Application cases of MySQL master-slave replication technology in cluster environments

Introduction:
With the development of the Internet, the amount of data has increased With the continuous growth and the number of users, the traditional stand-alone database can no longer meet the needs of high concurrency and high availability. In this context, distributed databases have become one of the popular solutions. As one of the most commonly used relational databases, MySQL's master-slave replication technology has also received widespread attention in distributed databases. This article will introduce the application cases of MySQL master-slave replication technology to achieve data redundancy and expansion in a cluster environment, and provide corresponding code examples.

1. Introduction to MySQL master-slave replication technology
MySQL master-slave replication technology is a data replication method based on binary logs. It records the modification operations on the master database into the binary log in real time, and transmits the binary log to the slave database for replay, thereby ensuring data consistency between the master and slave databases. In a cluster environment, we can achieve data redundancy and expansion by deploying multiple slave libraries on different servers.

2. Deployment of cluster environment

1. Main library configuration
   First, we need to build the MySQL main library on a server. Assume that the operating system we are using is Linux and the database version is MySQL 5.7. The following are some commonly used main library configuration parameters:

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

2. Slave library configuration
   Build MySQL slave library on other servers. It should be noted that the server ID of the slave database must be unique and different from the master database. The following is an example configuration of a slave library:

[mysqld]
server-id=2
relay_log=mysql-relay-bin
read_only=1

3. Construction of cluster environment

1. Main library settings
   On the main library, we need to create a user for replication and give it the corresponding permissions. Assume that the user name we created is replication and the password is 123456. The corresponding SQL command is as follows:

CREATE USER 'replication'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON . TO 'replication'@'%';

2. Slave database settings
   On the slave database, we need to configure it to connect to the main database for data replication. Assume that the IP address of the main database is 192.168.1.100, the user name is replication, the password is 123456, and the ID of the slave database is 2. The corresponding SQL command is as follows:

CHANGE MASTER TO
MASTER_HOST ='192.168.1.100',
MASTER_USER='replication',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=123456;

3. Start replication
   After the slave library is set up, we need to start the replication function. First execute the following command to start replication:

START SLAVE;

Then, check the replication status through the following command:

SHOW SLAVE STATUSG;

If both "Slave_IO_Running" and "Slave_SQL_Running" in the displayed content are "Yes", it means that replication is running normally.

4. Application Cases: Data Redundancy and Expansion
In a cluster environment, we can distribute read and write requests to multiple slave libraries to achieve data redundancy and expansion. The following is a simple application case to demonstrate the effect of data redundancy and expansion.

1. Create test table
   Create a test table on the main database to store user information.

CREATE TABLE user (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(20 ) NOT NULL,
age INT(3) NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

2. Insert test data
   Insert some test data on the main library.

INSERT INTO user (name, age) VALUES ('Alice', 25), ('Bob', 30 ), ('Chris', 35);

3. Query data
   In the application, we can send read requests to any slave library. Assume that our application server has two slave libraries whose IP addresses are 192.168.1.101 and 192.168.1.102. We can send read requests through the following code example:

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

public class ReadRequestDemo {

public static void main(String[] args) {
    String url = "jdbc:mysql://192.168.1.101:3306/test";
    String username = "username";
    String password = "password";

    try {
        Connection conn = DriverManager.getConnection(url, username, password);
        String sql = "SELECT * FROM user";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);

        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            int age = rs.getInt("age");
            System.out.println("id=" + id + ", name=" + name + ", age=" + age);
        }

        rs.close();
        stmt.close();
        conn.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

}

Through the above code example, we can see that the slave library returns the test data inserted on the main library. Since we have configured multiple slave libraries, read requests can be distributed to different slave libraries, thereby achieving data redundancy and expansion.

Conclusion:
In the application case of MySQL master-slave replication technology in a cluster environment, we achieve data redundancy and expansion by building a master database and multiple slave databases. Through reasonable configuration and tuning, the concurrency performance and scalability of the system can be improved. At the same time, master-slave replication technology can also provide high availability and disaster recovery capabilities for data. For application scenarios that need to handle a large number of concurrent read operations, MySQL master-slave replication technology is a solution worth considering.

The above is the detailed content of Achieving data redundancy and expansion: application cases of MySQL master-slave replication technology in cluster environments. For more information, please follow other related articles on the PHP Chinese website!