MySql搭建主从复制-读写分离

定西2023-09-162024-06-08

主从复制基础配置

准备

准备两台虚拟机,如果是克隆的虚拟机,注意修改:

修改静态的ip地址
修改mac地址
修改UUID
修改mysql的uuid
修改主机名(可选)

配置

修改配置文件后要进行服务重启

主机配置文件:

这个有坑,感觉log-bin只要换成文件夹指定报错/mysql-bin,也不懂是为啥

#主服务器唯一ID
server-id=1
#
##启用二进制日志
log-bin=mysql-bin
#
##设置不要复制的数据库(可设置多个)
#binlog-ignore-db=mysql
#binlog-ignore-db=information_schema
#
##设置需要复制的数据库。注意：MySQL是从接入点开始复制操作的
binlog-do-db=zzmr-master-slave
#
##设置logbin格式
binlog_format=STATEMENT

然后在主机中新建用户:

create user 'slave'@'%' identified by '010203';

GRANT REPLICATION SLAVE ON *.* TO slave@'%';

ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '010203';

flush privileges;

从机配置文件

#从服务器唯一ID
server-id=2

#启用中继日志
relay-log=mysql-relay

从机配置要进行复制的主机

需要先在主机中执行show master status,得到一个表格:

然后在从机中执行,要注意表格中的内容

CHANGE MASTER TO MASTER_HOST='192.168.168.101',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=156;

启用

执行start slave
执行show slave status\G,得到两个yes即代表配置成功

测试

此时在主库创建指定的数据库,插入数据,从库都能进行同步
主机:
20230916094023
从机:
20230916094038

关闭

从机执行stop slave

然后在重新打开就行了,如果出问题,执行reset slave,然后重新执行上面的CHANGE MASTER TO

读写分离基础配置

MyCat分库分表

问题分析
应用系统的数据量增加,若采用单数据库进行数据存储,存在以下性能瓶颈

IO瓶颈:热点数据太多,数据库缓存不足,产生大量磁盘IO,效率较低,请求数据太多,带宽不够,网络IO瓶颈
CPU瓶颈,排序,分组,连接查询,聚合统计等Sql会耗费大量的CPU资源,请求次数太多,CPU出现瓶颈

分库分表的中心思想都是将数据分散存储,使得单一数据库/表的数据量变小来缓解单一数据库性能的问题,从而达到提升数据库性能的目的

拆分策略

垂直拆分
- 垂直分库
- 垂直分表
水平拆分
- 水平分表
- 水平分库

垂直分库:以表为依据,根据业务将不同表拆分到不同库中

特点:

每个库的表结构都不一样
每个库的数据也不一样
所有库的并集是全量数据

垂直分表:以字段为依据,根据字段属性将不同字段拆分到不同表中

特点:

每个表的结构都不一样
每个表的数据也不一样,一般通过一列(主键/外键)关联
所有表的并集是全量数据

水平分库:以字段为依据,按照一定策略,将一个库的数据拆分到多个数据库中

特点:

每个库的表结构都一样
每个库的数据都不一样
所有库的并集是全量数据

水平分表:以字段为依据,按照一定的策略,将一个表的数据拆分到多个表中

特点:

每个表结构都一样
每个表的数据不一样
所有数据的并集是全量数据

其实就是,垂直是变的结构,将库中的表拆分到多台服务器中,或者是将表的字段拆分到多个服务器中,而水平是不变结构,将数据拆分,将数据拆分到多台服务器中,每台服务器的数据不一样,或者是将一张表的数据拆分到多台服务器上

MyCat概述

MyCat是开源的,活跃的,基于Java语言编写的MySql数据库中间件,可以像使用mysql一样来使用mycat,对于开发人员来说根本感觉不到mycat的存在
20230916105954

核心概念:
20230917095821

mycat是不存储数据的,只是负责分片处理

安装

下载链接

这个又需要4台虚拟机了?

不对,是3台,3台mysql,其中一台还要配置mycat

所以我现在缺少一台

再clone一台出来

虽然不知道为什么原来的从机和新克隆出来的机器的ip地址都是和配置文件中的不一样,一个129，一个130

但又不是不能用,现在把101这个机器再搞一个mycat就算完成了

首先安装好JDK,然后安装mycat

bin:存放可执行文件,用于启动停止mycat
conf:存放mycat的配置文件
lib:存放mycat的项目依赖包
logs:存放mycat的日志文件

在安装之后,查看lib文件夹,发现里面的数据库连接驱动是5.x版本,但是我们使用的是8.x版本,所以要进行替换

Mycat入门

需求
由于tb_order表中数据量很大,磁盘IO及容量都达到了瓶颈,现在需要对tb_order表进行数据分片,分为三个数据节点,每个节点主机位于不同的服务器上,具体的结构如下:
20230917100149

环境配置
20230917100304

当然我的是101,129,130,其中101是mycat也是mysql

步骤

在三台数据库中新建数据库:db01

