Linux 离线安装 mycat 配置主从复制与读写分离

RunwayLoli

已于 2023-07-27 01:28:56 修改

阅读量273

点赞数

分类专栏：安装教程文章标签： linux mysql

于 2023-07-27 01:27:09 首次发布

本文链接：https://blog.csdn.net/weixin_42725397/article/details/131862794

版权

安装教程专栏收录该内容

8 篇文章 0 订阅

订阅专栏

mycat

1. Mycat 安装
2. 主从复制与读写分离
3. 分库分表

1. Mycat 安装

1.1 安装

cd /opt/software
tar -zxvf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz
cp -r mycat/ /usr/local
rm -rf mycat
cd /usr/local/mycat/conf

cd /usr/local/mycat/conf
vim server.xml
vim schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- 虚拟库与真实库的映射 
	name="TESTDB" 虚拟库的名字，对应刚刚在server.xml中设置的TESTDB
	sqlMaxLimit="100"，允许最大查询记录数
	checkSQLschema="false" 是否检查自动删除 “虚拟库名”
	dataNode="dn1" 虚拟库对应的真实database，值为dataNode标签的name
	-->
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        <!-- 可以访问的表，只有设置在这里的表才会被MyCat管理访问 
		dataNode:虚拟库对应的真实database，对应<dataNode>标签。如果做分片，则配置多个，用逗号分隔；或者使用db$0-99，代表db0到db99的database
		rule：分片规则，如果没有则删除
		-->
		<!-- <table name="tb_item" dataNode="dn1"/> -->
	</schema>
	
	<!-- 真实的database信息，每一个dataNode就是一个数据库分片
		name：虚拟名称
		dataHost：真实库的主机信息，对应<dataHost>标签
		database：真实MySQL中真实的物理数据库名称
	-->
	<dataNode name="dn1" dataHost="host1" database="testdb" />
	
	<!-- 真实库的主机信息
		name：主机名,name属性值要与dataNode节点中的dataHost属性值对应
		maxCon：最大连接， minCon：最小连接
		balance：负载均衡方式：0~3四种选项。0，不开启读写分离。1~3都开启，区别是主是否参与读
		writeType：写负载均衡。永远设置0
		dbDriver：驱动类型，推荐native，可选jdbc
		switchType：主从的自动切换
	-->
	<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

		<heartbeat>select user()</heartbeat>
		<!-- 这里设置写主机信息 -->
		<writeHost host="hostM1" url="192.168.10.111:3306" user="root" password="Mycat_110">

			<!-- 这里设置读主机信息 -->
			<readHost host="hostS1" url="192.168.10.112:3306" user="root" password="Mycat_110" />
		</writeHost>
	
	</dataHost>

</mycat:schema>

mysql -uroot -pMycat_110 -h 192.168.10.111 -P 3306
mysql -uroot -pMycat_110 -h 192.168.10.112 -P 3306

1.2 启动

cd /usr/local/mycat/bin
./mycat console
./mycat start
./mycat stop
./mycat status

登录mycat

# 登录后台管理窗口
mysql -umycat -p123456 -P 9066 -h 192.168.10.110
# 登录数据窗口
mysql -umycat -p123456 -P 8066 -h 192.168.10.110

show databases;
use TESTDB;
show tables;

1.3 错误

ERROR 1184 (HY000): Invalid DataSource:0

执行 show tables; 命令特别卡顿，等待好久出现 ERROR 1184 (HY000): Invalid DataSource:0
在这里插入图片描述
解决方案：
进入 logs 查看 mycat.log 里面有这行信息： io.mycat.sqlengine.SQLJob.connectionError(SQLJob.java:125)) - can’t get connection for sql :select user()
从上面的提示信息中，可以发现客户端不支持服务端的认证协议，提示（- can’t connect to mysql server ,errmsg:Client does not support authentication protocol requested by server;），通过进一步的查看和对比，发现是8.0.11版本将默认认证机制变更为“caching_sha2_password”引起。
通过进一步百度和查看mysql8.0.11的官方文档，发现可以通过变更数据库用户的默认登陆认证机制来规避。
于是将“root@%”这个用户的默认认证机制从” caching_sha2_password“变更为”mysql_native_password“。
参考如下：

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Mycat_110';
FLUSH PRIVILEGES;
ALTER USER 'root'@'%' IDENTIFIED BY 'Mycat_110'; 
FLUSH PRIVILEGES;

