MySQL搭建主从复制集群，实现读写分离

绝地灬酷狼 2024-03-03 03:13 40阅读 0赞

**目录**

一、准备

二、配置

2.1 配置主库

修改配置文件/etc/my.cnf

重启服务

为主库再创建一个账户并授权

查看状态

2.2 配置从库

修改配置文件/etc/my.cnf

重启mysql服务

配置需要同步的主机

启动salve同步

查看是否同步

三、测试主从复制是否生效

四、读写分离案例

4.1 背景

4.2 Sharding-JDBC介绍

4.3 项目测试前期准备

数据库准备

导入依赖

配置文件

代码生成器

添加依赖

添加配置文件规则

4.4 验证

创建测试方法，包含增删改查

打上断点，以DEBUG的方式启动

发送请求测试

五、主从复制数据不同步的问题

--------------------

##    一、准备 ##

准备两台服务器，为每台都安装好mysql。

此时的两台服务器它们的mysql之间没有半毛钱关系，各自是独立的。

如果不会安装，后续我也会出一篇安装教程。请关注我的【编程环境安装】专栏。

![ca8c6065a4884aa3b97acb4dd0c06fc7.png][]

## 二、配置 ##

### 2.1 配置主库 ###

#### 修改配置文件/etc/my.cnf ####

追加如下：

[mysqld]
    log-bin=mysql-bin
    server-id=110

以后在进行增删改操作的时候，它都会进行记录日志。

![f8c43d771d274a4b811ace31b9dbd06d.png][]

#### 重启服务 ####

systemctl restart mysqld

![27d2d3dcde6c45d2beda391f8df503e5.png][]

#### 为主库再创建一个账户并授权 ####

CREATE USER '主库用户名'@'%' IDENTIFIED BY '主库用户名密码';
    GRANT REPLICATION SLAVE ON *.* TO '主库名称'@'%';
    ALTER USER '主库用户名'@'%' IDENDIFIED WITH mysql_native_password BY '主库用户名密码';
    flush privileges;

![73458488e737436a8b3e68d61be26ade.png][]

这一步是为主库创建一个用户，并为这个用户授予复制从库的权限。

#### 查看状态 ####

我们先记住这个File和Position的值

![3bcd0a37bb664ebda201375b2a60548f.png][]

记得现在就不要再在主库操作了，否则这个位置就会发生变化。

###  ###

### 2.2 配置从库 ###

#### 修改配置文件/etc/my.cnf ####

增加配置：服务器的唯一标识

server-id=111

![5dd01aeae20a4143ac5b8c81c85f5618.png][]

#### 重启mysql服务 ####

systemctl restart mysqld

![28021eaf550a44f692e6f0bdfcc36bb1.png][]

#### 配置需要同步的主机 ####

CHANGE MASTER TO
    MASTER_HOST='主机的ip地址',
    MASTER_USER='主机刚才创建的用户名',
    MASTER_PASSWORD='主机用户名的密码',
    MASTER_LOG_FILE='主机上记录的File值',
    MASTER_LOG_POS=主机上记录的Position值;

![175aaafadec04b9bb4b2cb96499231b1.png][]

#### 启动salve同步 ####

start slave;

#### ![50777960707742da8b4622f15f352f99.png][]查看是否同步 ####

show slave status;

![c0e7e584146946eda3cee6879fb574a7.png][] 很乱啊，我们把它复制到文本编辑器上来看看

![6a92685e0c1147ea9e2837f07d1e91ab.png][]

至此，主从复制集群就搭建完成了！

## 三、测试主从复制是否生效 ##

我们通过navicat连接上这两个数据库，通过操作看看，它们的变化。

在主库创建一个数据库。

![c057e37c96f2490ba155cc8e779d476e.png][]

![57aec6851a7745d7ad993675681ac0ae.png][]

![6f6e63995eca44ecb0f491e7109c73b3.png][]

## 四、读写分离案例 ##

### 4.1 背景 ###

如果系统的访问量增大，既要查询又要写入，单台数据库已经不能满足访问压力了。

