ShardingJDBC

最新推荐文章于 2024-02-01 18:52:07 发布

喝口茶

最新推荐文章于 2024-02-01 18:52:07 发布

阅读量544

点赞数

分类专栏： Computer 文章标签：中间件

本文链接：https://blog.csdn.net/kkoyy459/article/details/126924673

版权

Computer 专栏收录该内容

42 篇文章 1 订阅

订阅专栏

根据以下学习视频，个人整理的笔记

https://www.bilibili.com/video/BV1ei4y1K7dn?spm_id_from=333.999.0.0&vd_source=7a8946d22777450e46486d5fd60d8d4d

ShardingSphere的概述

ShardingSphere的官网：http://shardingsphere.apache.org/index_zh.html

在这里插入图片描述

**Apache ShardingSphere是一套开源的分布式数据库解决方案组成的生态圈，它由JDBC、Proxy和Sidecar（规划中）这3款既能够独立部署，又支持混合部署配合使用的产品组成。**它们均提供标准化的数据水平扩展、分布式事务和分布式治理等功能，可适用于如Java同构、异构语言、云原生等各种多样化的应用场景。

**Apache ShardingSphere旨在充分合理地在分布式的场景下利用关系型数据库的计算和存储能力，而并非实现一个全新的关系型数据库。**关系型数据库当今依然占有巨大市场份额，是企业核心系统的基石，未来也难于撼动，我们更加注重在原有基础上提供增量，而非颠覆。

Apache ShardingSphere 5.x版本开始致力于可插拔架构，项目的功能组件能够灵活的以可插拔的方式进行扩展。

目前，数据分片、读写分离、数据加密、影子库压测等功能，以及MySQL、PostgreSQL、SQLServer、Oracle等 SQL 与协议的支持，均通过插件的方式织入项目。开发者能够像使用积木一样定制属于自己的独特系统。Apache ShardingSphere目前已提供数十个 SPI 作为系统的扩展点，仍在不断增加中。

ShardingSphere已于2020年4月16日成为Apache软件基金会的顶级项目。

功能

数据分片
分库分表
读写分离
分片策略定制化
无中心化分布式主键
分布式事务
- 标准化事务接口
- XA强一致事务
- 柔性事务
- 数据库治理
分布式治理
- 弹性伸缩
- 可视化链路追踪
- 数据加密

数据分片内核剖析

ShardingSphere的3个产品的数据分片主要流程是完全一致的

核心主要是以下流程组成：

在这里插入图片描述

SQL解析
- 分为词法解析和语法解析。先通过词法解析器将SQL拆分为一个个不可再分的单词。再使用语法解析器对SQL进行理解，并最终提炼出解析上下文。解析上下文包括表、选择项、排序项、分组项、聚合函数、分页信息、查询条件以及可能需要修改的占位符的标记
执行器优化
- 合并和优化分片条件，如OR等
SQL路由
- 根据解析上下文匹配用户配置的分片策略，并生成路由路径。目前支持分片路由和广播路由
SQL改写
- 将SQL改写为在真实数据库中可以正确执行的语句。SQL改写分为正确性改写和优化改写
SQL执行
- 通过多线程执行器异步执行
结果归并
- 将多个执行结果集归并以便于通过统一的JDBC接口输出。结果归并包括流式归并、内存归并和使用装饰者模式的追加归并这几种方式

认识ShardingJDBC

在这里插入图片描述

ShardingJDBC定位为轻量级Java框架，在Java的JDBC层提供额外的服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架

ShardingJDBC适用于任何基于JDBC的ORM框架，如：JPA、Hibernate、Mybatis、SpringJDBC、Template或直接使用JDBC

ShardingJDBC支持任何第三方的数据库连接池，如：DBCP、C3P0、BoneCP、Druid、HikariCP等

ShardingJDBC支持任意实现JDBC规范的数据库，目前支持MySQL、Oracle、SQLServer、PostgreSQL以及任何遵循 SQL92 标准的数据库

功能架构图

在这里插入图片描述

混合架构

在这里插入图片描述

