Mysql 主从复制配置和程序读写分离配置

一、主从复制配置

首先，准备两台服务器，master:192.168.174.10，slave:192.168.174.11。
然后两台服务器上安装Mysql 5.5.5以上版本，因为其默认的执行引擎是支持事务的Innodb。
Centos上安装Mysql。

配置主服务器：

进入 /etc/my.cnf，在[mysqld]标识符下新增以下配置：
server-id =10 # 主服务器标识，唯一即可
log_bin=/usr/local/mysql/data/sql_log/mysql-bin #二进制日志存放路径
binlog-do-db=miaosha # 需要主从复制的数据库，不写默认为全部库
binlog-ignore-db=mysql # 不需要复制的数据库
max_binlog_size = 1000M # 二进制日志文件的最大容量
binlog_format = row # 它不记录sql语句上下文相关信息，仅保存哪条记录被修改
expire_logs_days = 7 # 日志文件保存天数
sync_binlog = 1

bin-log文件：

基本定义：二进制日志，也称为二进制日志，记录对数据发生或潜在发生更改的SQL语句，并以二进制的形式保存在磁盘中；
作用：可以用来查看数据库的变更历史（具体的时间点所有的SQL操作）、数据库增量备份和恢复（增量备份和基于时间点的恢复）、MySQL的复制（主主数据库的复制、主从数据库的复制）。
文件位置：默认存放位置为数据库文件所在目录下，也可以自定义，但是必须是mysql用户的包下。
文件的命名方式： 名称为hostname-bin.xxxxx （重启mysql一次将会自动生成一个新的binlog）

sync_binlog：

sync_binlog=0为默认情况，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。
如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。

配置从服务器：

同样进入 /etc/my.cnf，在[mysqld]标识符下新增以下配置：
server-id = 11 # 从服务器标识，唯一即可
relay_log=/usr/local/mysql/data/sql_log/mysqld-relay-log #relay_log 存放位置，必须是mysql用户的包下
master_info_repository = TABLE
relay_log_info_repository =TABLE
read_only=on # 只读
relay_log_recovery = on # 开启

relay log 文件：

由IO thread线程从主库读取的二进制日志事件组成，该日志被Slave上的SQL thread线程执行，从而实现数据的复制。

master_info_repository :

该文件保存slave连接master的状态以及配置信息，如用户名，密码，日志执行的位置。master_info_repository 配置为TABLE,这些信息会被写入mysql.slave_master_info 表中，代替原来的master.info文件了。使用表来代替原来的文件，主要为了crash-safe replication，从而大大提高从库的可靠性。

relay_log_info_repository :

该文件保存slave上relay log的执行位置。设置为TABLE可以避免relay.info更新不及时，SLAVE 重启后导致的主从复制出错。

relay_log_recovery :

当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。

数据备份与传输：

使用mysqldump 从主库中备份数据保存到all.sql文件，然后将all.sql文件传输给从数据库，最后从数据库执行该all.sql，从而实现数据传输。
mysqldump -u用户名-p密码 数据库 > sql脚本文件路径全名，
上述命令将指定数据库备份到某dump文件（转储文件）中，比如：
mysqldump -uroot -p123456 miaosha > all.sql；
然后将all.sql传输给从服务器：scp all.sql root@192.168.174.11:/home；
最后从服务器执行all.sql文件，实现数据传输：mysql –u用户名 –p密码 –D数据库 < sql脚本文件路径全名，
示例：mysql -uroot -p123456 -Dmiaosha < /home/all.sql

在主服务器上创建一个用户user，并且赋予REPLICATION SLAVE 权限：

登录主服务器的Mysql数据库，执行以下语句：
mysql> CREATE USER user@'192.168.174.%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON * . * TO user@'192.168.174.%';
192.168.174.%：表示192.168.174.0~192.168.174.255范围内的服务器都可以使用user用户。
这样子，从服务器就可以通过user用户访问主服务器的二进制日志文件，从而实现数据的复制。
同时，使用 show master status命令，查看主服务器日志文件信息，后续从服务器上创建复制链路需要这些参数。


主服务器日志文件信息

从服务器上创建复制链路：

登录从服务器的Mysql数据库，执行以下语句：
CHANGE MASTER TO
-> MASTER_HOST='192.168.174.10', # 主服务器ip
-> MASTER_USER='user', # 主服务器上创建的user用户
-> MASTER_PASSWORD='123456', # user用户密码
-> MASTER_LOG_FILE='mysql-bin.000001', # 为master中的二进制日志文件，与上面show master status结果的File一致
-> MASTER_LOG_POS=501; # master中二进制日志文件的起始复制位置，与上面show master status结果的Position一致
然后，开启从服务器的链路，执行 start slave 命令;至此，我们就实现了主从复制，我们可以通过执行 show slave status\G 命令来查询是否开启成功。


从服务器连接状态

二、程序读写分离配置

本文主从数据库的切换需要用到AOP，所以直接引用jar包；

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>