2. 主从复制与读写分离

2.1 主从复制：一主一从

在这里插入图片描述

mycat 读写分离依赖于 mysql的主从复制

2.1.1 设置主数据库 192.168.10.111

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

server-id=1
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-do-db=testdb
binlog_format=STATEMENT

#主服务器唯一ID
server-id=1

#启用二进制日志
log-bin=mysql-bin

#设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema

#设置需要复制的数据库。注意：MySQL是从接入点开始复制操作的
# 需要复制的主数据库名字
binlog-do-db=testdb

#设置logbin格式
binlog_format=STATEMENT

重启mysql

systemctl restart mysqld

登录mysql，在Master主机上建立帐户并授权slave，执行以下sql语句
mysql8.0以下

#在主机MySQL里执行授权主从复制的命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';

mysql8.0以上

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

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

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

flush privileges;

查询master的状态

#查看当前最新的一个binlog日志的编号名称，及最后一个事件结束的位置
show master status;

在这里插入图片描述

记录下File和Position的值

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

2.1.2 从数据库配置 192.168.10.112

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

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

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

重启mysql

systemctl restart mysqld

在从机上配置需要复制的主机
看主数据库的show master status; 命令展示的值，填入到MASTER_LOG_FILE与MASTER_LOG_POS

CHANGE MASTER TO MASTER_HOST='192.168.10.111',
MASTER_USER='slave',
MASTER_PASSWORD='HelloWorld_123',
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1157;

启动从服务器复制功能, 执行sql:

start slave;
show master status;

查看从服务器状态，输入sql

show slave status\G;

出现这个就说明成功了
在这里插入图片描述
测试主从复制
在主数据库中进行建表或者增删改查操作，在从数据库中观察数据有没有发生变化

2.1.3 Mycat 登录访问

在Mycat 中进行增删改查操作，查看主数据库与从数据库有没有变化

2.1.4 错误

This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL ‘’ first.
在这里插入图片描述
若果以前进行主从复制的操作，数据库中会有记录，会报这个错误，
解决方案：

# 停止主从复制
stop slave; 
# 删除之前的relay_log信息。然后重新执行 change master to ...语句即可。
reset master;

error connecting to master ‘slave@192.168.10.111:3306’ - retry-time: 60 retries: 1 message: Access denied for user ‘slave’@‘192.168.10.112’ (using password: YES)

显示这个就是复制服务开启失败
在这里插入图片描述
可能得错误原因有：

1. 网络不通
2. 账户密码错误
3. 防火墙
4. mysql配置文件问题
5. 连接服务器时语法
6. 主服务器mysql权限

我这里是在从数据库执行这个语句的时候，密码写错了，改成正确的密码就可以了

CHANGE MASTER TO MASTER_HOST='192.168.10.111',
MASTER_USER='slave',
MASTER_PASSWORD='HelloWorld_123',
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1157;

2.2 读写分离：一主一从

之前的在mycat中配置了读写主机，并为mysql配置了主从复制，现在并不能实现读写分离，需要再添加配置

cd /usr/local/mycat/conf
vim schema.xml

将 balance 值改为 3

负载均衡类型，目前的取值有4 种：
（1）balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。

（2）balance="1"，全部的readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 2M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。

（3）balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。

（4）balance="3"，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力。对应单主单从。

<dataHost name="host1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

重新启动

cd /usr/local/mycat/bin
./mycat stop
./mycat start

2.3 搭建主从复制：双主双从

在这里插入图片描述