ShardingSphere-JDBC采用无中心化架构，适用于Java开发的高性能的轻量级 OLTP（连接事务处理）应用；ShardingSphere-Proxy提供静态入口以及异构语言的支持，适用于 OLAP（连接数据分析）应用以及对分片数据库进行管理和运维的场景

**Apache ShardingSphere是多接入端共同组成的生态圈。**通过混合使用 ShardingSphere-JDBC 和 ShardingSphere-Proxy，并采用同一注册中心统一配置分片策略，能够灵活的搭建适用于各种场景的应用系统，使得架构师更加自由地调整适合与当前业务的最佳系统架构

认识ShardingProxy

在这里插入图片描述

ShardingProxy定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。目前提供MySQL和PostgreSQL版本，它可以使用任何兼容 MySQL/PostgreSQL 协议的访问客户端（如：MySQL Command Client、MySQL Workbench、Navicat等）操作数据，对 DBA 更加友好

ShardingProxy向应用程序完全透明，可直接当做 MySQL/PostgreSQL 使用

ShardingProxy适用于任何兼容 MySQL/PostgreSQL 协议的的客户端

认识ShardingSidecar

目前仍然在开发完善中！！！

三个组件的比较

	Sharding-JDBC	Sharding-Proxy	Sharding-Sidecar
数据库	任意	MYSQL	MYSQL
连接消耗数	高	低	低
异构语言	仅Java	任意	任意
性能	损耗低	损耗高	损耗低
中心化	是	否	是
静态入口	无	有	无

Linux安装MySQL

1、先下载MySQL的rpm，下载地址：http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/

在这里插入图片描述

2、然后把rpm上传到阿里云服务器

在这里插入图片描述

3、执行如下命令

[root@iZwz9cj1ytrolpgw50tiksZ mysql]# pwd
/usr/mysql
[root@iZwz9cj1ytrolpgw50tiksZ mysql]# ll
total 28
-rw-r--r-- 1 root root 25548 Aug 30 23:50 mysql57-community-release-el7-10.noarch.rpm
[root@iZwz9cj1ytrolpgw50tiksZ mysql]# rpm -ivh mysql57-community-release-el7-10.noarch.rpm
...............
...............
...............
[root@iZwz9cj1ytrolpgw50tiksZ mysql]#

4、执行如下命令

yum install mysql-community-server -y

# 如果出现密钥已过期的错误提示，则执行下面的命令
# rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
# 然后再次执行：yum install mysql-community-server -y

5、启动MySQL服务并查看状态

[root@iZwz9cj1ytrolpgw50tiksZ mysql]# systemctl start mysqld
[root@iZwz9cj1ytrolpgw50tiksZ mysql]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2022-08-31 00:11:41 CST; 19s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 32587 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 32530 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 32590 (mysqld)
    Tasks: 27
   Memory: 324.2M
   CGroup: /system.slice/mysqld.service
           └─32590 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Aug 31 00:11:35 iZwz9cj1ytrolpgw50tiksZ systemd[1]: Starting MySQL Server...
Aug 31 00:11:41 iZwz9cj1ytrolpgw50tiksZ systemd[1]: Started MySQL Server.
[root@iZwz9cj1ytrolpgw50tiksZ mysql]#

6、查看MySQL的默认密码

[root@iZwz9cj1ytrolpgw50tiksZ mysql]# grep "password" /var/log/mysqld.log
2022-08-30T16:11:38.491862Z 1 [Note] A temporary password is generated for root@localhost: FPWcD,!sy3B_
[root@iZwz9cj1ytrolpgw50tiksZ mysql]# 

# 默认密码为：FPWcD,!sy3B_

7、登录MySQL

在这里插入图片描述

8、降低密码的级别（因为数据库默认密码规则必须携带大小写字母、特殊符号、字符长度大于8，否则会报错。因此我们想要设定较为简单的密码时，需要降低密码的级别！）

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql>

9、修改密码

mysql> set password for root@localhost = password('mkxiaoer');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

10、对可视化的工具进行授权

