文章目錄

• • • 1 讀寫分離原理
    • 2 Mysql 配置主從復制
    • • 2.1 創建2個Mysql 數據庫服務，并啟動兩個Mysql服務
      • 2.2 配置Master庫的/etc/my.cnf
      • 2.3 配置Slave庫的/etc/my.cnf
      • 2.4 Master上創建用于主從復置的賬號
      • 2.5 Slave上設置從庫向主庫同步
• 首先切換到從庫登錄
• • • 3 ShardingSphere實現讀寫分離
    • • 3.1 配置properties
      • 3.2 編寫po
      • 3.3 編寫mapper
      • 3.4 編寫Test
      • 3.5 TestRest
      • • 3.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%2B8 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%2B8 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_userspring.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;}@Overridepublic 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 {@AutowiredStudentMapper studentMapper;// ================= 測試讀寫分離 ======================@Testpublic void addStudentMaster() {Student student = new Student();student.setName("Lily");student.setAge(23);studentMapper.insert(student);}@Testpublic 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，實現了目標主從復制、讀寫分離

總結

以上是生活随笔為你收集整理的6、ShardingSphere 之 读写分离的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。