修改schema.xml配置文件,整体来看还是挺清晰明了的

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
		<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
	</schema>
	
	<dataNode name="dn1" dataHost="dhost1" database="db01" />
	<dataNode name="dn2" dataHost="dhost2" database="db01" />
	<dataNode name="dn3" dataHost="dhost3" database="db01" />
	
	<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		
		<writeHost host="master" url="jdbc:mysql://192.168.168.101:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="010203" />
	</dataHost>
	
	<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		
		<writeHost host="master" url="jdbc:mysql://192.168.168.129:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="010203" />
	</dataHost>
	
	<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		
		<writeHost host="master" url="jdbc:mysql://192.168.168.130:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="010203" />
	</dataHost>
</mycat:schema>

修改server.xml,只需要修改下面这部分,把password和schemas修改一下

<user name="root" defaultAccount="true">
		<property name="password">010203</property>
		<property name="schemas">DB01</property>
		
		<!-- 表级 DML 权限设置 -->
		<!-- 	
		<privileges check="true">
			<schema name="DB01" dml="0110" > 
				<table name="TB_ORDER" dml="1110"></table>
			</schema>
		</privileges>		
		 -->
	</user>

	<user name="user">
		<property name="password">010203</property>
		<property name="schemas">DB01</property>
		<property name="readOnly">true</property>
	</user>

配置完成,测试
启动:
20230917102622

启动成功:
20230917103845

一直连接不上,最后执行了一条:mysql -uroot -p010203 -P8066 -h192.168.153.132 --default_auth=mysql_native_password总算连接上了
20230917112610

会发现,mycat中只有一个数据库:
20230917112735

快疯了,现在是无论如何都连接不上了,一直提示是密码错误,但是我用同样的密码在navicat中就可以连接上,甚至创建表还可以同步

?好像是ip地址有问题

还是问题挺大的,但是目前先看看能不能实现水平分表吧

在mycat中添加数据,随便加了几条,发现都是在101中,因为这是默认的规则/usr/local/mycat/conf/autopartition-long.txt决定的,意思就是id在0-500万的,就会在第一数据库中,以此类推-注意,500M还是在第一库中,如果超出了15000000,那么就会报错

# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2

Mycat配置

schema.xml:schema.xml作为mycat的重要配置文件之一,涵盖了Mycat的逻辑库,逻辑表,分片规则,分片节点及数据源的配置,主要包含以下三组标签:
- schema标签
- datanode标签
- datahost标签

schema标签用于定义MyCat实例中的逻辑库,一个mycat实例中可以有多个逻辑库,可以通过schema标签来划分不同的逻辑库,mycat中的逻辑库的概念,等同于mysql中的database概念,需要操作某个逻辑库下的表时,也需要切换逻辑库(use xxx)

name:指定自定义的逻辑库库名
checkSQLschema:在Sql语句操作时指定了数据库名称,执行时是否自动去除,ture,自动去除

sqlMaxLimit:如果未指定limit进行查询,列表查询模式查询多少条记录

1
2
3

<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
	<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
</schema>

table标签定义了Mycat中逻辑库schema下的逻辑表,所有需要拆分的表都需要在table标签中定义

name:定义逻辑表表名
dataNode:定义逻辑表所属的dataNode,该属性需要与dataNode标签中name对应,多个dataNode逗号分隔
rule:分片规则的名字,分片规则名字是在rule.xml中定义的
primaryKey:逻辑表对应真实表的主键
type:逻辑表的类型,目前逻辑表只有全局表和普通表,如果未配置,就是普通表,全局表,配置为global

datanode标签
dataNode标签中定义了Mycat中的数据节点,也就是我们通常说的数据分片,一个dataNode标签就是一个独立的数据分片

name:定义数据节点名称
dataHost:数据库实例主机名称,引用自dataHost标签中name属性

database:定义分片所属数据库

1
2
3

<dataNode name="dn1" dataHost="dhost1" database="db01" />
<dataNode name="dn2" dataHost="dhost2" database="db01" />
<dataNode name="dn3" dataHost="dhost3" database="db01" />

datahost标签
该标签在Mycat逻辑库中作为底层标签存在,直接定义了具体的数据库实例,读写分离,心跳语句

name:唯一标识,供上层标签使用
maxCon/minCon:最大/最小连接数
balance:负载均衡策略:取值0,1,2,3
writeType:写操作分发方式(0:写操作转发到第一个writeHost,第一个挂了,切换到第二个,1:写操作随机分发到配置的writeHost)

dbDriver:数据库驱动,支持native,jdbc

<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
		  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
	<heartbeat>select user()</heartbeat>
	
	<writeHost host="master" url="jdbc:mysql://192.168.168.101:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="010203" />
</dataHost>

rule.xml,rule.xml中定义所有拆分表的规则,在使用过程中可以灵活的使用分片算法,或者对同一个分片算法使用不同的参数,它让分片过程可配置化,主要包含两类标签:tableRule,Function

<tableRule name="auto-sharding-long">
	<rule>
		<columns>id</columns>
		<algorithm>rang-long</algorithm>
	</rule>
</tableRule>

<function name="rang-long"
	class="io.mycat.route.function.AutoPartitionByLong">
	<property name="mapFile">autopartition-long.txt</property>
</function>

server.xml,server.xml配置文件包含了Mycat的系统配置信息,主要有两个重要的标签,system,user

