文章目录

1 读写分离原理2 Mysql 配置主从复制2.1 创建2个Mysql 数据库服务，并启动两个Mysql服务2.2 配置Master库的/etc/my.cnf2.3 配置Slave库的/etc/my.cnf2.4 Master上创建用于主从复置的账号2.5 Slave上设置从库向主库同步 首先切换到从库登录3 ShardingSphere实现读写分离3.1 配置properties3.2 编写po3.3 编写mapper3.4 编写Test3.5 TestRest3.5.1 主库3.5.2 从库3.5.3 写入时3.5.4 读取时

1 读写分离原理

为了确保数据库产品的稳定性，很多数据库拥有双机热备功能，也就是，第一台数据库服务器对外提供增删改业务的生产服务器，第二台数据库服务器，主要进行读的操作。

原理：让主数据库（master）处理事务性增删改操作，而从数据库（slave）处理select 查询操作。

2 Mysql 配置主从复制

2.1 创建2个Mysql 数据库服务，并启动两个Mysql服务

2.2 配置Master库的/etc/my.cnf

[mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/data socket=/tmp/mysql.sock user=mysql port=3306 # Master # 开启日志 log-bin = mysql-bin # 日志格式按行同步 binlog-format = ROW # 设置服务id，主从不能一致 server-id = 1 # 设置需要同步的数据库 binlog-do-db = user_db # 设置忽略系统库同步 binlog-ignore-db = mysql binlog-ignore-db = information_schema binlog-ignore-db = performance_schema binlog-ignore-db = sys # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # character config character_set_server=utf8mb4 [mysqld_safe] log-error=/usr/local/mysql/data/mysql.log pid-file=/usr/local/mysql/data/mysql.pid # include all files from the config directory !includedir /etc/my.cnf.d

主要配置

# Master # 开启日志 log-bin = mysql-bin # 日志格式按行同步 binlog-format = ROW # 设置服务id，主从不能一致 server-id = 1 # 设置需要同步的数据库 binlog-do-db = user_db # 设置忽略系统库同步 binlog-ignore-db = mysql binlog-ignore-db = information_schema binlog-ignore-db = performance_schema binlog-ignore-db = sys

2.3 配置Slave库的/etc/my.cnf

[mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/data socket=/tmp/mysql.sock user=mysql port=3306 # Slave # 开启日志 log-bin = mysql-bin # 日志按照行进行数据同步 binlog-format = ROW # 设置服务id，主从不能一致 server-id = 2 # 设置需要同步的数据库 replicate-do-db=user_db # 设置忽略系统库 replicate-ignore-db = mysql replicate-ignore-db = information_schema replicate-ignore-db = performance_schema replicate-ignore-db = sys #replicate-wild-ignore_table = mysql.% #replicate-wild-ignore_table = information_schema.% #replicate-wildignore_table = performance_schema.% #replicate-wild-ignore_table = sys.% # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 #character config character_set_server=utf8mb4 [mysqld_safe] log-error=/usr/local/mysql/data/mysql.log pid-file=/usr/local/mysql/data/mysql.pid # # include all files from the config directory # !includedir /etc/my.cnf.d

主要配置

# Slave # 开启日志 log-bin = mysql-bin # 日志按照行进行数据同步 binlog-format = ROW # 设置服务id，主从不能一致 server-id = 2 # 设置需要同步的数据库 replicate-do-db=user_db # 设置忽略系统库 replicate-ignore-db = mysql replicate-ignore-db = information_schema replicate-ignore-db = performance_schema replicate-ignore-db = sys

2.4 Master上创建用于主从复置的账号

mysql -uroot -p123456 -h192.168.124.12 CREATE USER 'sync'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; # 授权主备复制专用账号 GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%'; show grants for sync; # 刷新权限 flush privileges; # 确认位点，记录下文件名及位点 show master status;

mysql 数据库中查询user表获取文件名和点位

mysql-bin.0000011 156

2.5 Slave上设置从库向主库同步

首先切换到从库登录

mysql -uroot -p123456 -h192.168.124.11 -P3306 # 先停止同步 stop slave; # 修改从库指向到主库，使用上一步记录的文件名以及位点 CHANGE MASTER TO master_host='192.168.124.12', master_port=3306, master_user='sync', master_password='123456', master_log_file='mysql-bin.000011', master_log_pos=156; # 启动同步 start slave; # 查看slave 的状态 show slave status; CREATE SCHEMA `testdb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ; CREATE TABLE `testdb`.`t_user` ( `id` BIGINT NOT NULL, `name` VARCHAR(45) NOT NULL, `age` INT NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; INSERT INTO `testdb`.`t_user` (`id`, `name`, `age`) VALUES ('1', 'zhangsan', '3');

需要注意的是数据库的字符集编码必须一致

3 ShardingSphere实现读写分离

3.1 配置properties

# sharding-JDBC分片策略（主从复制配置） # 配置数据源，给数据源命名 spring.shardingsphere.datasource.names=m0,s0 # 配置数据源具体内容，连接池、驱动、地址、用户名和密码 # testdb 主服务器 spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.m0.url=jdbc:mysql://192.168.124.12:3306/testdb?serverTimezone=GMT+8 spring.shardingsphere.datasource.m0.username=root spring.shardingsphere.datasource.m0.password=123456 # 配置数据源具体内容，连接池、驱动、地址、用户名和密码 # testdb 从服务器 spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.s0.url=jdbc:mysql://192.168.124.11:3306/testdb?serverTimezone=GMT+8 spring.shardingsphere.datasource.s0.username=root spring.shardingsphere.datasource.s0.password=123456 # 一个实体类对应两张表，覆盖 spring.main.allow-bean-definition-overriding=true # 主从库逻辑数据源定义 ds0 为testdb spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0 spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s0 # t_user 分表策略，固定分配至ds0的t_user真实表 spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds$->{0}.t_user spring.shardingsphere.sharding.tables.t_user.key-generator.column=id spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE # 打印sql输出日志 spring.shardingsphere.props.sql.show=true

3.2 编写po

package com.ccb.sharding.po; import com.baomidou.mybatisplus.annotation.TableId; import com.baomidou.mybatisplus.annotation.TableName; @TableName("t_user") public class Student { @TableId("id") private Long id; private String name; private int age; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } @Override public String toString() { return "Student{" + "id=" + id + ", name='" + name + '\'' + ", age=" + age + '}'; } }

3.3 编写mapper

package com.ccb.sharding.mapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.ccb.sharding.po.Student; import com.ccb.sharding.po.User; import org.springframework.stereotype.Repository; @Repository public interface StudentMapper extends BaseMapper<Student> { }

3.4 编写Test

package com.ccb.sharding; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.ccb.sharding.mapper.StudentMapper; import com.ccb.sharding.po.Student; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; @SpringBootTest class ShardingApplicationTests { @Autowired StudentMapper studentMapper; // ================= 测试读写分离 ====================== @Test public void addStudentMaster() { Student student = new Student(); student.setName("Lily"); student.setAge(23); studentMapper.insert(student); } @Test public void getStudentSlave(){ QueryWrapper queryWrapper = new QueryWrapper(); queryWrapper.eq("id",1278468912563892226L); Student student = studentMapper.selectOne(queryWrapper); System.out.println(student); } }

3.5 TestRest

3.5.1 主库

3.5.2 从库

Result

3.5.3 写入时

3.5.4 读取时

以上可以看到主从都有数据，并且写入时是主库master，读取时是从库slave，实现了目标主从复制、读写分离