mysql> grant all on *.* to root@'%' identified by 'mkxiaoer';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

11、刷新一下权限

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

12、先保证服务器的防火墙处于关闭状态（如果防火墙打开的话，不仅安全组的3306端口要打开，我们还需要手动把3306这个端口添加到防火墙上才可以！我们直接把防火墙关闭，就可以直接一步到位，代价是服务器处于不安全的状态）

[root@iZwz9cj1ytrolpgw50tiksZ mysql]# systemctl status firewalld # 查看防火墙状态
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: active (running) since Thu 2022-08-11 13:23:52 CST; 2 weeks 5 days ago
     Docs: man:firewalld(1)
 Main PID: 20098 (firewalld)
    Tasks: 2
   Memory: 26.5M
   CGroup: /system.slice/firewalld.service
           └─20098 /usr/bin/python2 -Es /usr/sbin/firewalld --nofork --nopid

Aug 11 13:23:51 iZwz9cj1ytrolpgw50tiksZ systemd[1]: Starting firewalld - dynamic firewall daemon...
Aug 11 13:23:52 iZwz9cj1ytrolpgw50tiksZ systemd[1]: Started firewalld - dynamic firewall daemon.
Aug 11 13:23:53 iZwz9cj1ytrolpgw50tiksZ firewalld[20098]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w10 -D FORWARD -i docker0 -o docker0 -j DROP' failed: iptables:...t chain?).
Aug 11 13:23:53 iZwz9cj1ytrolpgw50tiksZ firewalld[20098]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w10 -D FORWARD -i docker0 -o docker0 -j DROP' failed: iptables:...t chain?).
Aug 12 12:08:25 iZwz9cj1ytrolpgw50tiksZ firewalld[20098]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w10 -D FORWARD -i br-ec8a763caf1d -o br-ec8a763caf1d -j DROP' f...t chain?).
Aug 12 14:54:13 iZwz9cj1ytrolpgw50tiksZ firewalld[20098]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w10 -D FORWARD -i br-6f79f0a21b1f -o br-6f79f0a21b1f -j DROP' f...t chain?).
Aug 13 14:47:17 iZwz9cj1ytrolpgw50tiksZ firewalld[20098]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w10 -D FORWARD -i br-0867f70d356f -o br-0867f70d356f -j DROP' f...t chain?).
Aug 14 15:13:01 iZwz9cj1ytrolpgw50tiksZ firewalld[20098]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w10 -D FORWARD -i br-41be374279ca -o br-41be374279ca -j DROP' f...t chain?).
Aug 14 17:39:24 iZwz9cj1ytrolpgw50tiksZ firewalld[20098]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w10 -D FORWARD -i br-2de355ce9a61 -o br-2de355ce9a61 -j DROP' f...t chain?).
Hint: Some lines were ellipsized, use -l to show in full.
[root@iZwz9cj1ytrolpgw50tiksZ mysql]# systemctl stop firewalld # 关闭防火墙
[root@iZwz9cj1ytrolpgw50tiksZ mysql]# systemctl status firewalld # 再次查看防火墙状态
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead) since Wed 2022-08-31 00:47:08 CST; 1s ago
     Docs: man:firewalld(1)
  Process: 20098 ExecStart=/usr/sbin/firewalld --nofork --nopid $FIREWALLD_ARGS (code=exited, status=0/SUCCESS)
 Main PID: 20098 (code=exited, status=0/SUCCESS)

