Mysql读写分离配置

最新推荐文章于 2024-05-20 14:31:07 发布

韩金龙小傻瓜

最新推荐文章于 2024-05-20 14:31:07 发布

阅读量1.9k

点赞数

文章标签： mysql 服务器数据库

本文链接：https://blog.csdn.net/weixin_46633487/article/details/121331974

版权

mysql配置主从复制

CentOS7 64位安装MySql教程

先检查系统是否装有mysql
rpm -qa | grep mysql
下载mysql的repo源
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
报错: -bash: wget: 未找到命令
安装插件 yum -y install wget
安装mysql-community-release-el7-5.noarch.rpm包
sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm
安装MySQL
sudo yum install mysql-server
重置MySQL密码
mysql -u root
报错：
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
原因：原因是/var/lib/mysql的访问权限问题。
chown root /var/lib/mysql/
重启MySQL服务
service mysqld restart
接着登陆设置密码
mysql -u root
use mysql;
update user set password=password(‘123456’) where user=‘root’;
exit;

接着继续重启MySQL服务
service mysqld restart

接着设置Root账户远程连接密码
mysql -u root -p
GRANT ALL PRIVILEGES ON . TO root@"%" IDENTIFIED BY “root”;　
exit;

重启服务器 service mysqld restart

使用外网工具连接MySQL
关闭防火墙
systemctl stop firewalld.service

MySQL主从复制配置

主服务器节点

vi /etc/my.cnf 新增以下内容
server_id=177 ###服务器id
log-bin=mysql-bin ###开启日志文件

重启mysql服务 service mysqld restart
验证是否已经配置成功
show variables like ‘%server_id%’;
能够查询对应配置文件中的server_id 说明已经配置成功
show master status;
能够看到同步的文件，和行数说明已经配置成功。

从服务器节点

克隆服务器
vi /etc/my.cnf
server_id=178 ###从服务器server_id
log-bin=mysql-bin ###日志文件同步方式
binlog_do_db=test ###同步数据库

重启mysql服务 service mysqld restart
验证是否已经配置成功
show variables like ‘%server_id%’;
能够查询对应配置文件中的server_id 说明已经配置成功

从服务器同步主服务器配置
change master to master_host=‘192.168.212.200’,master_user=‘root’,master_password=‘root’,
master_log_file=‘mysql-bin.000002’,master_log_pos=216;
开始同步
start slave
检查从服务器复制功能状态
SHOW SLAVE STATUS

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

因为服务器克隆的时候导致UUID产生了重复，服务器全手动安装不会出现这个错误
解决办法

Cat /etc/my.cnf
cd /var/lib/mysql
rm -rf auto.cnf
重启服务器即可
service mysqld restart

mysql配置读写分离

MyCat

linux环境安装MyCat，实现读写分离
1.上传Mycat包
2.解压安装包 tar -zxvf 安装包名
3.配置schema.xml和server.xml
4.客户端连接端口号：8066

文件说明server.xmlMycat的配置文件，设置账号、参数等schema.xmlMycat对应的物理数据库和数据库表的配置rule.xmlMycat分片（分表分库）规则

文件	说明
server.xml	Mycat的配置文件，设置账号、参数等
schema.xml	Mycat对应的物理数据库和数据库表的配置
rule.xml	Mycat分片（分表分库）规则

schema.xml配置

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- TESTDB1 是mycat的逻辑库名称，链接需要用的 -->
    <schema name="mycat_testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
        <!-- database 是MySQL数据库的库名 -->
    <dataNode name="dn1" dataHost="localhost1" database="test" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- 可以配置多个主从 -->
        <writeHost host="hostM1" url="192.168.213.132:3306" user="root" password="root">
            <!-- 可以配置多个从库 -->
            <readHost host="hostS2" url="192.168.213.133:3306" user="root" password="root" />
        </writeHost>
    </dataHost>
</mycat:schema>

