Mycat 安装配置

微风至夏

已于 2022-12-05 20:26:58 修改

阅读量1k

点赞数

文章标签： docker linux 数据库 mycat mysql

于 2022-12-05 20:24:49 首次发布

本文链接：https://blog.csdn.net/qq_38974073/article/details/128192655

版权

sql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

参考：https://www.cnblogs.com/binghe001/p/13959992.html

介绍

Mycat支持水平分片、垂直分片。

水平分片：将一个表数据分隔到多个节点上，以行数据为基本单元。
垂直分片：一个数据的多个表分别分布到不同节点。如数据库A的a,b,c表分别在节点A、B、C上。

核心概念

Schema：称逻辑库，代表数据库实例的抽象。
Table：表，代表了某张表。这张表跟一个或多个DataNode依赖。
DataNode：代表了一个分片节点，它是存放表的物理节点。通过DataHost来配置具体的地址信息。
- DataHost：定义具体的物理库的访问地址、账号密码，同时指定读、写库。

在schema.xml文件我们可以清楚的认识这些概念。

下载

根据服务器情况下载对应的版本。
https://github.com/MyCATApache/Mycat-download/tree/master/1.6-RELEASE

# 解压
tar -xvf xxx

# 解压结果
root@ubuntu:/home/liangshijie/mycat# ls
bin  catlet  conf  lib  logs  version.txt

# 配置环境变量
vim /etc/profile
# 追加：
export PATH=/home/liangshijie/mycat/bin:${PATH}
# 刷新环境变量
source /etc/profile

配置

基本情况

环境：ubuntu18、使用docker启动；
数据库：study；
数据表：user；
mysql5.7主从集群：

节点	地址
主	192.168.204.139
从	192.168.204.138
从	192.168.204.140

配置文件

# 进入mycat 的conf目录
cd /home/liangshijie/mycat/conf

# 修改
vim server.xml

# 将schemas改为study。
# 在这里可以mycat客户端登录密码。
# root账户默认读写
# user账户是只读

<user name="root">
        <property name="password">123456</property>
        <property name="schemas">study</property>
</user>

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

# 修改物理节点相关配置
vim schema.xml
# 先修改schema的name为我们的数据库study
# 修改table标签，表为user，使用dn1节点
# 根据集群修改dataHost

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

	<schema name="study" checkSQLschema="true" sqlMaxLimit="100">
		<!-- auto sharding by id (long) -->
		<table name="user" dataNode="dn1"  />	

		<!-- 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 -->
		<!-- random sharding using mod sharind rule -->
		<!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global"
			needAddLimit="false"/> <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3"
			rule="mod-long" /> -->
		<!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
			/> -->
	</schema>
	<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
		/> -->
	<dataNode name="dn1" dataHost="node1" database="study" />
	<!--	<dataNode name="dn2" dataHost="localhost1" database="study" />
	<dataNode name="dn3" dataHost="localhost1" database="study" /> 
--> 
	<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
	 <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="node1" maxCon="1000" minCon="10" balance="1"
			  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.204.139:3306" user="root"  password="123456">
			<!-- can have multi read hosts -->
			<readHost host="hostS2" url="192.168.204.137:3306" user="root" password="123456" />

			<readHost host="hostS3" url="192.168.204.140:3306" user="root" password="123456" />
		</writeHost>
		<!--		<writeHost host="hostS1" url="localhost:3316" user="root"
				   password="123456" /> -->
		<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
	<!--
		<dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc">
		<heartbeat> 		</heartbeat>
		 <writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng" 	password="jifeng"></writeHost>
		 </dataHost>

	  <dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0" 	dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat>
		<connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
		<writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base" 	password="123456" > </writeHost> </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="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc">
		<heartbeat> </heartbeat>
		 <writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng" 	password="jifeng"></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>

启动命令

mycat start | stop | restart | status

链接客户端

服务器开放端口8066。
使用可视化工具链接。账户密码：root\123456。

测试

插入或查询数据。
查看当前server_id： SELECT @@server_id;

整合Springboot

跟Springboot整合只需修改mysql的数据源配置。


spring:
  datasource:
    username: root
    password: 123456
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://192.168.204.142:8066/study?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true
server:
  port: 8062

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>cn.lsj</groupId>
    <artifactId>read-write-separation</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>read-write-separation</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>
        <!--jdbc-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <!--Druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.22</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

@SpringBootApplication
@MapperScan("cn.lsj.readwriteseparation.mapper")
public class ReadWriteSeparationApplication {

    public static void main(String[] args) {
        SpringApplication.run(ReadWriteSeparationApplication.class, args);
    }

}

@RequestMapping("test")
@AllArgsConstructor
@RestController
public class TestController {

    private final UserService userService;

    @GetMapping("read")
    public User read() {
        return userService.getById(1);
    }

    @PostMapping("write")
    public String write(@RequestBody User user) {
        userService.saveOrUpdate(user);
        return "成功";
    }
}

@Data
public class User {
    private int id;
    private String username;
}


public interface UserMapper extends BaseMapper<User> {
}


public interface UserService extends IService<User> {
}


@Service
public class UserServiceImpl  extends ServiceImpl<UserMapper, User> implements UserService {
}