【原创】拉勾课程速推指南-搭建MySQL分库分表的高可用集群

文章内容输出来源：拉勾教育Java高薪训练营

其他内容

【拉勾课程速推指南】阶段四模块三分布式文档存储独角兽MongoDB、知识图谱存储数据库Neo4j新鲜出炉
【拉勾课程速推指南】阶段三模块五 SpringCloud组件设计原理及实战（下）

Java工程师高薪训练营

拉勾教育的《Java工程师高薪训练营》是大厂实战讲师团队，历时15个月打磨而成。针对性搭建技术能力框架，覆盖99%公司的技术要求。帮助每一位Java工程师，达到阿里P7技术能力。

课程评价

从2月份开始参加了第2期班，到现在也有四个月时间了。经过这几个月的学习不仅巩固了以前不太牢固的基础，还了解了很多最近非常热门的开发技术，可以说是非常有收获的。除了视频课程中老师精彩的讲解，课后比较有针对性的作业演练，以及非常及时耐心辅导的班主任与导师都是功不可没的。

学习心得

为了记录自己的学习成果，并分享学习心得来帮助更多的同学，特此编写此文。

学习目标

用最短时间通关作业，避免作业扣分，追上课程计划进度。

学习方法

1. 作业驱动
每次开放新模块，先把视频课程进度拖动到最后。优先看到当前模块作业题目，便于制定学习计划，并避免作业延期扣分。

2. 重点优先
根据作业题目内容选择优先学习内容，跳过已知、简介、源码等明显与作业无关内容。

3. 加速播放
一刷2倍速起步，可以使用外挂js代码任意调速（支持最高16倍速）。坚持高速，慢慢习惯，可以节省一半时间。二刷3倍、三刷5倍、、、直到16倍速（静音）还能明白课程内容。

// 打开浏览器开发者工具，粘贴到Console，回车执行
document.getElementsByTagName("video")[0].playbackRate = 3

4. 直撸作业
不需要看完全部视频或者跟着老师练习一遍再做作业，只要先看完重点视频，理解作业题目要求即可开撸。

5. 边学边做
不用跟着老师做demo，可以直接开始做作业，节省学习时间，避免听完就忘，作业时还要反复再看，浪费时间。而且老师demo的环境和版本可能和自己的也不一样，学习套路，领会精神即可，不必邯郸学步，直接举一反三，照猫画虎。

6. 基于实战
课程demo和作业要求有时候比较简单，甚至不合乎实际工作需求。为了以后可以将作业模板沿用到实际工作中，尽量稍微考虑实际工作场景，提高作业价值。但是也不要过度设计，浪费太多时间。

7. 早问避坑
遇到问题或者不理解作业时，及时询问导师和群内同学，避免闭门造车，南辕北辙。还有很多课程没有讲到的内容容易踩坑，也要及时询问已经开荒的同学，节约时间。

8. 重复刷课
快速通关不是目的，只是手段，最终掌握课程内容才是关键。所以，还需要定期反复刷课才能强化记忆，避免学完就忘。

课程介绍

第四阶段大型分布式存储系统架构进阶模块一和模块二的课程内容为MySQL相关内容，作业内容为搭建高可用集群和实现分库分表，实际应用场景下这两个功能可以结合使用，所以特此将作业实现步骤整合在一起，方便日后工作时查看。

作业说明

第四阶段大型分布式存储系统架构进阶
模块一 MySQL海量数据存储与优化（上）
本模块对MySQL体系架构、运行机制、存储引擎、索引原理、事务和锁以及集群架构设计等方面的内容进行深入系统的介绍，并对SQL和架构进行分析及提出性能优化方案。

软件版本

VirtualBox 6.1.8
CentOS 7.7
MySQL 5.7.28
MHA 0.58

架构说明

服务器信息

角色	IP	主机名	server_id	功能
master	192.168.0.111	Master	1	接受写请求
candidate master	192.168.0.112	Slave01	2	接受读请求
slave	192.168.0.113	Slave02	3	接受读请求
MHA manager	192.168.0.114	Manager	4	监控管理

效果演示

参考实现步骤 11.故障测试

https://v.youku.com/v_show/id_XNDcyNjk2MTE2MA==.html

实现步骤

安装MySQL

安装相关依赖包

$ yum install -y perl-Module-Install.noarch
$ yum -y install net-tools

删除MariaDB

$ rpm -qa | grep mariadb
$ rpm -e mariadb-libs-5.5.64-1.el7.x86_64 --nodeps

