docker部署mysql集群并与springboot整合

青旅半醒 2024-03-26 00:36 95阅读 0赞

## 一、mysql的集群部署方案 ##

MySQL 集群部署方案有以下几种：

1.  MySQL Cluster：MySQL 官方提供的 MySQL 集群方案，它通过对 MySQL 的源码进行修改，实现了 MySQL 集群的分布式功能。

1.  Galera Cluster：Galera Cluster 是一个基于 MySQL 的高可用性解决方案，它通过对 MySQL 的主从复制功能进行扩展，实现了 MySQL 集群的高可用性和扩展性。

1.  Percona XtraDB Cluster：Percona XtraDB Cluster 是一个基于 MySQL 的高可用性解决方案，它通过对 MySQL 的主从复制功能进行扩展，实现了 MySQL 集群的高可用性和扩展性。

1.  MySQL Replication：MySQL Replication 是 MySQL 官方提供的高可用性方案，它通过对 MySQL 的主从复制功能进行扩展，实现了 MySQL 集群的高可用性。

1.  MySQL Proxy：MySQL Proxy 是一个 MySQL 代理，它可以将多个 MySQL 实例转化为一个 MySQL 集群，提供了对 MySQL 集群的负载均衡、故障转移等功能。

## 二、docker部署mysql主从复制（一主多从） ##

主节点

准备docker-compose.yml文件

version: '3.1'
    services:
      mysql-master:
        image: mysql:8.0.32
        container_name: mysql-master
        restart: always 
        network_mode: "host"
        environment:         
          TZ: Asia/Shanghai # 时区上海
          MYSQL_ROOT_PASSWORD: "123456" #mysql密码
        ports:
          - "3306:3306"
        volumes:
          - ./data:/var/lib/mysql
          - ./log:/var/log/mysql
          - ./conf:/etc/mysql/conf.d

准备 my.cnf配置文件(可自行去官网下载，修改以下字段即可)

[mysqld]
    #Mysql服务的唯一编号 每个mysql服务Id需唯一
    server-id=11
    # [必须]启用二进制日志
    log-bin=mysql-bin
    log-bin-index=mysql-bin.index
    binlog-ignore-db=mysql
    # 设置需要同步的数据库 binlog_do_db = 数据库名； 
    # 如果是多个同步库，就以此格式另写几行即可。
    # 如果不指明对某个具体库同步，表示同步所有库。除了binlog-ignore-db设置的忽略的库
    # 跳过所有的错误，继续执行复制操作
    slave-skip-errors=all
    # 二进制日志过期清理时间。默认值为0，表示不自动清理。
    binlog_expire_logs_seconds=2592000  
    
    
    #以下内容每个节点保持一致
    # By default we only accept connections from localhost
    bind-address="0.0.0.0"
    #数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
    character-set-server=utf8mb4
    #数据库字符集对应一些排序等规则，注意要和character-set-server对应
    collation-server=utf8mb4_general_ci
    #设置client连接mysql时的字符集,防止乱码
    init_connect='SET NAMES utf8mb4'
    #是否对sql语句大小写敏感，1表示不敏感
    lower_case_table_names = 1
    #设置加密方式
    default_authentication_plugin=mysql_native_password

从节点

准备docker-compose.yml文件

version: '3.1'
    services:
      mysql-master:
        image: mysql:8.0.32
        container_name: mysql-slave01
        restart: always 
        network_mode: "host"
        environment:         
          TZ: Asia/Shanghai # 时区上海
          MYSQL_ROOT_PASSWORD: "123456" #mysql密码
        ports:
          - "3306:3306"
        volumes:
          - ./data:/var/lib/mysql
          - ./log:/var/log/mysql
          - ./conf:/etc/mysql/conf.d

准备 my.cnf配置文件

[mysqld]
    #Mysql服务的唯一编号 每个mysql服务Id需唯一
    server-id=12
    # 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
    log-bin=mysql-bin
    log-bin-index=mysql-bin.index
    binlog-ignore-db=mysql
    # 设置需要同步的数据库 binlog_do_db = 数据库名； 
    # 如果是多个同步库，就以此格式另写几行即可。
    # 如果不指明对某个具体库同步，表示同步所有库。除了binlog-ignore-db设置的忽略的库
    # 跳过所有的错误，继续执行复制操作
    slave-skip-errors = all
    # slave设置为只读（具有super权限的用户除外）
    read_only=1
    ## relay_log配置中继日志
    relay_log=mysql-relay-bin
    ## log_slave_updates表示slave将复制事件写进自己的二进制日志
    log_slave_updates=1
    # 二进制日志过期清理时间。默认值为0，表示不自动清理。
    binlog_expire_logs_seconds=2592000  
    
    
    #以下内容每个节点保持一致
    # By default we only accept connections from localhost
    bind-address="0.0.0.0"
    #数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
    character-set-server=utf8mb4
    #数据库字符集对应一些排序等规则，注意要和character-set-server对应
    collation-server=utf8mb4_general_ci
    #设置client连接mysql时的字符集,防止乱码
    init_connect='SET NAMES utf8mb4'
    #是否对sql语句大小写敏感，1表示不敏感
    lower_case_table_names = 1
    #设置加密方式
    default_authentication_plugin=mysql_native_password

