MySQL-主從復制分庫分表

5 MySQL-主從復制&分庫分表

5.1mysql 主從復制

5.1.1. 概述

主從復制是將主數據庫的DDL和DML操作通過二進制日志（binlog文件）傳送到從庫服務器，然后在從庫上對這些日志重新執行，從而使得主庫和從庫的數據保持同步。

MySQL支持一臺主庫同時向多臺從庫進行復制，從庫也可以從其他從庫復制。（主從，從從等模式）。
在這里插入圖片描述

MySQL復制的優點包含以下幾個方面：

主庫出現問題，可以快速切換到從庫提供服務。
實現讀寫分離，降低主庫的訪問壓力。（增刪改操作在主庫，讀取在從庫）
可以在從庫中執行備份（因為加了全局鎖，鎖了庫），以避免備份期間影響主庫服務。

5.1.2. 原理

基于binlog實現主從模式。
從庫有兩組線程，IO Thread和SQL Thread

IO Thread復制連接到主庫，并且讀取主庫的binlog日志，并且將其保存到從節點的中繼日志，即relay日志。SQL Thread復制讀取relay日志，并且執行。
在這里插入圖片描述

5.1.3. 搭建

基于Docker實現MySQL主從復制（全網最詳細！！！）_docker mysql-CSDN博客

5.1.3.1. 搭建主節點

在這里插入圖片描述

docker run -d \
--name mysql_master \
-p 3307:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /usr/local/dlh/mysql_master/data:/var/lib/mysql \
-v /usr/local/dlh/mysql_master/conf:/etc/mysql \
-v /usr/local/dlh/mysql_master/mysql-files:/var/lib/mysql-files \
-v /usr/local/dlh/mysql_master/log:/var/log/mysql \
mysql:8.0.23

！注意修改MYSQL的配置文件！

[mysqld]
# 服務器唯一id，保證主從集群環境中唯一，取值范圍:1~(2^32-1),默認為1
server-id=1
# 是否只讀，1代表只讀，0代表讀寫
read-only=0
# 二進制日志名，默認binlog
# log-bin=binlog
# 設置需要復制的數據庫，默認復制全部數據庫
# binlog-do-db=mytestdb
# 設置不需要復制的數據庫
# binlog-ignore-db=mysql
# binlog-ignore-db=infomation_schema
skip-name-resolve

5.1.3.1.1. 創建主從用戶

登錄mysql，創建遠程連接的賬號，并授予主從復制權限。
docker exec -it mysql_master /bin/bash
mysql -uroot -p123456
# 創建remote_user用戶，并且指定其密碼
# 該用戶可以在任意的主機連接該MYSQL服務
create user 'remote_user'@'%' 
identified with mysql_native_password by 'Root@123456';或者：
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'Root@123456';# 給'remote_user'@'%' 用戶分配主從復制權限
grant replication slave on *.* to 'remote_user'@'%';

查看二進制日志坐標(binlog和offset)：

show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      673 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

file:從哪個日志文件開始推送日志文件
position：文件日志偏移量

5.1.3.2. 搭建從節點

docker run -d \
--name mysql_slave \
-p 3308:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /usr/local/dlh/mysql_slave/log:/var/log/mysql \
-v /usr/local/dlh/mysql_slave/data:/var/lib/mysql \
-v /usr/local/dlh/mysql_slave/conf:/etc/mysql \
-v /usr/local/dlh/mysql_slave/mysql-files:/var/lib/mysql-files \
mysql:8.0.23

添加從節點配置文件。

[mysqld]
# 服務器唯一id，保證主從集群環境中唯一，取值范圍:1~(2^32-1),默認為1
server-id=2
# 是否只讀，1代表只讀，0代表讀寫
read-only=1
# 二進制日志名，默認binlog
# log-bin=binlog
# 設置需要復制的數據庫，默認復制全部數據庫
# binlog-do-db=mytestdb
# 設置不需要復制的數據庫
# binlog-ignore-db=mysql
# binlog-ignore-db=infomation_schema
skip-name-resolve

5.1.3.2.1. 從庫關聯到主庫

#進入從庫
docker exec -it mysql_slave /bin/bash
mysql -uroot -p123456#與主庫連接
CHANGE MASTER TO MASTER_HOST='192.168.171.108', 
MASTER_USER='remote_user',MASTER_PASSWORD='Root@123456', MASTER_PORT=3307,
MASTER_LOG_FILE='binlog.000005',MASTER_LOG_POS=673;

