mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456';#創(chuàng)建用戶
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';#分配權(quán)限
mysql>flush privileges; #刷新權(quán)限

[mysqld]
server-id=1
log-bin=master-bin
log-bin-index=master-bin.index

[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

CHANGE MASTER TO
  MASTER_HOST='192.168.226.5',
  MASTER_USER='root',
  MASTER_PASSWORD='123456',
  MASTER_LOG_FILE='master-bin.000003',
  MASTER_LOG_POS=154;

# 不同步哪些數(shù)據(jù)庫(kù)
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
# 只同步哪些數(shù)據(jù)庫(kù)，除此之外，其他不同步
binlog-do-db = game

mysql:
 datasource:
 #讀庫(kù)數(shù)目
 num: 1
 type-aliases-package: com.example.dxfl.dao
 mapper-locations: classpath:/mapper/*.xml
 config-location: classpath:/mybatis-config.xml
 write:
 url: jdbc:mysql://192.168.226.5:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=true
 username: root
 password: 123456
 driver-class-name: com.mysql.jdbc.Driver
 read:
 url: jdbc:mysql://192.168.226.6:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=true
 username: root
 password: 123456
 driver-class-name: com.mysql.jdbc.Driver

/**
 * Description 這里切換讀/寫模式
 * 原理是利用ThreadLocal保存當(dāng)前線程是否處于讀模式（通過(guò)開始READ_ONLY注解在開始操作前設(shè)置模式為讀模式，
 * 操作結(jié)束后清除該數(shù)據(jù)，避免內(nèi)存泄漏，同時(shí)也為了后續(xù)在該線程進(jìn)行寫操作時(shí)任然為讀模式
 * @author fxb
 * @date 2018-08-31
 */
public class DbContextHolder {

 private static Logger log = LoggerFactory.getLogger(DbContextHolder.class);
 public static final String WRITE = "write";
 public static final String READ = "read";

 private static ThreadLocal<String> contextHolder= new ThreadLocal<>();

 public static void setDbType(String dbType) {
 if (dbType == null) {
  log.error("dbType為空");
  throw new NullPointerException();
 }
 log.info("設(shè)置dbType為：{}",dbType);
 contextHolder.set(dbType);
 }

 public static String getDbType() {
 return contextHolder.get() == null ? WRITE : contextHolder.get();
 }

 public static void clearDbType() {
 contextHolder.remove();
 }
}

public class MyAbstractRoutingDataSource extends AbstractRoutingDataSource {

 @Value("${mysql.datasource.num}")
 private int num;

 private final Logger log = LoggerFactory.getLogger(this.getClass());

 @Override
 protected Object determineCurrentLookupKey() {
 String typeKey = DbContextHolder.getDbType();
 if (typeKey == DbContextHolder.WRITE) {
  log.info("使用了寫庫(kù)");
  return typeKey;
 }
 //使用隨機(jī)數(shù)決定使用哪個(gè)讀庫(kù)
 int sum = NumberUtil.getRandom(1, num);
 log.info("使用了讀庫(kù){}", sum);
 return DbContextHolder.READ + sum;
 }
}

@ConfigurProperties 自動(dòng)生成數(shù)據(jù)源：
 /**
 * 寫數(shù)據(jù)源
 *
 * @Primary 標(biāo)志這個(gè) Bean 如果在多個(gè)同類 Bean 候選時(shí)，該 Bean 優(yōu)先被考慮。
 * 多數(shù)據(jù)源配置的時(shí)候注意，必須要有一個(gè)主數(shù)據(jù)源，用 @Primary 標(biāo)志該 Bean
 */
 @Primary
 @Bean
 @ConfigurationProperties(prefix = "mysql.datasource.write")
 public DataSource writeDataSource() {
 return new DruidDataSource();
 }

 /**
 * 設(shè)置數(shù)據(jù)源路由，通過(guò)該類中的determineCurrentLookupKey決定使用哪個(gè)數(shù)據(jù)源
 */
 @Bean
 public AbstractRoutingDataSource routingDataSource() {
 MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource();
 Map<Object, Object> targetDataSources = new HashMap<>(2);
 targetDataSources.put(DbContextHolder.WRITE, writeDataSource());
 targetDataSources.put(DbContextHolder.READ+"1", read1());
 proxy.setDefaultTargetDataSource(writeDataSource());
 proxy.setTargetDataSources(targetDataSources);
 return proxy;
 }

 /**
 * 多數(shù)據(jù)源需要自己設(shè)置sqlSessionFactory
 */
 @Bean
 public SqlSessionFactory sqlSessionFactory() throws Exception {
 SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
 bean.setDataSource(routingDataSource());
 ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
 // 實(shí)體類對(duì)應(yīng)的位置
 bean.setTypeAliasesPackage(typeAliasesPackage);
 // mybatis的XML的配置
 bean.setMapperLocations(resolver.getResources(mapperLocation));
 bean.setConfigLocation(resolver.getResource(configLocation));
 return bean.getObject();
 }

 /**
 * 設(shè)置事務(wù)，事務(wù)需要知道當(dāng)前使用的是哪個(gè)數(shù)據(jù)源才能進(jìn)行事務(wù)處理
 */
 @Bean
 public DataSourceTransactionManager dataSourceTransactionManager() {
 return new DataSourceTransactionManager(routingDataSource());
 }

@Target({ElementType.METHOD,ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ReadOnly {
}

@Aspect
@Component
public class ReadOnlyInterceptor implements Ordered {
 private static final Logger log= LoggerFactory.getLogger(ReadOnlyInterceptor.class);

 @Around("@annotation(readOnly)")
 public Object setRead(ProceedingJoinPoint joinPoint,ReadOnly readOnly) throws Throwable{
 try{
  DbContextHolder.setDbType(DbContextHolder.READ);
  return joinPoint.proceed();
 }finally {
  //清楚DbType一方面為了避免內(nèi)存泄漏，更重要的是避免對(duì)后續(xù)在本線程上執(zhí)行的操作產(chǎn)生影響
  DbContextHolder.clearDbType();
  log.info("清除threadLocal");
 }
 }

 @Override
 public int getOrder() {
 return 0;
 }
}