MySQL 读写分离

MySQL 读写分离

一、配置主库(Master)

1.修改主库的配置文件

修改主库的 my.cnf 配置文件，生成二进制日志 (binary log) 和服务器唯一ID，这是实现主从复制的必要配置

[mysqld]
# skip-grant-tables
user=root
port=3306
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/usr/local/mysql/socket/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
log-error=/usr/local/mysql/logs/mysqld.log
pid_file=/var/run/mysqld/mysqld.pid
symbolic-links=0# master 配置
server-id=1            # 主库的服务器ID，必须唯一
log-bin=mysqls-bin      # 开启二进制日志，文件名可选
binlog-format=ROW      # （可选,不指定默认 STATEMENT ）使用行级复制（推荐）# 需要重启mysql服务，master配置才会生效

2.创建用于复制的用户

• 登录到Mysql 的主库中，创建一个专门的用户供从库使用，用于复制

# 创建用户
CREATE USER '用户名'@'%' IDENTIFIED BY '用户密码';
e.g
CREATE USER 'replica_user'@'%' IDENTIFIED BY 'replica123456';# 给用户赋予 '复制' 的权限
GRANT REPLICATION SLAVE ON *.* TO '用户名'@'%';
e.g
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';# 这里必须要执行,否则在从库执行 SHOW SLAVE STATUS\G; 时会报错 2061
alter user '用户名'@'%' identified with mysql_native_password by 'mysql数据库登录密码';
e.g
alter user 'replica_user'@'%' identified with mysql_native_password by 'replica123456';# 刷新权限
FLUSH PRIVILEGES;

Tip

从库配置启动 复制时报错

SHOW SLAVE STATUS\G;

报错：Last_IO_Errno: 2061
Last_IO_Error: error connecting to master ‘replica_user@120.77.27.139:3306’ - rery-time: 60 retries: 1 message: Authentication plugin ‘caching_sha2_password’ reported error: Auhentication requires secure connection.

3 获取主库的二进制日志位置

• 锁住主库防止数据变化，获取当前的二进制文件名和位置

# 锁住主库
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)# 获取当前二进制文件名和位置
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqls-bin.000001 |      157 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)# 解锁主库
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

二、配置从库(Slave)

1.修改从库的配置文件

• 在从库的MySQL的配置文件 my.cnf 中进行必要的配置，确保其有唯一的服务器ID并启用中继日志

[mysqld]
server-id=2          # 从库的服务器ID，确保唯一
relay-log=relay-bin  # 启用中继日志（用于接收主库的二进制日志）
replicate-do-db=tbb-iov # 指定从主库中需要复制的数据库
read-only=1 # 只读

2.连接到主库并启动复制

• 登录从库，通过以下配置指定主库的信息并启动复制

CHANGE MASTER TOMASTER_HOST='主库的IP地址',           # 主库的IP地址MASTER_PORT=3306,  -- 如果主库使用非默认端口，这里需要指定    MASTER_USER='replica_user',          # 复制用户MASTER_PASSWORD='replica_password',  # 复制用户的密码MASTER_LOG_FILE='mysql-bin.000001',  # 主库的二进制日志文件名MASTER_LOG_POS=154;                  # 主库的二进制日志位置e.g
CHANGE MASTER TOMASTER_HOST='1x0.xx.xx.13x',MASTER_PORT=3306,MASTER_USER='replica_user',MASTER_PASSWORD='replica123456',MASTER_LOG_FILE='mysqls-bin.000001',MASTER_LOG_POS=157;

• 启动从库的复制相关命令

# 启动
START SLAVE;
# 停止
STOP SLAVE;
# 查看同步状态
SHOW SLAVE STATUS\G;

• 主从复制状态参数

               Slave_IO_State: Waiting for source to send eventMaster_Host: xx.xx.xx.xxMaster_User: replica_userMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysqls-bin.000001Read_Master_Log_Pos: 7657Relay_Log_File: relay-bin.000006Relay_Log_Pos: 2135Relay_Master_Log_File: mysqls-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: tbb-iov,demo

