MySQL 读写分离、主从复制案例

场景描述

假设你有一个在线商城应用，数据库用于存储用户信息和商品数据。写操作包括新增用户和更新商品信息，而读操作包括查询用户和商品详情。

1. 数据库环境准备

1.1. 主库配置

假设你的从库服务器 IP 地址为 192.168.1.101。

1. 修改从库的配置文件 my.cnf：

假设你的主库服务器 IP 地址为 192.168.1.100，数据库名为 shop_db。

1. 修改主库的配置文件 my.cnf（通常在 /etc/my.cnf 或 /etc/mysql/my.cnf）：

   [mysqld]
   server-id = 1
   log-bin = mysql-bin
   binlog-do-db = shop_db  # 需要复制的数据库
   

2. 重启主库 MySQL 服务：

   sudo service mysql restart

3. 创建用于复制的用户并授予权限：

   CREATE USER 'replication_user'@'%' IDENTIFIED BY 'your_password';
   GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
   FLUSH PRIVILEGES;
   

4. 查看主库状态以获取 File 和 Position：

   SHOW MASTER STATUS;

5. 假设输出如下：

   +---------------------+----------+--------------+------------------+
   | File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +---------------------+----------+--------------+------------------+
   | mysql-bin.000001   | 123456   | shop_db      |                  |
   +---------------------+----------+--------------+------------------+
   

1.2. 从库配置

假设你的从库服务器 IP 地址为 192.168.1.101。

1. 修改从库的配置文件 my.cnf：

   [mysqld]
   server-id = 2
   relay-log = mysql-relay-bin
   log-bin = mysql-bin
   read-only = 1
   

2. 重启从库 MySQL 服务：

   sudo service mysql restart
   

3. 配置从库连接到主库：

   CHANGE MASTER TO
   MASTER_HOST = '192.168.1.100',
   MASTER_USER = 'replication_user',
   MASTER_PASSWORD = 'your_password',
   MASTER_LOG_FILE = 'mysql-bin.000001',  -- 使用主库的 File 值
   MASTER_LOG_POS = 123456;                -- 使用主库的 Position 值
   

4. 启动从库的复制进程：

   START SLAVE;

5. 检查复制状态：

   SHOW SLAVE STATUS\G
   

6. 确保 Slave_IO_Running 和 Slave_SQL_Running 都是 Yes。

2. 应用层实现读写分离

接下来，我们将展示如何在应用层进行读写分离。假设你使用 Java 和 Spring Boot。

2.1. 配置数据源

在 application.yml 中配置两个数据源，一个用于主库（写），一个用于从库（读）。

spring:
  datasource:
    write:
      url: jdbc:mysql://192.168.1.100:3306/shop_db
      username: root
      password: your_password
      driver-class-name: com.mysql.cj.jdbc.Driver
    read:
      url: jdbc:mysql://192.168.1.101:3306/shop_db
      username: root
      password: your_password
      driver-class-name: com.mysql.cj.jdbc.Driver

2.2. 创建数据源配置类

在 Spring Boot 中，创建一个配置类来定义主库和从库的数据源。

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.write")
    public DataSource writeDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.read")
    public DataSource readDataSource() {
        return DataSourceBuilder.create().build();
    }
}

2.3. 使用 AOP 实现读写分离

接下来，使用 AOP 实现读写分离。在 Spring 中，可以通过自定义注解和切面来实现。

import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.annotation.Before;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;

@Aspect
@Component
public class DataSourceAspect {

    @Autowired
    private DataSource writeDataSource;

    @Autowired
    private DataSource readDataSource;

    @Pointcut("execution(* com.example.service.*.get*(..))")
    public void readPointcut() {}

    @Pointcut("execution(* com.example.service.*.add*(..)) || execution(* com.example.service.*.update*(..)) || execution(* com.example.service.*.delete*(..))")
    public void writePointcut() {}

    @Before("readPointcut()")
    public void setReadDataSource() {
        DataSourceContextHolder.setDataSourceType(DataSourceType.READ);
    }

    @Before("writePointcut()")
    public void setWriteDataSource() {
        DataSourceContextHolder.setDataSourceType(DataSourceType.WRITE);
    }
}

2.4. 数据源上下文管理

定义一个上下文持有者来存储当前线程的数据源类型：

public class DataSourceContextHolder {
    private static final ThreadLocal<DataSourceType> CONTEXT = new ThreadLocal<>();

    public static void setDataSourceType(DataSourceType type) {
        CONTEXT.set(type);
    }

    public static DataSourceType getDataSourceType() {
        return CONTEXT.get();
    }

    public static void clear() {
        CONTEXT.remove();
    }
}

定义数据源类型的枚举：

public enum DataSourceType {
    READ, WRITE
}

2.5. 创建服务

最后，在服务中实现读写逻辑。例如，创建一个用户服务：

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class UserService {

    @Autowired
    private UserRepository userRepository;

    @Transactional
    public void addUser(User user) {
        userRepository.save(user);  // 写操作
    }

    public User getUserById(Long id) {
        return userRepository.findById(id).orElse(null);  // 读操作
    }
}

3. 总结

通过上述步骤，你实现了 MySQL 的读写分离。具体步骤包括：

1. 设置 MySQL 主从复制。
2. 在应用层配置多个数据源，分别指向主库和从库。
3. 使用 AOP 实现读写分离，通过自定义注解和切面控制数据源的选择。
4. 在服务层实现读写逻辑。