在這里插入圖片描述

5.1.3.2.2. 開啟主從同步

start replica;

輸入后，查看從庫狀態。

show replica status\G# 輸出：
******************** 1. row ***************************Replica_IO_State: Waiting for master to send eventSource_Host: 192.168.171.108Source_User: remote_userSource_Port: 3307Connect_Retry: 60Source_Log_File: binlog.000002Read_Source_Log_Pos: 673Relay_Log_File: 1edd24b6c96d-relay-bin.000002Relay_Log_Pos: 321Relay_Source_Log_File: binlog.000002Replica_IO_Running: YesReplica_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Source_Log_Pos: 673Relay_Log_Space: 537Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Source_SSL_Allowed: NoSource_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Source_Server_Id: 1Source_UUID: 26e38018-4127-11f0-ab8e-0242ac110002Source_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLReplica_SQL_Running_State: Slave has read all relay log; waiting for more updatesSource_Retry_Count: 86400Source_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Source_SSL_Crl: Source_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Source_TLS_Version: Source_public_key_path: Get_Source_public_key: 0Network_Namespace:

注意：如果發現IO線程或者SQL線程未啟用；可執行以下命令重試：

在從庫上執行 STOP SLAVE;
使用 RESET SLAVE; 命令清除現有的復制信息。
然后重新設置從庫的主庫信息，使用 CHANGE MASTER TO ... 命令，并提供正確的日志文件名和位置。
最后，執行 START SLAVE; 嘗試重新開始復制。

5.1.4. shadingSphere實戰演示

官網：shardingsphere官網

5.1.4.1簡介

ShardingSphere 是一個開源的分布式數據庫中間件，提供數據分片、分布式事務和數據庫治理功能。其核心原理包括 SQL 解析、路由、改寫、執行和歸并等多個步驟。
在這里插入圖片描述

詳細可參考文檔：ShardingSphere內核原理及核心源碼剖析

5.1.4.2 Springboot集成shadingSphere實現讀寫分離

spring.application.name=sharding# 數據庫名
spring.shardingsphere.datasource.names = mysql_master,mysql_slave
# 數據庫詳細信息
spring.shardingsphere.datasource.mysql_master.type = com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.mysql_master.driver-class-name = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.mysql_master.url = jdbc:mysql://192.168.171.108:3307/test?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.mysql_master.username = root
spring.shardingsphere.datasource.mysql_master.password = 123456spring.shardingsphere.datasource.mysql_slave.type = com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.mysql_slave.driver-class-name = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.mysql_slave.url = jdbc:mysql://192.168.171.108:3308/test?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.mysql_slave.username = root
spring.shardingsphere.datasource.mysql_slave.password = 123456# 路由規則
spring.shardingsphere.rules.readwrite-splitting.data-sources.readwrite_ds.static-strategy.write-data-source-name = mysql_master
spring.shardingsphere.rules.readwrite-splitting.data-sources.readwrite_ds.static-strategy.read-data-source-names = mysql_master,mysql_slave# 打印sql
spring.shardingsphere.props.sql-show=true

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>com.dlh</groupId><artifactId>sharding</artifactId><version>1.0-SNAPSHOT</version><name>sharding</name><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.7.7</version><relativePath/> <!-- lookup parent from repository --></parent><properties><java.version>11</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><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.7</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.16</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>org.yaml</groupId><artifactId>snakeyaml</artifactId><version>1.33</version></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build></project>

5.1.4. 總結

在這里插入圖片描述

5.2分庫&分表

5.2.1介紹

在這里插入圖片描述

如果大數據量進行數據存儲，存在以下性能瓶頸：

IO瓶頸：請求太多，導致大量的磁盤IO，效率太低。請求的數據太多，帶寬不夠，網絡IO平靜。
CPU瓶頸：排序，分組，連接查詢，聚合統計等SQL會消耗大量的CPU資源，請求數太多，CPU出現瓶頸。

5.2.2拆分策略

分庫分表只是粒度層面的不同，垂直拆分和水平拆分只是在拆分維度上的區別。

分庫就是將一個數據庫分成多個數據庫。
分表就是將一個表拆分成多個表。

5.2.2.1垂直分庫&垂直分表

在這里插入圖片描述

5.2.2.2水平拆分

在這里插入圖片描述

5.2.3垂直分庫

5.2.3.1搭建docker環境

在這里插入圖片描述

搭建mysql_server1

