Day132.Mysql集群、MyCat数据库中间件、读写分离、分库分表、E-R | 全局表、全局序列(分布式ID)

焰火青年·

已于 2022-08-25 21:09:02 修改

阅读量788

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库

于 2022-08-23 10:02:33 首次发布

本文链接：https://blog.csdn.net/a111042555/article/details/126477927

版权

MySQL 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

1. 分库 — 垂直拆分 (拆分库中的表)

编辑编辑

2. 分表 — 水平拆分 (拆分表中的行)

3. E-R表 (JOIN)

4. 全局表 (本地JOIN)

3、全局序列 (分布式id)

简历这么写：熟悉mysql 集群架构方案，技术选型，了解mha，mmm等解决方案。

一、阿里开发规约

小数类型使用decimal，禁止使用flloat和double

count(*) 会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行

不得使用外键与级联；高并发集群下存在数据更新风暴的风险。

JOIN禁止超过三张表，多表关联查询时，保证被关联的字段需要有索引。

页面搜索严禁左模糊或全模糊，如果需要，把表导到搜索引擎中去查(倒排索引)

利用覆盖索引来进行查询操作，避免回表(不要SELECT *)

SQL性能优化至少要达到range级别

一、Mysql 集群主从同步

master负责写操作，slave 负责读操作

MySQL复制三步骤：

1 master将写操作记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；

2 slave将master的binary log events拷贝到它的中继日志（relay log）；

从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致！

3 slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从接入点开始复制。

1. 克隆虚拟机，修改ip

vim /etc/sysconfig/network-scripts/ifcfg-ens33

基本原则：

1. 每个slave只有一个master；每个master可以有多个salve

2. 每个slave只能有一个唯一的服务器ID

主节点修改 vi /etc/my.cnf

server-id=1
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=infomation_schema
binlog-do-db=mytestdb
binlog_format=STATEMENT

2. 从机配置文件修改 vi /etc/my.cnf

server-id=2
relay-log=mysql-relay

3. 修改uuid，随便改一下两个mysql 改一个就行

vi /var/lib/mysql/auto.cnf

主从机重启systemctl restart mysqld

4. 在 master 上添加Mysql用户、权限

创建用户
CREATE USER slave IDENTIFIED BY 'sgg1minggE@666';
授权
GRANT ALL PRIVILEGES ON *.* TO 'slave';
flush privileges;

添加授权用户之后，建议在远程工具里测试一下

2、修改加密规则（可以直接复制）
ALTER USER 'slave'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
3、刷新权限（可以直接复制）
FLUSH PRIVILEGES;
4、重置密码
alter user 'slave'@'%' identified by '123456';

集群配置

获取主节点的记录offset

show master status;

执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

主被直间关联

修改之后，完整的三行一起在 slave 执行。

CHANGE MASTER TO MASTER_HOST='主机ip地址',
MASTER_USER='用户',MASTER_PASSWORD='密码',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;

CHANGE MASTER TO MASTER_HOST='192.168.86.87',
MASTER_USER='slave',MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=1019;

开启主从同步

start slave;

停止同步

stop slave;

查看集群状态

show slave status\G;

主机新建库、新建表、insert记录，从机复制

CREATE DATABASE mytestdb;

use mytestdb;
CREATE TABLE mytbl(id INT,NAME VARCHAR(16));
INSERT INTO mytbl VALUES(1, 'zhang3');
INSERT INTO mytbl VALUES(2, 'li4');

从机slave 中也有了数据

二、MyCat 数据库中间件 ★★

Mycat是数据库中间件。

可以在读写操作之间加入一个组件，把它们包容起来；通过虚拟数据库链接真实数据库

官网：Mycat1.6

1. 读写分离

2. 数据分片：垂直拆分(分库)、水平拆分(分表)、垂直+水平拆分(分库分表)
3. 多数据源整合

1、安装、配置、使用

下载最新驱动：Index of / ; 一定要下载新版本！！