dataNode节点中各属性说明：
name：指定逻辑数据节点名称；
dataHost：指定逻辑数据节点物理主机节点名称；
database：指定物理主机节点上。如果一个节点上有多个库，可使用表达式db$0-99，     表示指定0-99这100个数据库；
dataHost 节点中各属性说明：
    name：物理主机节点名称；
    maxCon：指定物理主机服务最大支持1000个连接；
    minCon：指定物理主机服务最小保持10个连接；
    writeType：指定写入类型；
        0，只在writeHost节点写入；
        1，在所有节点都写入。慎重开启，多节点写入顺序为默认写入根据配置顺序，第一个挂掉切换另一个；
    dbType：指定数据库类型；
    dbDriver：指定数据库驱动；
    balance：指定物理主机服务的负载模式。
        0，不开启读写分离机制；
        1，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡；
        2，所有的readHost与writeHost都参与select语句的负载均衡，也就是说，当系统的写操作压力不大的情况下，所有主机都可以承担负载均衡；

server.xml配置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
   
   <!-- 读写都可用的用户 -->
    <user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">mycat_testdb</property>

        <!-- 表级 DML 权限设置 -->
        <!--        
        <privileges check="false">
            <schema name="TESTDB" dml="0110" >
                <table name="tb01" dml="0000"></table>
                <table name="tb02" dml="1111"></table>
            </schema>
        </privileges>       
         -->
    </user>

    <!-- 只读用户 -->
    <user name="user">
        <property name="password">user</property>
        <property name="schemas">mycat_testdb</property>
        <property name="readOnly">true</property>
    </user>

</mycat:server>

Maven依赖信息

<parent>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-parent</artifactId>
	<version>2.0.4.RELEASE</version>
	<relativePath /> <!-- lookup parent from repository -->
</parent>
<dependencies>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-aop</artifactId>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-web</artifactId>
	</dependency>
	<dependency>
		<groupId>org.mybatis.spring.boot</groupId>
		<artifactId>mybatis-spring-boot-starter</artifactId>
		<version>1.3.2</version>
	</dependency>
 
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<scope>runtime</scope>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-test</artifactId>
		<scope>test</scope>
	</dependency>
 
	<dependency>
		<groupId>com.alibaba</groupId>
		<artifactId>druid</artifactId>
		<version>1.0.23</version>
	</dependency>
</dependencies>
 
<build>
	<plugins>
		<plugin>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-maven-plugin</artifactId>
		</plugin>
	</plugins>
</build>

application.yml

  datasource:
    ###可读数据源
    select:
      jdbc-url: jdbc:mysql://192.168.212.204:8066/TESTDB1
      driver-class-name: com.mysql.jdbc.Driver
      username: user
      password: user
    ####可写数据源  
    update:
      jdbc-url: jdbc:mysql://192.168.212.204:8066/TESTDB1
      driver-class-name: com.mysql.jdbc.Driver
      username: user
      password: user
    type: com.alibaba.druid.pool.DruidDataSource

DataSourceContextHolder

@Component
@Lazy(false)
public class DataSourceContextHolder {
// 采用ThreadLocal 保存本地多数据源
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
 
// 设置数据源类型
public static void setDbType(String dbType) {
contextHolder.set(dbType);
}
 
public static String getDbType() {
return contextHolder.get();
}
 
public static void clearDbType() {
contextHolder.remove();
}
 
}

DataSourceConfig
@Configuration
public class DataSourceConfig {
 
// 创建可读数据源
@Bean(name = "selectDataSource")
@ConfigurationProperties(prefix = "spring.datasource.select") // application.properteis中对应属性的前缀
public DataSource dataSource1() {
return DataSourceBuilder.create().build();
}
 
// 创建可写数据源
@Bean(name = "updateDataSource")
@ConfigurationProperties(prefix = "spring.datasource.update") // application.properteis中对应属性的前缀
public DataSource dataSource2() {
return DataSourceBuilder.create().build();
}
}

DynamicDataSource

//在Spring 2.0.1中引入了AbstractRoutingDataSource, 该类充当了DataSource的路由中介, 能有在运行时, 根据某种key值来动态切换到真正的DataSource上。
 