配置mysql主从

进入master节点

docker exec -it mysql-master mysql -u root -p

执行如下代码添加slave01、slave02用户。

#在主机MySQL里执行授权命令 
    CREATE USER 'slave01'@'%' IDENTIFIED BY '123456'; 
    GRANT REPLICATION SLAVE ON *.* TO 'slave01'@'%'; 
     
    #此语句必须执行。否则见下面。 
    ALTER USER 'slave01'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
    
    #在主机MySQL里执行授权命令 
    CREATE USER 'slave02'@'%' IDENTIFIED BY '123456'; 
    GRANT REPLICATION SLAVE ON *.* TO 'slave02'@'%'; 
     
    #此语句必须执行。否则见下面。 
    ALTER USER 'slave02'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; 
    flush privileges;
    
    #查询master的状态,记住file和position对应的信息，为后来添加从到主做准备。
    show master status;
    
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000003 |     1912 |              | mysql            |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

slave01中配置主从

docker exec -it mysql-slave01 mysql -u root -p
    
    #host替换为配置master的ip，master_log_file就是之前master对应的file信息，master_log_pos就是之前position对应的信息
    change master to master_host='192.168.10.11', master_user='slave01', master_password='123456', master_port=3306, master_log_file='mysql-bin.000003', master_log_pos=1912, master_connect_retry=30;
    
    start slave;
    # 查看slave信息，如果Slave_IO_Running、Slave_SQL_Running两个信息是yes代表搭建从库成功
    show slave status \G;
    
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for source to send event
                      Master_Host: 192.168.10.11
                      Master_User: slave01
                      Master_Port: 3306
                    Connect_Retry: 30
                  Master_Log_File: mysql-bin.000003
              Read_Master_Log_Pos: 1912
                   Relay_Log_File: mysql-relay-bin.000002
                    Relay_Log_Pos: 326
            Relay_Master_Log_File: mysql-bin.000003
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 1912
                  Relay_Log_Space: 536
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 11
                      Master_UUID: 18e50f5e-cba8-11ed-b275-000c2930730f
                 Master_Info_File: mysql.slave_master_info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
           Master_public_key_path: 
            Get_master_public_key: 0
                Network_Namespace: 
    1 row in set, 1 warning (0.00 sec)

slave02同上