解压MySQL安装包

$ tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

按顺序运行安装包

$ rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
$ rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
$ rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
$ rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
$ rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
$ rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm

克隆虚拟镜像的场合，需要重新生成MySQL的UUID

$ mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak

初始化数据库

$ mysqld --initialize --user=mysql

启动服务

$ systemctl start mysqld.service

查看初始化密码

$ cat /var/log/mysqld.log

登录MySQL服务器（输入初始化密码）

$ mysql -uroot -p

修改密码

mysql> set password=password('root');

主从同步

设置主库

配置my.cnf

$ vi /etc/my.cnf

server_id=1
log_bin=master_bin
sync_binlog=1
binlog_ignore_db=performance_schema
binlog_ignore_db=information_schema
binlog_ignore_db=sys

重启服务

$ systemctl restart mysqld.service

设置权限

mysql> grant replication slave on *.* to 'root'@'%' identified by 'root';
mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';
mysql> flush privileges;
mysql> show master status;

设置从库

配置my.cnf

$ vi /etc/my.cnf

MHA时因为有可能升级为master，所以不能在配置文件设置只读，只能通过环境变量设置

server_id=2
relay_log=relay_log

重启服务

$ systemctl restart mysqld.service

设置权限

mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';
mysql> flush privileges;
mysql> show slave status;

设置并开启同步

mysql> change master to master_host='192.168.0.111', master_port=3306, master_user='root', master_password='root', master_log_file='master_bin.000001', master_log_pos=869;
mysql> start slave;
mysql> show slave status \G;

设置第二台从库

server-id=3
relay-log=relay-log

其他步骤同上（略）

半同步复制

设置主库

查看是否支持

mysql> select @@have_dynamic_loading;

检查并安装插件

mysql> show plugins;
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';

检查并开启插件

mysql> set global rpl_semi_sync_master_enabled=1;
mysql> set global rpl_semi_sync_master_timeout=1000;
mysql> show variables like '%semi%';

设置从库

检查并安装插件

mysql> show plugins;
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

检查并开启插件

mysql> set global rpl_semi_sync_slave_enabled=1;
mysql> show variables like '%semi%';

重启从库

mysql> stop slave;
mysql> start slave;

设置第二台从库

步骤同上（略）

MHA架构

域名解析

为了方便通过host解析使用私钥访问，在所有服务器的/etc/hosts文件中设置ip对应域名。

$ vi /etc/hosts

192.168.0.111 Master
192.168.0.112 Slave01
192.168.0.113 Slave02
192.168.0.114 Manager

ssh互通访问

MHA集群中的各节点彼此之间均需要基于ssh互信通信，以实现远程控制及数据管理功能。简单起见，可在Manager节点生成密钥对，并设置其可远程连接本地主机后，将私钥文件及authorized_keys文件复制给余下的所有节点。

Master

$ ssh-keygen -t rsa
$ ssh-copy-id -i ~/.ssh/id_rsa.pub root@Slave01
$ ssh-copy-id -i ~/.ssh/id_rsa.pub root@Slave02
$ ssh-copy-id -i ~/.ssh/id_rsa.pub root@Manager

Slave01

$ ssh-keygen -t rsa
$ ssh-copy-id -i ~/.ssh/id_rsa.pub root@Master
$ ssh-copy-id -i ~/.ssh/id_rsa.pub root@Slave02
$ ssh-copy-id -i ~/.ssh/id_rsa.pub root@Manager

Slave02

$ ssh-keygen -t rsa
$ ssh-copy-id -i ~/.ssh/id_rsa.pub root@Master
$ ssh-copy-id -i ~/.ssh/id_rsa.pub root@Slave01
$ ssh-copy-id -i ~/.ssh/id_rsa.pub root@Manager

Manager

$ ssh-keygen -t rsa
$ ssh-copy-id -i ~/.ssh/id_rsa.pub root@Master
$ ssh-copy-id -i ~/.ssh/id_rsa.pub root@Slave01
$ ssh-copy-id -i ~/.ssh/id_rsa.pub root@Slave02

完成后可在各个节点查看authorized_keys中其他节点的公钥

$ cat ~/.ssh/authorized_keys

配置 my.cnf

$ vi /etc/my.cnf

主库 Master

server_id=1
log_bin=master_bin
relay_log=relay_log
sync_binlog=1
binlog_ignore_db=performance_schema
binlog_ignore_db=information_schema
binlog_ignore_db=sys

从库 Slave01