docker run -d \
--name mysql_server1 \
-p 3309:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /usr/local/dlh/mysql_server1/data:/var/lib/mysql \
-v /usr/local/dlh/mysql_server1/conf:/etc/mysql \
-v /usr/local/dlh/mysql_server1/mysql-files:/var/lib/mysql-files \
-v /usr/local/dlh/mysql_server1/log:/var/log/mysql \
mysql:8.0.23

搭建mysql_server2

docker run -d \
--name mysql_server2 \
-p 3310:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /usr/local/dlh/mysql_server2/data:/var/lib/mysql \
-v /usr/local/dlh/mysql_server2/conf:/etc/mysql \
-v /usr/local/dlh/mysql_server2/mysql-files:/var/lib/mysql-files \
-v /usr/local/dlh/mysql_server2/log:/var/log/mysql \
mysql:8.0.23

在mysql-server1 服務器上, 創建數據庫 payorder_db,并創建表 pay_order

CREATE DATABASE msb_payorder_db CHARACTER SET 'utf8';CREATE TABLE pay_order (order_id bigint(20) NOT NULL AUTO_INCREMENT,user_id int(11) DEFAULT NULL, -- 表關聯product_name varchar(128) DEFAULT NULL,COUNT int(11) DEFAULT NULL,PRIMARY KEY (order_id)
) ENGINE=InnoDB AUTO_INCREMENT=12345679 DEFAULT CHARSET=utf8

在mysql-server2 服務器上, 創建數據庫 msb_user_db,并創建表users

