主从复制
背景
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();
}
}