Mysql主从复制，读写分离加分表分库

一.准备2台Linux服务器 在每台服务器上安装Mysql5.7

1.1 下载Mysql5.7安装包 推荐大家直接下载我的

链接: https://pan.baidu.com/s/1oKRlfq1M4PmesMXDv4AZAw 提取码: 1999

1.2 在linux中输入如下命令

1.3 上传mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz到/usr/local路径下

1.4 解压mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz

[root@VM-12-10-centos ~]# cd /usr/local/
[root@VM-12-10-centos local]# tar -zxvf  mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz

1.5 将解压的mysql-5.7.36-linux-glibc2.12-x86_64移动并命名为mysql

[root@VM-12-10-centos local]# mv mysql-5.7.36-linux-glibc2.12-x86_64  mysql

1.6 进入mysql 创建数据目录 并赋予权限

[root@VM-12-10-centos local]# cd mysql
[root@VM-12-10-centos mysql]# mkdir data
[root@VM-12-10-centos mysql]# chmod -R 777 /usr/local/mysql/data
## 如果出现：chmod: invalid mode: ‘–R’  是减号 有问题，复制出来，在编辑下 减号

1.7 创建用户 、组、并将用户加入组,修改配置文件

[root@VM-12-10-centos mysql]# groupadd mysql
[root@VM-12-10-centos mysql]# useradd -g mysql mysql
[root@VM-12-10-centos mysql]# vi /etc/my.cnf
## 建议直接复制我的进行覆盖my.cnf文件
[mysqld]
bind-address=0.0.0.0
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
log-error=/usr/local/mysql/data/mysql.err
pid-file=/usr/local/mysql/data/mysql.pid
#character config
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true

1.8 MySQL 安装并初始化mysql

## 进入mysql bin 目录下面
[root@VM-12-10-centos mysql]# cd bin
## 执行命令
[root@VM-12-10-centos bin]# ./mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data/ --basedir=/usr/local/mysql/
## 有很多人在执行的过程中报错:
./mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory
## 是因为没有安装依赖  libaio
[root@VM-12-10-centos bin] yum -y install libaio-devel.x86_64
[root@VM-12-10-centos bin] yum -y install numactl
## 查看mysql密码（root@localhost的地方是密码）
[root@VM-12-10-centos bin]# cat /usr/local/mysql/data/mysql.err
## 2023-04-18T13:03:07.935182Z 1 [Note] A temporary password is generated for root@localhost: HG:2Ieh=kVlg

1.9 添加软连接，并重启mysql服务

[root@VM-12-10-centos bin]# ln -s /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
[root@VM-12-10-centos bin]# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
[root@VM-12-10-centos bin]# service mysql start
## 出现Starting MySQL. SUCCESS! 则说明启动成功

1.10 登录mysql 修改密码，修改访问权限

## 进入mysql  bin目录下面
[root@VM-12-10-centos bin]# ./mysql -hlocalhost -uroot -p
## 输入密码
## 修改密码（设置密码尽量设置复杂一点，拒绝弱口令）
mysql> set password=password('******');
## 刷新配置
mysql> flush privileges;
## 修改访问权限
mysql> use mysql;
mysql> update user set Host='%' where User='root';
flush privileges;

1.11 开启防火墙 同时注意服务器3306端口是否开放 需要去服务器网站添加端口规则

## 退出mysql客户端
mysql> exit;
## 打开防火墙
[root@VM-12-10-centos bin]# systemctl start firewalld.service
## 将3306加入防火墙的白名单，然后重启防火墙
[root@VM-12-10-centos bin]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
[root@VM-12-10-centos bin]# systemctl restart firewalld.service
## 查看防火墙开放端口
[root@VM-12-10-centos bin]# firewall-cmd --zone=public --list-ports

1.12 访问测试

通过工具：Navicat Premium 进行连接，如果出现以下错误，查看防火墙是否开启
防火墙关闭命令
systemctl stop firewalld.service
如果不想关闭防火墙，则将3306加入防火墙的白名单，然后重启防火墙
firewall-cmd --zone=public --add-port=3306/tcp --permanent
systemctl restart firewalld.service
// 查看防火墙开放端口
firewall-cmd --zone=public --list-ports
再次连接连接测试
数据库安装成功

二.配置2个Mysql的主从

2.1 主库修改配置文件

## 主库
[root@VM-12-10-centos /]# vim /etc/my.cnf
## 修改内容 在最为尾加上以下代码即可
log-bin=mysql-bin   #[必须]启用二进制日志
server-id=1         #[必须]服务器唯一ID(唯一即可)

2.2 重启服务

[root@VM-12-10-centos /]# service mysql restart
Shutting down MySQL.. SUCCESS!

2.3 登录主库mysql 创建数据库同步的用户并授权

## 登录用户密码
[root@VM-12-10-centos /]# mysql -uroot -p
## 创建数据同步的用户并授权
mysql> GRANT REPLICATION SLAVE ON *.* to 'test'@'%' identified by 'test@123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
## 通过Navicat登录Mysql数据库，查看master同步状态 随便进一个库 输入查询语句 
show master status;
## 记录下结果中File和Position的值 后面从库绑定需要这两个变量值
File mysql-bin.000001
Position 154