CREATE DATABASE user_db CHARACTER SET 'utf8';CREATE TABLE users (id int(11) NOT NULL,username varchar(255) NOT NULL COMMENT '用戶昵稱',phone varchar(255) NOT NULL COMMENT '注冊手機',PASSWORD varchar(255) DEFAULT NULL COMMENT '用戶密碼',PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶表'

5.2.3.2配置文件

使用sharding-jdbc 對數據庫中水平拆分的表進行操作,通過sharding-jdbc對分庫分表的規則進行配置,配置內容包括：數據源、主鍵生成策略、分片策略等。

spring.application.name=sharding_2# 定義多個數據源
spring.shardingsphere.datasource.names = mysql-server1,mysql-server2
# datasource 1
spring.shardingsphere.datasource.mysql-server1.type = com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.mysql-server1.driver-class-name = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.mysql-server1.url = jdbc:mysql://192.168.31.145:3309/payorder_db?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.mysql-server1.username = root
spring.shardingsphere.datasource.mysql-server1.password = 123456
# datasource 2
spring.shardingsphere.datasource.mysql-server2.type = com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.mysql-server2.driver-class-name = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.mysql-server2.url = jdbc:mysql://192.168.31.145:3310/user_db?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.mysql-server2.username = root
spring.shardingsphere.datasource.mysql-server2.password = 123456
# 配置規則
spring.shardingsphere.rules.sharding.tables.pay_order.actual-data-nodes=mysql-server1.pay_order
spring.shardingsphere.rules.sharding.tables.users.actual-data-nodes=mysql-server2.users# print sql
spring.shardingsphere.props.sql-show=true

配置規則格式：
在這里插入圖片描述

測試是否垂直分庫成功。

@SpringBootTest
class Sharding2ApplicationTests {@Resourceprivate PayOrderMapper payOrderMapper;@Resourceprivate UsersMapper usersMapper;@Testpublic void testInsert(){Users user = new Users();user.setId(1002);user.setUsername("大遠哥");user.setPhone("15612344321");user.setPassword("123456");usersMapper.insert(user);PayOrder payOrder = new PayOrder();payOrder.setOrderId(12345679L);payOrder.setProductName("獼猴桃");payOrder.setUserId(user.getId());payOrder.setCount(2);payOrderMapper.insert(payOrder);}
}

5.2.4水平分表

如果單表數據量越來越多的時候，對一張表的操作性能將會下降。所以我們將一張表水平拆分成多個表，分散在不同的數據庫中存儲。

場景：用戶訂單表pay_order中數據量太多，如何實現水平分表？

5.2.4.1數據準備

docker run -d \
--name mysql_server3 \
-p 3311:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /usr/local/dlh/sharding/mysql_server3/log:/var/log/mysql \
-v /usr/local/dlh/sharding/mysql_server3/data:/var/lib/mysql \
-v /usr/local/dlh/sharding/mysql_server3/conf:/etc/mysql \
-v /usr/local/dlh/sharding/mysql_server3/mysql-files:/var/lib/mysql-files \
mysql:8.0.23

在這里插入圖片描述

需求說明:

在mysql-server3服務器上, 創建數據庫 course_db
創建表 t_course_1 、 t_course_2
約定規則：如果添加的課程 id 為偶數添加到 t_course_1 中，奇數添加到 t_course_2 中。

水平分片的id需要在業務層實現，不能依賴數據庫的主鍵自增

CREATE TABLE t_course_1 (`cid` BIGINT(20) NOT NULL,`user_id` BIGINT(20) DEFAULT NULL,`cname` VARCHAR(50) DEFAULT NULL,`brief` VARCHAR(50) DEFAULT NULL,`price` DOUBLE DEFAULT NULL,`status` INT(11) DEFAULT NULL,PRIMARY KEY (`cid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8CREATE TABLE t_course_2 (`cid` BIGINT(20) NOT NULL,`user_id` BIGINT(20) DEFAULT NULL,`cname` VARCHAR(50) DEFAULT NULL,`brief` VARCHAR(50) DEFAULT NULL,                                 `price` DOUBLE DEFAULT NULL,`status` INT(11) DEFAULT NULL,PRIMARY KEY (`cid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

5.2.4.2修改配置文件

spring.shardingsphere.datasource.names = mysql-server3spring.shardingsphere.datasource.mysql-server3.type = com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.mysql-server3.driver-class-name = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.mysql-server3.url = jdbc:mysql://192.168.31.145:3309/course_db?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.mysql-server3.username = root
spring.shardingsphere.datasource.mysql-server3.password = 123456

5.2.4.3數據節點配置

修改t_course表的實際節點映射。（即表在哪個節點上）

spring.shardingsphere.rules.sharding.tables.t_course.actual-data-nodes=master-server3.t_course_1
spring.shardingsphere.rules.sharding.tables.t_course.actual-data-nodes=master-server3.t_course_2

如果實際節點太多，那么將會寫很多次。可以用下面的方式簡化。
行表達式的使用: 行表達式

spring.shardingsphere.rules.sharding.tables.t_course.actual-data-nodes=mysql-server3.t_course_$->{1..2}
spring.shardingsphere.rules.sharding.tables.t_course.actual-data-nodes=master-server3.t_course_$->{1..2}

表達式 db1.t_course_$->{1..2}

$ 會被大括號中的 {1..2} 所替換, ${begin..end} 表示范圍區間
會有兩種選擇: master-server3.t_course_1 和 master-server3.t_course_2

5.2.4.4 配置分片策略

分片策略包括分片鍵和分片算法.
分片規則,約定cid值為偶數時,添加到t_course_1表，如果cid是奇數則添加到t_course_2表
分片算法比如取模，hash等。
在這里插入圖片描述

#1.配置數據節點
#指定course表的分布情況(配置表在哪個數據庫,表名是什么)
spring.shardingsphere.rules.sharding.tables.t_course.actual-data-nodes=db1.t_course_$->{1..2}
##2.配置分片策略(分片策略包括分片鍵和分片算法)
#2.1 分片鍵名稱: cid
spring.shardingsphere.rules.sharding.tables.t_course.table-strategy.standard.sharding-column=cid
#2.2 分片算法名稱
spring.shardingsphere.rules.sharding.tables.t_course.table-strategy.standard.sharding-algorithm-name=table-inline
#2.3 分片算法類型: 行表達式分片算法(標準分片算法下包含->行表達式分片算法)
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.type=INLINE
#2.4 分片算法屬性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.algorithm-expression=t_course_$->{cid % 2 + 1}

5.2.4.5 測試

測試插入：

@Test
public void testInsertCourse(){for (int i = 1; i < 10; i++) {Course course = new Course();course.setCid(1L+i);course.setUserId(1L+i);course.setCname("Java經典面試題講解");course.setBrief("課程涵蓋目前最容易被問到的10000道Java面試題");course.setPrice(100.0);course.setStatus(1);courseMapper.insert(course);}
}

測試查詢：

@Test
public void test3(){List<Course> courses = courseMapper.selectList(null);for (Course cours : courses) {System.out.println(cours);}
}

測試分頁查詢：

@Test
public void test4() {//全量如何分頁？List<Course> courses1 = courseMapper.selestectPage(1, 5);for (Course c : courses1) {System.out.println(c);}
}