主从复制和读写分离

发表于 阅读次数：

实战

环境介绍

环境&软件 版本
虚拟机 virtualbox 6.1
服务器 ubuntu 18.04
数据库 mysql 5.7.32

服务架构图

机器名称 IP 角色 权限和作用
master1 192.168.56.15 数据库 master 可读写、主库
slave1 192.168.56.16 数据库 Slave 只读、从库
slave2 192.168.56.17 数据库 Slave 只读、从库
master2 192.168.56.25 数据库 master 可读写、主库
slave3 192.168.56.26 数据库 Slave 只读、从库
slave4 192.168.56.27 数据库 Slave 只读、从库

bin_log 配置 & relay_log 配置

vi /etc/mysql/mysql.conf.d/mysqld.cnf

修改 uuid

show variables like ‘%server_uuid%’;

通过 select @@datadir 找到 msyql 的数据目录在哪，在这个目录下会有一个 auto.cnf 的文件，修改里面的内容，然后重启即可解决。
vi /var/lib/mysql/auto.cnf
service mysql restart

开启同步配置

1
2
3
4
5
6
7
8
9
10
-- 在master上运行，记录下file名称和position
show master status;
change master to master_host='192.168.56.15',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000018',master_log_pos=1731;

change master to master_host='192.168.56.25',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000024',master_log_pos=154;

start slave; -- 开启从模式

show slave status; -- 查看slave状态
stop slave; -- 关闭从模式

题目

https://gitee.com/lagouedu/alltestfile/raw/master/MySQL/MySQL%E4%B8%8B%E4%BD%9C%E4%B8%9A.pdf

作业资料说明：
1、提供资料：说明文档，代码，验证及讲解视频。
2、讲解内容包含：题目分析、实现思路、环境介绍。
3、说明文档包含：
l  环境介绍涉及的各个软件的版本
l  介绍各个机器对应角色&作用&ip地址

4、效果视频验证：
l  集群环境
* 6台
* 主master1负责写，从slave1 slave2负责读
* 主master2负责写，从slave3 slave4负责读

l  代码
* 主要类&方法&参数&返回值及代码行标注注释
* 基于user_id对c_order表进⾏数据分⽚
* 基于master1和master2主从集群实现读写分离

l  运行效果
* 项目各个类作用介绍，重点代码进行介绍，启动
* 演示基于user_id对c_order表进⾏数据分⽚，基于master1和master2主从集群实现读写分离
* 添加数据，通过Navicat展示数据分片成功，通过代码控制台真实sql展示数据分别在master1和master2进行写入
* 查询数据，通过代码控制台真实sql展示数据是通过4个从节点查询的

验证资料

作业讲解视频地址

解答

环境介绍
|环境&软件| 版本|
|—|—|
|虚拟机 virtualbox|6.1|
|服务器 ubuntu | 18.04|
|数据库 mysql | 5.7.32|

服务架构图

机器名称 IP 角色 权限和作用
master1 192.168.56.15 数据库 master 可读写、主库
slave1 192.168.56.16 数据库 Slave 只读、从库
slave2 192.168.56.17 数据库 Slave 只读、从库
master2 192.168.56.25 数据库 master 可读写、主库
slave3 192.168.56.26 数据库 Slave 只读、从库
slave4 192.168.56.27 数据库 Slave 只读、从库

配置

pom

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.lyloou</groupId>
    <artifactId>practice-mysql</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>practice-mysql</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>11</java.version>
    </properties>

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

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

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

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

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.0</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- c_order_0 c_order_1
CREATE TABLE `c_order_0` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被删除',
  `user_id` int(11) NOT NULL COMMENT '用户id',
  `company_id` int(11) NOT NULL COMMENT '公司id',
  `publish_user_id` int(11) NOT NULL COMMENT 'B端⽤户id',
  `position_id` int(11) NOT NULL COMMENT '职位ID',
  `resume_type` int(2) NOT NULL DEFAULT '0' COMMENT '简历类型：0附件 1在线',
  `status` varchar(256) NOT NULL COMMENT '投递状态投递状态WAIT-待处理理 AUTO_FILTER-⾃自动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝ARRANGE_INTERVIEW-通知⾯面试',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '处理理时间',
  PRIMARY KEY (`id`),
  KEY `index_userId_positionId` (`user_id`,`position_id`),
  KEY `idx_userId_operateTime` (`user_id`,`update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=551043954997460994 DEFAULT CHARSET=utf8mb4;

sharding jdbc 配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
# 显示sql语句
spring.shardingsphere.props.sql.show=true

#数据源
spring.shardingsphere.datasource.names=master1,slave1,slave2,master2,slave3,slave4

spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master1.jdbc-url=jdbc:mysql://192.168.56.15:3306/lagou?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=root

spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.56.16:3306/lagou?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=root

spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:mysql://192.168.56.17:3306/lagou?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=root

spring.shardingsphere.datasource.master2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master2.jdbc-url=jdbc:mysql://192.168.56.25:3306/lagou?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master2.username=root
spring.shardingsphere.datasource.master2.password=root

spring.shardingsphere.datasource.slave3.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave3.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave3.jdbc-url=jdbc:mysql://192.168.56.26:3306/lagou?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.slave3.username=root
spring.shardingsphere.datasource.slave3.password=root

spring.shardingsphere.datasource.slave4.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave4.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave4.jdbc-url=jdbc:mysql://192.168.56.27:3306/lagou?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.slave4.username=root
spring.shardingsphere.datasource.slave4.password=root

#分库分表
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=company_id
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=master$->{company_id%2 + 1}
spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=master$->{1..2}.c_order_$->{0..1}
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.algorithm-expression=c_order_$->{user_id % 2}
spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE

#读写分离
spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave1, slave2
spring.shardingsphere.sharding.master-slave-rules.master2.master-data-source-name=master2
spring.shardingsphere.sharding.master-slave-rules.master2.slave-data-source-names=slave3, slave4

测试
测试添加

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@Test
@Repeat(value = 100)
public void testAdd() {
    Random random = new Random();
    final int companyId = random.nextInt(10);
    final int userId = random.nextInt(100);
    final Date now = new Date();
    final COrder cOrder = new COrder();
    cOrder.setIsDel(false);
    cOrder.setUserId(userId);
    cOrder.setCompanyId(companyId);
    cOrder.setPublishUserId(10);
    cOrder.setPositionId(100);
    cOrder.setResumeType(userId % 2);
    cOrder.setStatus("AUTO_FILTER");
    cOrder.setCreateTime(now);
    cOrder.setUpdateTime(now);
    cOrderRepository.save(cOrder);
}

image-20201230150829936

测试查询

1
2
3
4
5
@Test
public void testFind() {
    final List<COrder> allByUserId = cOrderRepository.findAllByUserId(24);
    System.out.println(allByUserId);
}

image-20201230151141903