构建读写分离的数据库集群

王先森 WXQ

于 2024-01-02 17:53:12 发布

阅读量490

点赞数 15

分类专栏： Linux 文章标签：数据库 linux 运维

本文链接：https://blog.csdn.net/2302_77222295/article/details/135346353

版权

Linux 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

mycat部署

节点规划

IP	主机名	节点
192.168.15.24	mysql1	mysql数据库集群主节点
192.168.15.25	mysql2	mysql数据库集群从节点
192.168.15.26	mycat	mycat中间件服务节点

基础环境准备(所有节点都执行）

# 关闭防火墙以及selinux
systemctl dissable firewalld --now
sed -i "s/SELINUX=.*/SELINUX=disabled/g" /etc/selinux/config
setenforce 0
# 编辑映射文件
vi /etc/hosts
192.168.15.24 mysql1
192.168.15.25 mysql2
192.168.15.26 mycat
scp /etc/hosts mysql2:/etc/hosts
scp /etc/hosts mycat:/etc/hosts
# 时间同步
yum install -y ntp 
systemctl start ntpd
systemctl enable ntpd
# 其他节点连接
ntpdate mysql1

MySQL 主从部署

mysql主从基础环境配置（主从两节点配置）

# 安装MySQL
yum install -y mariadb mariadb-server
# 启动服务
systemctl start mariadb
# 开机自启
systemctl enable mariadb

mysql1节点

# 初始化数据库
mysql_secure_installation

   Enter current password for root (enter fore none):  #默认回车
   Set root password? [Y/n]                            # y
   New password:                                       # 输入数据库密码‘000000’
   Re-enter new password:                              # 再次输入密码‘000000’
   Remove anonymous users?[Y/n]                        # y
   Disallow root login remotely?[Y/n]                  # n
   Remove test database and access to it?[Y/n]         # y
   Reload  privilege tables now?[Y/n]                  # y

# 编辑配置文件
vim /etc/my.cnf

# 启动二进制日志
log_bin=mysql-bin
binlog_ignore_db=mysql
server-id=24
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# 重启mariadb服务
systemctl restart mariadb
# 查看状态t
systemctl status mariadb
# 登录mysql
mysql -uroot -p000000
# 授权
MariaDB[(none)]> grant all privileges on *.* to root@'%' identified by "000000";
MariaDB[(none)]> grant replication slave on *.* to 'user'@'mysql2' identified by '000000';

mysql2节点

# 初始化数据库
mysql_secure_installation

   Enter current password for root (enter fore none):  #默认回车
   Set root password? [Y/n]                            # y
   New password:                                       # 输入数据库密码‘000000’
   Re-enter new password:                              # 再次输入密码‘000000’
   Remove anonymous users?[Y/n]                        # y
   Disallow root login remotely?[Y/n]                  # n
   Remove test database and access to it?[Y/n]         # y
   Reload  privilege tables now?[Y/n]                  # y
# 编辑配置文件 
vim /etc/my.cnf

# 启动二进制日志
log_bin=mysql-bin
binlog_ignore_db=mysql
server-id=25
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# 重启mariadb服务
systemctl restart mariadb
# 查看状态t
systemctl status mariadb
# 登录mysql
mysql -uroot -p000000
MariaDB[(none)]> change master to master_host='mysql1',master_user='user',master_password='000000';
# 开启服务器
start slave;
# 查看连接信息，若如下图所示都为Yes则配置成功
show slave status \G;

在这里插入图片描述

测试

# 在主服务器创建num1库,插入数据，查看数据
mysql -uroot -p000000
MariaDB[(none)]> create database num1;
MariaDB[(none)]> use num1;
 MariaDB[num1]> create table company(id int not null primary key,name varchar(50),addr varchar(255));
 MariaDB[num1]> insert into company values(1,"alibaba","china");
 MariaDB[num1]> select * from company;
# 从节点上查看数据库信息
show databases;
# 在从节点上，查看数据库列表。找到 test 数据库，查询表，并查询内容验证从数据库的复制功能
show databases;
use num1;
show tables;
select * from company;

在这里插入图片描述

安装JDK环境（mycat节点）

# 创建文件目录
mkdir /usr/local/java
tar -zxvf jdk-8u91-linux-x64.tar.gz -C /usr/local/java
# 编辑环境变量
vi /etc/profile
export JAVA_HOME=/usr/local/java/jdk1.8.0_91
export CLASSPATH=.:$JAVA_HOME/jre/lib/rt.jar:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
export PATH=$PATH:$JAVA_HOME/bin
# 生效配置
source /etc/profile
# 查看jdk版本
java -version
java version "1.8.0_91"
Java(TM) SE Runtime Environment (build 1.8.0_91-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.91-b14, mixed mode)

部署Mycat读写分离中间件服务

安装Mycat服务

# 下载地址
https://github.com/MyCATApache/Mycat-download/tree/master/1.6-RELEASE
# 将二进制软件包解压到/usr/local/ 目录下
tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local
# 赋予目录权限
chown -R 777 /usr/local/mycat/
# 添加环境变量
echo export MYCAT_HOME=/usr/local/mycat/ >> /etc/profile
# 生效配置
source /etc/profile

编辑 Mycat 的逻辑库配置文件

schema.xml 原件

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">

	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
		<!-- auto sharding by id (long) -->
		<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />

		<!-- global table is auto cloned to all defined data nodes ,so can join 
			with any table whose sharding node is in the same data node -->
		<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
		<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" />

		<!-- random sharding using mod sharind rule -->
		<table name="hotnews" primaryKey="ID" dataNode="dn1,dn2,dn3"
			rule="mod-long" />
			<!-- 
		<table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3" rule="mod-long" />
 -->
		<table name="employee" primaryKey="ID" dataNode="dn1,dn2"
			rule="sharding-by-intfile" />
		<table name="customer" primaryKey="ID" dataNode="dn1,dn2"
			rule="sharding-by-intfile">
			<childTable name="orders" primaryKey="ID" joinKey="customer_id"
				parentKey="id">
				<childTable name="order_items" joinKey="order_id"
					parentKey="id" />
			</childTable>
			<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
				parentKey="id" />
		</table>
	</schema>

	<dataNode name="dn1" dataHost="localhost1" database="db1" />
	<dataNode name="dn2" dataHost="localhost1" database="db2" />
	<dataNode name="dn3" dataHost="localhost1" database="db3" />
	<!-- 
	<dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
	<dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" />
	<dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" />
 -->
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
		writeType="0" dbType="mysql" dbDriver="native">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="localhost:3306" user="root"
			password="123456">
			<!-- can have multi read hosts -->
			<!-- <readHost host="hostS1" url="localhost:3306" user="root" password="123456" 
				/> -->
		</writeHost>
		<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
	<!-- 
		<dataHost name="jdbchost" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost>
	</dataHost>	
	-->
     <!-- 
	<dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0"
		dbType="mysql" dbDriver="jdbc">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM1" url="jdbc:mysql://localhost:3306"
			user="root" password="123456">
		</writeHost>
	</dataHost>
	 -->
</mycat:schema>

# 参数说明
sqlMaxLimit:配置默认查询数量
database:为真实数据库名
balance="0":不开启读写分离机制，所有读操作都发送到当前可用的writeHost 上
balance="1":全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单来说，当双主双从模式(M1->S1，M2->S2，并且M1 与M2 互为主备),正常情况下，M2、S1、S2 都参与 select 语的负载均衡
balance="2":所有读操作都随机的在 writeHost、readhost 上分发
balance="3”:所有读请求随机地分发到 wiriterHost 对应的readhost 执行writerHost 不负担读压力,注意 balance=3 只在 1.4 及其以后版本有,13 版本没有
writeType="0":所有写操作发送到配置的第一个 writeHost，第一个挂了需要切换到还生存的第二个 writeHost，重新启动后已切换后的为准，切换记录在配置文件 dnindex.properties中
writeType="1": 所有写操作都随机的发送到配置的 writeHost

修改schema.xml配置文件

# 修改配置文件
vi /usr/local/mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--  配置2个逻辑库 -->
<schema name="jsjgcx" checkSQLschema="true" sqlMaxLimit="100" dataNode="jsjgcx"/>
<schema name="rgznx" checkSQLschema="true" sqlMaxLimit="100" dataNode="rgznx"/>
<!--  逻辑库对应的真实数据库 -->
<dataNode name="jsjgcx" dataHost="localhost" database="jsjgcx"/>
<dataNode name="rgznx" dataHost="localhost" database="rgznx"/>
<!-- 真实数据库所在的服务器地址，这里配置了1主1从 -->
<dataHost name="localhost" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.15.24:3306" user="root" password="000000">
<readHost host="hostS1" url="192.168.15.25" user="root" password="000000"/>
</writeHost>
</dataHost>
</mycat:schema>

# 修改 schema.xml 的用户权限
chown root:root /usr/local/mycat/conf/schema.xml

在这里插入图片描述

server.xml 原件

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
	<system>
	<property name="defaultSqlParser">druidparser</property>
	<!-- <property name="processorBufferChunk">40960</property> -->
	<!-- 
	<property name="processors">1</property> 
	<property name="processorExecutor">32</property> 
	 -->
		<!--默认是65535 64K 用于sql解析时最大文本长度 -->
		<!--<property name="maxStringLiteralLength">65535</property>-->
		<!--<property name="sequnceHandlerType">0</property>-->
		<!--<property name="backSocketNoDelay">1</property>-->
		<!--<property name="frontSocketNoDelay">1</property>-->
		<!--<property name="processorExecutor">16</property>-->
		<!-- 
			<property name="mutiNodeLimitType">1</property> 0：开启小数量级（默认） ；1：开启亿级数据排序
	    	<property name="mutiNodePatchSize">100</property> 亿级数量排序批量
			<property name="processors">32</property> <property name="processorExecutor">32</property> 
			<property name="serverPort">8066</property> <property name="managerPort">9066</property> 
			<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> 
			<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
	</system>
	<user name="test">
		<property name="password">test</property>
		<property name="schemas">TESTDB</property>
	</user>

	<user name="user">
		<property name="password">user</property>
		<property name="schemas">TESTDB</property>
		<property name="readOnly">true</property>
	</user>
	<!-- <cluster> <node name="cobar1"> <property name="host">127.0.0.1</property> 
		<property name="weight">1</property> </node> </cluster> -->
	<!-- <quarantine> <host name="1.2.3.4"> <property name="user">test</property> 
		</host> </quarantine> -->

</mycat:server>

修改 server.xml 配置文件

# 修改配置文件
vi /usr/local/mycat/conf/server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="useSqlStat">0</property>
<!--  1为开启实时统计、0为关闭  -->
<property name="useGlobleTableCheck">0</property>
<!--  1为开启全加班一致性检测、0为关闭  -->
<property name="sequnceHandlerType">2</property>
<property name="processorBufferPoolType">0</property>
<property name="handleDistributedTransactions">0</property>
<property name="useOffHeapForMerge">1</property>
<property name="memoryPageSize">1m</property>
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<property name="systemReserveMemorySize">384m</property>
</system>
<user name="root">
<property name="password">000000</property>
<property name="schemas">jsjgcx,rgznx</property>
</user>
<user name="jsjgcx">
<property name="password">000000</property>
<property name="schemas"jsjgcx</property>
<property name="readOnly">true</property>
</user>
<user name="rgznx">
<property name="password">000000</property>
<property name="schemas">jsjgcx</property>
<property name="readOnly">true</property>
</user>
</mycat:server>

在这里插入图片描述

启动mycat服务

# 启动mycat服务
/bin/bash /usr/local/mycat/bin/mycat start
Starting Mycat-server...
# 查看启动信息
/usr/local/mycat/bin/mycat status
Mycat-server is running (1572).
# 查看端口信息
netstat -ntpl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 127.0.0.1:32000         0.0.0.0:*               LISTEN      1574/java
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1088/sshd
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1213/master
tcp6       0      0 :::35231                :::*                    LISTEN      1574/java
tcp6       0      0 :::1984                 :::*                    LISTEN      1574/java
tcp6       0      0 :::8066                 :::*                    LISTEN      1574/java
tcp6       0      0 :::9066                 :::*                    LISTEN      1574/java
tcp6       0      0 :::39691                :::*                    LISTEN      1574/java
tcp6       0      0 :::22                   :::*                    LISTEN      1088/sshd
tcp6       0      0 ::1:25                  :::*                    LISTEN      1213/master

查询数据库信息

# 验证数据库集群服务读写分离功能
# 安装mariadb-client服务
wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
yum localinstall mysql57-community-release-el7-8.noarch.rpm -y
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
yum install -y mysql-community-client.x86_64 
# 在 Mycat节点上使用mysql命令查看mycat服务的逻辑库
mysql -h127.0.0.1 -P8066 -uroot -p000000

在这里插入图片描述

测试mycat读写分离

# 登录到mycat
mysql -h127.0.0.1 -P8066 -uroot -p000000
use jsjgcx;

在这里插入图片描述

# 创建一个表
CREATE TABLE `student` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
 `contact` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '联系⼈姓名',
 `addressDesc` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '收货地址明细',
 `postCode` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '邮编',
 `tel` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '联系⼈电话',
 `createdBy` bigint(20) DEFAULT NULL COMMENT '创建者',
 `creationDate` datetime DEFAULT NULL COMMENT '创建时间',
 `modifyBy` bigint(20) DEFAULT NULL COMMENT '修改者',
 `modifyDate` datetime DEFAULT NULL COMMENT '修改时间',
 `userId` bigint(20) DEFAULT NULL COMMENT '⽤户ID',
 PRIMARY KEY (`id`)
  )
 ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 # 在表中插⼊数据
  insert into `student`(`id`,`contact`,`addressDesc`,`postCode`,`tel`,`createdBy`,`creationDate`,
 `modifyBy`,`modifyDate`,`userId`) values
 (1,'⼩丽','北京市','100010','13689999',1,'2016-04-13 00:00:00',NULL,NULL,1),
 (2,'⼩丽','北京市','100000','185672312',1,'2016-04-13 00:00:00',NULL,NULL,1),
 (3,'⼩强','北京市','100021','133876742',1,'2016-04-13 00:00:00',NULL,NULL,1),
 (4,'⼩颖','北京市','100053','13560223',1,'2016-04-13 00:00:00',NULL,NULL,2),
 (5,'⼩慧','北京市','100032','18056666',1,'2016-04-13 00:00:00',NULL,NULL,3),
 (6,'⼩强','北京市','100061','13787222',1,'2016-04-13 00:00:00',NULL,NULL,3);
# 刷新
flush privileges;
# 查询表信息
select * from student;

在这里插入图片描述

# 执⾏如下查看读写是否⾃动分离
mysql -h127.0.0.1 -P 9066 -uroot -p -e 'show @@datasource'

在这里插入图片描述

王先森 WXQ

关注

15
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
构建读写分离的数据库集群

【代码】构建读写分离的数据库集群。
复制链接

扫一扫

专栏目录

构建读写分离的数据库集群

mycat部署

节点规划

基础环境准备(所有节点都执行）

MySQL 主从部署

mysql主从基础环境配置（主从两节点配置）

mysql1节点

mysql2节点

测试

安装JDK环境（mycat节点）

部署Mycat读写分离中间件服务

安装Mycat服务

编辑 Mycat 的逻辑库配置文件

schema.xml 原件

修改schema.xml配置文件

server.xml 原件

修改 server.xml 配置文件

启动mycat服务

查询数据库信息

测试mycat读写分离

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