Slave_IO_Running 和 Slave_SQL_Running 必须要为 Yes 才表示成功启动主从复制

• 查询同步状态的参数说明

连接和状态信息
Slave_IO_State: 当前 IO 线程的状态。例如，Waiting for source to send event 表示从库正在等待主库发送事件。
Master_Host: 主库的 IP 地址或主机名。
Master_User: 用于复制的用户名。
Master_Port: 主库的端口号。
Connect_Retry: 从库尝试重新连接到主库的间隔时间（秒）。
Master_Log_File: 当前正在读取的主库二进制日志文件。
Read_Master_Log_Pos: 当前读取的主库二进制日志的位置。
Relay_Log_File: 当前正在使用的中继日志文件。
Relay_Log_Pos: 当前中继日志的位置。
Relay_Master_Log_File: 当前中继日志对应的主库二进制日志文件。
Slave_IO_Running: IO 线程是否正在运行。Yes 表示正在运行，No 表示停止。
Slave_SQL_Running: SQL 线程是否正在运行。Yes 表示正在运行，No 表示停止。
Slave_SQL_Running_State: SQL 线程的当前状态。例如，Replica has read all relay log; waiting for more updates 表示从库已经读取了所有中继日志，正在等待更多的更新。复制过滤规则
Replicate_Do_DB: 需要复制的数据库列表。
Replicate_Ignore_DB: 不需要复制的数据库列表。
Replicate_Do_Table: 需要复制的表列表。
Replicate_Ignore_Table: 不需要复制的表列表。
Replicate_Wild_Do_Table: 需要复制的表的通配符模式。
Replicate_Wild_Ignore_Table: 不需要复制的表的通配符模式。错误信息
Last_Errno: 最近一次错误的错误码。
Last_Error: 最近一次错误的错误信息。
Skip_Counter: 跳过的错误事务计数器。
Exec_Master_Log_Pos: 当前已执行的主库二进制日志的位置。
Last_IO_Errno: 最近一次 IO 错误的错误码。
Last_IO_Error: 最近一次 IO 错误的错误信息。
Last_SQL_Errno: 最近一次 SQL 错误的错误码。
Last_SQL_Error: 最近一次 SQL 错误的错误信息。其他信息
Relay_Log_Space: 中继日志占用的空间大小（字节）。
Until_Condition: 停止复制的条件。
Until_Log_File: 停止复制的日志文件。
Until_Log_Pos: 停止复制的日志位置。
Master_SSL_Allowed: 是否允许 SSL 连接。
Master_SSL_CA_File: SSL 证书颁发机构文件路径。
Master_SSL_CA_Path: SSL 证书颁发机构路径。
Master_SSL_Cert: SSL 证书文件路径。
Master_SSL_Cipher: SSL 密码套件。
Master_SSL_Key: SSL 私钥文件路径。
Seconds_Behind_Master: 从库落后于主库的时间（秒）。
Master_SSL_Verify_Server_Cert: 是否验证主库的 SSL 证书。
Master_Server_Id: 主库的服务器 ID。
Master_UUID: 主库的 UUID。
Master_Info_File: 存储主库信息的文件。
SQL_Delay: SQL 线程延迟时间（秒）。
SQL_Remaining_Delay: 剩余的延迟时间。
Replicate_Ignore_Server_Ids: 不需要复制的服务器 ID 列表。
Master_Retry_Count: 从库尝试重新连接到主库的最大次数。
Master_Bind: 绑定的网络接口。
Last_IO_Error_Timestamp: 最近一次 IO 错误的时间戳。
Last_SQL_Error_Timestamp: 最近一次 SQL 错误的时间戳。
Master_SSL_Crl: SSL 证书吊销列表文件路径。
Master_SSL_Crlpath: SSL 证书吊销列表路径。
Retrieved_Gtid_Set: 已检索的 GTID 集合。
Executed_Gtid_Set: 已执行的 GTID 集合。
Auto_Position: 是否启用自动定位（基于 GTID）。
Replicate_Rewrite_DB: 数据库重写规则。
Channel_Name: 复制通道名称。
Master_TLS_Version: 主库支持的 TLS 版本。
Master_public_key_path: 主库的公钥文件路径。
Get_master_public_key: 是否获取主库的公钥。
Network_Namespace: 网络命名空间。

