mysql集群，主从同步，读写分离，水平拆分，垂直拆分，双主双从+mycat

主从复制

MySQL主从复制的核心就是二进制日志，具体的过程如下：

从上图来看，复制分成三步：

Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。
slave重做中继日志中的事件，将改变反映它自己的数据。

搭建准备

准备好两台服务器之后，在上述的两台服务器中分别安装好MySQL，并完成基础的初始化准备(安装、
密码配置等操作)工作。其中：
192.168.200.200 作为主服务器master
192.168.200.201 作为从服务器slave

主库配置

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

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 232-1，默认为1
server-id=1
#是否只读,1 代表只读, 0 代表读写
read-only=0
#忽略的数据, 指不需要同步的数据库
#binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01

重启MySQL服务器
登录mysql，创建远程连接的账号，并授予主从复制权限

#创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
# 解释：创建名为itcast的用户，@'%'可以在任意主机上访问，密码'Root@123456'
CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
#为 'itcast'@'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';

通过指令，查看二进制日志坐标

show master status ;

字段含义说明：
file : 从哪个日志文件开始推送日志文件
position ：从哪个位置开始推送日志
binlog_ignore_db : 指定不需要同步的数据库

从库配置

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

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，和主库不一样即可
server-id=2
#是否只读,1 代表只读, 0 代表读写，只针对普通用户，超级管理员不影响，
# 如果要root也只读，加一行super-read-only=1（中划线下划线都可）
read-only=1

重新启动MySQL服务
登录mysql，设置主库配置

CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.200.200', master_port=3306,SOURCE_USER='itcast',SOURCE_PASSWORD='Root@123456', SOURCE_LOG_FILE='binlog.000004',SOURCE_LOG_POS=663;

上述是8.0.23中的语法。如果mysql是 8.0.23 之前的版本，执行如下SQL：

CHANGE MASTER TO MASTER_HOST='192.168.200.200', master_port=3306,MASTER_USER='itcast',MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='binlog.000004',MASTER_LOG_POS=663;

参数名	含义	8.0.23之前
SOURCE_HOST	主库IP地址	MASTER_HOST
SOURCE_USER	连接主库的用户名	MASTER_USER
SOURCE_PASSWORD	连接主库的密码	MASTER_PASSWORD
SOURCE_LOG_FILE	binlog日志文件名	MASTER_LOG_FILE
SOURCE_LOG_POS	binlog日志文件位置	MASTER_LOG_POS

开启同步操作

start replica ; #8.0.22之后
start slave ; #8.0.22之前

查看主从同步状态

show replica status ; #8.0.22之后
show slave status ; #8.0.22之前

假如是虚拟机复制的，会出现一些问题：

mysql的uuid是同一个，uuid是启动时自动生成的auto.cnf
错误：Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs；

[root@localhost ~]# systemctl stop mysqld         #关闭mysql
[root@localhost mysql]# rm -rf /var/lib/mysql/auto.cnf        #删除从库auto.cnf文件
[root@localhost ~]# systemctl start mysqld                 #启动从库mysqld服务

已经绑定过
错误：ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread； run STOP SLAV
错误原因：
mysql从机上已经进行过绑定了，如果继续绑定需要先进行重置。
解决办法
1. 停止已经启动的绑定
```
stop slave;
```
1. 重置绑定
```
reset master;
```
1. 执行复制主机命令
```
CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.25.110', SOURCE_USER='itcast',SOURCE_PASSWORD='Root@123456', SOURCE_LOG_FILE='binlog.000009',SOURCE_LOG_POS=1595;
```
1. 发现此时已经不报错
2. 启动复制
```
start replica ;
```
1. 查看状态
```
show replica status;
```

测试

在主库插入数据：

create database db01;
use db01;
create table tb_user(
id int(11) primary key not null auto_increment,
name varchar(50) not null,
sex varchar(1)
)engine=innodb default charset=utf8mb4;
insert into tb_user(id,name,sex) values(null,'Tom', '1'),(null,'Trigger','0'),
(null,'Dawn','1');

验证从库是否同步。

此时一个最小配置的mysql主从同步已搭建完成，增加从库重复从库操作即可。增加主从集群，从头到尾重复即可。注意server-id做区分。

安装mycat

目录介绍

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

MyCat配置 schema.xml

schema.xml 作为MyCat中最重要的配置文件之一 , 涵盖了MyCat的逻辑库、逻辑表、分片规则、分片节点及数据源的配置。

主要包含以下三组标签：

schema标签
datanode标签
datahost标签

schema标签 schema 定义逻辑库

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

核心属性：

name：指定自定义的逻辑库库名
checkSQLschema：在SQL语句操作时指定了数据库名称，执行时是否自动去除；true：自动去除，false：不自动去除
sqlMaxLimit：如果未指定limit进行查询，列表查询模式查询多少条记录

