SpringBoot第 12 讲:SpringBoot+MySQL主从复制、读写分离

云惠网小编 2021年10月12日07:19:27
评论
13327字阅读44分25秒
摘要

一、MySQL主从复制(CentOS7+MySQL5.7)1.1、主从复制的原理1.2、数据库信息10.211.55.4:主库master10.211.55.5:从库slave10.211.55.6:从库slave1.3、启动/停止service mysqld start|stop|restartsystemctl start|stop|restart mysqld1.4、主库设置1.4.1、停止MySQL服务1.4.2、编辑/etc…

广告也精彩
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
start slave;
show slave status\G 

show variables like ‘log_bin%’;

log-bin=mysql-bin # 非必需

 1.5.3.2、执行命令

 

例如:我们有一张表member

 至此,问题解决

1.4、主库设置

一、MySQL主从复制(CentOS7+MySQL5.7)

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.personal.qin.demos</groupId>
<artifactId>mysql_master_slave</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>mysql_master_slave</name>
<description></description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.5.RELEASE</version>
<relativePath /> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- MySQL:Begin -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- MySQL:End -->
<!-- mybatis:Begin -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.0</version>
</dependency>
<!-- mybatis:End -->
<!-- BoneCP连接池(JDBC):Begin -->
<dependency>
<groupId>com.jolbox</groupId>
<artifactId>bonecp-spring</artifactId>
<version>0.8.0.RELEASE</version>
</dependency>
<!-- BoneCP连接池(JDBC):End -->
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>

3.8、定义Service和实现类

vi /var/lib/mysql/auto.cnf 

3.1、创建Maven项目

负责与主机(master)的io通信

service  mysqld  start|stop|restart
systemctl  start|stop|restart  mysqld

server-id=2

 

application.yum

首先,我们定义一个枚举来代表这三个数据源

1.5.6、设置从库为只读账户

 

关于MyBatis的详细设置请参考:SpringBoot第 7 讲:SpringBoot+MyBatis_秦毅翔的专栏-CSDN博客

1.4.3.1、启动MySQL

CREATE USER [email protected]%’ IDENTIFIED BY ‘Aa123123.’;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.211.55.%';
FLUSH PRIVILEGES;

四、源代码

1.3、启动/停止

1.5.5.1、重启mysql,再次登录MySQL

 

 2.1、Slave_SQL_Running:No

1.4.3、创建用于数据同步的账户

package com.cjs.example.enums;
public enum DBTypeEnum {
MASTER, SLAVE1, SLAVE2;
}

1.5、从库设置

3.4、MyBatista配置MyBatisConfig

1.5.5.2、查看从库状态

 show slave status\G    

2.2.1、问题1:UUIDs相同

2.2.2.3、找到问题:发现Master_Log_File没有对应 

show master status\G    

登录MySQL客户端 

package demo.datasource.config;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
/**
* MyBatis配置
*/
@EnableTransactionManagement
@Configuration
public class MyBatisConfig {
@Resource(name = "myRoutingDataSource")
private DataSource myRoutingDataSource;
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
// 设置数据源
sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
// 添加一行如下代码,解决setTypeAliasesPackage别名设置不起作用问题
sqlSessionFactoryBean.setVfs(SpringBootVFS.class);
//设置mybatis的主配置文件
ResourcePatternResolver resourceResolver = new PathMatchingResourcePatternResolver();
org.springframework.core.io.Resource mybatisConfigXml = resourceResolver.getResource(Cast.MYBATIS_CONFIG);
sqlSessionFactoryBean.setConfigLocation(mybatisConfigXml);
// 设置别名包
sqlSessionFactoryBean.setTypeAliasesPackage(Cast.PO_PACKAGE);
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
sqlSessionFactoryBean.setMapperLocations(resolver.getResources(Cast.MAPPER_SCANNER));
return sqlSessionFactoryBean.getObject();
}
@Bean
public PlatformTransactionManager platformTransactionManager() {
return new DataSourceTransactionManager(myRoutingDataSource);
}
}

 10.211.55.6:从库slave

有一般情况就有特殊情况,特殊情况是某些情况下我们需要强制读主库,针对这种情况,我们定义一个注解,用该主句标注的Service方法就读主库

create user [email protected]%’ identified by ‘Aa123123.’;
grant select on test.* to [email protected]%’
flush privileges;
package com.cjs.example.annotation;
public @interface Master {
}

1.5.3.1、启动MySQL、登录MySQL

package com.cjs.example.bean;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.lang.Nullable;
public class MyRoutingDataSource extends AbstractRoutingDataSource {
@Nullable
@Override
protected Object determineCurrentLookupKey() {
return DBContextHolder.get();
}
}

2.2.2、问题2:主从日志文件mysql-bin不对应