Aug 11 13:23:52 iZwz9cj1ytrolpgw50tiksZ systemd[1]: Started firewalld - dynamic firewall daemon.
Aug 11 13:23:53 iZwz9cj1ytrolpgw50tiksZ firewalld[20098]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w10 -D FORWARD -i docker0 -o docker0 -j DROP' failed: iptables:...t chain?).
Aug 11 13:23:53 iZwz9cj1ytrolpgw50tiksZ firewalld[20098]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w10 -D FORWARD -i docker0 -o docker0 -j DROP' failed: iptables:...t chain?).
Aug 12 12:08:25 iZwz9cj1ytrolpgw50tiksZ firewalld[20098]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w10 -D FORWARD -i br-ec8a763caf1d -o br-ec8a763caf1d -j DROP' f...t chain?).
Aug 12 14:54:13 iZwz9cj1ytrolpgw50tiksZ firewalld[20098]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w10 -D FORWARD -i br-6f79f0a21b1f -o br-6f79f0a21b1f -j DROP' f...t chain?).
Aug 13 14:47:17 iZwz9cj1ytrolpgw50tiksZ firewalld[20098]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w10 -D FORWARD -i br-0867f70d356f -o br-0867f70d356f -j DROP' f...t chain?).
Aug 14 15:13:01 iZwz9cj1ytrolpgw50tiksZ firewalld[20098]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w10 -D FORWARD -i br-41be374279ca -o br-41be374279ca -j DROP' f...t chain?).
Aug 14 17:39:24 iZwz9cj1ytrolpgw50tiksZ firewalld[20098]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w10 -D FORWARD -i br-2de355ce9a61 -o br-2de355ce9a61 -j DROP' f...t chain?).
Aug 31 00:47:07 iZwz9cj1ytrolpgw50tiksZ systemd[1]: Stopping firewalld - dynamic firewall daemon...
Aug 31 00:47:08 iZwz9cj1ytrolpgw50tiksZ systemd[1]: Stopped firewalld - dynamic firewall daemon.
Hint: Some lines were ellipsized, use -l to show in full.
[root@iZwz9cj1ytrolpgw50tiksZ mysql]#

12、使用SQLyog可视化工具，进行远程数据库的连接

在这里插入图片描述

MySQL的主从复制

主从复制的优点

横向扩展解决方案。在多个从站之间分配负载以提高性能。在此环境中，所有写入和更新都必须在主服务器上进行。但是，读取可以在一个或多个从设备上进行。该模型可以提高写入性能（因为主设备专用于更新），同时显着提高了越来越多的从设备的读取速度
数据安全性。因为数据被复制到从站，并且从站可以暂停复制过程，所以可以在从站上运行备份服务而不会破坏相应的主数据
分析。可以在主服务器上创建实时数据，而信息分析可以在从服务器上进行，而不会影响主服务器的性能
远程数据分发。您可以使用复制为远程站点创建数据的本地副本，而无需永久访问主服务器

主从复制的原理

在这里插入图片描述

前提是作为主服务器角色的数据库服务器必须开启二进制日志

主服务器上面的任何修改都会通过自己的 I/O thread （I/O线程）保存在二进制日志 Binary log 里面

从服务器：

从服务器上面也启动一个 I/O thread ，通过配置好的用户名和密码，连接到主服务器上面请求读取二进制日志，然后把读取到的二进日志写到本地的一个 Relay log （中继日志）里面
从服务器上面同时开启一个 SQL thread 定时检查 Realy log （这个文件也是二进制的），如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。
每个从服务器都会收到主服务器二进制日志的全部内容的副本
从服务器设备负责决定应该执行二进制日志中的哪些语句。除非另行指定，否则主从二进制日志中的所有事件都在从站上执行。如果需要，您可以将从服务器配置为仅处理一些特定数据库或表的事件

主从复制的实现

1、再购买一台按量付费的阿里云服务。即我们拥有两台服务器，一台作为主节点（Master），一台作为从节点（Slave）。两台服务器的MySQL环境必须保持一致

2、配置主节点

vim /etc/my.cnf # 修改配置文件，修改完后，一定要重启MySQL服务

# =============在配置文件中添加如下内容=============

# 同一局域网内注意要唯一
server-id=100  
# 开启二进制日志功能
log-bin=mysql-bin
# 复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）
binlog-ignore-db=mysql
# 分配内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式（mixed、statement、row。默认格式是statement）
binlog_format=mixed