@Component
@Primary
public class DynamicDataSource extends AbstractRoutingDataSource {
@Autowired
@Qualifier("selectDataSource")
private DataSource selectDataSource;
 
@Autowired
@Qualifier("updateDataSource")
private DataSource updateDataSource;
 
/**
 * 这个是主要的方法，返回的是生效的数据源名称
 */
@Override
protected Object determineCurrentLookupKey() {
System.out.println("DataSourceContextHolder：：：" + DataSourceContextHolder.getDbType());
return DataSourceContextHolder.getDbType();
}
 
/**
 * 配置数据源信息
 */
@Override
public void afterPropertiesSet() {
Map<Object, Object> map = new HashMap<>();
map.put("selectDataSource", selectDataSource);
map.put("updateDataSource", updateDataSource);
setTargetDataSources(map);
setDefaultTargetDataSource(updateDataSource);
super.afterPropertiesSet();
}
}


SwitchDataSourceAOP
@Aspect
@Component
@Lazy(false)
@Order(0) // Order设定AOP执行顺序 使之在数据库事务上先执行
public class SwitchDataSourceAOP {
// 这里切到你的方法目录
@Before("execution(* com.mayikt.service.*.*(..))")
public void process(JoinPoint joinPoint) {
String methodName = joinPoint.getSignature().getName();
if (methodName.startsWith("get") || methodName.startsWith("count") || methodName.startsWith("find")
|| methodName.startsWith("list") || methodName.startsWith("select") || methodName.startsWith("check")) {
DataSourceContextHolder.setDbType("selectDataSource");
} else {
// 切换dataSource
DataSourceContextHolder.setDbType("updateDataSource"); }
}
}

原理：

1.首先在项目中，有两个数据源，分别为读和写的数据源
2.使用aop技术拦截业务逻辑层方法的前缀，比如get,select,find,list等
判断方法时候需要做读或者写的操作
3.如果是写的操作的时候，传递一个key给RountingDataSource指明使用写的数据源（updateDataSource）
读的话也是传递一个key给RountingDataSource指明使用读的数据源（selectDataSource）

动态数据源环境配置：

1.创建读和写的数据源
2.将读和写的数据源注册到RountingDataSource中
3.使用AOP技术拦截业务逻辑层方法，判断方法的前缀，是读还是写

数据库集群会产生哪些问题？

数据库集群的时候，如果自动增长id产生重复问题，怎么解决？(自增id问题)
a.使用uuid做主键id(不推荐，查询慢)，如果是oracle推荐
b.设置数据库的步长
设定起始值，设定每次增加的大小
eg：节点一：1，3，5，7，9
节点二：2，4，6，8，10
注意：在最开始设置好了每台节点自增方式步长后，确定好了mysql集群数量后，无法扩展新的mysql，不然生成步长的规则可能会发生变化
2.数据关联查询问题(水平拆分)
3.数据同步问题

分表分库

1.在数据库分表分库原则中，遵循两个设计理论：垂直拆分、水平拆分

垂直拆分：根据不同的业务，拆分成不同的数据库，比如会员数据库、订单数据库等
（缺点：跨数据库查询，必须采用接口形式通讯，会出现分布式事务问题，增加开发成本）
水平拆分：按照某个字段的某种规则分散到多个库中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他数据库中，主要有分表，分库两种模式，该方式提高了系统的稳定性跟负载能力，但是跨库join性能较低

水平拆分常用的算法：取模，分片，轮询，枚举
主键取模的缺点：不能扩容服务器

分片枚举：
分片枚举这种规则适用于特定的场景，比如有些业务需要按照省份或区县来做保存，而全国的省份区县固定的，这类业务使用这一规则

Sharding-Jdbc

通过Sharding-Jdbc实现读写分离

Maven依赖

   <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
   <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
   <java.version>1.8</java.version>
</properties>

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

   <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
   </dependency>
   <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.0-beta</version>
   </dependency>
   <dependency>
      <groupId>io.shardingjdbc</groupId>
      <artifactId>sharding-jdbc-core</artifactId>
      <version>2.0.3</version>
   </dependency>
   <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
   </dependency>