show slave status\G

https://download.csdn.net/download/qzc70919700/30565891​​​​​​​

3.5、设置路由key/查找数据源 

 

3.6、定义切面

当我们配置好主从同步后,如果重启服务器或者其他原因导致服务器宕机,则数据将无法同步,以下两种情况最常见

server-id=1    # 必需

三、SpringBoot+MySQL读写分离

package demo.datasource.aop;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;
import demo.datasource.config.bean.DBContextHolder;
/**
* 设置路由key
* 默认情况下,所有的查询都走从库,插入/修改/删除走主库。我们通过方法名来区分操作类型(CRUD)
*/
@Aspect
@Component
public class DataSourceAop {
@Pointcut("[email protected](demo.datasource.annotation.Master) " +
"&& (execution(* demo.datasource.service..*.select*(..)) " +
"|| execution(* demo.datasource.service..*.get*(..)))")
public void readPointcut() {
}
@Pointcut("@annotation(demo.datasource.annotation.Master) " +
"|| execution(* demo.datasource.service..*.insert*(..)) " +
"|| execution(* demo.datasource.service..*.add*(..)) " +
"|| execution(* demo.datasource.service..*.update*(..)) " +
"|| execution(* demo.datasource.service..*.edit*(..)) " +
"|| execution(* demo.datasource.service..*.delete*(..)) " +
"|| execution(* demo.datasource.service..*.remove*(..))")
public void writePointcut() {
}
@Before("readPointcut()")
public void read() {
DBContextHolder.slave();
}
@Before("writePointcut()")
public void write() {
DBContextHolder.master();
}
/**
* 另一种写法:if...else...  判断哪些需要读从数据库,其余的走主数据库
*/
//    @Before("execution(* demo.datasource.service.impl.*.*(..))")
//    public void before(JoinPoint jp) {
//        String methodName = jp.getSignature().getName();
//
//        if (StringUtils.startsWithAny(methodName, "get", "select", "find")) {
//            DBContextHolder.slave();
//        }else {
//            DBContextHolder.master();
//        }
//    }
}

数据库效果:

由于两台从服务器(slave)是通过主服务器(master)克隆来的,所以三台机器安装MySQL的UUIDs是相同的,我们需要修改两台从服务器(slave)的UUIDs的值,让3台服务器的UUID值都不相同

Ps:这里需要记住File和Position字段的值,在从库的设置中会用到 

1.5.4、修改从库UUID

 1.4.3.3、依次执行命令

1.4.1、停止MySQL服务

这里,我们配置了4个数据源,1个master,2两个slave,1个路由数据源。前3个数据源都是为了生成第4个数据源,而且后续我们只用这最后一个路由数据源。

负责自己的slave mysql进程,用于update数据

2.2、Slave_IO_Running:No 

目标数据源就是那前3个这个我们是知道的,但是使用的时候是如果查找数据源的呢?

1.5.5、查看从库slave状态

2.2.2.4、解决问题:在出现Slave_IO_Running:No的从服务器上操作

10.211.55.5:从库slave

slave stop;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000026', MASTER_LOG_POS=0;
slave start;
show slave status\G

1.2、数据库信息

 执行查询时控制台效果,会在两个从库中轮询:

解决办法:修改从库UUIDs

vi /var/lib/mysql/auto.cnf

1.5.1、停止MySQL服务

1.4.3.2、登录MySQL客户端

1.5.3、执行同步语句,并启动slave 

Ps:由于两个从库的Linux服务器是从主库Linux服务器克隆来的,所以会导致主库和2个从库的MySQL数据库的UUID相同,这时我们需要修改2个从库的UUID否则从库slave_io_runnint值一致为no;只要保证3个数据库UUID值不一样就行

3.7、添加例外

 

3.3、配置数据源

由于Spring容器中现在有4个数据源,所以我们需要为事务管理器和MyBatis手动指定一个明确的数据源。

至此,问题解决

二、MySQL主从复制容易出现的问题

1.4.4、查看主库master状态 

3.9、使用单元测试进行测试

2.2.2.1、查看主服务器(master) 

 10.211.55.4:主库master

package demo.datasource.test;
import java.util.List;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import demo.datasource.entity.Member;
import demo.datasource.service.MemberService;
@RunWith(SpringRunner.class)
@SpringBootTest
public class DatasourceDemoApplicationTests {
@Autowired
private MemberService memberService;
@Test
public void testWrite() {
Member member = new Member();
member.setName("mike");
memberService.insert(member);
}
@Test
public void testRead() {
for (int i = 0; i < 4; i++) {
List<Member> selectAll = memberService.selectAll();
for(Member m : selectAll) {
System.out.println(m);
}
}
}
@Test
public void testSave() {
Member member = new Member();
member.setName("wangwu");
memberService.save(member);
}
@Test
public void testReadFromMaster() {
memberService.getToken("1234");
}
}