server_id=2
log_bin=master_bin
relay_log=relay_log
relay_log_purge=0
log_slave_updates=1
sync_binlog=1
binlog_ignore_db=performance_schema
binlog_ignore_db=information_schema
binlog_ignore_db=sys

从库 Slave02

server_id=3
其他同上（略）

设置完重启数据库

$ systemctl restart mysqld

确认主从是否失效，如果失效可以重置从库

mysql> stop slave;
mysql> reset slave;
mysql> start slave;

从库设置只读（因为从库会升为主库，所以没有配置在启动文件中，而是通过参数方式设置）

$ mysql -e 'set global read_only=1' -uroot -p

授权

授权MHA账号远程访问MySQL的Master节点。

mysql> grant all on *.* to 'mhaadmin'@'%' identified by 'mhaadmin';

安装MHA

四个节点

$ yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm

Manager

$ yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

如果失败，则先安装环境依赖

$ yum -y install wget
$ cp /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.bak
$ yum -y install wget
$ wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
$ wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
$ yum clean all
$ yum makecache
$ yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager -y

初始化配置

Manager

$ vi /etc/mha_master/mha.cnf

[server default]
user=mhaadmin
password=mhaadmin
manager_workdir=/etc/mha_master/app1
manager_log=/etc/mha_master/manager.log
remote_workdir=/mydata/mha_master/app1
ssh_user=root
repl_user=root
repl_password=root
ping_interval=3
[server1]
hostname=192.168.0.111
ssh_port=22
candidate_master=1
[server2]
hostname=192.168.0.112
ssh_port=22
candidate_master=1
[server3]
hostname=192.168.0.113
ssh_port=22
candidate_master=1

检测ssh互信状态

$ masterha_check_ssh --conf=/etc/mha_master/mha.cnf

最后显示为 [info]All SSH connection tests passed successfully. 则表示成功。

检测MySQL集群的连接配置参数

$ masterha_check_repl --conf=/etc/mha_master/mha.cnf

最后显示为 MySQL Replication Health is OK. 则表示成功。否则，Master添加账号远程访问权限。

mysql> grant replication slave,replication client on *.* to 'root'@'%' identified by 'root';
mysql> flush privileges;

启动MHA

$ nohup masterha_manager --conf=/etc/mha_master/mha.cnf &> /etc/mha_master/manager.log &

检查状态

$ masterha_check_status --conf=/etc/mha_master/mha.cnf

如果需要停止MHA

$ masterha_stop --conf=/etc/mha_master/mha.cnf

故障测试

修改主库Master节点数据。
在从库Slave01和Slave02节点确认数据已同步。
停止Master节点服务器（手动关机）
查看Manager节点的日志文件，确认故障转移成功。

$ tail -200 /etc/mha_master/manager.log

修改从库Slave01节点数据，确认从库Slave02节点数据已同步。

修复故障

原有 Master 节点故障后，需要重新准备好一个新的 MySQL 节点。基于来自于Master 节点的备份恢复数据后，将其配置为新的 Master 的从节点即可。

注意，新加入的节点如果为新增节点，其 IP 地址要配置为原来 Master 节点的 IP，否则，还需要修改 mha.cnf 中相应的 ip 地址。随后再次启动 Master ，并再次检测其状态。

Slave01

$ mysqldump --all-database > /backup/mysql-backup-`date +%F-%T`-all.sql
$ scp /backup/mysql-backup-2020-05-30-06\:22\:02-all.sql root@master:~

Master

$ mysql < mysql-backup-2020-05-30-06\:22\:02-all.sql

然后在MySQL中重新设置Master的主从管理

主：Slave01

从：Master

检查集群状态

$ masterha_check_repl --conf=/etc/mha_master/mha.cnf

启动MHA，并记录日志

$ masterha_manager --conf=/etc/mha_master/mha.cnf > /etc/mha_master/manager.log 2>&1 &

检查MHA状态

$ masterha_check_status --conf=/etc/mha_master/mha.cnf

相关SQL

建表

-- 数据库：lagou
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for position
-- ----------------------------
DROP TABLE IF EXISTS `position`;
CREATE TABLE `position` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `salary` varchar(32) DEFAULT NULL,
  `city` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Table structure for position_detail