[root@iZwz9cj1ytrolpgw50tiksZ etc]# vim /etc/my.cnf # 修改配置文件
[root@iZwz9cj1ytrolpgw50tiksZ etc]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# 同一局域网内注意要唯一
server-id=100
# 开启二进制日志功能
log-bin=mysql-bin
# 复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）
binlog-ignore-db=mysql
# 分配内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式（mixed、statement、row。默认格式是statement）
binlog_format=mixed

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@iZwz9cj1ytrolpgw50tiksZ etc]# systemctl restart mysqld # 重启MySQL服务
[root@iZwz9cj1ytrolpgw50tiksZ etc]#

3、配置从节点

vim /etc/my.cnf # 修改配置文件

# =============在配置文件中添加如下内容=============

# 设置server_id，注意要唯一
server-id=102
# 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=mysql-slave-bin
# 配置relay_log（中继日志）
relay_log=edu-mysql-relay-bin
# 复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）
binlog-ignore-db=mysql
# 如果需要同步函数或者存储过程
log_bin_trust_function_creators=true
# 分配内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式（mixed、statement、row。默认格式是statement）
binlog_format=mixed
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

[root@iZwz918okfdpgh3bdh196mZ mysql]# vim /etc/my.cnf # 修改配置文件
[root@iZwz918okfdpgh3bdh196mZ mysql]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# 设置server_id，注意要唯一
server-id=102
# 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=mysql-slave-bin
# 配置relay_log（中继日志）
relay_log=edu-mysql-relay-bin
# 复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）
binlog-ignore-db=mysql
# 如果需要同步函数或者存储过程
log_bin_trust_function_creators=true
# 分配内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式（mixed、statement、row。默认格式是statement）
binlog_format=mixed
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@iZwz918okfdpgh3bdh196mZ mysql]# systemctl restart mysqld # 重启MySQL服务
[root@iZwz918okfdpgh3bdh196mZ mysql]#

4、登录进主节点的MySQL服务，然后在主节点服务器上，授予从节点服务器可以同步的权限

# 授予从节点权限
# grant replication slave, replication client on *.* to 'root'@'slave服务器的ip' identified by 'slave服务器MySQL的密码';

mysql> set global validate_password_policy=0; # 重启过MySQL服务器后，密码的级别恢复为默认值，需要手动重新降级一下
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave, replication client on *.* to 'root'@'39.108.154.227' identified by 'mkxiaoer';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges; # 刷新权限
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user; # 查看MySQL现在有哪些用户及对应的IP权限
+---------------+----------------+
| user          | host           |
+---------------+----------------+
| root          | %              |
| root          | 39.108.154.227 |
| mysql.session | localhost      |
| mysql.sys     | localhost      |
| root          | localhost      |
+---------------+----------------+
5 rows in set (0.00 sec)

mysql> show master status; # 查看主节点服务器MySQL的日志文件名和从节点开始复制的位置。这里日志文件名为：mysql-bin.000001，从节点开始复制的位置为：926
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      926 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

5、登录进从节点的MySQL服务，绑定主节点的MySQL服务

# 绑定主节点的MySQL服务
# change master to master_host='master服务器的ip', master_user='root', master_password='master服务器MySQL的密码', master_port=3306, master_log_file='主节点服务器MySQL的日志文件名',master_log_pos=从节点开始复制的位置;

mysql> change master to master_host='47.106.9.114', master_user='root', master_password='mkxiaoer', master_port=3306, master_log_file='mysql-bin.000001',master_log_pos=926;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave; # 开启主从复制
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G; # 查看slave的状态，\G代表换行查看
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 47.106.9.114 # 这里是Master节点的信息
                  Master_User: root # 这里是Master节点的信息
                  Master_Port: 3306 # 这里是Master节点的信息
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001 # 这里是Master节点的同步日志
          Read_Master_Log_Pos: 926
               Relay_Log_File: edu-mysql-relay-bin.000002 # 这里是Slave的中继日志
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes # 如果主从复制状态成功，这里必须为Yes
            Slave_SQL_Running: Yes # 如果主从复制状态成功，这里必须为Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 926
              Relay_Log_Space: 531
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 100
                  Master_UUID: 6d23269f-287e-11ed-932b-00163e1241aa
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