编号	角色	IP地址	机器名
1	Master1	192.168.140.111	mycat111
2	Slave1	192.168.140.112	mycat112
3	Master2	192.168.140.113	mycat113
4	Slave2	192.168.140.114	mycat114

2.3.1 准备

另外准备两台虚拟机，安装mysql8.0
记得删除演示一主一从模式时创建的数据库`testdb`。
记得在之前的从机上执行：`stop slave` 和 `reset master `。
停止110上面的mycat

stop slave;
reset master;

2.3.2 主机设置

① 双主机配置

mycat111 Master1配置：

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

server-id=1
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-do-db=testdb
binlog_format=STATEMENT

log-slave-updates 
auto-increment-increment=2 
auto-increment-offset=1

#【必须】主服务器唯一ID
server-id=1

#【必须】启用二进制日志
log-bin=mysql-bin

#设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema

#【必须】设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字

#设置logbin格式
binlog_format=STATEMENT

#【必须】在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates 

#【必须】表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2 

#【必须】表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=1

mycat113 Master2配置：

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

server-id=3
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-do-db=testdb
binlog_format=STATEMENT


log-slave-updates 
auto-increment-increment=2 
auto-increment-offset=2

#【必须】主服务器唯一ID
server-id=3

#【必须】启用二进制日志
log-bin=mysql-bin

#设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema

#【必须】设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字

#设置logbin格式
binlog_format=STATEMENT

#【必须】在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates 

#【必须】表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
auto-increment-increment=2 

#【必须】表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
auto-increment-offset=2

2.3.3 从机设置

② 双从机配置

Slave1配置：

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

#【必须】从服务器唯一ID
server-id=2
#【必须】启用中继日志
relay-log=mysql-relay

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

#【必须】从服务器唯一ID
server-id=4
#【必须】启用中继日志
relay-log=mysql-relay

2.3.4 重启mysql服务, 关闭防火墙

systemctl restart mysqld.service
systemctl stop firewalld.service

2.3.5 在两台主机上分别建立帐户并授权slave

搭建一主一从时候， mycat111已经创建过，无需再创建，只需要再 mycat113上创建一下

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

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

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

flush privileges;

2.3.6 查询两个主机的服务状态

分别记录下File和Position的值。

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

	show master status;

2.3.7 在从机上配置需要复制的主机

Slave1复制Master1，Slave2复制Master2。
mycat112 复制 mycat111, mycat114 复制 mycat113

mycat112

CHANGE MASTER TO MASTER_HOST='192.168.10.111',
MASTER_USER='slave',
MASTER_PASSWORD='HelloWorld_123',
MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=156;

mycat114

CHANGE MASTER TO MASTER_HOST='192.168.10.113',
MASTER_USER='slave',
MASTER_PASSWORD='HelloWorld_123',
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1152;

2.3.8 启动两台从服务器复制功能并查看状态

mycat112与mycat114

start slave;
# 这个命令在navcat里面无法执行，需要上虚拟机执行
show slave status\G;

mycat112与mycat114的这两个都是yes说明主从配置成功！
在这里插入图片描述

2.3.8 两个主服务器互相复制

Master2复制Master1，Master1复制Master2

mycat113

CHANGE MASTER TO MASTER_HOST='192.168.10.111',
MASTER_USER='slave',
MASTER_PASSWORD='HelloWorld_123',
MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=156;

mycat111

CHANGE MASTER TO MASTER_HOST='192.168.10.113',
MASTER_USER='slave',
MASTER_PASSWORD='HelloWorld_123',
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1152;

启动复制功能，查看状态
mycat111与mycat113

mysql -hlocalhost -P3306 -uroot -pMycat_110
start slave;
# 这个命令在navcat里面无法执行，需要上虚拟机执行
show slave status\G;

2.4 搭建读写分离：双主双从

编辑 schema.xml

