devbox
煮酒与君饮
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

搭建MYSQL主从_mysql主从搭建

作者：煮酒与君饮 | 2024-08-07 21:39:23

赞

踩

mysql主从搭建

搭建MYSQL主从

一、背景

因为在项目开发需求，本地无法直接连接服务器MYSQL主从，考虑在开发测试时，可以测试方便发现基本问题。考虑在本地虚拟机中搭建，模拟测试环境及线上环境，故选用虚拟机的docker来搭建。选择搭建一主两从

二、MYSQL主从模式简介

读写分离，顾名思义就是读和写分开，更具体来说，就是：

  • 写操作在主数据库进行
  • 读操作在从数据库进行

使用读写分离的根本目的就是为了提高并发性能，如果读写都在同一台MySQL上实现，相信会不如一台MySQL写，另外两台MySQL读这样的配置性能高。另一方面，在很多时候都是读操作的请求要远远高于写操作，这样就显得读写分离非常有必要了。

主从复制，顾名思义就是把主库的数据复制到从库中，因为读写分离之后，写操作都在主库进行，但是读操作是在从库进行的，也就是说，主库上的数据如果不能复制到从库中，那么从库就不会读到主库中的数据。严格意义上说，读写分离并不要求主从复制，只需要在主库写从库读即可，但是如果没有了主从复制，读写分离将失去了它的意义。因此读写分离通常与主从复制配合使用。

因为本示例使用的是MySQL，这里就说一下MySQL主从复制的原理，如下图所示：
在这里插入图片描述

工作流程如下：

  • 主库修改数据后，将修改日志写入binlog
  • 从库的I/O线程读取主库的binlog，并拷贝到从库本地的binlog中
  • 从库本地的binlog被SQL线程读取，执行其中的内容并同步到从库中

三、环境搭建

1、前置环境

基础环境安装：

  • docker
  • docker-compose
  • mysql镜像版本：mysql:5.7

2、创建以下目录

  • mysql
    • docker-compose.yml
    • config
      • master.cnf
      • slave1.cnf
      • slave2.cnf

3、配置内容

docker-compose.yml
version: '3.6'
services:
  mysql-master:
    image: mysql:5.7
    container_name: mysql-master
    restart: always
    privileged: true
    environment:
      MYSQL_ROOT_PASSWORD: 123456
      TZ: Asia/Shanghai
    command:
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
      --max_allowed_packet=128M;
    ports:
      - 3316:3306
    volumes:
      - ./config/master.cnf:/etc/mysql/my.cnf
  mysql-slave1:
    image: mysql:5.7
    container_name: mysql-slave1
    restart: always
    privileged: true
    environment:
      MYSQL_ROOT_PASSWORD: 123456
      TZ: Asia/Shanghai
    command:
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
      --max_allowed_packet=128M;
    ports:
      - 3317:3306
    volumes:
      - ./config/slave1.cnf:/etc/mysql/my.cnf
  mysql-slave2:
    image: mysql:5.7
    container_name: mysql-slave2
    restart: always
    privileged: true
    environment:
      MYSQL_ROOT_PASSWORD: 123456
      TZ: Asia/Shanghai
    command:
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
      --max_allowed_packet=128M;
    ports:
      - 3318:3306
    volumes:
      - ./config/slave2.cnf:/etc/mysql/my.cnf


  • 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
master.cnf
[mysqld]
pid-file  = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir   = /var/lib/mysql
#log-error  = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

port        = 3306
#全局唯一，取值[1,2^32-1]，默认为1
server-id = 1

log-bin = master-bin 
#只保留7天的二进制日志，以防磁盘被日志占满(可选)
expire-logs-days = 7

#不备份的数据库 （可选）
binlog-ignore-db=information_schema  
binlog-ignore-db=performation_schema
binlog-ignore-db=sys


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
slave1.cnf
[mysqld]
pid-file  = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir   = /var/lib/mysql
#log-error  = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

port        = 3306
#全局唯一，取值[1,2^32-1]，默认为1
server-id = 2

#只保留7天的二进制日志，以防磁盘被日志占满(可选)
expire-logs-days = 7