这个时候就可以考虑主从复制。将数据库拆分成主库和从库。主库主要负责处理事务性的增删改操作，从库负责处理查询操作。

![8e5a96d549904dc2b877bf88c7912c0e.png][]

但是我们的系统怎么知道，我们的操作是查询操作，还是增删改操作？又怎么根据操作的类型选择主库还是从库呢？

这个时候就用到了Sharding-JDBC！

### 4.2 Sharding-JDBC介绍 ###

Sharding-JDBC定位为轻量级的Java框架，在Java的jdbc层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可以理解为增强版的jdbc驱动，完全兼容jdbc和各种orm框架。

*  适用于任何基于JDBC的ORM框架，如：JPA,Hibernate，Mybatis，Spring JDBC Template或直接使用JDBC。
 *  支持任何第三方的数据库连接池，如：DBCP,C3P0,Druid，HikariCP等。
 *  支持任意实现JDBC规范的数据库。目前支持Mysql，Oracle，SQL server，PostgreSQL以及任何遵顼SQL92标准的数据库。

### 4.3 项目测试前期准备 ###

在使用分库分表之前，先要搭建好主从复制的数据库。

然后我们先创建一个web项目：

#### 数据库准备 ####

CREATE TABLE `user` (
      `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
      `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '姓名',
      `age` tinyint DEFAULT NULL COMMENT '年龄',
      `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '住址',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户'

![fcc696bce0664adb94f6e6397043951d.png][]

#### 导入依赖 ####

作为web项目基本的一些依赖，没什么好讲的。

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
            </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-web</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
            </dependency>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>fastjson</artifactId>
                <version>1.2.76</version>
            </dependency>
    
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.5.1</version>
            </dependency>
    
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-generator</artifactId>
                <version>3.5.1</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.velocity</groupId>
                <artifactId>velocity-engine-core</artifactId>
                <version>2.1</version>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.2.12</version>
            </dependency>

#### 配置文件 ####

目前只配置了端口号和mybatisplus的相关配置

连数据源连接我们都没有配置，这是因为后面要使用sharding-jdbc

server.port=8080
    
    mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
    mybatis-plus.global-config.db-config.logic-delete-value=1
    mybatis-plus.global-config.db-config.logic-not-delete-value=0
    mybatis-plus.configuration.map-underscore-to-camel-case=true
    mybatis-plus.global-config.db-config.id-type=assign_id

#### 代码生成器 ####

使用代码生成器，快速生成刚才创建的表的controller、entity、service、mapper等文件。

![16aace7506f94130803f59e5b972ff87.png][]

这些做好以后，下面开始使用到sharding-jdbc！

#### 添加依赖 ####

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

#### 添加配置文件规则 ####

# 定义数据源的名字，有几个填写几个，通过.隔开，名字随便取，但是后面配置主从数据源需要根据此名字进行设置
    spring.shardingsphere.datasource.names=master-haha,slave-haha
    # 数据源1
    spring.shardingsphere.datasource.master-haha.type=com.alibaba.druid.pool.DruidDataSource
    spring.shardingsphere.datasource.master-haha.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.shardingsphere.datasource.master-haha.url=jdbc:mysql://192.168.17.110:3306/db_test1?characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    spring.shardingsphere.datasource.master-haha.username=root
    spring.shardingsphere.datasource.master-haha.password=123456
    # 数据源2
    spring.shardingsphere.datasource.slave-haha.type=com.alibaba.druid.pool.DruidDataSource
    spring.shardingsphere.datasource.slave-haha.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.shardingsphere.datasource.slave-haha.url=jdbc:mysql://192.168.17.111:3306/db_test1?characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    spring.shardingsphere.datasource.slave-haha.username=root
    spring.shardingsphere.datasource.slave-haha.password=123456
    
    # 配置从库负载均衡策略：轮询。就是说从库实际不一定只有一台，当每次查询操作进来的时候，轮询去查每台从库。
    spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
    # 设置最终数据源的名称    其实就是spring中bean对象的名称
    spring.shardingsphere.masterslave.name=dataSource
    # 指定主库数据源名称
    spring.shardingsphere.masterslave.master-data-source-name=master-haha
    # 指定从库数据源名称     从库如果有多个，通过逗号隔开
    spring.shardingsphere.masterslave.slave-data-source-names=slave-haha
    # 开启控制台的sql显示，默认是false
    spring.shardingsphere.props.sql.show=true

以上规则定义好了，它查询就会去从库（每台从库轮询查），增删改去主库。

但是，最后还需要修改一下spring的bean覆盖策略，默认是不允许同名bean的。

为什么要修改呢？因为我们定义的bean的名称和druid中定义的bean的名字重复了。

而spring中默认不允许同名的bean的。

所以需要在配置文件中再加上一段配置

# 允许bean定义覆盖，后创建的bean会覆盖前面的同名bean对象
    spring.main.allow-bean-definition-overriding=true

否则启动项目会报错

![d929204e49d743248e883d030443a700.png][] ok,下面我们创建一些测试方法来验证一下。

###  ###

### 4.4 验证 ###

#### 创建测试方法，包含增删改查 ####

注意这里引入了一个DataSource对象，虽然实际业务代码并没有使用到，但是我们只是想看看它到底是谁。

@RestController
    @RequestMapping("/user")
    @Slf4j
    public class UserController {
    
        @Autowired
        private DataSource dataSource;
    
        @Autowired
        private UserService userService;
    
        @PostMapping
        public User save(User user){
            userService.save(user);
            return user;
        }
    
        @DeleteMapping("/{id}")
        public void delete(@PathVariable Long id){
            userService.removeById(id);
        }
    
        @PutMapping
        public User update(User user){
            userService.updateById(user);
            return user;
        }
    
        @GetMapping("/{id}")
        public User getById(@PathVariable Long id){
            User user = userService.getById(id);
            return user;
        }
    
        @GetMapping("/list")
        public List<User> list(User user){
            LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
            queryWrapper.eq(user.getId() != null,User::getId,user.getId());
            queryWrapper.eq(user.getName() != null,User::getName,user.getName());
            List<User> list = userService.list(queryWrapper);
            return list;
        }
    }

#### 打上断点，以DEBUG的方式启动 ####

断点的位置我们如图

![fa79e722ffb946caaa6d1e3fc8c94429.png][]

![eb63233f546f4d9dbb3db6fa8e7470ac.png][]

#### 发送请求测试 ####

我们可以通过postman来发送请求试试，我这里使用apifox来测试。

![b741b765c1684fa3a9f3e56c600932f1.png][]

先来查询一下

![4d6a84e8bf9c4cfc899d0dad4bdedf0a.png][]

我们发现这个datasource是shardingjdbc提供的

![cadd749c43fc44c492da0d1d16aa8203.png][]

接着放行该请求

![6697994ef5bc4237ad5b40d9974066fd.png][]

我们通过控制台看到，这条sql是从库去查询的！

![72f47c5758a24b0bb9eb316c8544a226.png][]

ok，再发送添加请求试试。

![baac881e297a41c7bc9d0ac912fa2cf9.png][]

我们直接放行，发现添加操作是主库执行的。

![fa7175b3e7b64635b20322846ff8c7fa.png][]

ok，下面我们再检查一下数据库

![c1727709d19b4ad5a488b6838a4ebce5.png][]

![e4b1f4f4856b4ad2ab494a9f8c4a640b.png][]

ok，主库也同步到了从库了。

##  ##

## 五、主从复制数据不同步的问题 ##

案例：误操作向从库添加了数据。

我通过navicat向**从库**的user表中生成假数据1w条，这肯定不会同步到主库的。

因为是主库写，从库读。不能乱来。

这就导致从库出现问题。我们通过`show slave status;`命令查看从库是否同步，发现sql转储线程停掉了。那肯定就不能继续同步数据了。

![70e8138d48054229bc5a5147d5ac3ff7.png][]

这个时候解决办法就是重新同步。具体方法时间关系就不再赘述了。附上思维导图：

[MySQL主从复制那些事儿| ProcessOn免费在线作图,在线流程图,在线思维导图][MySQL_ ProcessOn]

文章花费了我大量心血和时间，

吃水不忘挖井人，如果对你有帮助，别忘了三连，点赞收藏评论 ~

[ca8c6065a4884aa3b97acb4dd0c06fc7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/7991b5aa2a334c41a8fa91c4bef0daf3.png
[f8c43d771d274a4b811ace31b9dbd06d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/61bac4142c1f48d8a740c7efe9d45d70.png
[27d2d3dcde6c45d2beda391f8df503e5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/56ec681ad6124e87ac090982411f1c0b.png
[73458488e737436a8b3e68d61be26ade.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/954d7f2c18c24f3b959bffa0be55ff72.png
[3bcd0a37bb664ebda201375b2a60548f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/ec51835c904d46c5b50d2d0fc7ffbfcf.png
[5dd01aeae20a4143ac5b8c81c85f5618.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/e7ede19a8f6d4ee0bc5931b2098aff12.png
[28021eaf550a44f692e6f0bdfcc36bb1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/6ad30cb49b85471fad30089b35154822.png
[175aaafadec04b9bb4b2cb96499231b1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/8c5cc325a7d847348bc958763541a9bc.png
[50777960707742da8b4622f15f352f99.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/b6e7815dc6374f72acafbbf5e6cf2e49.png
[c0e7e584146946eda3cee6879fb574a7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/5b3bc95d6e3b46cea4ebe54cfef0a09e.png
[6a92685e0c1147ea9e2837f07d1e91ab.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/b49775f3c8714d169780ee6645fb08ef.png
[c057e37c96f2490ba155cc8e779d476e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/380ea75b072a412f8ef5a6ded545b2ea.png
[57aec6851a7745d7ad993675681ac0ae.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/2f977b043ca942d096f8e8026deaf736.png
[6f6e63995eca44ecb0f491e7109c73b3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/a38acdbcfe244968a30f36969257a12b.png
[8e5a96d549904dc2b877bf88c7912c0e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/91d92a4fcd524c8ba1790bc2af81bafa.png
[fcc696bce0664adb94f6e6397043951d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/4b537632736a490c85352233e95c0b5d.png
[16aace7506f94130803f59e5b972ff87.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/e2d36b010e904d579959773ab8f16879.png
[d929204e49d743248e883d030443a700.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/91275bdd225c45b4ac31304f030be135.png
[fa79e722ffb946caaa6d1e3fc8c94429.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/e846ca9ba21a4889a0a5a4f02b2fa20f.png
[eb63233f546f4d9dbb3db6fa8e7470ac.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/fecc0009615d4928b1d8df68e43a786b.png
[b741b765c1684fa3a9f3e56c600932f1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/bf975e31709d4a1abdc62bf23524bf4d.png
[4d6a84e8bf9c4cfc899d0dad4bdedf0a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/66763f3b4f744b4d8283d5cf3fb3a03c.png
[cadd749c43fc44c492da0d1d16aa8203.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/3b98dde37c3947f5a8f35186309b6c63.png
[6697994ef5bc4237ad5b40d9974066fd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/d8941c33a0af469081353ca30ea31a90.png
[72f47c5758a24b0bb9eb316c8544a226.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/c65a40d804f1418b946d9cd025248aa4.png
[baac881e297a41c7bc9d0ac912fa2cf9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/f25343d71911467bb83b55996af7621e.png
[fa7175b3e7b64635b20322846ff8c7fa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/3c0f5b87b5004dc7adb1c8719268414d.png
[c1727709d19b4ad5a488b6838a4ebce5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/47bfe3ad2e734c84a5a3a899ccbd96af.png
[e4b1f4f4856b4ad2ab494a9f8c4a640b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/4f7932ce34ca4aa286a04771777d98ef.png
[70e8138d48054229bc5a5147d5ac3ff7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/fe130903837a41a486bcb3f9de2057b1.png
[MySQL_ ProcessOn]: https://www.processon.com/view/link/6280dc6d1efad40df042ddda