2.4 从库修改配置文件

## 主库
[root@VM-24-4-centos ~]# vim /etc/my.cnf
## 修改内容 在最为尾加上以下代码即可
server-id=2         #[必须]服务器唯一ID(唯一即可)

2.5 重启服务

[root@VM-24-4-centos ~]# service mysql restart
Shutting down MySQL.. SUCCESS!

2.6 登录从库mysql 绑定主库同步用户及同步偏移量

## 登录用户密码
[root@VM-12-10-centos /]# mysql -uroot -p
## 绑定主库同步用户及偏移量
## 主库服务器ip 118.126.91.205 
## 主库同步用户 test 密码 test@123456 
## master_log_file File  
## master_log_pos Position 
mysql> change master to master_host='118.126.91.205',master_user='test',master_password='test@123456',master_log_file='mysql-bin.000001',master_log_pos=154;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

2.7 查看从库状态

mysql> show slave statusG;
## Slave_IO_Running,Slave_SQL_Running 如果都为yes 则配置成功

2.8 去主库创建数据库或表 看从库是否数据同步

测试主从复制是否成功
第一步是在主库中创建新的数据库 然后查看从库中是否有创建的新库
一定不要在主库删除从库没有的数据库或数据表 这样会导致主从同步失效 需重新配置

三.运用Sharding-JDBC来实现读写分离 分库分表

以下放入代码 大家创建springboot项目复制粘贴类文件即可

3.1 项目目录

3.2 项目代码

pom.xml

 <dependencies>
        <!--Springboot项目自带 -->
        <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>
        <!--Springboot Web项目 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
        </dependency>

        <!--Mysql依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- mybatis 用于接口注解快速开发(@Insert @Select @Update) -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
        <!--用于扫描mybatis注解 @Mapper @MapperScan-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

        <!-- hutool  -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.20</version>
        </dependency>

        <!--依赖数据源druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.21</version>
        </dependency>

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

entity

package com.weige.javaskillpoint.entity;

import lombok.Data;

@Data
public class Test {
    private Long id;
    private String name;
    private Integer sex;
}

controller

package com.weige.javaskillpoint.controller;

import com.weige.javaskillpoint.entity.Test;
import com.weige.javaskillpoint.service.TestService;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.util.List;

@RestController
@RequestMapping("/test")
public class TestController {

    @Resource
    private TestService testService;

    @GetMapping("/v1")
    public List<Test> getIndex() {
        return testService.getList();
    }

    @PostMapping("/insert")
    public String insert(@RequestBody Test index) {
    	// id自定义 当前时间戳
        index.setId(System.currentTimeMillis());
        testService.insert(index);
        return "添加成功";
    }
}

service

package com.weige.javaskillpoint.service;

import com.weige.javaskillpoint.entity.Test;

import java.util.List;

public interface TestService {

    void insert(Test index);

    List<Test> getList();
}

serviceImpl

package com.weige.javaskillpoint.service.impl;

import com.weige.javaskillpoint.dao.TestDao;
import com.weige.javaskillpoint.entity.Test;
import com.weige.javaskillpoint.service.TestService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service
public class TestServiceImpl implements TestService {
    @Resource
    private TestDao testDao;

    @Override
    public void insert(Test index) {
        testDao.insert(index);
    }

    @Override
    public List<Test> getList() {
        return testDao.getList();
    }
}

dao

package com.weige.javaskillpoint.dao;

import com.weige.javaskillpoint.entity.Test;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface TestDao {

    @Insert("INSERT into test(id,name,sex) values(#{id},#{name},#{sex})")
    void insert(Test index);

    @Select("select * from test")
    List<Test> getList();

}

3.3 application.yml配置文件

这里有3种配置

• 读写分离
• 分库分表
• 读写分离加分库分表

3.3.1 读写分离

• 在主数据库中118.126.91.205创建test数据库 从库会自动同步test数据库
• 在主test数据库中创建test表 从库自动同步test表
• 建表语句