#不备份的数据库 （可选）
binlog-ignore-db=information_schema  
binlog-ignore-db=performation_schema
binlog-ignore-db=sys

## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=slave1-bin   
## relay_log配置中继日志
relay_log=mysql-relay-bin  
read_only=1  ## 设置为只读,该项如果不设置，表示slave可读可写

  • 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
slave2.cnf
[mysqld]
pid-file  = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir   = /var/lib/mysql
#log-error  = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

port        = 3306
#全局唯一，取值[1,2^32-1]，默认为1
server-id = 3

#只保留7天的二进制日志，以防磁盘被日志占满(可选)
expire-logs-days = 7

#不备份的数据库 （可选）
binlog-ignore-db=information_schema  
binlog-ignore-db=performation_schema
binlog-ignore-db=sys

## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=slave2-bin   
## relay_log配置中继日志
relay_log=mysql-relay-bin  
read_only=1  ## 设置为只读,该项如果不设置，表示slave可读可写

  • 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

4、启动docker容器

进入mysql目录，执行启动命令启动docker容器

  • 启动：docker-compose up -d
  • 查看：docker-compose ps
  • 停止：docker-compose down

5、设置主从

1、进行master容器，连接mysql

  1. docker exec -it mysql-master bash
  2. muysql -r root -p 123456

2、查看master status
show master status

+-------------------+----------+--------------+--------------------------------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB                           | Executed_Gtid_Set |
+-------------------+----------+--------------+--------------------------------------------+-------------------+
| master-bin.000003 |      643 |              | information_schema,performation_schema,sys |                   |
+-------------------+----------+--------------+--------------------------------------------+-------------------+

  • 1
  • 2
  • 3
  • 4
  • 5

记录下file和position的值

3、分别进行slave1和slave2容器并连接mysql

4、然后执行1)：

change master to 
master_host='192.168.232.130',--master的IP
master_user='root',--连接master的用户名
master_log_file='master-bin.000003',--第2步骤查询到的结果file字段的值
master_log_pos=154,--第2步骤查询到的结果position字段的值
master_port=3316,--master的端口
master_password='123456';--连接master的密码

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

执行2 ：开启从节点start slave;

执行3：显示从节点信息show slave status;

6、查看数据同步

在主节点master上添加数据库、表以及数据看从节点是否同步。

四、SpringBoot+MYSQL主从

搭建MYSQL主从只是为了使用，重点是在于怎么做读写分离。

在网上也看到了很多种做读写分离的方案，综合考虑，这里我选择了shardingsphere来做读写分离。

环境

  1. jdk1.8
  2. springboot：2.7.5
  3. Mybatis plus：3.5.1
  4. shardingsphere-jdbc：5.1.1
  5. druid：1.1.22
  6. maven：3.6.1

新建springboot项目

Maven版本：

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.1.1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.22</version>
        </dependency>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

使用MybatisX插件生成代码

步骤参考：MybatisX插件使用方式

配置参考

server:
  port: 8084
spring:
  shardingsphere:
    datasource:
      names: master,slave1,slave2
      master:
        url: jdbc:mysql://192.168.232.130:3316/test?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: 123456
      slave1:
        url: jdbc:mysql://192.168.232.130:3317/test?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: 123456
      slave2:
        url: jdbc:mysql://192.168.232.130:3318/test?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: 123456
    rules:
      sharding:
      readwrite-splitting:
        load-balancers:
          round_robin:
            type: ROUND_ROBIN
        data-sources:
          read_write_db:
            type: Static
            props:
              write-data-source-name: master
              read-data-source-names: slave1,slave2
            load-balancer-name: round_robin
    props:
      sql-show: true


  • 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

准备测代码

MysqlInit.java

/**
 * 项目初始化执行
 */
@Slf4j
@Component
public class MysqlInit {

    @Autowired
    private OperationService operationService;

    @PostConstruct
    public void init(){
        for (int i = 0; i < 10000; i++) {
            operationService.save(i);
            if(i>100){
                operationService.query(i);
            }
        }
    }

}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

OperationService.java

/**
 * 数据库操作
 */
@Slf4j
@Service
public class OperationService {

    @Autowired
    private UserService userService;