schema 中的table定义逻辑表

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

核心属性：

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

datanode标签

核心属性：

name：定义数据节点名称
dataHost：数据库实例主机名称，引用自 dataHost 标签中name属性
database：定义分片所属数据库

datahost标签

该标签在MyCat逻辑库中作为底层标签存在, 直接定义了具体的数据库实例、读写分离、心跳语句。
核心属性：

name：唯一标识，供上层标签使用
maxCon/minCon：最大连接数/最小连接数
balance：负载均衡策略，取值 0,1,2,3
writeType：写操作分发方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二个；1：写操作随机分发到配置的writeHost）
dbDriver：数据库驱动，支持 native、jdbc

rule.xml

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

server.xml

server.xml配置文件包含了MyCat的系统配置信息，主要有两个重要的标签：system、user。
1). system标签

主要配置MyCat中的系统配置信息，对应的系统配置项及其含义，如下：

属性	取值	含义
charset	utf8	设置Mycat的字符集, 字符集需要与MySQL的字符集保持一致
nonePasswordLogin	0,1	0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户
useHandshakeV10	0,1	使用该选项主要的目的是为了能够兼容高版本的jdbc驱动, 是否采用HandshakeV10Packet来与client进行通信, 1:是, 0:否
useSqlStat	0,1	开启SQL实时统计, 1 为开启 , 0 为关闭 ; 开启之后, MyCat会自动统计SQL语句的执行情况 ; mysql -h 127.0.0.1 -P 9066 -u root -p 查看MyCat执行的SQL, 执行效率比较低的SQL , SQL的整体执行情况、读写比例等 ; show @@sql ; show @@sql.slow ; show @@sql.sum ;
useGlobleTableCheck	0,1	是否开启全局表的一致性检测。1为开启，0为关闭。
sqlExecuteTimeout	1000	SQL语句执行的超时时间 , 单位为 s ;
sequnceHandlerType	0,1,2	用来指定Mycat全局序列类型，0 为本地文件，1 为数据库方式，2 为时间戳列方式，默认使用本地文件方式，文件方式主要用于测试
sequnceHandlerPattern	正则表达式	必须带有MYCATSEQ或者 mycatseq进入序列匹配流程注意MYCATSEQ_有空格的情况
subqueryRelationshipCheck	true,false	子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false
useCompression	0,1	开启mysql压缩协议 , 0 : 关闭, 1 : 开启
fakeMySQLVersion	5.5,5.6	设置模拟的MySQL版本号
defaultSqlParser		由于MyCat的最初版本使用了FoundationDB的SQL解析器, 在MyCat1.3后增加了Druid解析器, 所以要设置defaultSqlParser属性来指定默认的解析器; 解析器有两个 : druidparser 和 fdbparser, 在MyCat1.4之后,默认是druidparser, fdbparser已经废除了
processors	1,2…	指定系统可用的线程数量, 默认值为CPU核心 x 每个核心运行线程数量; processors 会影响processorBufferPool, processorBufferLocalPercent, processorExecutor属性, 所有, 在性能调优时, 可以适当地修改processors值
processorBufferChunk		指定每次分配Socket Direct Buffer默认值为4096字节, 也会影响BufferPool长度, 如果一次性获取字节过多而导致buffer不够用, 则会出现警告, 可以调大该值
processorExecutor		指定NIOProcessor上共享 businessExecutor固定线程池的大小; MyCat把异步任务交给 businessExecutor线程池中, 在新版本的MyCat中这个连接池使用频次不高, 可以适当地把该值调小
packetHeaderSize		指定MySQL协议中的报文头长度, 默认4个字节
maxPacketSize		指定MySQL协议可以携带的数据最大大小, 默认值为16M
idleTimeout	30	指定连接的空闲时间的超时长度;如果超时,将关闭资源并回收, 默认30分钟
txIsolation	1,2,3,4	初始化前端连接的事务隔离级别,默认为 REPEATED_READ , 对应数字为3 READ_UNCOMMITED=1; READ_COMMITTED=2; REPEATED_READ=3; SERIALIZABLE=4;
sqlExecuteTimeout	300	执行SQL的超时时间, 如果SQL语句执行超时,将关闭连接; 默认300秒;
serverPort	8066	定义MyCat的使用端口, 默认8066
managerPort	9066	定义MyCat的管理端口, 默认9066

2). user标签

配置MyCat中的用户、访问密码，以及用户针对于逻辑库、逻辑表的权限信息，具体的权限描述方式及配置说明如下：

在测试权限操作时，我们只需要将 privileges 标签的注释放开。在 privileges 下的schema 标签中配置的dml属性配置的是逻辑库的权限。在privileges的schema下的table标签的dml属性中配置逻辑表的权限。

