配置Mysql主从同步
为了配置Mysql的主从同步,首先需要两个安装了Mysql的主机。
我自己有两台阿里云的Linux服务器,IP分别为:
- 118.xx.xx.211
- 106.xx.xx.56
(为了保护的我服务器免受攻击,所以不方便公开IP的全部地址。没有阿里云服务器的小伙伴也可以在电脑上开两个虚拟机并安装好Mysql,效果也是一样的)
我们以118.xx.xx.211的服务器为Mysql主服务器(master),106.xx.xx.56为从服务器(slave)。
二、操作步骤
我们开启mysql主从复制,大体需要做以下操作:
- 开启master库的二进制日志
- 开启slave库的二进制日志
- 将slave指向master
- 之后master库的所有增删改数据都会自动同步到slave库
下面开始正式配置工作
1.1 编辑master库的配置文件
vim /etc/my.cnf
添加二进制日志配置,开启二进制(master-bin只是日志文件名称,可以自己指定)
server-id=1
log-bin=master-bin
log-bin-index=master-bin.index
1.2 保存配置然后重启mysql
service mysqld restart
登入mysql然后查看master的状态
mysql>show master status;
出现上面的结果就说明master已经配置成功了。
1.3 给slave配置一个用户权限
mysql>GRANT REPLICATION SLAVE ON *.* TO '用户名'@'slave数据库的IP地址' IDENTIFIED BY '密码';
这行命令的意思是:允许在某个IP地址的某个slave以某个密码对当前数据库的所有库和所有表进行复制操作。命令中的*.*
表示所有数据库的所有表,这里可以根据实际场景改为具体的某个库、某个表。
1.4 然后刷新权限
mysql>flush privileges;
然后开始配置从库
2.1 同样编辑mysql的配置文件
vim /etc/my.cnf
添加二进制日志配置,开启二进制(relay-bin只是日志文件名称,可以自己指定)
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
注意:server-id是要指定的,不然会报错,每一台指定一个唯一标识符
2.2 保存配置重启slave
service mysqld restart
重启后登入mysql
3.1 将slave指向master
mysql>change master to master_host='master所在服务器的IP',master_port=master数据库的的端口,master_user='master授权的账号',master_password='授权账号的密码',master_log_file='二进制文件名',master_log_pos=0;
3.2开启主从复制
mysql>start slave
查看从库的状态
mysql>show slave status \G;
OK,主从复制就配置成功了。
随便在主库里面建个表,去从库里面就可以看到一样的表了。具体测试就不详细说了,大家都会的。
其他注意点
1)如果要停止slave的复制可以使用命令:
mysql>stop slave;
2)从库最好是只读取不写入,网上有大神说slave库如果写入数据的话,可能导致数据回滚从而主从复制线程中断,解决方法如下(亲测有效):
mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; //跳过一行错误
mysql> start slave;
上面的语句是告诉slave跳过当前卡住的event,然后重新起来干活。如果有多个错误事件,就需要执行多次这个命令。
3)由于主从复制是基于I/O的日志,所以会存在一定延时,如果对数据一致性要求非常高的话,简单的主从复制在实际环境中会存在问题
编写Mybatis拦截器和数据源路由器实现程序自动读写分离
我们先来梳理一下Mybatis执行SQL的流程:
- 当我们调用一个Dao层接口的方法时,会将通知传给Mybatis的代理对象,代理对象会根据这个方法的方法名和参数类型,确定Statement Id(也就是我们定义在xml的select、update这类标签中的id),
- 根据Statement Id找到对应的mappedstatament对象。根据传入参数解析mappedstatement对象,得到最终要执行的SQL语句。
- 之后就是从数据库连接池DataSource中获取Connection对象,用Connection对象获取Statement对象,然后通过Statement的execute*方法执行SQL。
- 获取执行结果,封装成return的对象。
- 释放连接。
那么如果我们想要程序根据SQL语句属于增删改还是查询来自动的选择对应的数据库,我们应该在哪一个步骤添加一些额外的处理呢?没错,就在2,3之间,也就是在得到SQL语句之后,在选择数据库连接池之前。
Spring中有一个抽象类AbstractRoutingDataSource
,看名字也很容易理解,这是一个路由数据源,它的作用就是将getConnection()方法的调用路由到目标数据源DataSource上,至于路由的规则,由我们通过自己编写实现类来定义。那么我们又遇到一个问题了,当我们编写了一个路由数据源的实现类,定义了路由规则,在程序运行时又该怎么将条件变量传递给路由器呢,即我们定义了“将增删改SQL交给主库的数据源执行,将查询SQL交给从库的数据源执行”的规则,但是怎么能够知道SQL语句是增删改还是查询呢?这时候我们就需要编写一个Mybatis的拦截器,这个拦截器会判断出SQL语句是增删改还是查询,如果是增删改,就通知路由器去获取主库的连接,如果是查询,就通知路由器去获取从库的连接。
依据上面的思路,我们开始编写代码:
1.我们首先编写一个工具类
/**
* 一个工具类,mybatis拦截器会把获取主库还是获取从库的通知放在这个类的contextHolder中,
* 数据源路由器再从这个contextHolder中获取通知
*/
public class DynamicDataSourceHolder {
private static final Logger LOGGER = LoggerFactory.getLogger(DynamicDataSourceHolder.class);
private static ThreadLocal<String> contextHolder = new ThreadLocal<>();
public static final String DB_MASTER = "master";
public static final String DB_SLAVE = "slave";
/**
* 获取线程的DbType
* @return
*/
public static String getDbType() {
String result = contextHolder.get();
if (result == null) {
result = DB_MASTER;
}
return result;
}
/**
* 设置线程的DbType
* @param dbType
*/
public static void setDbType(String dbType) {
LOGGER.info("使用的数据源为:" + dbType);
contextHolder.set(dbType);
}
/**
* 清除数据源类型
*/
public static void removeDbType() {
contextHolder.remove();
}
}
2.然后我们编写一个动态数据源
/**
* 数据源路由器,用于自动将sql语句分发给对应的数据源
* 比如将增删改sql语句分发给主库数据源,将查询sql语句分发给从库数据源
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceHolder.getDbType();
}
}
3.再编写一个Mybatis拦截器
/**
* mybatis拦截器
*/
@Intercepts(
//mybatis会将增删改的操作封装在update里面,所以insert和delete就不需要写了
////注意这里的Executor所属的包是org.apache.ibatis.executor
{@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
})
public class DynamicDataSourceInterceptor implements Interceptor {
private static final Logger LOGGER = LoggerFactory.getLogger(DynamicDataSourceInterceptor.class);
private static final Pattern PATTERN = Pattern.compile(".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*");
@Override
public Object intercept(Invocation invocation) throws Throwable {
//判断本次执行是否被事务管理
boolean synchronizationActive = TransactionSynchronizationManager.isActualTransactionActive();
//获取mybatis传过来的参数
Object[] args = invocation.getArgs();
//sql是属于哪种操作(增删改查)往往是保存在第一个对象中
MappedStatement ms = (MappedStatement) args[0];
String lookupKey = DynamicDataSourceHolder.DB_MASTER;
if (!synchronizationActive) {
//如果是读操作
if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
//selectKey为自增id查询主键 SELECT LAST_INSERT_ID()方法,使用主库
if (ms.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) {
lookupKey = DynamicDataSourceHolder.DB_MASTER;
} else {
BoundSql boundSql = ms.getSqlSource().getBoundSql(args[1]);
String sql = boundSql.getSql().toLowerCase(Locale.CHINA)
.replaceAll("[\\t\\n\\r]", " ");//将所有的制表符换行符回车符替换掉
//匹配sql语句是否为增删改
if (PATTERN.matcher(sql).matches()) {
lookupKey = DynamicDataSourceHolder.DB_MASTER;
} else {
lookupKey = DynamicDataSourceHolder.DB_SLAVE;
}
}
}
} else {
//用事务管理的操作一般都是写操作,因此我们用主库
lookupKey = DynamicDataSourceHolder.DB_MASTER;
}
LOGGER.debug("设置方法[{}], use[{}], Strategy, SqlCommandType[{}]...",
ms.getId(), lookupKey, ms.getSqlCommandType().name());
DynamicDataSourceHolder.setDbType(lookupKey);
return invocation.proceed();
}
}
4.编写了Mybatis拦截器之后,我们要写一个Mybatis配置文件mybatis-config.xml,这个配置文件就创建在classpath目录下
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 配置全局属性 -->
<settings>
<!-- 使用jdbc的getGeneratedKeys获取数据库自增主键值 -->
<setting name="useGeneratedKeys" value="true" />
<!-- 使用列别名替换列名 默认:true -->
<setting name="useColumnLabel" value="true" />
<!-- 开启驼峰命名转换:Table{create_time} -> Entity{createTime} -->
<setting name="mapUnderscoreToCamelCase" value="true" />
<!-- 打印查询语句 -->
<setting name="logImpl" value="STDOUT_LOGGING" />
</settings>
<plugins>
<!-- 使Mybatis拦截器生效,如果没有下面配置的话,Mybatis拦截器就不会生效-->
<plugin interceptor="com.example.masterslavedb.dao.split.DynamicDataSourceInterceptor"/>
</plugins>
</configuration>
5.接下来我们来配置数据源
因为有两个数据库,所以我们配置两个DataSource,分别叫masterDataSource和slaveDataSource
先修改application.properties文件
#主库配置
master.datasource.jdbc-url=jdbc:mysql://118.xx.xxx.211:3306/db_test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL
master.datasource.username=root
master.datasource.password=xxxxxxx #这里写真实的主库密码
master.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
master.datasource.hikari.maximum-pool-size=30
master.datasource.hikari.minimum-idle=10
master.datasource.hikari.max-lifetime=1800000
master.datasource.hikari.connection-timeout=30000
master.datasource.hikari.idle-timeout=600000
master.datasource.hikari.connection-test-query=SELECT 1
master.datasource.hikari.read-only=false
#从库配置
slave.datasource.jdbc-url=jdbc:mysql://106.xx.xx.56:3306/db_test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL
slave.datasource.username=root
slave.datasource.password=xxxxxxxxx #这里写真实的从库密码
slave.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
slave.datasource.hikari.maximum-pool-size=30
slave.datasource.hikari.minimum-idle=10
slave.datasource.hikari.max-lifetime=1800000
slave.datasource.hikari.connection-timeout=30000
slave.datasource.hikari.idle-timeout=600000
slave.datasource.hikari.connection-test-query=SELECT 1
slave.datasource.hikari.read-only=true
/**
* dao层配置
*/
@Configuration
@MapperScan(basePackages = "com.example.masterslavedb.dao", sqlSessionFactoryRef = "sqlSessionFactoryBean")
public class SpringDaoConf {
/**
* 主库数据源
* @return
*/
@Bean(name = "masterDataSource")
@ConfigurationProperties(prefix = "master.datasource")
public DataSource masterDataSource() {
return DataSourceBuilder.create().build();
}
/**
* 从库数据源
* @return
*/
@Bean(name = "slaveDataSource")
@ConfigurationProperties(prefix = "slave.datasource")
public DataSource slaveDataSource() {
return DataSourceBuilder.create().build();
}
/**
* 我们自己实现的动态数据源
* @return
*/
@Bean(name = "dynamicDataSource")
public DynamicDataSource dynamicDataSource() {
DynamicDataSource result = new DynamicDataSource();
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put("master", masterDataSource());
targetDataSources.put("slave", slaveDataSource());
result.setTargetDataSources(targetDataSources);
return result;
}
/**
* 因为要在确定了sql语句之后才能确定选择哪个数据源,所以我们需要一个懒加载数据源
* @return
*/
@Bean(name = "dataSourceProxy")
public LazyConnectionDataSourceProxy dataSourceProxy() {
LazyConnectionDataSourceProxy result = new LazyConnectionDataSourceProxy();
result.setTargetDataSource(dynamicDataSource());
return result;
}
@Bean(name = "sqlSessionFactoryBean")
public SqlSessionFactoryBean sqlSessionFactoryBean() throws IOException {
SqlSessionFactoryBean result = new SqlSessionFactoryBean();
result.setDataSource(dataSourceProxy());
//设置entity包扫描路径
result.setTypeAliasesPackage("com.example.masterslavedb.entity");
//注入mybatis配置
result.setConfigLocation(new PathMatchingResourcePatternResolver().getResource("classpath:mybatis-config.xml"));
//设置sql映射文件包扫描路径
result.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/*.xml"));
return result;
}
@Bean(name = "transactionManager")
public DataSourceTransactionManager transactionManager() {
return new DataSourceTransactionManager(dataSourceProxy());
}
}
好了,这样自动读写分离就配置完成了,接下来写两个方法测试一下,这里就不写测试代码了,大家都会写的,直接给测试结果吧。
当我们用插入语句的时候,会打印日志:
然后去看下主库和从库,会发现都新增了一条刚才插入的数据
在用查询语句,会打印出日志:
OK,这样就成功通过测试啦。
完结,撒花。