设置路由key

package demo.datasource.config;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
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 demo.datasource.config.bean.MyRoutingDataSource;
import demo.datasource.config.enums.DBTypeEnum;
/**
* 多数据源配置
*/
@Configuration
public class DataSourceConfig {
@Bean
@ConfigurationProperties("spring.datasource.master")
public DataSource masterDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.slave1")
public DataSource slave1DataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.slave2")
public DataSource slave2DataSource() {
return DataSourceBuilder.create().build();
}
@Bean
public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
@Qualifier("slave1DataSource") DataSource slave1DataSource,
@Qualifier("slave2DataSource") DataSource slave2DataSource) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource);
targetDataSources.put(DBTypeEnum.SLAVE2, slave2DataSource);
MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
myRoutingDataSource.setTargetDataSources(targetDataSources);
return myRoutingDataSource;
}
}

1.1、主从复制的原理

3.2、修改pom.xml

package demo.datasource.config.bean;
import java.util.concurrent.atomic.AtomicInteger;
import demo.datasource.config.enums.DBTypeEnum;
import demo.datasource.utils.Log;
/**
* 通过ThreadLocal将数据源设置到每个线程上下文中
*/
public class DBContextHolder {
private static final ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<>();
private static final AtomicInteger counter = new AtomicInteger(-1);
public static void set(DBTypeEnum dbType) {
contextHolder.set(dbType);
}
public static DBTypeEnum get() {
return contextHolder.get();
}
public static void master() {
set(DBTypeEnum.MASTER);
Log.i(DBContextHolder.class, "切换到master");
}
public static void slave() {
//  轮询
int index = counter.getAndIncrement() % 2;
if (counter.get() > 9999) {
counter.set(-1);
}
Log.i(DBContextHolder.class, "轮询index:"+index);
if (index == 0) {
set(DBTypeEnum.SLAVE1);
Log.i(DBContextHolder.class, "切换到slave1");
} else {
set(DBTypeEnum.SLAVE2);
Log.i(DBContextHolder.class, "切换到slave2");
}
}
}

解决办法如下,进入mysql客户端,依次执行命令:

接下来,通过ThreadLocal将数据源设置到每个线程的上下文中

package demo.datasource.service.impl;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import demo.datasource.annotation.Master;
import demo.datasource.entity.Member;
import demo.datasource.mapper.MemberMapper;
import demo.datasource.service.MemberService;
@Service
public class MemberServiceImpl implements MemberService {
@Autowired
private MemberMapper memberMapper;
//    @Transactional
@Override
public int insert(Member member) {
return memberMapper.insert(member);
}
@Master
@Override
public int save(Member member) {
return memberMapper.insert(member);
}
@Override
public List<Member> selectAll() {
return memberMapper.selectByExample();
}
@Master
@Override
public String getToken(String appId) {
//  有些读操作必须读主数据库
//  比如,获取微信access_token,因为高峰时期主从同步可能延迟
//  这种情况下就必须强制从主数据读
return appId;
}
}
spring:
datasource:
master:
jdbc-url: jdbc:mysql://10.211.55.4:3306/test_datasource
username: root
password: Aa123123.
driver-class-name: com.mysql.jdbc.Driver
slave1:
jdbc-url: jdbc:mysql://10.211.55.5:3306/test_datasource
username: pig   # 只读账户
password: Aa123123.
driver-class-name: com.mysql.jdbc.Driver
slave2:
jdbc-url: jdbc:mysql://10.211.55.6:3306/test_datasource
username: pig   # 只读账户
password: Aa123123.
driver-class-name: com.mysql.jdbc.Driver

change master to master_host='10.211.55.4', master_user='repl', master_password=‘Aa123123.’, master_log_file='mysql-bin.000001', master_log_pos=767; 

show master status;

参考:SpringBoot第 1 讲:HelloWorld_秦毅翔的专栏-CSDN博客

解决办法如下,进入mysql客户端:

1.4.2、编辑/etc/my.cnf文件,在[mysqld]下增加如下两行设置

 1.5.2、编辑/etc/my.cnf文件,在[mysqld]下增加如下设置server-id

2.2.2.2、查看从服务器(slave)

本文转自 https://blog.csdn.net/qzc70919700/article/details/120690398

腾讯云618
云惠网小编
自己开发的音乐视频网站 java

自己开发的音乐视频网站

1.需求开发基于B/S模式的web音乐网站,要包含音乐和mv。为用户提供一个友好的视频和音乐播放环境,包含了大量最新一代的视频和音乐,同时它还具有评论收藏功能,方便以后注册的用户观...
腾讯云618

发表评论