启动

#启动

bin/mycat start

#停止

bin/mycat stop

Mycat启动之后，占用端口号 8066。
启动完毕之后，可以查看logs目录下的启动日志，查看Mycat是否启动完成。

tail -f logs/wrapper.log

连接MyCat

通过如下指令，就可以连接并登陆MyCat。

mysql -h 192.168.200.210 -P 8066 -uroot -p123456

在mycat中创建表，真实库也会同步创建

mycat配置文件示例：普通配置

schema.xml普通配置：




	
	
	
		
		
	
	
	
	
	
		select user()
		
		
	
	
	
		select user()
		
		
	
	
	
		select user()
		
		
	

 
server.xml 




	
	0 
	1
	1  
	0  
		300  
		2
		
		
		(?:(s*nexts+values+fors*MYCATSEQ_(w+))(,|)|s)*)+
	false 
       
         
	
	
        
		0
		
		
		
		
		
		
		
		
		0
		
			
		0

		
        64k

		
		1k

		0

		
		384m


		
		false

		
		

		
		
		
		false
		
		true
		
	
	
	
	
	
	
	
	
	
	

	
	
		
		123456
		
		DB01
		
		
		
	
	
	
		123456
		DB01
		
		true
	



 
垂直拆分 
schema.xml 

	
	
	
	

	

	

	

	

	

	

	

	

	

	

	

	




select user()



select user()



select user()


 
server.xml 

123456
SHOPPING




123456
SHOPPING
true

 
水平拆分 
schema.xml 






 
tb_log表最终落在3个节点中，分别是 dn4、dn5、dn6 ，而具体的数据分别存储在 dhost1、dhost2、dhost3的itcast数据库中。 
MyCat管理 
Mycat默认开通2个端口，可以在server.xml中进行修改。 
8066 数据访问端口，即进行 DML 和 DDL 操作。
9066 数据库管理端口，即 mycat 服务管理控制功能，用于管理mycat的整个集群状态
 连接MyCat的管理控制台： 
mysql -h 192.168.200.210 -p 9066 -uroot -p123456
 


命令 含义
show @@help 查看Mycat管理工具帮助文档
show @@version 查看Mycat的版本
reload @@config 重新加载Mycat的配置文件
show @@datasource 查看Mycat的数据源信息
show @@datanode 查看MyCat现有的分片节点信息
show @@threadpool 查看Mycat的线程池信息
show @@sql 查看执行的SQL
show @@sql.sum 查看执行的SQL统计
双主双从 
一个主机 Master1 用于处理所有写请求，它的从机 Slave1 和另一台主机 Master2 还有它的从机 Slave2 负责所有读请求。当 Master1 主机宕机后，Master2 主机负责写请求，Master1 、Master2 互为备机。架构图如下:
  
主库配置 
修改配置文件 /etc/my.cnf 
#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，默认为1
server-id=1
#指定同步的数据库
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
 
重启MySQL服务器
 登录mysql，创建远程连接的账号，并授予主从复制权限 
#创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
# 解释：创建名为itcast的用户，@'%'可以在任意主机上访问，密码'Root@123456'
CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
#为 'itcast'@'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';
 
通过指令，查看二进制日志坐标 
show master status ;
 
2个主库都执行相同操作，server-id需不同 
从库配置 
修改配置文件 /etc/my.cnf 
#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，和主库不一样即可
server-id=2
 
重新启动MySQL服务，执行与单个主从复制相同的操作。 
另一个从库也相同，server-id需不同。 
主库配置 
2台主库相互复制对方，操作与从库一致。 
mycat配置 

 属性说明： 
balance="1"
 代表全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡 ; 
writeType
 0 : 写操作都转发到第1台writeHost, writeHost1挂了, 会切换到writeHost2上;
 1 : 所有的写操作都随机地发送到配置的writeHost上 ; 
switchType
 -1 : 不自动切换
 1 : 自动切换 
双主双从+读写分离完整配置（没有分库分表）：
 schema.xml： 



	
	
	
	
	
	
	
	
		select user()
		
			
		
		
			
		
	

 
记得server.xml 中配库的权限

命令	含义
show @@help	查看Mycat管理工具帮助文档
show @@version	查看Mycat的版本
reload @@config	重新加载Mycat的配置文件
show @@datasource	查看Mycat的数据源信息
show @@datanode	查看MyCat现有的分片节点信息
show @@threadpool	查看Mycat的线程池信息
show @@sql	查看执行的SQL
show @@sql.sum	查看执行的SQL统计

mysql集群，主从同步，读写分离，水平拆分，垂直拆分，双主双从+mycat

Linux相关栏目本月热门文章