# 停止主从复制
# mysql> stop slave;

6、测试一下主从复制，在主节点上创建一个数据库，然后查看从节点，从节点也会自动同步新创建的那个数据库，说明主从复制成功。（在主节点下新增表，新增记录等操作都会同步给从节点）

# =============================主节点下的操作=============================

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database ksd_order_db;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ksd_order_db       |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

# =============================从节点下的操作=============================

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ksd_order_db       |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

切记：在主从复制操作的时候，不要基于主从复制去创建数据库或者相关操作，然后又去删除。这样会造成主从复制的pos改变，从而造成主从复制失败，如果出现此类问题，查看下面的相关解决方法

主从复制相关问题的解决

Slave_IO_Running为Connecting的问题

在这里插入图片描述

使用 start slave 开启主从复制后，如果Slave_IO_Running一直是Connecting，则说明主从复制一直处于连接状态，这种情况一般是下面几种原因造成的，我们可以根据 Last_IO_Error 的提示予以排除

网络不通。请检查ip、端口
密码不对。请检查是否创建用于同步的用户和用户密码是否正确

pos不对。请检查Master的Position

mysql> show master status; # 查看主节点服务器MySQL的日志文件名和从节点开始复制的位置。这里日志文件名为：mysql-bin.000001，从节点开始复制的位置为：926
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      926 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

Slave_SQL_Running为No的问题，解决方案一

在从节点的mysql中，依次执行如下命令

mysql> stop slave; # 停止主从复制

mysql> set global sql_slave_skip_counter=1; # 重新设置一下

mysql> start slave; # 开启主从复制

mysql> show slave status\G; # 重新查看slave的状态，\G代表换行查看

Slave_SQL_Running为No的问题，解决方案二

在主节点的mysql中，执行如下命令

mysql> show master status; # 查看主节点服务器MySQL的日志文件名和从节点开始复制的位置

在从节点的mysql中，执行如下命令

mysql> stop slave; # 停止主从复制

mysql> CHANGE MASTER TO MASTER_LOG_FILE='主节点服务器MySQL的日志文件名', MASTER_LOG_POS=从节点开始复制的位置; # 重新绑定主节点的MySQL服务 

mysql> start slave; # 开启主从复制

mysql> show slave status\G; # 重新查看slave的状态，\G代表换行查看

造成这类问题的原因一般是在主从复制的时候，创建了数据库或者表，然后又去删除了数据库或者表

更多的问题，上网百度解决

ShardingJDBC的读写分离实战

读写分离一定是构建在主从复制的基础上

1、新建一个SpringBoot项目

在这里插入图片描述

2、pom.xml文件的内容如下

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.xuexiangban</groupId>
    <artifactId>sharding-jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding-jdbc</name>
    <description>Demo project for Spring Boot</description>
    
    <properties>
        <java.version>1.8</java.version>
        <sharding-sphere.version>4.0.0-RC1</sharding-sphere.version>
    </properties>
    <dependencies>
        <!-- 依赖web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- 依赖mybatis和mysql驱动 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--依赖lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!--依赖sharding-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-core-common</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>
        <!--依赖数据源druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.21</version>
        </dependency>
        <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>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

3、把application.properties文件的后缀改为yml，然后编写application.yml

server:
  port: 8085
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    # 参数配置，显示sql
    props:
      sql:
        show: true
    # 配置数据源
    datasource:
      # 给每个数据源取别名，下面的ds1,ds2,ds3任意取名字
      names: ds1,ds2,ds3
      # 给master-ds1每个数据源配置数据库连接信息
      ds1:
        # 配置druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://47.106.9.114:3306/ksd_order_db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: mkxiaoer
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds2-slave
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://39.108.154.227:3306/ksd_order_db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: mkxiaoer
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds3-slave，我只有两个服务器，所以这里的ds3-slave和ds2-slave是一样的，实际操作上应该是不同的服务器
      ds3:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://39.108.154.227:3306/ksd_order_db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: mkxiaoer
        maxPoolSize: 100
        minPoolSize: 5
    # 配置默认数据源ds1
    sharding:
      # 默认数据源，主要用于写，注意一定要配置读写分离。如果不配置默认数据源，那么就会把三个节点都当做从节点，新增，修改和删除会出错。
      default-data-source-name: ds1
    # 配置数据源的读写分离，但是数据库一定要做主从复制
    masterslave:
      # 配置主从名称，可以任意取名字
      name: ms
      # 配置主库master，负责数据的写入
      master-data-source-name: ds1
      # 配置从库slave，负责数据的读
      slave-data-source-names: ds2,ds3
      # 配置slave节点的负载均衡策略，采用轮询机制：round_robin
      load-balance-algorithm-type: round_robin