1. 克隆一个有jdk1.8、mysql8.0的虚拟机，修改端口

vim /etc/sysconfig/network-scripts/ifcfg-ens33

1. 解压缩文件拷贝到linux下

解压：tar zxvf Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gz

移动到user/local：mv mycat/ /usr/local/

2. 添加Mycat用户，用来连接物理mysql服务器。

(master)

CREATE USER mycat IDENTIFIED BY '123456';

GRANT ALL PRIVILEGES ON *.* TO 'mycat';

FLUSH PRIVILEGES;

修改加密方式 (没权限登到mycat用户操作)

ALTER USER 'mycat'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

重置密码

alter user 'mycat'@'%' identified by '123456';

(slave)

因为之前配置了集群，87添加用户，88mysql也会添加，只需要修改权限即可

GRANT ALL PRIVILEGES ON *.* TO 'mycat';

FLUSH PRIVILEGES;

修改加密方式 (没权限登到mycat用户操作)

ALTER USER 'mycat'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

重置密码

alter user 'mycat'@'%' identified by '123456';

3. 修改myCat 配置文件 server.xml

4. 修改myCat 配置文件 schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 这是一个虚拟数据库，客户端连得都是这个库 -->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
<!--  物理库的数据源配置 mytestdb 真实的数据库-->
        <dataNode name="dn1" dataHost="host1" database="mytestdb" />
<!-- dataHost 和后端 真实 的物理mysql服务器 相关的连接配置，地址 密码等 -->
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>


		<!-- 数据写入的服务器 insert ，delete ，update -->
                <writeHost host="hostM1" url="jdbc:mysql://192.168.86.87:3306" user="mycat" password="123456">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS1" url="jdbc:mysql://192.168.86.88:3306" user="mycat" password="123456" />
                </writeHost>
        </dataHost>
</mycat:schema>

5. 启动myCat

6. 复制一个窗口(会话)，登录myCat (8066)

mysql -umycat -p -P8066 -h 192.168.86.89

7. 观察主从同步

use TESTDB;

show tables;

select * from mytbl; #查询的哪个库？{应该是主机}

修改从机的数据，再次查询！

插入测试

insert into mytbl values(111,"222");

对虚拟数据进行的所有操作，都会指向物理数据库

日志的种类

mysql 靠binlog去记录每一条对数据库操作

undolog 数据修改前

记录数据修改前的值，update类似的操作，方便后续出问题后可以恢复到修改前的状态

redolog 数据修改后

数据已经成功提交正在处理中，还没有真正写入磁盘，此时如果断电，可以通过redolog恢复

binlog 数据记录完

记录真正持久化后的日志，对应到idb文件。

在服务器刚刚开始运行的时候，开启了binlog，一旦有一天服务器里的数据丢失了，还可以通过binlog日志，逐条执行，就可以恢复数据了。

log和sql是有区别的

UndoLog记录的是在数据被修改之前的值

RedoLog记录的是在数据被修改之后的值

二者记录都发生在提交数据、存盘之前

BinLog记录所有木已成舟的确定性变化。

架构方案

One master and Muti salve 一主多备

一般用来做读写分离的，master 写，其他 slave 读，这种架构最大问题 I/O 压力集中在 Master 上<多台同步影响 IO>

M-S-S

使用一台 slave 作为中继，分担 Master 的压力，slave 中继需要开启 bin-log,并配置 log-slave-updates Slave 中继可使用 Black-hole 存储引擎，不会把数据存储到磁盘，只记录二进制日志。

M-M 双主互备 (互为主从)

很多人误以为这样可以做到 MySQL 负载均衡，实际没什么好处，每个服务器需要做同样的同步更新，破坏了事物的隔离性和数据的一致性。

M-M-M

监控三台机器互相作对方的 master 天生的缺陷：复制延迟，slave 上同步要慢于 master,如果大并发的情况那延迟更严重

MHA