cd /usr/local/mycat/conf
vim 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>
	
	<dataNode name="dn1" dataHost="host1" database="testdb" />
	
	<dataHost name="host1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<writeHost host="hostM1" url="192.168.10.111:3306" user="root" password="Mycat_110">
			<readHost host="hostS1" url="192.168.10.112:3306" user="root" password="Mycat_110" />
		</writeHost>
		
		<writeHost host="hostM2" url="192.168.10.113:3306" user="root" password="Mycat_110">
			<readHost host="hostS2" url="192.168.10.114:3306" user="root" password="Mycat_110" />
		</writeHost>
	</dataHost>
	
</mycat:schema>

重启 Mycat

cd /usr/local/mycat/bin
./mycat stop
./mycat start
./mycat status
./mycat console

3. 分库分表

3.1 垂直分库

分库的原则：

能不切分尽量不要切分。数据量不是很大的库或者表，尽量不要分片。
尽量按照功能模块分库，避免跨库join。
按照功能模块将表分到不同的库中

3.3.1 修改配置文件： schema.xml

为了演示分库效果，这里使用两个从库 113与114，不能使用有互相复制服务的库，不然就达不到分库的效果了
vim /usr/local/mycat/conf/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">
		<!--指定customer使用dn2这个节点的数据库，也就是114的库-->
		<table name="customer" dataNode="dn2" ></table>
	</schema>

	<dataNode name="dn1" dataHost="host1" database="orders" />
	<dataNode name="dn2" dataHost="host2" database="orders" />

	<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

		<heartbeat>select user()</heartbeat>
    
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="192.168.10.113:3306" user="root" password="Mycat_110">
		</writeHost>
	</dataHost>

	<dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

		<heartbeat>select user()</heartbeat>

		<!-- can have multi write hosts -->
		<writeHost host="hostM2" url="192.168.10.114:3306" user="root" password="Mycat_110">
		</writeHost>
	</dataHost>

</mycat:schema>

重启mycat

cd /usr/local/mycat/bin
./mycat stop
./mycat start

3.3.2 数据验证

在113与114这两个机器上分别执行命令创建数据库orders

CREATE DATABASE orders;

登录mycat

mysql -umycat -p123456 -P 8066 -h 192.168.10.110
show databases;
use TESTDB;
show tables;

在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)
);

3.2 水平分表

3.2.1 修改配置文件 schema.xml

/usr/local/mycat/conf/schema.xml

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

在这里插入图片描述

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<schema name="orders" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
		<!--指定customer使用dn2这个节点的数据库，也就是114的库-->
		<table name="customer" dataNode="dn2" ></table>
		<table name="orders" dataNode="dn1,dn2"  rule="mod_rule" ></table>
	</schema>

	<dataNode name="dn1" dataHost="host1" database="orders" />
	<dataNode name="dn2" dataHost="host2" database="orders" />

	<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

		<heartbeat>select user()</heartbeat>
    
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="192.168.10.113:3306" user="root" password="Mycat_110">
		</writeHost>
	</dataHost>

	<dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

		<heartbeat>select user()</heartbeat>

		<!-- can have multi write hosts -->
		<writeHost host="hostM2" url="192.168.10.114:3306" user="root" password="Mycat_110">
		</writeHost>
	</dataHost>

</mycat:schema>

3.2.2 修改配置文件rule.xml

vim /usr/local/mycat/conf/rule.xml

<!--添加一个规则：mod_rule -->
<tableRule name="mod_rule">
	<rule>
		<columns>customer_id</columns>
		<algorithm>mod-long</algorithm>
	</rule>
</tableRule>
…
<!--找到函数配置mod-long ， 将count的值从3改成2-->
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
	<!--有几个数据节点-->
	<property name="count">2</property>
</function>

重启mycat

cd /usr/local/mycat/bin
./mycat stop
./mycat start

3.2.3 配置数据

113上有 orders 表， 114上面没有，再114上创建 orders表

#订单表   rows:600万
CREATE TABLE orders(
    id INT AUTO_INCREMENT,
    order_type INT,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id)  
);

再mycat中执行sql

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);