# 整合mybatis的配置XXXXX
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.xuexiangban.shardingjdbc.entity

4、创建User实体类

package com.xuexiangban.shardingjdbc.entity;
import lombok.Data;
/**
 * @author: 学相伴-飞哥
 * @description: User
 * @Date : 2021/3/10
 */
@Data
public class User {
    // 主键
    private Integer id;
    // 昵称
    private String nickname;
    // 密码
    private String password;
    // 性
    private Integer sex;
    // 生日
    private String birthday;
}

5、编写UserMapper

package com.xuexiangban.shardingjdbc.mapper;
import com.xuexiangban.shardingjdbc.entity.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/**
 * @author: 学相伴-飞哥
 * @description: UserMapper
 * @Date : 2021/3/10
 */
@Mapper
public interface UserMapper {
    /**
     * @author 学相伴-飞哥
     * @description 保存用户
     * @params [user]
     * @date 2021/3/10 17:14
     */
    @Insert("insert into ksd_user(nickname,password,sex,birthday) values(#{nickname},#{password},#{sex},#{birthday})")
    void addUser(User user);
    /**
     * @author 学相伴-飞哥
     * @description 保存用户
     * @params [user]
     * @date 2021/3/10 17:14
     */
    @Select("select * from ksd_user")
    List<User> findUsers();
}

6、编写UserController

package com.xuexiangban.shardingjdbc.controller;
import com.xuexiangban.shardingjdbc.entity.User;
import com.xuexiangban.shardingjdbc.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.List;
import java.util.Random;
/**
 * @author: 学相伴-飞哥
 * @description: UserController
 * @Date : 2021/3/10
 */
@RestController
@RequestMapping("/user")
public class UserController {
    @Resource
    private UserMapper userMapper;
    @GetMapping("/save")
    public String insert() {
        User user = new User();
        user.setNickname("zhangsan"+ new Random().nextInt());
        user.setPassword("1234567");
        user.setSex(1);
        user.setBirthday("1988-12-03");
        userMapper.addUser(user);
        return "success";
    }
    @GetMapping("/listuser")
    public List<User> listuser() {
        return userMapper.findUsers();
    }
}

7、测试结果

在这里插入图片描述

MySQL的分库分表

为什么要分库分表

一般的机器（4核16G），单库的MySQL并发（QPS+TPS）一旦超过2k，系统基本就完蛋了。最好是并发量控制在1k左右。所以这里就引出解决方案，分库分表

分库分表的目的：解决高并发和数据量大的问题

在高并发的情况下，会造成IO读写频繁，自然就会造成读写缓慢，甚至是宕机。一般单库不要超过2k并发，很厉害的机器除外
出现数据量大的问题，主要由于底层索引实现导致，MySQL的索引实现为B+树，如果数据量极大，会导致索引树十分庞大，造成查询缓慢。另外，innodb的最大存储限制为64TB

要解决上述问题，最常见做法，就是分库分表

分库分表的目的，是将一个表拆成N个表，就是让每个表的数据量控制在一定范围内，保证SQL的性能。一个表的数据建议不要超过500W

在这里插入图片描述

分库分表的方式

**水平拆分：**统一个表的数据拆到不同的库不同的表中。可以根据时间、地区、或某个业务键维度，也可以通过hash进行拆分，最后通过路由访问到具体的数据。拆分后的每个表结构要保持一致