</dependencies>

<build>
   <plugins>
      <plugin>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
   </plugins>

</build>

application.yml

mybatis-plus:
#  mapper-locations: classpath*:/mapper/*.xml
  global-config:
    db-config:
      column-underline: true
#shardingjdbc配置      
sharding:
  jdbc:
    data-sources:
     ###配置第一个从数据库
      ds_slave_0:
        password: root
        jdbc-url: jdbc:mysql://192.168.213.133:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=true
        driver-class-name: com.mysql.jdbc.Driver
        username: root
      ###主数据库配置  
      ds_master:
        password: root
        jdbc-url: jdbc:mysql://192.168.213.132:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=true
        driver-class-name: com.mysql.jdbc.Driver
        username: root
    ###配置读写分离    
    master-slave-rule:
    ###配置从库选择策略，提供轮询与随机，这里选择用轮询
      load-balance-algorithm-type: round_robin
      ####指定从数据库
      slave-data-source-names: ds_slave_0
      name: ds_ms
      ####指定主数据库
      master-data-source-name: ds_master

ShardingDataSourceConfig

@Configuration
@EnableConfigurationProperties(ShardingMasterSlaveConfig.class)
@Log4j2
// 读取ds_master主数据源和读写分离配置
@ConditionalOnProperty({ "sharding.jdbc.data-sources.ds_master.jdbc-url",
      "sharding.jdbc.master-slave-rule.master-data-source-name" })
public class ShardingDataSourceConfig {
   @Autowired
   private ShardingMasterSlaveConfig shardingMasterSlaveConfig;
   @Bean
   public DataSource masterSlaveDataSource() throws SQLException {
      final Map<String, DataSource> dataSourceMap = Maps.newHashMap();
      dataSourceMap.putAll(shardingMasterSlaveConfig.getDataSources());
      final Map<String, Object> newHashMap = Maps.newHashMap();
      // 创建 MasterSlave数据源
      DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(dataSourceMap,
            shardingMasterSlaveConfig.getMasterSlaveRule(), newHashMap);
      log.info("masterSlaveDataSource config complete");
      return dataSource;
   }
}

ShardingMasterSlaveConfig

@Data
@ConfigurationProperties(prefix = "sharding.jdbc")
public class ShardingMasterSlaveConfig {
   // 存放本地多个数据源
   private Map<String, HikariDataSource> dataSources = new HashMap<>();
   private MasterSlaveRuleConfiguration masterSlaveRule;
}

目录结构：

通过Sharding-Jdbc实现分表分库

总结

Sharding-Jdbc与MyCat区别

Mycat是一个基于第三方应用中间件数据库代理框架，客户端所有的jdbc请求都必须要先交给Mycat，再由MyCat转发到具体的真是服务器中

Sharding-Jdbc是一个Jar形式，在本地应用层重写Jdbc原生的方法，实现数据库分片形式。
MyCat属于服务器端数据库中间件，而Sharding-Jdbc是一个本地数据库中间件框架

从设计理念上看确实有一定的相似性。主要流程都是SQL解析—》 SQL路由 —》SQL改写 —》 SQL执行 —》结果归并。但架构设计上是不同的。MyCat是基于Proxy，他复写了Mysql协议，将Mycat Server伪装成一个Mysql数据库，而Sharding-Jdbc是基于JDBC的扩展，是以jar包形式提供轻量级服务的

Mycat虚拟数据库的方式实现分表分库
sharding-JDBC基于本地AOPjdbc拦截改写sql语句

韩金龙小傻瓜

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql读写分离配置

mysql配置主从复制CentOS7 64位安装MySql教程先检查系统是否装有mysqlrpm -qa | grep mysql下载mysql的repo源wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm报错: -bash: wget: 未找到命令安装插件 yum -y install wget安装mysql-community-release-el7-5.noarch.rpm包sudo r
复制链接

扫一扫