Perl语言写的，开源的MYSQL故障切换方案；全称：Master High Availability，故障切换时间10-30s

MMM

MMM即Multi-Master Replication Manager for MySQL:mysql多主复制管理器,基于perl实现,关于mysql主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件（在任何时候只有一个节点可以被写入）

2、搭建读写分离、分库分表 ★★

1. 修改schema.xml配置文件

默认：balance="0"

（1）balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
（2）balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。
（3）balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。
（4）balance="3"，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力

修改一个数据库字段，可以看到随机读效果

修改 schema.xml文件

1. 分库 — 垂直拆分 (拆分库中的表)

IO密集型 ->硬盘：Mysql(数据)、Redis(日志)、文件服务器

计算密集型->cpu：微服务、Controller(单例)

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将查询压力分担到不同的库上面。

1. 先停止主从同步：stop slave;

2. 修改配置文件 (大换血)

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
		<!-- 这是一个虚拟数据库，客户端连得都是这个库 -->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
			<table name="customer" dataNode="dn2" ></table>
        </schema>
			<!--  物理库的数据源配置 mytestdb 真实的数据库-->
			<dataNode name="dn1" dataHost="host1" database="orders" />
			<dataNode name="dn2" dataHost="host2" database="orders" />
		
		<!-- dataHost 和后端 真实 的物理mysql服务器 相关的连接配置，地址 密码等 -->
		<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="jdbc:mysql://192.168.86.87:3306" user="mycat" password="123456">
                </writeHost>
        </dataHost>
        <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM2" url="jdbc:mysql://192.168.86.88:3306" user="mycat" password="123456">
                </writeHost>
        </dataHost>
</mycat:schema>

3. 重启myCat，再次查询

4. 创建数据库

#在主从机 dn1、dn2 上分别创建数据库orders

CREATE DATABASE orders;

5. 在myCat执行建表语句

#客户表  rows:20万 
CREATE TABLE customer(
    id INT AUTO_INCREMENT,
    NAME VARCHAR(200),
    PRIMARY KEY(id)
);
#订单表   rows:600万
CREATE TABLE orders(
    id INT AUTO_INCREMENT,
    order_type INT,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id)  
); 
#订单详细表  rows:600万
CREATE TABLE orders_detail(
    id INT AUTO_INCREMENT,
    detail VARCHAR(2000),
    order_id INT,
    PRIMARY KEY(id)
);
#订单状态字典表   rows:20条
CREATE TABLE dict_order_type(
    id INT AUTO_INCREMENT,
    order_type VARCHAR(200),
    PRIMARY KEY(id)
);

6. 查看：master(87) 上有三张表，slave(88)上有一张表

myCat的BUG：建出来表是大写的，并且区分大小写；改成小写就行。

阿里框架很多刷KPI，注册中心 Eureka；配置中心阿波罗

2. 分表 — 水平拆分 (拆分表中的行)

MySQL单表存储数据条数是有瓶颈的，单表达到1000万条数据就达到了瓶颈，会影响查询效率，需要进行水平拆分（分表）进行优化。

1. 修改schema.xml；添加表分配的策略

<table name="orders" dataNode="dn1,dn2" rule="xxoo_role" ></table>

2. 修改rule.xml 112 行左右；修改分片的数据库节点数，添加rule配置

	<tableRule name="xxoo_role">
		<rule>
			<columns>customer_id</columns>
			<algorithm>mod-long</algorithm>
	    </rule>
    </tableRule>

3. 在 slave 中建表

CREATE TABLE orders(
    id INT AUTO_INCREMENT,
    order_type INT,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id)  
);

4. 访问Mycat灌数据实现分片

在mycat里向orders表插入数据，INSERT时字段不能省略

INSERT INTO orders(id,order_type,customer_id,amount) VALUES (1,101,100,100100);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020);

myCat以customer_id区别写入了哪台机器

3. E-R表 (JOIN)

虽然分好了表，但是不支持JOIN分表查询；