-- 建表语句 
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` bigint(19) NOT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci NOT NULL COMMENT '姓名',
  `sex` tinyint(1) NOT NULL COMMENT '性别',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_german2_ci ROW_FORMAT = Dynamic;

server:
  port: 8080
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    # 参数配置，显示sql
    props:
      sql:
        show: true
    # 配置数据源
    datasource:
      # 给每个数据源取别名，任意取名字ds0,ds1
      names: ds0,ds1
      # 给ds0每个数据源配置数据库连接信息
      ds0:
        # 配置druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://118.126.91.205:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: ****** # 输入自己的数据库用户密码
        maxPoolSize: 100
        minPoolSize: 5
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://43.143.132.109:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: ****** # 输入自己的数据库用户密码
        maxPoolSize: 100
        minPoolSize: 5
    sharding:
      master-slave-rules:
        ds0:
          master-data-source-name: ds0
          slave-data-source-names: ds1
          load-balance-algorithm-type: round_robin

3.3.2 分库分表

• 单独分库分表不需要主从配置
• 在数据库中118.126.91.205创建test_0，test_1数据库
• 在test_0，test_1数据库中创建test_0 test_1表
• 我们只需要看根据sex是否分库到test_0或test_1 根据id是否分表到test_0或test_1
• 建表语句

-- 建表语句 
DROP TABLE IF EXISTS `test_0`;
DROP TABLE IF EXISTS `test_1`;
CREATE TABLE `test_0`  (
  `id` bigint(19) NOT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci NOT NULL COMMENT '姓名',
  `sex` tinyint(1) NOT NULL COMMENT '性别',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_german2_ci ROW_FORMAT = Dynamic;
CREATE TABLE `test_1`  (
  `id` bigint(19) NOT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci NOT NULL COMMENT '姓名',
  `sex` tinyint(1) NOT NULL COMMENT '性别',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_german2_ci ROW_FORMAT = Dynamic;

server:
  port: 8080

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    # 参数配置，显示sql
    props:
      sql:
        show: true
    # 配置数据源
    datasource:
      # 给每个数据源取别名，ds0,ds1
      names: ds0,ds1
      # 给ds0每个数据源配置数据库连接信息
      ds0:
        # 配置druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://118.126.91.205:3306/test_0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: ****** # 输入自己的数据库用户密码
        maxPoolSize: 100
        minPoolSize: 5
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://118.126.91.205:3306/test_1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: ****** # 输入自己的数据库用户密码
        maxPoolSize: 100
        minPoolSize: 5
    sharding:
      default-data-source-name: ds0
      tables:
        test:
          actual-data-nodes: ds$->{0..1}.test_$->{0..1}
          # 拆分库策略
          database-strategy:
            inline:
              sharding-column: sex    # 分片字段（分片键）
              algorithm-expression: ds$->{sex % 2} # 分片算法表达式
          table-strategy:
            inline:
              sharding-column: id
              algorithm-expression: test_$->{id % 2}
          key-generator:
            column: id
            type: SNOWFLAKE

3.3.2 读写分离加分库分表

• 读写分离加分库分表需要主从数据库
• 在主数据库中118.126.91.205创建test_0，test_1数据库 从数据库同步test_0，test_1数据库
• 在test_0，test_1数据库中创建test_0 test_1表 从数据库同步test_0 test_1表
• 我们需要看根据sex是否分库到test_0或test_1 根据id是否分表到test_0或test_1 同时数据是否在从库中同步
• 建表语句

-- 建表语句 
DROP TABLE IF EXISTS `test_0`;
DROP TABLE IF EXISTS `test_1`;
CREATE TABLE `test_0`  (
  `id` bigint(19) NOT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci NOT NULL COMMENT '姓名',
  `sex` tinyint(1) NOT NULL COMMENT '性别',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_german2_ci ROW_FORMAT = Dynamic;
CREATE TABLE `test_1`  (
  `id` bigint(19) NOT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci NOT NULL COMMENT '姓名',
  `sex` tinyint(1) NOT NULL COMMENT '性别',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_german2_ci ROW_FORMAT = Dynamic;

server:
  port: 8080

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    # 参数配置，显示sql
    props:
      sql:
        show: true
    # 配置数据源
    datasource:
      # 给每个数据源取别名，任意取名字master1,slave1
      names: ds0,ds1,ds2,ds3
      # 给master1每个数据源配置数据库连接信息
      ds0:
        # 配置druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://118.126.91.205:3306/test_0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: ****** # 输入自己的数据库用户密码
        maxPoolSize: 100
        minPoolSize: 5
      ds1:
        # 配置druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://118.126.91.205:3306/test_1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: ****** # 输入自己的数据库用户密码
        maxPoolSize: 100
        minPoolSize: 5
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://43.143.132.109:3306/test_0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: ****** # 输入自己的数据库用户密码
        maxPoolSize: 100
        minPoolSize: 5
      ds3:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://43.143.132.109:3306/test_1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: ****** # 输入自己的数据库用户密码
        maxPoolSize: 100
        minPoolSize: 5
    sharding:
      master-slave-rules:
        ds0: #这里的ds0 为主库 ds2为ds0的从库
          master-data-source-name: ds0
          slave-data-source-names: ds2
          load-balance-algorithm-type: round_robin
        ds1: #这里的ds1 为主库 ds3为ds1的从库
          master-data-source-name: ds1
          slave-data-source-names: ds3
          load-balance-algorithm-type: round_robin
      default-data-source-name: ds0
      tables:
        test:
          actual-data-nodes: ds$->{0..1}.test_$->{0..1}
          # 拆分库策略
          database-strategy:
            inline:
              sharding-column: sex    # 分片字段（分片键）
              algorithm-expression: ds$->{sex % 2} # 分片算法表达式
          table-strategy:
            inline:
              sharding-column: id
              algorithm-expression: test_$->{id % 2}
          key-generator:
            column: id
            type: SNOWFLAKE