配置文件application.yml的数据库配置：

spring:
  datasource:
    master:
      driverClassName: com.mysql.jdbc.Driver
      url: jdbc:mysql://192.168.174.10:3306/miaosha?useUnicode=true&characterEncoding=utf8&useSSL=true
      username: root
      password: 123456
      type: com.alibaba.druid.pool.DruidDataSource
      max-active: 1000
      initial-size: 100
      min-idle: 500
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      validation-query: select 1
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      max-open-prepared-statements: 20
      #对于长时间不使用的连接强制关闭
      remove-abandoned: true
      #超过5分钟开始关闭空闲连接
      remove-abandoned-timeout: 300
    slave:
      driverClassName: com.mysql.jdbc.Driver
      url: jdbc:mysql://192.168.174.11:3306/miaosha?useUnicode=true&characterEncoding=utf8&useSSL=true
      username: root
      password: 123456
      type: com.alibaba.druid.pool.DruidDataSource
      max-active: 1000
      initial-size: 100
      min-idle: 500
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      validation-query: select 1
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      max-open-prepared-statements: 20
      #对于长时间不使用的连接强制关闭
      remove-abandoned: true
      #超过5分钟开始关闭空闲连接
      remove-abandoned-timeout: 300

数据库配置类 DataSourceConfiguration：

@Configuration
@Slf4j
public class DataSourceConfiguration {

    /**
     * 主数据源
     * @return
     */
    @Bean(name = "writeDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource writeDataSource(){
        log.info("---------------writeDataSource init -----------------");
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }

    /**
     * 从数据源
     * @return
     */
    @Bean(name = "readDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource readDataSource(){
        log.info("---------------readDataSource init -----------------");
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }
}

自定义一个数据库注解，用于标识读写数据库 @DataSource。

package com.imooc.miaosha.config;

import java.lang.annotation.Retention;
import java.lang.annotation.Target;

import static java.lang.annotation.ElementType.METHOD;
import static java.lang.annotation.RetentionPolicy.RUNTIME;

@Retention(RUNTIME)
@Target(METHOD)
public @interface DataSource {
    String value() default "";
}

@DataSource的value有两种，一种是“write”写库，另一种是"read"读库，所以我们写一个枚举类。

package com.imooc.miaosha.enums;

import lombok.AllArgsConstructor;
import lombok.Getter;

@Getter
@AllArgsConstructor
public enum DataSourceType {
    READ("read"),
    WRITE("write");

    private String type;
}

使用ThreadLocal来存放当前线程的读写类型（write或者read），后续会采用AOP获取@DataSource的value值，往ThreadLocal中set进去。

package com.imooc.miaosha.config;

import com.imooc.miaosha.enums.DataSourceType;

/**
 * 本地线程全部变量-数据源
 */
public class DataSourceContextHolder {

    private static final ThreadLocal<String> dataSourceContext = new ThreadLocal<>();

    public static void read(){
        dataSourceContext.set(DataSourceType.READ.getType());
    }

    public static void write(){
        dataSourceContext.set(DataSourceType.WRITE.getType());
    }

    public static String getJdbcType(){
        return dataSourceContext.get();
    }
}

创建MyAbstractRoutingDataSource，继承AbstractRoutingDataSource，并重写determineCurrentLookupKey()方法。每次访问数据库，都会调用getConnection()方法，去获取数据库连接，该方法里面调用了determineTargetDataSource()方法，然后在determineTargetDataSource()方法里面调用了AbstractRoutingDataSource类里的抽象方法determineCurrentLookupKey()。这时候我们需要重写该抽象方法来通过ThreadLocal获取当前的数据库类型的标识（write或者read），从而决定采用哪种数据库。

package com.imooc.miaosha.config;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * 重写determineCurrentLookupKey方法，因为每次获取数据库连接时，会调用getConnection()方法，
 * 该方法里面调用了determineTargetDataSource()方法，然后determineTargetDataSource()方法里面调用了
 * AbstractRoutingDataSource类里的抽象方法determineCurrentLookupKey()。
 */
public class MyAbstractRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getJdbcType();
    }
}

Mybatis配置类 MyBatisConfiguration ：

package com.imooc.miaosha.config;

import com.google.common.collect.Maps;
import com.imooc.miaosha.enums.DataSourceType;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;

@Configuration
public class MyBatisConfiguration {

    @Resource(name = "writeDataSource")
    private DataSource writeDataSource;
    @Resource(name = "readDataSource")
    private DataSource readDataSource;

    @Bean
    public DataSource dynamicDataSource(){
        MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource();
        Map<Object, Object> targetDataSources = Maps.newHashMap();
        targetDataSources.put(DataSourceType.READ.getType(), readDataSource);
        targetDataSources.put(DataSourceType.WRITE.getType(), writeDataSource);
        proxy.setDefaultTargetDataSource(writeDataSource);
        proxy.setTargetDataSources(targetDataSources);
        return proxy;
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dynamicDataSource());
        org.apache.ibatis.session.Configuration configuration = sqlSessionFactoryBean.getObject().getConfiguration();
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setUseGeneratedKeys(true);
        return sqlSessionFactoryBean.getObject();
    }

}