Mycat分片

垂直拆分

垂直分库
在业务系统中,射击以下表结构,但是由于用户与订单每天都会产生大量大的数据,单台服务器的数据存储及处理能力有限,可以对数据库表进行拆分,原有的数据库表如下
20230917144125

然后就是修改schema配置文件

<schema name="SHOPPING" checkSQLschema="true" sqlMaxLimit="100">
		<!-- 商品 -->
		<table name="tb_goods_base" dataNode="dn1" primaryKey="id" />
		<table name="tb_goods_brand" dataNode="dn1" primaryKey="id" />
		<table name="tb_goods_cat" dataNode="dn1" primaryKey="id" />
		<table name="tb_goods_desc" dataNode="dn1" primaryKey="goods_id" />
		<table name="tb_goods_item" dataNode="dn1" primaryKey="id" />

		<!-- 订单 -->
		<table name="tb_order_item" dataNode="dn2" primaryKey="id" />
		<table name="tb_order_master" dataNode="dn2" primaryKey="order_id" />
		<table name="tb_order_pay_log" dataNode="dn2" primaryKey="out_trade_no" />

		<!-- 用户 -->
		<table name="tb_user" dataNode="dn3" primaryKey="id" />
		<table name="tb_user_address" dataNode="dn3" primaryKey="id" />
		<table name="tb_areas_provinces" dataNode="dn3" primaryKey="id" />
		<table name="tb_areas_city" dataNode="dn3" primaryKey="id" />
		<table name="tb_areas_region" dataNode="dn3" primaryKey="id" />
	</schema>

	<dataNode name="dn1" dataHost="dhost1" database="shopping" />
	<dataNode name="dn2" dataHost="dhost2" database="shopping" />
	<dataNode name="dn3" dataHost="dhost3" database="shopping" />

以及修改server.xml

<user name="root" defaultAccount="true">
		<property name="password">010203</property>
		<property name="schemas">SHOPPING</property>
		
		<!-- 表级 DML 权限设置 -->
		<!-- 	
		<privileges check="true">
			<schema name="DB01" dml="0110" > 
				<table name="TB_ORDER" dml="1110"></table>
			</schema>
		</privileges>		
		 -->
	</user>

	<user name="user">
		<property name="password">010203</property>
		<property name="schemas">SHOPPING</property>
		<property name="readOnly">true</property>
	</user>

在mycat中新增表结构,和插入数据,在另外三个节点都能查看到对应的数据

多表查询也没问题:
1231321321

但是如果执行的sql,涉及到分片,就是表不在同一个数据库中,则会报错
137747

SELECT
	order_id,
	payment,
	receiver,
	province,
	city,
	area 
FROM
	tb_order_master o,
	tb_areas_provinces p,
	tb_areas_city c,
	tb_areas_region r 
WHERE
	o.receiver_province = p.provinceid 
	AND o.reveiver_city = c.cityid 
	AND o.receiver_region = r.areaid

这时就可以把一些表设置为全局表,只需要在table标签中加入type="gloabl",然后节点改成dn1,dn2,dn3

感觉这种东西用处不大?

水平拆分

20230917153943

<schema name="ITCAST" checkSQLschema="true" sqlMaxLimit="100">
	<!-- 切换分片算法 对id进行取模运算 -->
	<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />
</schema>

<dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

server.xml中要给root用户新增数据库<property name="schemas">SHOPPING,ITCAST</property>

mycat中:
20230917154803

dn1: 20230917154817
dn2: 20230917154828
dn3: 20230917154838

默认是对3取模,得0,所以在第一个节点

读写分离配置

读写分离是基于主从复制的,简单地说是把数据库的读和写操作分开,以对应不同的数据库服务器,主数据库提供写操作,从数据库提供读操作,这样能有效地减轻单台数据库的压力
通过MyCat即可轻易实现上述功能,不仅可以支持mysql,也支持其他数据库

20230917221748

先搭建好一主一从的数据库

然后就可以开始配置读写分离了

Mycat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控制
20230917222450

这里要配置的参数中,balance要改为1或者3,一主一从,有的是主机只负责写,从机只负责读,也有的为了减轻读的压力,主机和从机都参与读,1是主机和从机都负责读,而3是主机只负责写
20230917225655

schema.xml

<!-- 配置读写分离 -->
	<schema name="ZZMR-MASTER-SLAVE" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7">
	</schema>
	<dataNode name="dn7" dataHost="dhost4" database="zzmr-master-slave" />

	<!-- 读写分离的datahost -->
	<dataHost name="dhost4" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="master" url="jdbc:mysql://192.168.168.101:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="010203">
			<readHost host="slave" url="jdbc:mysql://192.168.168.129:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="010203">
			</readHost>
		</writeHost>
	</dataHost>

此时mycat中就能看到逻辑库逻辑表了

如何验证实现了读写分离呢?
此时在从库中直接修改数据,然后在mycat中查询,发现查询到的是从库修改的数据,而主库的数据是没有变化的,当插入时,主库是能插入的,所以实现了读写分离

好,可算是把mysql的主从复制,读写分离配置好了!