-- ----------------------------
DROP TABLE IF EXISTS `position_detail`;
CREATE TABLE `position_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL,
  `description` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SET FOREIGN_KEY_CHECKS = 1;

数据01

-- Master 192.168.0.111
insert position values(1, 'sunli', '30K', 'Beijing');
insert position_detail values(1, 1, 'Java高级工程师');
select p.id, p.name, p.salary, p.city, pd.description
  from position p inner join position_detail pd on pd.pid=p.id;

-- Slave01 192.168.0.112
select p.id, p.name, p.salary, p.city, pd.description
  from position p inner join position_detail pd on pd.pid=p.id;

-- Slave02 192.168.0.113
select p.id, p.name, p.salary, p.city, pd.description
  from position p inner join position_detail pd on pd.pid=p.id;

数据02

-- Slave01 192.168.0.112
insert position values(2, 'zhangsan', '40K', 'Shanghai');
insert position_detail values(2, 2, '架构师');
select p.id, p.name, p.salary, p.city, pd.description
  from position p inner join position_detail pd on pd.pid=p.id;

-- Slave02 192.168.0.113
select p.id, p.name, p.salary, p.city, pd.description
  from position p inner join position_detail pd on pd.pid=p.id;

更加详细内容，请参看Gitee部分
https://gitee.com/sunli1103/lg-mysql-mha

============================== 分割线 ==============================

第四阶段大型分布式存储系统架构进阶
模块二 MySQL海量数据存储与优化（下）
本模块主要对MySQL海量数据处理中的分库分表架构、ShardingSphere、MyCat中间件实战应用、数据库实战规范、以及一些运维分析工具等内容进行讲解。

作业内容

采⽤Sharding-JDBC实现c_order表分库分表+读写分离

基于user_id对c_order表进⾏数据分⽚

分别对master1和master2搭建⼀主⼆从架构

基于master1和master2主从集群实现读写分离

c_order建表SQL如下：

CREATE TABLE `c_order`(
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `is_del` bit(1) NOT NULL DEFAULT 0 COMMENT '是否被删
除',
 `user_id` int(11) NOT NULL COMMENT '⽤户id',
 `company_id` int(11) NOT NULL COMMENT '公司id',
 `publish_user_id` int(11) NOT NULL COMMENT 'B端⽤户id',
 `position_id` int(11) NOT NULL COMMENT '职位ID',
 `resume_type` int(2) NOT NULL DEFAULT 0 COMMENT '简历类型：
0附件 1在线',
 `status` varchar(256) NOT NULL COMMENT '投递状态 投递状态
WAIT-待处理 AUTO_FILTER-⾃动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝
ARRANGE_INTERVIEW-通知⾯试',
 `create_time` datetime NOT NULL COMMENT '创建时间',
 `update_time` datetime NOT NULL COMMENT '处理时间',
 PRIMARY KEY (`id`),
 KEY `index_userId_positionId` (`user_id`, `position_id`),
 KEY `idx_userId_operateTime` (`user_id`, `update_time`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

课程目录及观看建议

2倍速模式

课程：1小时
需要观看Sharding-JDBC的分库配置内容。
作业：2小时
主要花费在搭建虚拟机系统环境上，可以沿用上次作业环境或者克隆已经搭建好的虚拟机镜像，提高速度。

*** 必看
** 建议看
* 可不看
无标记不用看

任务一：分库分表实战（略）

任务二：ShardingSphere实战

ShardingSphere项目介绍（14:59）*
Sharding-JDBC之功能介绍（07:54） *
Sharding-JDBC之内部结构剖析（18:55）
Sharding-JDBC之分片核心概念（20:40）
Sharding-JDBC之分片算法和策略剖析（22:29）
Sharding-JDBC之分片流程剖析（08:35）
Sharding-JDBC之分片SQL使用规范（13:54）
Sharding-JDBC之分片分页优化方案（07:55）
Sharding-JDBC之Inline行表达式解析（14:38）
Sharding-JDBC之主键生成原理（08:42）
Sharding-JDBC实战之案例环境搭建（15:26）***
Sharding-JDBC实战之职位分库业务（17:23）***
Sharding-JDBC实战之职位分库测试（07:50）***
Sharding-JDBC实战之职位主键生成器（16:43）***
Sharding-JDBC实战之职位拆表+分库（17:01）***
Sharding-JDBC实战之城市广播表（07:50）
Sharding-JDBC实战之订单分库分表（25:18）***
读写分离和数据分片概念回顾（11:21）
读写分离应用架构方案（07:21）
Sharding-JDBC之读写分离功能介绍（09:10）
Sharding-JDBC实战之读写分离应用（16:33）***
Sharding-JDBC之Hint强制路由剖析（15:04）
Sharding-JDBC实战之Hint强制路由应用（15:07）
Sharding-JDBC之数据脱敏实现架构剖析（07:38）
Sharding-JDBC之数据脱敏配置剖析（07:26）
Sharding-JDBC之数据脱敏处理流程剖析（06:05）
Sharding-JDBC之加密策略解析（12:37）
Sharding-JDBC实战之用户密码加密（20:30）
分布式事务之CAP和BASE回顾（06:46）
分布式事务之2PC和3PC模式回顾（20:43）
分布式事务之XA模式剖析（10:38）
分布式事务之TCC模式剖析（11:57）
分布式事务之基于消息队列的TCC模式剖析（10:58）
分布式事务之基于Saga模式剖析（10:04）
分布式事务之基于Seata框架AT模式剖析（11:55）
Sharding-JDBC整合XA原理（11:55）
Sharding-JDBC整合Saga原理（10:10）
Sharding-JDBC整合Seata原理（11:05）
Sharding-JDBC分布式事务实战（20:59）
Sharding-JDBC之SPI加载解析（10:39）
Sharding-JDBC之编排治理解析（15:01）
Sharding-Proxy之功能和使用介绍（05:49）
Sharding-Proxy之职位表分库实战（16:56）
Sharding-Proxy之SpringBoot+Proxy实战（10:49）

任务三：Mycat实战（略）

任务四：运维工具（略）

注意点：

需要准备多个虚拟机环境，如果磁盘空间不足，每个分片至少保证1主1从（已与导师确认）。
克隆虚拟镜像的场合，需要重新生成MySQL的UUID，否则会有冲突。

$ mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak
$ systemctl start mysqld.service

配置文件推荐采用properties格式，便于参考课程demo，快速完成作业。如需使用yml格式，需要去官网查阅具体内容（和properties内容不一致，但是官网内容可能有版本冲突）。
官网提示：行表达式标识符可以使用 ${...} 或 $->{...}，但前者与Spring本身的属性文件占位符冲突，因此在Spring环境中使用行表达式标识符建议使用 $->{...}。
{0..1}表示分库分表的编号开始和结束范围，选择值由 inline.algorithm-expression 的计算结果决定。比如：

{0..1}的计算表达式为 inline.algorithm-expression=master$->{user_id % 2}
{1..2}的计算表达式为 inline.algorithm-expression=master$->{user_id % 2 + 1}

官网内容不一定是最新的，有可能有版本冲突。所以尽量不参考官网配置，避免踩坑。
例如：

maven依赖包
应该为 sharding-jdbc-spring-boot-starter，不是官网的 shardingsphere-jdbc-core，否则提示找不到。

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.0</version>
</dependency>

数据库的配置
目前apache sharding-jdbc 4.1.0配合springboot 2.3.0、hikari应该为 jdbc-url，不是官网的 url，

spring.shardingsphere.datasource.ds0.jdbc-url=xxx

需要的环境依赖

$ yum -y install wget
$ cp /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.bak
$ yum -y install wget
$ wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
$ wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
$ yum clean all
$ yum makecache
$ yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager -y

作业说明

作业思路

使用宿主机（安装虚拟机的真实机器）创建具有读写功能的演示项目，并使用Sharding-JDBC进行分库的设置。
先在宿主机的MySQL环境中测试代码和配置，实现分库分表效果。
再使用虚拟机搭建两组主从架构的MySQL环境。
最后在宿主机中使用该工程访问虚拟机中MySQL环境，分库分表的基础上再实现读写分离。

实现步骤

创建SpringBoot项目
a. 引入Sharding-JDBC相关依赖。
b. 使用JPA实现读写数据库操作。
c. 在Test方法中调用dao层方法，进行数据的插入和查询。
配置Sharding-JDBC属性文件
a. 先配置分库分表的效果，参考课程【17. Sharding-JDBC实战之订单分库分表】。
b. 再配置读写分离的效果，参考课程【21. Sharding-JDBC实战之读写分离应用】。
搭建虚拟机环境
a. 主从架构搭建步骤见上次作业内容。
b. 可以使用上次作业的环境，或者搭建好一个虚拟机环境后使用克隆（记得重新生成MySQL的UUID），快速完成。
c. 因为机器磁盘空间不足，只搭建了两个从节点，分别为 slave1 和 slave3（已与导师确认过）。
d. 最后使用四个虚拟机环境，分别是 master1、master2、slave1、slave3（见架构图）。
进行测试
a. 运行插入数据的测试方法，确认数据是否分别插入到 master1 和 master2 两个数据库中，是否再次进行了分表处理。
b. 运行查询数据的测试方法，确认是否是从 slave1 和 slave3 中取得数据，是否实现读写分离的效果。

软件版本

VirtualBox 6.1.8
CentOS 7.7
MySQL 5.7.28
SpringBoot 2.3.0
  SpringBoot Data JPA
  SpringBoot Test
Sharding-JDBC 4.1.0

架构说明

服务器信息

角色	IP	主机名	server_id	功能
Master	192.168.3.111	master1	11	接受写请求
Slave	192.168.3.112	slave1	12	接受读请求
Master	192.168.3.121	master2	21	接受写请求
Slave	192.168.3.122	slave3	22	接受读请求

注意事项

磁盘空间不足，只搭建了两个从库节点，分别为 slave1、slave3。
数据库主从搭建步骤已省略，可搭建成mha高可用模式，参照上期作业内容。
分库采用随机生成 user_id 后奇偶划分，分表采用雪花算法生成 id 后奇偶划分。

具体配置

Sharding-JDBC配置

application.properties

spring.profiles.active=sharding
spring.shardingsphere.props.sql.show=true

application-sharding.properties

spring.shardingsphere.datasource.names=master1, slave1, master2, slave3

spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1.jdbc-url=jdbc:mysql://192.168.3.111:3306/lagou1
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=root

spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.3.112:3306/lagou1
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=root

spring.shardingsphere.datasource.master2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master2.jdbc-url=jdbc:mysql://192.168.3.121:3306/lagou2
spring.shardingsphere.datasource.master2.username=root
spring.shardingsphere.datasource.master2.password=root

spring.shardingsphere.datasource.slave3.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave3.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave3.jdbc-url=jdbc:mysql://192.168.3.122:3306/lagou2
spring.shardingsphere.datasource.slave3.username=root
spring.shardingsphere.datasource.slave3.password=root

# 数据节点
spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=master$->{1..2}.c_order$->{1..2}
# 分库
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=master$->{user_id % 2 + 1}
# 分表
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.algorithm-expression=c_order$->{id % 2 + 1}

# 主键生成器
# @GeneratedValue(strategy = GenerationType.IDENTITY)
spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE

# 读写分离
spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave1
spring.shardingsphere.sharding.master-slave-rules.master2.master-data-source-name=master2
spring.shardingsphere.sharding.master-slave-rules.master2.slave-data-source-names=slave3

测试方法

ShardingApplicationTests.java

@ExtendWith(SpringExtension.class)
@SpringBootTest
class ShardingApplicationTests {

    @Autowired
    private OrderRepository orderRepository;

    @Test
    @RepeatedTest(19)
    public void testAdd() {
        Random random = new Random();
        int userId = random.nextInt(10);
        COrder cOrder = new COrder();
        cOrder.setDel(false);
        cOrder.setUserId(userId);
        cOrder.setCompanyId(2);
        cOrder.setPositionId(3);
        cOrder.setPublishUserId(4);
        cOrder.setResumeType(0);
        cOrder.setStatus("WAIT");
        cOrder.setCreateTime(new Date());
        cOrder.setUpdateTime(new Date());
        orderRepository.save(cOrder);
    }

    @Test
    public void testQuery() {
        List<COrder> cOrderList = orderRepository.findAll();
        cOrderList.forEach(cOrder -> {
            System.out.println(cOrder.toString());
        });
        System.out.println("查询记录总件数：" + cOrderList.size());
    }
}

测试步骤

1. 分库分表
执行添加数据方法 testAdd()，插入20条数据。确认数据库数据。
master1.c_order1：只有偶数user_id，偶数id数据
master1.c_order2：只有偶数user_id，奇数id数据
master2.c_order1：只有奇数user_id，偶数id数据
master2.c_order2：只有奇数user_id，奇数id数据

2. 读写分离
只保留数据库从库中 user_id 和 id 都为偶数的数据（slave1.c_order1），清空其他表中奇数数据。执行查询方法 testQuery()，只返回偶数数据

更加详细内容，请参看Gitee部分
https://gitee.com/sunli1103/lg-mysql-sharding

（END）

【原创】拉勾课程速推指南-搭建MySQL分库分表的高可用集群

其他内容

Java工程师高薪训练营

课程评价

学习心得

学习目标

学习方法

课程介绍

作业说明

软件版本

架构说明

效果演示

实现步骤

作业内容

课程目录及观看建议

作业说明

软件版本

架构说明

注意事项

具体配置