mysql主从复制的原理
首先master服务需要开启binary-log日志文件,
1、master服务会开启一个log dump线程,将所有的DDL,DML,TCL写入BinaryLog的二进制日志文件中
2、slave服务会通过I/O线程请求master服务的bin-log日志文件,并写入到slave服务中的relaylog中继日志文件中
3、slave服务再通过sql线程读取relayLog日志,并解析成具体操作,来实现主从的操作一致,从而达到最终的数据一致。
一、搭建mysql主从服务
【模拟服务】:通过两个不同的端口号进行模拟两台mysql服务。
安装mysql服务。将master服务端口号定义为33337,将slave服务端口号定义为33338。
1、master - 修改mysql配置配置文件(my.ini)。
配置中有的就覆盖,配置中没有的就添加
[client]
port=33337
[mysqld]
#二进制文件
log-bin=mysql-bin
#服务id
server-id=1 //master服务和slave服务的selver-id一定不能相同
port=33337
basedir="D:/software/mysql/master"
datadir="D:/software/mysql/master/Data"
secure-file-priv="D:/software/mysql/master/Uploads"
slave - 修改mysql配置配置文件(my.ini)。
配置中有的就覆盖,配置中没有的就添加
[client]
port=33338
[mysqld]
#二进制文件
log-bin=mysql-bin
#服务id
server-id=100 //master服务和slave服务的selver-id一定不能相同
port=33338
basedir="D:/software/mysql/slave"
datadir="D:/software/mysql/slave/Data"
secure-file-priv="D:/software/mysql/slave/Uploads"
2、通过管理员身份打开打开cmd,然后分别进入master和slave的bin目录下
执行安装命令。
# master
mysqld --install master --defaults-file="D:\software\mysql\master\my.ini"
# slave
mysqld --install master --defaults-file="D:\software\mysql\slave\my.ini"
3、通过命令打开mysql服务。
net start 服务名
net start master //打开master服务
net start slave //打开slave服务
net stop master //关闭master服务
net stop slave //关闭slave服务
【注意】:每次修改完配置文件一定要重启服务
如果启动服务报错了,在安装mysql服务的data文件中找到.err文件,查看启动报错日志。如果是复制之前安装好的mysql服务,在启动的两台或多态服务的时候会报uuids相同。这时只用在data文件中找到auto.cnf文件并删除就行,然后重启服务。
4、通过连接mysql服务中连接好master服务和slave服务。分别进行服务主从服务配置
[1]:配置master服务
# 在master服务中授予slaveuser账号,密码为root,ip为192.168.10.1,查看ip的方式ipconfig
a: grant replication slave on *.* to 'slaveuser'@'192.168.10.1'identified by 'root'
# 查看master服务的状态
b: show master status
[2]:配置slave服务
# 身份认证
a: change master to
master_host='192.168.10.1', #master IP
master_user='slaveuser', #master数据库通过GRANT授权的账号
master_password='root', #master数据库通过GRANT授权的密码
master_port=33337, #master数据库的端口
master_log_file='mysql-bin.000010', #master数据库中通过show master status显示的File名称
master_log_pos=457 #master数据库的通过show master status显示的Position的值
# 启动slave服务
b: start slave
# 查看slave服务状态
c: show slave status
当显示为下图的状态表示为主从配置已经成功了
【当slave_sql_Running显示为null的时候,再次运行a步骤时,提示io线程在使用的时候的时候,可以使用 STOP SLAVE IO_THREAD 结束线程。
二、springboot中进行读写分离
1、导入pom依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.5</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.9</version>
</dependency>
</dependencies>
2、配置yml
mybatis-plus:
mapper-locations: classpath:mapper/*Mapper.xml
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
call-setters-on-nulls: true
type-aliases-package: com.mmm.domain
spring:
main:
allow-bean-definition-overriding: true #bean名称相同的话,允许覆盖,后者覆盖前者
application: aop-server
datasource:
master:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:33337/aaaa?useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai&useunicode=true&characterEncoding=utf-8
username: root
password: root
slave:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:33338/aaaa?useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai&useunicode=true&characterEncoding=utf-8
username: root
password: root
3、写配置文件
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class RoutingDataSourceHolder {
private static final Logger logger = LoggerFactory.getLogger(MyRoutingDataSource.class);
private static final ThreadLocal<String> dataSources = new ThreadLocal<>();
public static void setDataSource(String dataSourceName){
if(dataSources.get() == null){
dataSources.set(dataSourceName);
logger.info("设置数据源{}",dataSourceName);
}
}
public static String getDataSource(){
return dataSources.get();
}
public static void clearDataSource(){
dataSources.remove();
}
}
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class MyRoutingDataSource extends AbstractRoutingDataSource {
private static final Logger logger = LoggerFactory.getLogger(MyRoutingDataSource.class);
@Override
protected Object determineCurrentLookupKey() {
String dataSource = RoutingDataSourceHolder.getDataSource();
logger.info("使用的数据源{}",dataSource);
return dataSource;
}
}
import com.alibaba.druid.pool.DruidDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Primary;
import org.springframework.util.StringUtils;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Configuration
public class MyDataSourceConfigurer {
private static final Logger logger = LoggerFactory.getLogger(MyRoutingDataSource.class);
public static final String MASTER_DATASOURCE = "masterDataSource";
public static final String SLAVE_DATASOURCE ="slaveDataSource";
@Bean("masterDataSource")
@ConfigurationProperties(prefix = "spring.datasource.master")
public DruidDataSource getMasterDruidDataSource(){
DruidDataSource build = DataSourceBuilder.create().type(DruidDataSource.class).build();
logger.info("配置主数据库{}",build);
return build;
}
@Bean("slaveDataSource")
@ConfigurationProperties(prefix = "spring.datasource.slave")
public DruidDataSource getSlaveDruidDataSource(){
DruidDataSource build = DataSourceBuilder.create().type(DruidDataSource.class).build();
logger.info("配置主数据库{}",build);
return build;
}
/**
* Primary 优先使用该Bean
* DependsOn 先执行主从数据库的配置
* Qualifier 指定使用哪个Bean
*
* @param masterDataSource
* @param slaveDataSource
* @return
*/
@Bean
@Primary
@DependsOn(value = {MASTER_DATASOURCE, SLAVE_DATASOURCE})
public DataSource routingDataSource(@Qualifier("masterDataSource") DruidDataSource masterDataSource,
@Qualifier("slaveDataSource") DruidDataSource slaveDataSource){
if(StringUtils.isEmpty(masterDataSource.getUrl())){
logger.info("没有配置时默认使用master数据库");
return masterDataSource;
}
Map<Object ,Object> map = new HashMap<>();
map.put(MyDataSourceConfigurer.MASTER_DATASOURCE,masterDataSource);
map.put(MyDataSourceConfigurer.SLAVE_DATASOURCE,slaveDataSource);
MyRoutingDataSource routing = new MyRoutingDataSource();
//设置动态数据源
routing.setTargetDataSources(map);
routing.setDefaultTargetDataSource(masterDataSource);
logger.info("主从配置完成");
return routing;
}
}
import java.lang.annotation.*;
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE,ElementType.METHOD})
@Documented
public @interface DataSourceWith {
String key() default "";
}
import com.gao.config.RoutingDataSourceHolder;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
@Aspect
@Order(-1)
@Component
public class DataSourceWithAspect {
@Pointcut(value = "@annotation(com.gao.aop.DataSourceWith) || @within(com.gao.aop.DataSourceWith)")
public void myPointCut(){}
@Before("myPointCut()")
public void before(JoinPoint joinPoint){
MethodSignature methodSignature = (MethodSignature)joinPoint.getSignature();
Method method = methodSignature.getMethod();
//获取注解对象
DataSourceWith dataSource = method.getAnnotation(DataSourceWith.class);
if(dataSource == null){
//方法没有就获取类上的
dataSource = method.getDeclaringClass().getAnnotation(DataSourceWith.class);
}
String key = dataSource.key();
RoutingDataSourceHolder.setDataSource(key);
}
@After("myPointCut()")
public void after(JoinPoint joinPoint){
RoutingDataSourceHolder.clearDataSource();
}
}
4、测试
@Controller
@RequestMapping("/hello")
public class MyController {
@Resource
private IUserService userService;
@DataSourceWith(key = MyDataSourceConfigurer.SLAVE_DATASOURCE)
@ResponseBody
@GetMapping("/04")
public List<User> getListUser(){
return userService.getListUser();
}
@DataSourceWith(key = MyDataSourceConfigurer.MASTER_DATASOURCE)
@ResponseBody
@GetMapping("/03")
public String addUser(@RequestParam("name") String name,@RequestParam("age") int age){
userService.addUser(name,age);
return "用户信息插入成功";
}