[https://cloud.tencent.com/developer/article/2074869][https_cloud.tencent.com_developer_article_2074869]

## 三、springboot整合mysql集群 ##

读写分离，主库写从库读

以下是一些流行的分库分表中间件：

1.  Sharding-JDBC：Sharding-JDBC是一个非常流行的分库分表中间件，可以帮助您轻松地实现数据库的水平扩展。它支持多种分片策略，并且可以与Spring Boot集成。如果您需要对数据库进行水平扩展，那么Sharding-JDBC是一个非常好的选择。

1.  MyCAT：MyCAT是一个开源的分布式数据库系统，可以实现MySQL的分库分表和读写分离。它支持多种分片策略，并且可以与MySQL和MariaDB集成。

1.  TDDL：TDDL是一个阿里巴巴开发的分布式数据库中间件，可以实现MySQL的分库分表和读写分离。它支持多种分片策略，并且可以与Spring和MyBatis集成。

1.  Cobar：Cobar是一个开源的分布式数据库中间件，可以实现MySQL的分库分表和读写分离。它支持多种分片策略，并且可以与MySQL和MariaDB集成。

1.  ### Sharding-JDBC读写分离 ###

参考官方文档 [Yaml配置 :: ShardingSphere (apache.org)][Yaml_ _ ShardingSphere _apache.org]

注：shardingsphere-jdbc5和之前的版本配置有区别，对应的springboot-start包名有所不同

引入依赖

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.10</version>
        <relativePath/> 
    </parent>

<dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
        <version>5.2.1</version>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.5.3.1</version>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <scope>runtime</scope>
    </dependency>

编写yml配置

spring:
      #sharding-jdbc
      shardingsphere:
        datasource:
          names: master,slave01,slave02
          master:
            type: com.zaxxer.hikari.HikariDataSource
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://192.168.10.11:3306/test?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useUnicode=true
            username: root
            password: 123456
          slave01:
            type: com.zaxxer.hikari.HikariDataSource
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://192.168.10.12:3306/test?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useUnicode=true
            username: root
            password: 123456
          slave02:
            type: com.zaxxer.hikari.HikariDataSource
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://192.168.10.13:3306/test?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useUnicode=true
            username: root
            password: 123456
        rules:
          readwrite-splitting:
            data-sources:
              ## 读写分离名称
              pr_ds:
                type: STATIC #静态类型，（动态Dynamic）
                props:
                  ## 自动发现数据源名称
                  # auto-aware-data-source-name: slave0
                  ## 写数据源名称
                  write-data-source-name: master
                  ## 读数据源名称
                  read-data-source-names: slave01,slave02
                  ## 负载均衡算法名称
                  load-balancer-name: read-write-separation-load-balancer
            load-balancers:
              read-write-separation-load-balancer:
                ## 负载均衡算法类型
                type: ROUND_ROBIN
        props:
          sql-show: true # 开启SQL显示，默认false

测试

@Test
        void saveAndGet() {
            Student student = new Student("test", 11);
            studentService.save(student);
    
            Student student1 = studentService.getById(10);
            System.out.println(student1);
        }

日志输出

Creating a new SqlSession
    SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2a7ebe07] was not registered for synchronization because synchronization is not active
    JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.MasterSlaveConnection@3cd46491] will not be managed by Spring
    ==>  Preparing: INSERT INTO student ( name, age ) VALUES ( ?, ? )
    2023-04-06 21:14:40.535  INFO 22688 --- [main] ShardingSphere-SQL: Logic SQL: INSERT INTO student  ( name,age ) VALUES ( ?,? )
    2023-04-06 21:14:40.535  INFO 22688 --- [main] ShardingSphere-SQL: SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@7b29cdea, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@f08d676)
    2023-04-06 21:14:40.535  INFO 22688 --- [main] ShardingSphere-SQL: Actual SQL: master ::: INSERT INTO student  ( name,age )  VALUES  ( ?,? )
    ==> Parameters: test(String), 11(Integer)
    <==    Updates: 1
    Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2a7ebe07]
    Creating a new SqlSession
    SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@511d5e6e] was not registered for synchronization because synchronization is not active
    JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.MasterSlaveConnection@299eca90] will not be managed by Spring
    ==>  Preparing: SELECT id,name,age FROM student WHERE id=?
    2023-04-06 21:14:40.588  INFO 22688 --- [main] ShardingSphere-SQL: Logic SQL: SELECT id,name,age FROM student WHERE id=? 
    2023-04-06 21:14:40.588  INFO 22688 --- [main] ShardingSphere-SQL: SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@6b289535, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@74619273), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@74619273, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=17, distinctRow=false, projections=[ColumnProjection(owner=null, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=name, alias=Optional.empty), ColumnProjection(owner=null, name=age, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@38c55a8a, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@1d7f7962, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@32d1d6c5, containsSubquery=false)
    2023-04-06 21:14:40.588  INFO 22688 --- [main] ShardingSphere-SQL: Actual SQL: slave01 ::: SELECT id,name,age FROM student WHERE id=? 
    ==> Parameters: 10(Integer)
    <==    Columns: id, name, age
    <==        Row: 10, java, 22
    <==      Total: 1
    Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@511d5e6e]

显示插入走的是master，查询走的是slave01

Actual SQL: **master** ::: INSERT INTO student ( name,age ) VALUES ( ?,? )

Actual SQL: **slave01** ::: SELECT id,name,age FROM student WHERE id=?

1.  ### Sharding-JDBC分库分表 ###

水平分表

server:
      port: 8089
    
    spring:
      shardingsphere:
        mode:
          type: memory
        # 是否开启
        datasource:
          # 数据源（逻辑名字）
          names: master
          # 配置数据源
          master:
            type: com.zaxxer.hikari.HikariDataSource
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://192.168.10.11:3306/test?useSSL=false&autoReconnect=true&characterEncoding=UTF-8&serverTimezone=UTC
            username: root
            password: 123456
        # 分片的配置
        rules:
          sharding:
            # 表的分片策略
            tables:
              # 逻辑表的名称
              student:
                # 数据节点配置，采用Groovy表达式
                actual-data-nodes: master.student_$->{0..3}
                # 配置策略
                table-strategy:
                  # 用于单分片键的标准分片场景
                  standard:
                    sharding-column: id
                    # 分片算法名字
                    sharding-algorithm-name: student_inline
                key-generate-strategy: # 主键生成策略
                  column: id  # 主键列
                  key-generator-name: snowflake  # 策略算法名称(推荐使用雪花算法)
            key-generators:
              snowflake:
                type: SNOWFLAKE
            sharding-algorithms:
              student_inline:
                type: inline
                props:
                  algorithm-expression: student_$->{id % 4}
        props:
          # 日志显示具体的SQL
          sql-show: true
    
    
    logging:
      level:
        com.wang.test.demo: DEBUG
    
    mybatis-plus:
      configuration:
        #在映射实体或者属性时，将数据库中表名和字段名中的下划线去掉，按照驼峰命名法映射 address_book ---> addressBook
        map-underscore-to-camel-case: true

[https_cloud.tencent.com_developer_article_2074869]: https://cloud.tencent.com/developer/article/2074869
[Yaml_ _ ShardingSphere _apache.org]: https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-jdbc/configuration/config-yaml/