Tip

数据库开启主从复制之前，主库和从库的数据需要保持一致，

三、Spring Boot + MySQL+ Mybatis 主从复制，读写分离

1、Maven 依赖引入

主要依赖如下

<properties><java.version>1.8</java.version><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding><spring-boot.version>2.6.13</spring-boot.version><mybatis.version>2.2.2</mybatis.version><mysql.version>8.0.30</mysql.version><alibabadruid.version>1.2.16</alibabadruid.version><lombok.version>1.18.26</lombok.version></properties>
<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><!-- web依赖 --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><!-- aop --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-aop</artifactId></dependency><!-- mybatis --><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>${mybatis.version}</version></dependency><!-- mysql --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>${mysql.version}</version></dependency><!-- druid mysql数据库连接池--><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>${alibabadruid.version}</version></dependency><!-- lombok 工具 --><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>${lombok.version}</version></dependency></dependencies>

2、mysql主从配置

自定义mysql的主从数据源的连接参数，以及mybatis的配置

server:port: 8082# 自定义mysql配置
mysql:datasource:master:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://ip1:port/demo?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=trueusername: xxxpassword: xxxinitial-size: 5max-active: 20min-idle: 5max-wait: 60000time-between-eviction-runs-millis: 60000min-evictable-idle-time-millis: 300000max-evictable-idle-time-millis: 900000validation-query: SELECT 1 FROM DUALtest-while-idle: truetest-on-borrow: falsetest-on-return: falsepool-prepared-statements: truemax-open-prepared-statements: 50max-pool-prepared-statement-per-connection-size: 20filters: stat,walluse-global-data-source-stat: trueconnect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000slave:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://ip2:port/demo?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=trueusername: xxxx # 这里的账号最好是只读权限的mysql用户,从库只负责读,不能写入数据password: xxxxinitial-size: 5max-active: 25min-idle: 5max-wait: 60000time-between-eviction-runs-millis: 60000min-evictable-idle-time-millis: 300000max-evictable-idle-time-millis: 900000validation-query: SELECT 1 FROM DUALtest-while-idle: truetest-on-borrow: falsetest-on-return: falsepool-prepared-statements: truemax-open-prepared-statements: 50max-pool-prepared-statement-per-connection-size: 20filters: stat,walluse-global-data-source-stat: trueconnect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000# 指定mapper*.xml加载位置
mybatis:config-location: classpath:mybatis/mybatis-config.xmlmapper-locations: classpath:mybatis/mapper/*.xml

3、数据源配置

1. DataSourceConfig 数据源配置类

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.datasource.demo.enums.DataSourceType;
import org.springframework.beans.factory.annotation.Qualifier;
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 java.util.HashMap;
import java.util.Map;/*** ClassName: DataSourceConfig* Package: com.datasource.demo.config* Description:* 数据源配置** @Author wfk* @Create 2024/11/12 14:15* @Version 1.0*/
@Configuration
public class DataSourceConfig {/*** 主库数据源** @return*/@Bean("master")@ConfigurationProperties(prefix = "mysql.datasource.master")public DruidDataSource dataSource1() {return DruidDataSourceBuilder.create().build();}/*** 从库数据源** @return*/@Bean("slave")@ConfigurationProperties(prefix = "mysql.datasource.slave")public DruidDataSource dataSource2() {return DruidDataSourceBuilder.create().build();}/*** 配置默认数据源** @param masterDataSource* @param slaveDataSource** 必须要加 @Primary 注解,优先下面的配置* @return*/@Primary@Bean("dynamicDataSource")public DynamicDataSource dataSource(@Qualifier("master") DruidDataSource masterDataSource,@Qualifier("slave") DruidDataSource slaveDataSource) {Map<Object, Object> targetDataSources = new HashMap<>();targetDataSources.put(DataSourceType.MASTER.getName(), masterDataSource);targetDataSources.put(DataSourceType.SLAVE.getName(), slaveDataSource);DynamicDataSource dynamicDataSource = new DynamicDataSource();dynamicDataSource.setTargetDataSources(targetDataSources);dynamicDataSource.setDefaultTargetDataSource(masterDataSource);return dynamicDataSource;}
}

