数据库主从复制,读写分离,分库分表查询

主从复制

背景

1. 业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的,读操作则是在各从服务器上进行。
2. 如果配置了多个从服务器或者多个主服务器又涉及到相应的负载均衡问题。先考虑一主一从的主从复制功能。

原理

	MySQL之间数据复制的基础是二进制日志文件(binary log file)。一台MySQL数据库一旦启用二进制日志后,其作为master,
它的数据库中所有操作都会以“事件”的方式记录在二进制日志中,其他数据库作为slave通过一个I/O线程与主服务器保持通信,并监控
master的二进制日志文件的变化,如果发现master二进制日志文件发生变化,则会把变化复制到自己的中继日志中,然后slave的一个
SQL线程会把相关的“事件”执行到自己的数据库中,以此实现从数据库和主数据库的一致性,也就实现了主从复制。

实战

docker安装mysql

docker使用
1. 连接Mysql后,导入表
2. 克隆一个备份,再次连接
在这里插入图片描述

主从配置

主服务器配置
1. 开启二进制日志
2. 配置唯一的server-id
3. 获得master二进制日志文件名及位置
4. 创建一个用于slave和master通信的用户账号

实现:
1. 找到数据卷映射的mysqld.cnf文件(例如:/var/lib/docker/volumes/mysql_mysql-conf/_data/mysql.conf.d)
2. 修改配置
3. 连接mysql,创建用户并赋予权限 
如(GRANT REPLICATION SLAVE ON *.* TO 'wzh'@'%' IDENTIFIED BY '123456')
pid-file    = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir    = /var/lib/mysql

symbolic-links=0

character-set-server = utf8   
#skip-networking  
innodb_print_all_deadlocks = 1
max_connections = 2000  
max_connect_errors = 6000  
open_files_limit = 65535  
table_open_cache = 128   
max_allowed_packet = 4M  
binlog_cache_size = 1M  
max_heap_table_size = 8M  
tmp_table_size = 16M  
  
read_buffer_size = 2M  
read_rnd_buffer_size = 8M  
sort_buffer_size = 8M  
join_buffer_size = 28M  
key_buffer_size = 4M  
  
thread_cache_size = 8  
  
query_cache_type = 1  
query_cache_size = 8M  
query_cache_limit = 2M  
  
ft_min_word_len = 4  
  
log-bin = mysql-bin
server-id = 1
binlog_format = mixed  
 
performance_schema = 0  
explicit_defaults_for_timestamp  
  
#lower_case_table_names = 1  
  
interactive_timeout = 28800  
wait_timeout = 28800  

# Recommended in standard MySQL setup  
  
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES   
  
[mysqldump]  
quick  
max_allowed_packet = 16M  
  
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
从服务器配置
1. 配置唯一的server-id
2. 使用master分配的用户账号读取master二进制日志
3. 启用slave服务
实现:
4. 同主位置修改配置
pid-file    = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir    = /var/lib/mysql

symbolic-links=0

character-set-server = utf8   
#skip-networking  
innodb_print_all_deadlocks = 1
max_connections = 2000  
max_connect_errors = 6000  
open_files_limit = 65535  
table_open_cache = 128   
max_allowed_packet = 4M  
binlog_cache_size = 1M  
max_heap_table_size = 8M  
tmp_table_size = 16M  
  
read_buffer_size = 2M  
read_rnd_buffer_size = 8M  
sort_buffer_size = 8M  
join_buffer_size = 28M  
key_buffer_size = 4M  
  
thread_cache_size = 8  
  
query_cache_type = 1  
query_cache_size = 8M  
query_cache_limit = 2M  
  
ft_min_word_len = 4  
  
log-bin = mysql-bin
server-id = 2
binlog_format = mixed  
 
performance_schema = 0  
explicit_defaults_for_timestamp  
  
#lower_case_table_names = 1  
  
interactive_timeout = 28800  
wait_timeout = 28800  

# Recommended in standard MySQL setup  
  
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES   
  
[mysqldump]  
quick  
max_allowed_packet = 16M  
  
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
master库,查询show master status;

在这里插入图片描述

slave库CHANGE MASTER TO MASTER_HOST='192.168.79.130',MASTER_USER='wzh',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1329;
start slave;
show slave status;

在这里插入图片描述
两者都为yes即可成功

注意事项
1. docker-compose复制过来修改配置后需要重启 docker-compose restart
2. 复制从库后,需要修改mysql的uuid,find -name auto.cnf找到位置后修改,使主从不一致即可
3. 默认主从复制所有库,可以配置指定读写库
4. 如果主从表结构不同,修改主表后,slave-sql-running会变为No,需要stop slave后,修改表结构,重新建立连接启动。
因此,为了保持同步, 可以给从库添加一个只读权限的用户,从库不写只读,实现读写分离。
# 不同步哪些数据库  
binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema  
  
# 只同步哪些数据库,除此之外,其他不同步  
binlog-do-db = game  

读写分离

目录结构

项目结构

在这里插入图片描述

表结构

在这里插入图片描述

配置多数据源

  1. application.yml
server:
  port: 8080
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    datasource-user:
      jdbcUrl: jdbc:mysql://192.168.79.130:3306/user?characterEncoding=utf-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true&useSSL=true&zeroDateTimeBehavior=convertToNull&autoReconnect = true
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: 123456
    datasource-user-read:
      jdbcUrl: jdbc:mysql://192.168.79.131:3306/user?characterEncoding=utf-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true&useSSL=true&zeroDateTimeBehavior=convertToNull&autoReconnect = true
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: wzh
      password: yali
    datasource-order:
      jdbcUrl: jdbc:mysql://192.168.79.130:3306/order?characterEncoding=utf-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true&useSSL=true&zeroDateTimeBehavior=convertToNull&autoReconnect = true
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: 123456
    datasource-order-read:
      jdbcUrl: jdbc:mysql://192.168.79.131:3306/order?characterEncoding=utf-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true&useSSL=true&zeroDateTimeBehavior=convertToNull&autoReconnect = true
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: wzh
      password: yali
