# database-separation

**Repository Path**: Gene_orange/database-separation

## Basic Information

- **Project Name**: database-separation
- **Description**: 记录学习数据库读写分离（SpringBoot + Mybatis + Spring Aop + MySQL）
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 1
- **Forks**: 0
- **Created**: 2021-05-07
- **Last Updated**: 2021-09-28

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README


```
一、MySQL数据库主从搭建
    本人是以window的数据库作为主（写）库，然后虚拟机的数据库作为从（读）库；
这里先简单记录下虚拟机安装MySQL的命令以及搭建数据库主从备份：

# 查看有没有安装MySQL：
dpkg -l | grep mysql

# 安装MySQL：
apt install mysql-server

# 安装完成之后可以使用如下命令来检查是否安装成功：
netstat -tap | grep mysql

# 检查mysql服务状态：
systemctl status mysql

# 配置mysql允许远程访问，编辑mysqld.cnf
vim /etc/mysql/mysql.conf.d/mysqld.cnf

# 忘记密码，则在mysqld.cnf文件skip-external-locking下添加
skip-grant-tables

# 重启数据库
sudo service mysql restart


mysql> CREATE USER 'orange'@'%' IDENTIFIED BY 'XXXXXX'; # 创建用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'orange'@'%'; # 分配权限
mysql> flush privileges;   # 刷新权限

```


```
# 主库配置（my.ini)
[mysqld]
# Server Id.
server-id=1
log-bin=master-bin
log-bin-index=master-bin.index

重启之后，执行查询语句 show master status;
```

![输入图片说明](https://images.gitee.com/uploads/images/2021/0507/155631_15451eb8_5470869.png "屏幕截图.png")



```
# 从库配置 (/etc/mysql/mysql.conf.d/mysqld.cnf)
[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

# 重启数据库，执行连接主库数据库语句
CHANGE MASTER TO
          MASTER_HOST='192.168.25.5',
         MASTER_USER='orange',         
         MASTER_PASSWORD='123456',
         MASTER_LOG_FILE='master-bin.000004',
         MASTER_LOG_POS=154;


# 开启备份
mysql> start slave;
```

![输入图片说明](https://images.gitee.com/uploads/images/2021/0507/155613_ea2f146c_5470869.png "屏幕截图.png")




```
# 不同步哪些数据库
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema

# 只同步哪些数据库，除此之外，其他不同步
binlog-do-db = game
```



二、pom.xml

```
<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>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.10</version>
    </dependency>

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

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    
    <!-- MySQL -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>

    <!-- Mybatis -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.0.0</version>
    </dependency>

    <dependency>
        <groupId>tk.mybatis</groupId>
        <artifactId>mapper-spring-boot-starter</artifactId>
        <version>2.0.2</version>
    </dependency>

    <dependency>
        <groupId>commons-io</groupId>
        <artifactId>commons-io</artifactId>
        <version>2.6</version>
    </dependency>

    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-lang3</artifactId>
        <version>3.5</version>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-aop</artifactId>
    </dependency>
```



二、application.yml

```
spring:
  datasource:
    num: 1 # 读库数目
    type-aliases-package: com.gdkm.database.mapper
    mapper-locations: classpath*:mapper/*Mapper.xml
#    config-location: classpath:/mybatis-config.xml
    master:
      url: jdbc:mysql://192.168.25.5:3306/db_user?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
      username: orange
      password: XXXXX
      driver-class-name: com.mysql.jdbc.Driver
    slave:
      url: jdbc:mysql://192.168.25.6:3306/db_user?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
      username: root
      password: XXXXX
      driver-class-name: com.mysql.jdbc.Driver

```


三、代码实现


![输入图片说明](https://images.gitee.com/uploads/images/2021/0507/155732_0b8ddbf6_5470869.png "屏幕截图.png")

```
- DataSourceConfig : 主要是配置主从库的数据源，以及事务管理等，不再使用SpringBoot默认的配置
- DataSourceContextHolder：利用ThreadLocal封装的保存数据源上线的上下文context；
- DataSourceRouter extend AbstractRoutingDataSource: 最终的determineCurrentLookupKey返回的是从DataSourceContextHolder中拿到的,因此在动态切换数据源的时候注解应该给DataSourceContextHolder设值;
- DataSourceSwitcher: 自定义注解，用于动态切换数据源；
- DataSourceContextAop：切面类，用于拦截自定义注解从而设置DataSourceContextHolder的值来实现动态切换数据源；
- DataSourceAspect：切面类，用于拦截service包特定方法的前缀判断从而设置DataSourceContextHolder的值来实现动态切换数据源；

```