我们要对orders_detail也要进行分片操作。Join的原理如下图：

1. 修改schema.xml配置文件，添加配置

			<table name="orders" dataNode="dn1,dn2"  rule="xxoo_role" autoIncrement="true" fetchStoreNodeByJdbc="true">
				<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id"/>
			</table>

2. 在 slave 创建orders_detail表

CREATE TABLE orders_detail(
    id INT AUTO_INCREMENT,
    detail VARCHAR(2000),
    order_id INT,
    PRIMARY KEY(id)
);

3. 访问Mycat向orders_detail表插入数据

INSERT INTO orders_detail(id,detail,order_id) VALUES(1,'detail1',1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail1',2);
INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail1',3);
INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail1',4);
INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail1',5);
INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail1',6);

4. 再次JOIN测试

select * from orders t1 left join orders_detail t2 on t2.id = t2.order_id;

4. 全局表 (本地JOIN)

全局表的插入、更新操作会实时在所有节点上执行，保持各个分片的数据一致性；可以跟任何一个表进行 JOIN 操作；(参考数据字典)

用来方便JOIN，myCat不支持跨库JOIN，也不建议

1. schema.xml添加配置

2. slave 创建dict_order_type表

CREATE TABLE dict_order_type(
    id INT AUTO_INCREMENT,
    order_type VARCHAR(200),
    PRIMARY KEY(id)
);

3. 访问Mycat向dict_order_type表插入数据

INSERT INTO dict_order_type(id,order_type) VALUES(101,'type1');
INSERT INTO dict_order_type(id,order_type) VALUES(102,'type2');

master，slave同时添加了字段

3、全局序列 (分布式id)

分布式ID在极高的并发下才需要使用。

分布式环境下，实现分库分表的情况后，数据库自增主键已无法保证自增主键的全局唯一。为此，Mycat 提供了全局 sequence，并且提供了包含本地配置和数据库配置等多种实现方式

1. 本地文件 (没人用)

可以由myCat负责生成全局ID，存入文件统一管理。

优点：本地加载，读取速度较快
缺点：抗风险能力差，Mycat所在主机宕机后，无法读取本地文件。

2. 时间戳方式 (uuid | 雪花算法；主流；美团在用)

全局序列ID= 64 位二进制 (42(毫秒)+5(机器 ID)+5(业务编码)+12(重复累加) 换算成十进制为 18 位数的 long 类型，每毫秒可以并发 12 位二进制的累加。

优点：配置简单
缺点：无逻辑，无法排序，无法作为聚簇索引使用；18位ID过长

牺牲了有序性，极快速的生成；

3. 自主生成全局序列 (意淫出来的，没人用)

搭建一个Redis服务器专门存储ID，利用redis的单线程原子性(先进先出)来生成唯一ID

但，自主生成需要单独在工程中用java代码实现，增加了分布式项目的复杂性

4. 自主生成全局序列 (有人用)

根据数据库建立字符串索引，可使用步进实现。只能在服务器个数确定时使用。

5. 数据库的方式 (没人用)

通过存储过程创建有序id 存储过程太麻烦，略。

焰火青年·

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Day132.Mysql集群、MyCat数据库中间件、读写分离、分库分表、E-R | 全局表、全局序列(分布式ID)

mysql
复制链接

扫一扫

专栏目录

Day132.Mysql集群、MyCat数据库中间件、读写分离、分库分表、E-R | 全局表、全局序列(分布式ID)

一、Mysql 集群 主从同步

二、MyCat 数据库中间件 ★★

1、安装、配置、使用

日志的种类

架构方案

2、搭建读写分离、分库分表 ★★

1. 分库 — 垂直拆分 (拆分库中的表)

2. 分表 — 水平拆分 (拆分表中的行)

3. E-R表 (JOIN)

4. 全局表 (本地JOIN)

3、全局序列 (分布式id)

“相关推荐”对你有帮助么？

一、Mysql 集群主从同步