    private static Random random = new Random();

    /**
     * 生成随机内容添加数据
     * @param i
     */
    @Async("mysqlThreadPool")
    public void save(int i) {
        String sex="男";
        if(i%2==0){
            sex= "女";
        }
        User user = new User(null, UUID.randomUUID().toString(),sex,random.nextInt(120));
        boolean save = userService.save(user);
        if(save){
            log.info("插入数据成功：{}",user);
        }else{
            log.error("插入数据失败");
        }
    }

    @Async("mysqlThreadPool")
    public void query(int i) {
        User user = userService.getById(random.nextInt(i));
        log.info("随机查询数据为：{}",user);
    }
}


  • 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

启动项目

控制台显示日志：

2022-11-08 10:26:22.082  INFO 28064 --- [  restartedMain] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2022-11-08 10:26:22.082  INFO 28064 --- [  restartedMain] ShardingSphere-SQL                       : Actual SQL: master ::: INSERT INTO user  ( name,sex,age )  VALUES  ( ?,?,? ) ::: [489d32c0-8689-4e05-b68e-96b046ae5046, 女, 22]
2022-11-08 10:26:22.084  INFO 28064 --- [  restartedMain] c.mengl.mysql.service.OperationService   : 插入数据成功：User [Hash = 1085628063, id=7396, name=489d32c0-8689-4e05-b68e-96b046ae5046, sex=女, age=22, serialVersionUID=1]
2022-11-08 10:26:22.085  INFO 28064 --- [  restartedMain] ShardingSphere-SQL                       : Logic SQL: SELECT id,name,sex,age FROM user WHERE id=? 
2022-11-08 10:26:22.085  INFO 28064 --- [  restartedMain] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-11-08 10:26:22.085  INFO 28064 --- [  restartedMain] ShardingSphere-SQL                       : Actual SQL: slave2 ::: SELECT id,name,sex,age FROM user WHERE id=?  ::: [5072]
2022-11-08 10:26:22.086  INFO 28064 --- [  restartedMain] c.mengl.mysql.service.OperationService   : 随机查询数据为：User [Hash = 1109246400, id=5072, name=b740fae3-0aae-4542-822d-8dd6c6d274ef, sex=女, age=13, serialVersionUID=1]
2022-11-08 10:26:22.086  INFO 28064 --- [  restartedMain] ShardingSphere-SQL                       : Logic SQL: INSERT INTO user  ( name,sex,age )  VALUES  ( ?,?,? )
2022-11-08 10:26:22.086  INFO 28064 --- [  restartedMain] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2022-11-08 10:26:22.086  INFO 28064 --- [  restartedMain] ShardingSphere-SQL                       : Actual SQL: master ::: INSERT INTO user  ( name,sex,age )  VALUES  ( ?,?,? ) ::: [52bebb40-10c9-4117-b2fa-ccaf6104c759, 男, 73]
2022-11-08 10:26:22.089  INFO 28064 --- [  restartedMain] c.mengl.mysql.service.OperationService   : 插入数据成功：User [Hash = -74377815, id=7397, name=52bebb40-10c9-4117-b2fa-ccaf6104c759, sex=男, age=73, serialVersionUID=1]
2022-11-08 10:26:22.089  INFO 28064 --- [  restartedMain] ShardingSphere-SQL                       : Logic SQL: SELECT id,name,sex,age FROM user WHERE id=? 
2022-11-08 10:26:22.089  INFO 28064 --- [  restartedMain] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-11-08 10:26:22.089  INFO 28064 --- [  restartedMain] ShardingSphere-SQL                       : Actual SQL: slave1 ::: SELECT id,name,sex,age FROM user WHERE id=?  ::: [2592]
2022-11-08 10:26:22.091  INFO 28064 --- [  restartedMain] c.mengl.mysql.service.OperationService   : 随机查询数据为：User [Hash = 1138329258, id=2592, name=8998491e-b737-475b-888f-39dd3b0ae65a, sex=女, age=55, serialVersionUID=1]


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

可以清晰的看到插入数据使用的是master节点，查询数据使用的是slave1、slave2节点切换。

至此，完成。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/煮酒与君饮/article/detail/944627
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号