以上源码分析：

• 采用了阿里云的Druid数据库连接池，所以需要使用 DruidDataSource

• @ConfigurationProperties(prefix = “mysql.datasource.slave”) 加载 yml 配置文件的自定义属性，自定义参数名称需要和 druid 的配置的标准名称一样，不然无法自动加载

• DynamicDataSource 继承了 抽象类 AbstractRoutingDataSource，是实现动态数据源的核心类，将所有数据源注入到这个类中，通过 DataSourceContextHolder 修改数据源，实现动态切换。

• @Primary 注解必须要加上，标记为优先使用的数据源

2.DataSourceContextHolder 数据源上下文

/*** ClassName: DataSourceContextHolder* Package: com.datasource.demo.config.datasource* Description:* 本地线程，数据源上下文* @Author wfk* @Create 2024/11/12 14:45* @Version 1.0*/
public class DataSourceContextHolder {// 定义一个 ThreadLocal 变量，用于保存当前线程的数据源标识private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();// 设置当前线程的数据源标识public static void setDataSource(String dataSource){contextHolder.set(dataSource);}// 获取当前线程的数据源标识public static String getDataSource(){return contextHolder.get();}// 清除当前线程的数据源标识public static void clearDataSource(){contextHolder.remove();}
}

以上源码分析：

• ThreadLocal 是一个线程局部变量容器，每个线程都有自己独立的副本。这意味着每个线程都可以独立地设置和获取自己的 ThreadLocal 变量值，而不会影响其他线程。

• setDataSource(String dataSource) 方法用于设置当前线程的数据源标识。调用这个方法时，传入的数据源标识会被保存在当前线程的 ThreadLocal 变量中。

• getDataSource() 方法用于获取当前线程的数据源标识。这个方法通常在数据源路由逻辑中被调用，根据当前线程的数据源标识选择合适的数据源进行数据库操作。

• clearDataSource() 方法用于清除当前线程的数据源标识。这个方法通常在请求处理完毕后被调用，以释放资源并防止内存泄漏。

3.DynamicDataSource 数据源路由

import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;/*** ClassName: DynamicDataSource* Package: com.datasource.demo.config.datasource* Description:* * @Author wfk* @Create 2024/11/12 14:42* @Version 1.0*/
@Slf4j
public class DynamicDataSource extends AbstractRoutingDataSource {@Overrideprotected Object determineCurrentLookupKey() {String dataSource = DataSourceContextHolder.getDataSource();log.info("当前数据源 {}", dataSource);return dataSource;}
}

以上源码分析：

• AbstractRoutingDataSource 是 Spring 框架提供的一个抽象类，用于实现数据源的动态切换。它提供了一个 determineCurrentLookupKey 方法，该方法返回一个键值，用于从配置的数据源映射中查找当前应使用的数据源。

• determineCurrentLookupKey 方法用于确定当前线程应该使用哪个数据源，通过调用DataSourceContextHolder.getDataSource() 来获取当前线程的数据源标识

4.DataSourceType 自定义数据源类别枚举