**垂直拆分：**就是把一个有很多字段的表给拆分成多个表，或者是多个库上去。每个库表的结构都不一样，每个库表都包含部分字段。一般来说，可以根据业务维度进行拆分，如订单表可以拆分为订单、订单支持、订单地址、订单商品、订单扩展等表；也可以，根据数据冷热程度拆分，20%的热点字段拆到一个表，80%的冷字段拆到另外一个表

不停机分库分表的数据迁移

一般数据库的拆分也是有一个过程的，一开始是单表，后面慢慢拆成多表。那么我们就看下如何平滑的从MySQL单表过度到MySQL的分库分表架构

1、利用mysql+canal做增量数据同步，利用分库分表中间件，将数据路由到对应的新表中

2、利用分库分表中间件，全量数据导入到对应的新表中

3、通过单表数据和分库分表数据两两比较，更新不匹配的数据到新表中

4、数据稳定后，将单表的配置切换到分库分表配置上

在这里插入图片描述

小结

垂直拆分：业务模块拆分。商品库，用户库，订单库

水平拆分：对表进行水平拆分（也就是我们说的：分表）

ShardingJDBC关于分库分表的基础知识

逻辑表

逻辑表是指：水平拆分的数据库或者数据表的相同路基和数据结构表的总称。

比如用户数据根据用户id%2 的规则拆分为2个表，分别是：ksd_user0和ksd_user1。它们的逻辑表名是：ksd_user

在ShardingJDBC中的定义方式如下：

spring:
  shardingsphere:
    sharding:
      tables:
        # ksd_user 逻辑表名
        ksd_user:

数据节点和数据分片

在ShardingJDBC中的定义方式如下：

spring:
  shardingsphere:
    sharding:
      tables:
        # ksd_user 逻辑表名
        ksd_user:
            # 数据节点：多数据源：逻辑数据源$->{0..N}.逻辑表名$->{0..N}
            actual-data-nodes: ds$->{0..2}.ksd_user$->{0..1}
            # 数据节点：多数据源：数据源.逻辑表名$->{0..N}
            actual-data-nodes: ds0.ksd_user$->{0..1},ds1.ksd_user$->{2..4}
            # 指定单数据源的配置方式
            actual-data-nodes: ds0.ksd_user$->{0..4}
            # 全部手动指定
            actual-data-nodes: ds0.ksd_user0,ds1.ksd_user0,ds0.ksd_user1,ds1.ksd_user1,

数据分片是最小的单元。由数据源名称和数据表组成，比如：ds0.ksd_user0

在这里插入图片描述

分库分表的5种分片策略

5种分片策略

none不分片策略
inline分片策略（核心，必须要掌握）
按照标准规则分片策略（常用）
符合分片策略（了解）
hint分片策略（了解）

分片分为两种：

数据库分片（数据源分片）
表分片

这两种是不同维度的分片，但是它们能用的分片策略和规则是一样的，由两部分构成：

分片键
分片算法

inline分片策略的相关图片：

在这里插入图片描述

ShardingJDBC的分布式主键配置

分布式主键配置：主要解决分布式下的主键id可能存在冲突的问题。

ShardingSphere提供了灵活的配置分布式主键生成策略的方式。

支持两种方式配置：

SNOWFLAKE（默认使用雪花算法，生成64bit的长整型数据）
UUID

使用雪花算法，相关配置内容如下：

spring:
  shardingsphere:
    sharding:
      tables:
        # ksd_user 逻辑表名
        ksd_user:
          key-generator:
              # 主键的列明，
            column: id # 该id类型在Java中必须为Long类型。该id字段在数据库表中的类型为bigint，不需要自增
            type: SNOWFLAKE

使用UUID，相关配置内容如下：

spring:
  shardingsphere:
    sharding:
      tables:
        # ksd_user 逻辑表名
        ksd_user:
          key-generator:
              # 主键的列明，
            column: id # 该id类型在Java中为String类型。该id字段在数据库表中的类型为varchar，不需要自增
            type: UUID