資料庫主從複制，讀寫分離，分庫分表查詢

主從複制
- 背景
- 原理
- 實戰
- - docker安裝mysql
  - 主從配置
  - - 主伺服器配置
    - 從伺服器配置
    - 注意事項
讀寫分離
- 目錄結構
- - 項目結構
  - 表結構
- 配置多資料源
- 測試結果

主從複制

背景

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

讀寫分離

目錄結構

項目結構

資料庫主從複制，讀寫分離，分庫分表查詢主從複制讀寫分離

表結構

資料庫主從複制，讀寫分離，分庫分表查詢主從複制讀寫分離

配置多資料源

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

DbType

public enum DbType {
    DATASOURCE_USER,
    DATASOURCE_USER_READ,
    DATASOURCE_ORDER,
    DATASOURCE_ORDER_READ
}

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();
    }
}

DynamicDataSource

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return CustomerContextHolder.getCustomerType();
    }
}

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;
    }
}

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);
    }
}

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();
    }
}

測試結果

資料庫主從複制，讀寫分離，分庫分表查詢主從複制讀寫分離

資料庫主從複制，讀寫分離，分庫分表查詢主從複制讀寫分離

資料庫主從複制，讀寫分離，分庫分表查詢

主從複制

背景

原理

實戰

docker安裝mysql

主從配置

主伺服器配置

從伺服器配置

注意事項

讀寫分離

目錄結構

項目結構

表結構

配置多資料源

測試結果

繼續閱讀

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

微服務-性能壓測\緩存redis和分布式鎖redisson和SpringCache

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

java 8 Lamda 求 List 中 Bigdecimal類型的各種值求最大值求最小值求和求平均值

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

(SpringBoot)日志種類：log、monitor、access、out、gc、backup

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案