public enum DataSourceType {MASTER("master"),SLAVE("slave");private String name;DataSourceType(String name) {this.name = name;}public String getName() {return name;}
}

4、数据源切换

1.自定义注解实现

自定义注解，作用于方法上，通过AOP切面拦截，根据注解的value所对应的数据库源类别，实现数据源切换。

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DBDataSource {DataSourceType value() default DataSourceType.MASTER;
}

2.AOP切面实现

import com.datasource.demo.annotions.DBDataSource;
import com.datasource.demo.config.datasource.DataSourceContextHolder;
import com.datasource.demo.enums.DataSourceType;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.stereotype.Component;
import org.aspectj.lang.annotation.Aspect;import java.lang.reflect.Method;/*** ClassName: DataSourceAspect* Package: com.datasource.demo.aspect* Description:* 数据源AOP切面** @Author wfk* @Create 2024/11/12 16:05* @Version 1.0*/@Slf4j
@Aspect
@Component
public class DataSourceAspect {@Pointcut("execution(* com.datasource.demo.service..*.*(..))")public void aspect() {}@Before("aspect()")private void doBefore(JoinPoint joinPoint) {Object target = joinPoint.getTarget();Class<?> clazz = target.getClass();// 获取方法签名MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();// 获取方法名称String methodName = methodSignature.getName();// 获取方法参数列表Class<?>[] parameterTypes = methodSignature.getMethod().getParameterTypes();try {// 通过方法名称和参数列表可以唯一获取方法(可能有重载的同名方法)Method method = clazz.getMethod(methodName, parameterTypes);// 判断方法是否存在指定的注解if (method !=null && method.isAnnotationPresent(DBDataSource.class)){DBDataSource annotation = method.getAnnotation(DBDataSource.class);// 如果是从库那就切换当前线程的数据源if (DataSourceType.SLAVE == annotation.value()) {DataSourceContextHolder.setDataSource(DataSourceType.SLAVE.getName());return;}}} catch (NoSuchMethodException e) {e.printStackTrace();}// 默认是使用主库DataSourceContextHolder.setDataSource(DataSourceType.MASTER.getName());}
}

以上源码分析：

1.@Pointcut("execution( com.datasource.demo.service….(…))")*

• execution()：这是定义切入点表达式的关键字，用来匹配Java方法的执行连接点。
• *：第一个星号表示返回值类型，这里的星号意味着匹配任何返回类型的方法。‘com.datasource.demo.service…*.*(…)：这部分是切入点表达式的主体，指定了要匹配的方法的位置和名称。
  • com.datasource.demo.service：这是包名，指明了要匹配的方法所在的包。
  • ..：两个点号表示该包下的所有子包。
  • *.*：第一个星号代表类名，第二个星号代表方法名，这里使用两个星号表示匹配该包及其子包下所有类的所有方法。
  • (..)：括号内的两个点号表示参数列表，这里表示匹配任何参数列表的方法。

2.方法调用和注解检查

• 获取目标对象和方法签名。
• 通过反射获取方法对象，并检查方法是否标注了 DBDataSource 注解。
• 如果方法标注了 DBDataSource 注解且注解值为 DataSourceType.SLAVE，则设置当前线程的数据源为从库。
• 否则，默认设置当前线程的数据源为主库。

3.最终实现如下

在service层的实现类中，添加注解，查询相关的业务，通过注解指定从库，写入数据默认使用主库，实现读写分离。

@DBDataSource(DataSourceType.SLAVE)
@Override
public List<BookInfoPO> getBookInfoList() {return bookInfoMapper.getBookInfoListMapper();
}

总结

关于数据源切换还有很多种方式

• 在mapper层做拦截，对insert、update、delete 和 select 完全分离，可以通过MyCat、shardingsphere 等数据库中间件实现自动分离。
• 也可以通过规范方法名前缀，对get、find、query 开头等方法进行拦截，也可以实现读写分离。