mybatis:
  mapper-locations: classpath:/mappers/**/*.xml
logging:
  level:
    com.shyb: debug

  1. DbType
public enum DbType {
    DATASOURCE_USER,
    DATASOURCE_USER_READ,
    DATASOURCE_ORDER,
    DATASOURCE_ORDER_READ
}
  1. CustomerContextHolder
public class CustomerContextHolder {
    private static final ThreadLocal<DbType> contextHolder = new ThreadLocal<DbType>();

    public static void setCustomerType(DbType dbType){
        contextHolder.set(dbType);
    }

    public static DbType getCustomerType(){
        return contextHolder.get();
    }

    public static void clearCustomerType(){
        contextHolder.remove();
    }
}
  1. DynamicDataSource
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return CustomerContextHolder.getCustomerType();
    }
}
  1. DatasourceConfig
package com.shyb.masterslave.config;

import org.springframework.beans.factory.annotation.Value;
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.Primary;

import javax.sql.DataSource;

/**
 * @author wzh
 * @date 2019/6/11 - 10:32
 */
@Configuration
public class DatasourceConfig {
    @Value("${spring.datasource.type}")
    private Class datasourceType;
    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.datasource-user")
    public DataSource dataSourceUser(){
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.datasource-user-read")
    public DataSource dataSourceUserRead(){
    	//可以指定datasource类型,默认使用HikariDatasource  不同数据源配置不同 前面yml用的HikariDatasource的
        return DataSourceBuilder.create().type(datasourceType).build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.datasource-order")
    public DataSource dataSourceOrder(){
        return DataSourceBuilder.create().build();
    }

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

	@Bean
    public DynamicDataSource dynamicDataSource(@Qualifier("dataSourceUser") DataSource dataSourceUser,
                                               @Qualifier("dataSourceUserRead") DataSource dataSourceUserRead,
                                               @Qualifier("dataSourceOrder") DataSource dataSourceOrder,
                                               @Qualifier("dataSourceOrderRead") DataSource dataSourceOrderRead) {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        Map<Object, Object> targetDataSource = new HashMap<Object, Object>();
        targetDataSource.put(DbType.DATASOURCE_USER,dataSourceUser);
        targetDataSource.put(DbType.DATASOURCE_USER_READ,dataSourceUserRead);
        targetDataSource.put(DbType.DATASOURCE_ORDER,dataSourceOrder);
        targetDataSource.put(DbType.DATASOURCE_ORDER_READ,dataSourceOrderRead);
        dynamicDataSource.setTargetDataSources(targetDataSource);
        dynamicDataSource.setDefaultTargetDataSource(dataSourceUser);
        return dynamicDataSource;
    }
}

  1. MybatisConfig
@EnableTransactionManagement
@Configuration
public class MybatisConfig {
    @Autowired
    DynamicDataSource dynamicDataSource;
	@Value("${mybatis.mapper-locations}")
    String mapperLocations;
    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dynamicDataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public PlatformTransactionManager platformTransactionManager(){
        return new DataSourceTransactionManager(dynamicDataSource);
    }
}
  1. DatasourceAop
package com.shyb.masterslave.datasource;

import lombok.extern.slf4j.Slf4j;
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.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import java.util.Arrays;

/**
 * @author wzh
 * @date 2019/6/13 - 16:04
 */
@Aspect
@Component
@Slf4j
@Order(0)
public class DatasourceAop {
    /**
     * mapper层只读方法
     */
    private static final String[] reads = {"countByExample","selectByExample","selectByPrimaryKey","selectByExampleWithBLOBs"};
    /**
     * 当前线程是否使用过主库
     */
    private static final ThreadLocal<Boolean> FLAG = new ThreadLocal<Boolean>();
    @Pointcut("execution(* com.shyb.masterslave.mapper.user..*.*(..))")
    public void user(){
    };
    @Pointcut("execution(* com.shyb.masterslave.mapper.order..*.*(..))")
    public void order(){
    }


    @Before("user()")
    public void userDatasource(JoinPoint joinPoint){
        if(!isUserMaster() && isReadMethod(joinPoint)){
            CustomerContextHolder.setCustomerType(DbType.DATASOURCE_USER_READ);
        }else{
            FLAG.set(true);
            CustomerContextHolder.setCustomerType(DbType.DATASOURCE_USER);
        }
        log.info("当前数据库:"+CustomerContextHolder.getCustomerType());
    }

    @Before("order()")
    public void orderDatasource(JoinPoint joinPoint){
        if(!isUserMaster() && isReadMethod(joinPoint)){
            CustomerContextHolder.setCustomerType(DbType.DATASOURCE_ORDER_READ);
        }else{
            FLAG.set(true);
            CustomerContextHolder.setCustomerType(DbType.DATASOURCE_ORDER);
        }
        log.info("当前数据库:"+CustomerContextHolder.getCustomerType());
    }

    @After(value = "user() || order()")
    public void clearDatasource(JoinPoint joinPoint){
        CustomerContextHolder.clearCustomerType();
    }
    private Boolean isReadMethod(JoinPoint joinPoint){
        return Arrays.asList(reads).contains(joinPoint.getSignature().getName());
    }
    private Boolean isUserMaster(){
        return FLAG.get() == null ? false : FLAG.get();
    }
}

测试结果

在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值