这里将读写数据源的bean放入AbstractRoutingDataSource 中，其中key为数据库标识write或者read，value为对应的读写数据源bean。这样就可以通过ThreadLocal获取到的当前数据库标识，去取对应的数据源bean了，从而实现读写分离。
添加事务管理配置 DataSourceTransactionManager ,因为只有写库涉及到事务，所以只需要将写数据源放入事务管理即可：

package com.imooc.miaosha.config;

import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.Resource;
import javax.sql.DataSource;

@Configuration
@EnableTransactionManagement
public class DataSourceTransactionManager extends DataSourceTransactionManagerAutoConfiguration {

    @Resource(name = "writeDataSource")
    private DataSource writeDataSource;

    @Bean
    public org.springframework.jdbc.datasource.DataSourceTransactionManager transactionManager(){
        return new org.springframework.jdbc.datasource.DataSourceTransactionManager(writeDataSource);
    }
}

使用AOP，读取指定包下@DataSource的值：

package com.imooc.miaosha.aop;

import com.imooc.miaosha.config.DataSource;
import com.imooc.miaosha.config.DataSourceContextHolder;
import com.imooc.miaosha.enums.DataSourceType;
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.reflect.MethodSignature;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;

@Aspect
@Slf4j
@Component
public class DataSourceAop {
    /**
     * @annotation(com.imooc.miaosha.config.DataSource)
     * 所有含有@DataSource 注解的方法都将匹配到
     */
    @Before("@annotation(com.imooc.miaosha.config.DataSource)")
    public void setDataSourceType(JoinPoint joinPoint) {
        Object target = joinPoint.getTarget();
        // 获取方法名称
        String methodName = joinPoint.getSignature().getName();
        // 获取方法参数类型
        Class<?>[] parameterTypes = ((MethodSignature) joinPoint.getSignature()).getMethod().getParameterTypes();
        try {
            Method method = target.getClass().getMethod(methodName, parameterTypes);
            DataSource dataSource = method.getAnnotation(DataSource.class);
            // 为空什么都不做，因为一开始我们设置了master为默认数据库
            if (dataSource == null) return;
            String value = dataSource.value();
            if (DataSourceType.READ.getType().equals(value)){
                DataSourceContextHolder.read();
            }else if (DataSourceType.WRITE.getType().equals(value)){
                DataSourceContextHolder.write();
            }
            log.info("dataSource切换到：{}", value);
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
    }

    /**
     * 方法走完要清空ThreadLocal，否则下次如果没有指定@DataSource注解，
     * 则不会使用默认的master数据源(即写数据源)，而是上一次的数据源
     * @param joinPoint
     */
    @After("@annotation(com.imooc.miaosha.config.DataSource)")
    public void afterSetDataSourceType(JoinPoint joinPoint) {
        DataSourceContextHolder.clearDB();
    }

}

以上配置完成之后，就实现了数据库的读写分离，是不是很给力！

package com.imooc.miaosha.service.impl;

import java.util.List;

import com.imooc.miaosha.config.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.imooc.miaosha.mapper.GoodsMapper;
import com.imooc.miaosha.model.Goods;
import com.imooc.miaosha.model.MiaoshaGoods;
import com.imooc.miaosha.service.GoodsService;
import com.imooc.miaosha.vo.GoodsVO;

@Service
public class GoodsServiceImpl implements GoodsService {

    @Autowired
    private GoodsMapper goodsMapper;

    @Override
    @DataSource("read")
    public List<GoodsVO> getAllGoodsInfo() {
        return goodsMapper.findAllGoodsInfo();
    }

    @Override
    public Goods findOne(Long goodsId) {
        return goodsMapper.findOne(goodsId);
    }

    @Override
    @DataSource("write")
    public boolean reduceMiaoshaStock(Long goodsId) {
        int i = goodsMapper.decreaseStock(goodsId);
        return i == 1;
    }

小结：当一个客户端请求过来，会调用impl包下的service实现类，aop通过扫描实现类中方法上的@DataSource注解，如果没有该注解，则采用默认的写数据源；如果有该注解，则获取注解中的value值，并且set进去ThreadLocal。接着去获取数据源，即调用getConnection()方法，会调用我们重写的determineCurrentLookupKey()方法从ThreadLocal中获取当前的数据源类型，AbstractRoutingDataSource会根据当前的数据源类型，取出对应的数据源，从而执行SQL语句。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,324评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,303评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,192评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,555评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,569评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,566评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,927评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,583评论 0赞 257
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,827评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,590评论 2赞 320
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,669评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,365评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,941评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,928评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,159评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,880评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,399评论 2赞 342

推荐阅读更多精彩内容